sql server stored procedure execution

  • Thread starter Thread starter Sergei Shelukhin
  • Start date Start date
S

Sergei Shelukhin

I try to execute the stored procedure that returns value the way it is done
with Odbc* controls in the example from microsoft site, but I get errors all
the time.

Exception is thown in system.data.dll, with description "System error."
Connection is working and I can use it to get data to DataSets and Data
grids.
SP is also working normally, I created small BCB project and executed it via
standart TStoredProcedure component there, it worked, and returned value as
it should.

Here's the code. Commented bits mean I tried several ways to do that and all
of them failed.

SqlCommand inscomm = new SqlCommand("{? = call GetKey}",sqlConnection1);
//SqlCommand inscomm = new SqlCommand("{? = call GetKey()}",sqlConnection1);
//SqlCommand inscomm = new SqlCommand("{? = GetKey()}",sqlConnection1);
//SqlCommand inscomm = new SqlCommand("{? = GetKey}",sqlConnection1);
SqlParameter prm = inscomm.Parameters.Add("@RETURN_VALUE",SqlDbType.Int);
prm.Direction = ParameterDirection.ReturnValue;
//inscomm.Prepare();
inscomm.ExecuteReader(); // error here
//inscomm.ExecuteNonQuery();

What's the problem?
 
Sergei Shelukhin said:
I try to execute the stored procedure that returns value the way it is done
with Odbc* controls in the example from microsoft site, but I get errors all
the time.

Exception is thown in system.data.dll, with description "System error."
Connection is working and I can use it to get data to DataSets and Data
grids.
SP is also working normally, I created small BCB project and executed it via
standart TStoredProcedure component there, it worked, and returned value as
it should.

Here's the code. Commented bits mean I tried several ways to do that and all
of them failed.

SqlCommand inscomm = new SqlCommand("{? = call GetKey}",sqlConnection1);
//SqlCommand inscomm = new SqlCommand("{? = call GetKey()}",sqlConnection1);
//SqlCommand inscomm = new SqlCommand("{? = GetKey()}",sqlConnection1);
//SqlCommand inscomm = new SqlCommand("{? = GetKey}",sqlConnection1);
SqlParameter prm = inscomm.Parameters.Add("@RETURN_VALUE",SqlDbType.Int);
prm.Direction = ParameterDirection.ReturnValue;
//inscomm.Prepare();
inscomm.ExecuteReader(); // error here
//inscomm.ExecuteNonQuery();

What's the problem?
Click to expand...

For one thing, you haven't set the command type to be
CommandType.StoredProcedure. I'd try that to start with.
 
For one thing, you haven't set the command type to be
CommandType.StoredProcedure. I'd try that to start with.
Click to expand...
Thanks a lot, it did work. I also didn't need the whole {? = thing, it
caused separate errors with the type set correctly, I just placed "GetKey"
and that's all ;)
 
Back
Top