Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Forums » .NET » ASP.NET »
Stored Procedures + ASP.NET
Posted Date: 07 Nov 2009 Posted By: Sonia sardana Member Level: Gold Points: 1
Responses:
2
|
SP Working
CREATE Procedure submitrecord
(@ID integer,
@Password varchar(10),
@EMailID varchar(50),
@QueryType varchar(1)
)
as
if(@QueryType = 'I')
Begin
INsert into login(ID1,Password1,EMailID)
values(@ID,@Password,@EMailID)END
Else if(@QueryType = 'U')
Begin Update login Set Password1=@Password,
EMailID=@EMailID Where ID1=@ID
End
Else if(@QueryType = 'D')
begin DELETE From login Where ID1=@ID
End
Else if(@QueryType = 'S')
begin Select * From login Where ID1=@ID
End
GO
public partial class FrmStoredPro : System.Web.UI.Page
{
SqlCommand cmd = new SqlCommand();
protected void btnInsert_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
cmd = new SqlCommand("submitrecord", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = TextBox1.Text;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text;
cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value = TextBox3.Text;
cmd.Parameters.Add("@QueryType", SqlDbType.VarChar).Value = "I";
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
cmd = new SqlCommand("submitrecord", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = TextBox1.Text;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text;
cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value = TextBox3.Text;
cmd.Parameters.Add("@QueryType", SqlDbType.VarChar).Value = "U";
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
protected void btnDelete_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
cmd = new SqlCommand("submitrecord", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = TextBox1.Text;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text;
cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value = TextBox3.Text;
cmd.Parameters.Add("@QueryType", SqlDbType.VarChar).Value = "D";
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
protected void btnSerach_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
cmd = new SqlCommand("submitrecord", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = TextBox1.Text;
cmd.Parameters.Add("@QueryType", SqlDbType.VarChar).Value = "S";
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
FIRSTLY,I want to ask one thing ,as I define four parameters in SProc,is there any way to made it optional as in .Net we have optional?? Cz when i want to perform the Delete I want just the ID and query Type parameters, wats the use of other two parameters,If i do not pass the parameters Password and EmailID,I m getting the error
Procedure 'submitrecord' expects parameter '@Password', which was not supplied.
SECONDLY, I want to search the data using SProc,how to get that data????Means i want to get password & email.???
|
Responses
|
| Author: vipul 08 Nov 2009 | Member Level: Diamond | Rating:  Points: 3 |
hi,
you can create you sp this way
CREATE Procedure submitrecord
(@ID integer=0,
@Password varchar(10)=null,
@EMailID varchar(50)=null,
@QueryType varchar(1)=null
)
as
if(@QueryType = 'I')
Begin
INsert into login(ID1,Password1,EMailID)
values(@ID,@Password,@EMailID)END
Else if(@QueryType = 'U')
Begin Update login Set Password1=@Password,
EMailID=@EMailID Where ID1=@ID
End
Else if(@QueryType = 'D')
begin DELETE From login Where ID1=@ID
End
Else if(@QueryType = 'S')
begin Select * From login Where ID1=@ID
End
GO
if you doing this way you don't have pass all the data if you want only pass one "@id"
Please Rate This Answer If They Helpful
Thanks & Regards
Patel Vipul
| | Author: Sonia sardana 09 Nov 2009 | Member Level: Gold | Rating: Points: 2 |
hi thx vipul very much...
|
| Post Reply |
|
|
|
You must Sign In to post a response.
|
|
|
|