How to execute a stored procedure in C#

[LoanB]

Hi all

I have a program which needs to update a sql server database table.

I have a stored procedure called: "spPastelConfig"

My connection is passed in a property canned "scnn".

Can someone tell me what is wrong with my code? I keep getting this error:

"System.Data ExecuteNonQuery: Connection property has not been initialized"

I know I have to pass the connection some where, but not sure where. Can someone please provide me with the correct code?

Thanks in advance,

Lóan

[Shashi Sadasivan]

You havent providedd your code for us to comment on.

However have you set the command type to Stored procedure?

[LoanB]

Oh yeah - sorry:

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spPastelConfig";
cmd.Parameters.AddWithValue("@ServerName", this.ServerName);
cmd.Parameters.AddWithValue("@dbName", this.DatabaseName);
scnn.OpenConn();

cmd.ExecuteNonQuery();

[Shashi Sadasivan]

you have to pass the connection string to scnn (which tells it where it is connecting to)
At the moment scnn has no clue where to contact to request for the stored procedure

[ilearneditonline]

 
using (SqlConnection cn = new SqlConnection(<your connection string>)
{ 
SqlCommand cmd = new SqlCommand("spPastelConfig", cn);
cmd.Parameters.Add("@ServerName", SqlDbType.VarChar, 50).Value = this.ServerName;
cmd.Parameters.Add("@dbName", SqlDbType.VarChar, 50).Value = this.DatabaseName;
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
cmd.ExecuteNonQuery();
}
 

This is how I do this.

[LoanB]

Excellent.

Thanks for that!

[krille]

Have to say that i prefer doing it with an sql statement, like this:

string strSql  = "spPastelConfig @ServerName='"+this.ServerName+"' @dbName='"+this.DatabaseName+"'";
SqlCommand cmd = new SqlCommand(strSql, cn);
cn.Open();
cmd.ExecuteNonQuery();
 

just my personal oppinion...

[Shashi Sadasivan]

the conneting is string is good to be stored at a central place
the best places is app.config , or web.config depending upon your application type.

[LoanB]

Thanks all

Advice is always greatly appreciated. I consider it all.