InsertCommand generates code using sp_executesql

  • Thread starter Thread starter Vemund Haga
  • Start date Start date
V

Vemund Haga

Hello,
I have a project where I am using a DataAdapter. A Command object is created
to use a stored procedure to insert data and the appropriate columns are
attached. The Command object is attached to the InsertCommand and the Update
method is called. So far, so good and the database receives SQL code to
execute sp_executesql. Here it stops with an error saying that there is a
syntax error.

Two questions:

1: Why does the DataAdapter use sp_executesql to do the insert? Previously I
have done the same in a VB.NET project and then the stored procedure has
been called directly. Now I am using C#. How to control whether the SP is
called directly or sp_executesql is used.

2: Why does the sp_executesql fail? The syntax is 'sp_executesql N'Command',
N'Parameter definition', N'Parameters''. I have tried to run the stored
procedure directly using the first and third part of the executesql and it
works. Ie. the stored procedure inserts data into the table. The parameter
definitions (second part) are the same as both the stored procedure
parameters and the table definition.

The sp_executesql works with sample code.
 
Sorry, found the answer myself just afterwards.

I forgot to set the CommandType property on the Command object to
CommandType.StoredProcedure.

Now it works like a charm.
 
Back
Top