How to call a Stored Procedure?

Question

Dear Friend I typed this code and run the application.......... But there is an error... Please help me to verify this problem......... private void btnSave_Click(object sender EventArgs e) { string connectionstring = Data Source=LOCALSERVER;Initial Catalog=ACME_ERP;Integrated Security=True ; SqlConnection s = new SqlConnection(connectionstring); SqlCommand cmd = new SqlCommand(usp_insertcustomer s); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(@cust_code this.cmdCustCode.Text); cmd.Parameters.Add(@cust_name this.txtCustName.Text); //(@cust_code and @cust_name are parameters defined in the sql procedure cmd.ExecuteReader(); } //Error 1 The name 'cust_code' does not exist in the current

Dinusha Gamage · Answer

Thank you very much both of you !!! I also tried the given below coding and it is also working properly.... Thanks again for your support......... I'll try the new coding which provided by you........ :-) private void btnSave_Click(object sender EventArgs e) { string connectionstring = Data Source=LOCALSERVER;Initial Catalog=ACME_ERP;Integrated Security=True ; SqlConnection s = new SqlConnection(connectionstring); s.Open(); SqlCommand cmd = new SqlCommand(usp_insertcustomer s); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter(@cust_code (this.cmdCustCode.Text))); cmd.Parameters.Add(new SqlParameter(@cust_name (this.txtCustName.Text))); cmd.ExecuteReader(); s.Close(); ClearControls(); }

Bechir Bejaoui · Answer

Ok I will tell you how to call a stord procedure Imagine that we Create a stored procedure as follow: CREATE PROCEDURE GetUser @UserId int --Input paramerter AS SELECT * FROM UserTable WHERE UserID = @UserId RETURN GO It is a simple stored procedure to be invoked by C# or VB.Net code This is the code: first include using System.Data.SqlClient; then SqlConnection oConnection; try { //Put the connection string instead of ... oConnection = new SqlConnection(...); SqlCommand oCommand = new SqlCommand(GetUser oConnection); //Precise that the type is stored procedure oCommand.CommandType = System.Data.CommandType.StoredProcedure; //You have to define an sqlparameter SqlParameter myParam = new SqlParameter(@UserId System.Data.SqlDbType.Int); oCommand.Parameters.Add(myParam.Value); } finally { oConnection.Close(); } The most imporant remark It is very dangerous that the sored procedure recieve parameters directly from a text your application can be vicitim of sql injection so I advise you use the sqlparameter object to be more secure

Alan · Answer

The first parameter to the Add() method is a string so you'll have to put the parameter names in quotes: cmd.Parameters.Add(@cust_code this.cmdCustCode.Text); cmd.Parameters.Add(@cust_name this.txtCustName.Text);