Help on SQLAdapter Command ???

Dear all,

I have a function name GetLabData which call a store procedure .
For that function I need to create the Adapter.InsertCommand an DeleteCommand

My InsertCommand works fine and build as follow :

======================>
m_SQl_Insert = "insert into External_Data LINE_ID) "
m_SQl_Insert = m_SQl_Insert & "values (@Line_id)"

m_ObjSqDeleteCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
m_ObjSqDeleteCmd.Parameters.Clear()
m_ObjSqDeleteCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar, 16,
"LINE_ID")

m_ObjSqlInsertCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
m_ObjSqlInsertCmd.Parameters.Clear()
m_ObjSqlInsertCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar, 16,
"LINE_ID")
m_ObjSqlInsertCmd.CommandType = CommandType.Text
m_objLabAdapter.InsertCommand = m_ObjSqlInsertCmd
<======================

As soon as I do the same thing for the DeleteCommand, I get an exception
when that command gets call by the Adapter.Update method

What is the proper way to define Adapter command (Insert,Delete,Update) ???

I do the following for the DeletCommand but does not work :

===========>
m_SQl_Insert = "DELETE FROM EXTERNAL_DATA WHERE "
m_SQl_Insert = m_SQl_Insert & "Line_ID=' wse30'"
m_ObjSqDeleteCmd.CommandType = CommandType.Text
m_objLabAdapter.DeleteCommand = m_ObjSqDeleteCmd

What I ma doing wrong ? not that I have test those commnd within TSQL and
works ok

thanks for your help
regards
serge

Hi,

Are you sure that you use stored procedures? If yes, then your code can't
work at all, because "m_ObjSqlInsertCmd.CommandType = CommandType.Text"
means that you use standart sql query, not a stored procedure.

If no, you can use a code like this:
SqlDataAdapter da = new SqlDataAdapter("Select * From Lab");

SqlCommandBuilder cb = new SqlCommandBuilder(da);

After that your SqlDataAdapter has all the commands.

Have a nice work,

Aytaç ÖZAY
Software Developer

"serge calderara" <sergecalderara@discussions.microsoft.com> wrote in
message news:BC0F151D-EEC4-4736-8345-F960F26D9691@microsoft.com...
> Dear all,
>
> I have a function name GetLabData which call a store procedure .
> For that function I need to create the Adapter.InsertCommand an
> DeleteCommand
>
> My InsertCommand works fine and build as follow :
>
> ======================>
> m_SQl_Insert = "insert into External_Data LINE_ID) "
> m_SQl_Insert = m_SQl_Insert & "values (@Line_id)"
>
> m_ObjSqDeleteCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
> m_ObjSqDeleteCmd.Parameters.Clear()
> m_ObjSqDeleteCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar, 16,
> "LINE_ID")
>
> m_ObjSqlInsertCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
> m_ObjSqlInsertCmd.Parameters.Clear()
> m_ObjSqlInsertCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar,
> 16,
> "LINE_ID")
> m_ObjSqlInsertCmd.CommandType = CommandType.Text
> m_objLabAdapter.InsertCommand = m_ObjSqlInsertCmd
> <======================
>
> As soon as I do the same thing for the DeleteCommand, I get an exception
> when that command gets call by the Adapter.Update method
>
> What is the proper way to define Adapter command (Insert,Delete,Update)
> ???
>
> I do the following for the DeletCommand but does not work :
>
> ===========>
> m_SQl_Insert = "DELETE FROM EXTERNAL_DATA WHERE "
> m_SQl_Insert = m_SQl_Insert & "Line_ID=' wse30'"
> m_ObjSqDeleteCmd.CommandType = CommandType.Text
> m_objLabAdapter.DeleteCommand = m_ObjSqDeleteCmd
>
> What I ma doing wrong ? not that I have test those commnd within TSQL and
> works ok
>
> thanks for your help
> regards
> serge

HI,

In that the code in my previous mail is place in a function named "GetData"
which calle a store procedure.
In order to build that request I am using the same dataAdapter object as the
one used for the store procedure to create the Insert , Delete command.

The if I do so does it mean that I should leave the CommandType set for
store procedure ???

regards
serge

"Aytaç ÖZAY" wrote:

> Hi,
>
> Are you sure that you use stored procedures? If yes, then your code can't
> work at all, because "m_ObjSqlInsertCmd.CommandType = CommandType.Text"
> means that you use standart sql query, not a stored procedure.
>
> If no, you can use a code like this:
> SqlDataAdapter da = new SqlDataAdapter("Select * From Lab");
>
> SqlCommandBuilder cb = new SqlCommandBuilder(da);
>
> After that your SqlDataAdapter has all the commands.
>
> Have a nice work,
>
> Aytaç ÖZAY
> Software Developer
>
> "serge calderara" <sergecalderara@discussions.microsoft.com> wrote in
> message news:BC0F151D-EEC4-4736-8345-F960F26D9691@microsoft.com...
> > Dear all,
> >
> > I have a function name GetLabData which call a store procedure .
> > For that function I need to create the Adapter.InsertCommand an
> > DeleteCommand
> >
> > My InsertCommand works fine and build as follow :
> >
> > ======================>
> > m_SQl_Insert = "insert into External_Data LINE_ID) "
> > m_SQl_Insert = m_SQl_Insert & "values (@Line_id)"
> >
> > m_ObjSqDeleteCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
> > m_ObjSqDeleteCmd.Parameters.Clear()
> > m_ObjSqDeleteCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar, 16,
> > "LINE_ID")
> >
> > m_ObjSqlInsertCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
> > m_ObjSqlInsertCmd.Parameters.Clear()
> > m_ObjSqlInsertCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar,
> > 16,
> > "LINE_ID")
> > m_ObjSqlInsertCmd.CommandType = CommandType.Text
> > m_objLabAdapter.InsertCommand = m_ObjSqlInsertCmd
> > <======================
> >
> > As soon as I do the same thing for the DeleteCommand, I get an exception
> > when that command gets call by the Adapter.Update method
> >
> > What is the proper way to define Adapter command (Insert,Delete,Update)
> > ???
> >
> > I do the following for the DeletCommand but does not work :
> >
> > ===========>
> > m_SQl_Insert = "DELETE FROM EXTERNAL_DATA WHERE "
> > m_SQl_Insert = m_SQl_Insert & "Line_ID=' wse30'"
> > m_ObjSqDeleteCmd.CommandType = CommandType.Text
> > m_objLabAdapter.DeleteCommand = m_ObjSqDeleteCmd
> >
> > What I ma doing wrong ? not that I have test those commnd within TSQL and
> > works ok
> >
> > thanks for your help
> > regards
> > serge
>
>
>

HI again,

I will try to be more clear.
The whole stuff is called in a commo function Name GetData on which I have
following code together :


m_objSqlCmd = New SqlCommand("sp_GetReelLabDataValues", m_sqlConn)
m_objSqlCmd.CommandType = CommandType.StoredProcedure

' define first sqlprocedure paramter
m_objSqlCmd.Parameters.Add("@Line", SqlDbType.NVarChar).Value = LineId
m_objSqlCmd.Parameters.Add("@BatchId", SqlDbType.Int).Value = BatchId
m_objSqlCmd.Parameters.Add("@ReelId", SqlDbType.Int).Value = ReelId
m_SqlParam.Direction = ParameterDirection.Input

m_sqlConn = New SqlConnection(m_sConnection)
m_sqlConn.Open()

m_objLabAdapter = New SqlDataAdapter(m_objSqlCmd)

m_objLabAdapter.Fill(ds_LabValues)
m_sqlConn.Close()

Then I define the Insert comand for that function
===============================

m_SQl_Insert = "insert into External_Data "
m_SQl_Insert = m_SQl_Insert &
"(LINE_ID,Device_Id,production_id,Reel_Nb,Measure_Id,Measure_Value,"
m_SQl_Insert = m_SQl_Insert & "Measured_On,Author,Comments,Doc_Link) "
m_SQl_Insert = m_SQl_Insert & "values
(@Line_id,@Device_id,@Production_id,@Reel_Nb,@Measure_id,@Measure_value,@Measured_on,@Author,@Comments,@Doc_Link)"

m_ObjSqlInsertCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
m_ObjSqlInsertCmd.Parameters.Clear()
m_ObjSqlInsertCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar, 16,
"LINE_ID")
m_ObjSqlInsertCmd.Parameters.Add("@Device_id", SqlDbType.NVarChar,
64, "Device_id")
m_ObjSqlInsertCmd.Parameters.Add("@Production_id",
SqlDbType.NVarChar, 64, "Production_Id")
m_ObjSqlInsertCmd.Parameters.Add("@Reel_Nb", SqlDbType.Int, 4,
"Reel_Nb")
m_ObjSqlInsertCmd.Parameters.Add("@Measure_id", SqlDbType.VarChar,
64, "Measure_id")
m_ObjSqlInsertCmd.Parameters.Add("@Measure_value",
SqlDbType.VarChar, 64, "Measure_value")
m_ObjSqlInsertCmd.Parameters.Add("@Measured_on", SqlDbType.NVarChar,
50, "Measured_on")
m_ObjSqlInsertCmd.Parameters.Add("@Author", SqlDbType.NVarChar, 50,
"Author")
m_ObjSqlInsertCmd.Parameters.Add("@Comments", SqlDbType.NVarChar,
150, "Comments")
m_ObjSqlInsertCmd.Parameters.Add("@Doc_Link", SqlDbType.NVarChar,
254, "Doc_Link")

m_objLabAdapter.InsertCommand = m_ObjSqlInsertCmd
m_objLabAdapter.CommandType=CommandType.Text


Then the Delete command
==================

'build delete command
m_SQl_Insert = "DELETE FROM EXTERNAL_DATA WHERE "
m_SQl_Insert = m_SQl_Insert & "Line_ID=' wse30'" ' @Line_id'" 'AND
Device_id=@Device_id"
'm_SQl_Insert = m_SQl_Insert & " AND Production_Id=@Production_id
AND Reel_Nb=@Reel_Nb"
'm_SQl_Insert = m_SQl_Insert & " AND Measure_id=@Measure_id AND
Measure_value=@Measure_value"
' m_SQl_Insert = m_SQl_Insert & " AND Measured_On=@Measured_on AND
Author=@Author"
' m_SQl_Insert = m_SQl_Insert & " AND Comments=@Comments and
DOC_link=@Doc_Link)"

m_ObjSqDeleteCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
m_ObjSqDeleteCmd.Parameters.Clear()
m_ObjSqDeleteCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar, 16,
"LINE_ID")
m_objLabAdapter.DeleteCommand = m_ObjSqDeleteCmd


Note that if I execute an Insert command, it works fine, but when runing a
delete command I get an error of :


"Concurency violation : The DeleteCommand affected 0 records"

Note that the INSERT Command works fine

What is wrong ?

regards
serge

"Aytaç ÖZAY" wrote:

> Hi,
>
> Are you sure that you use stored procedures? If yes, then your code can't
> work at all, because "m_ObjSqlInsertCmd.CommandType = CommandType.Text"
> means that you use standart sql query, not a stored procedure.
>
> If no, you can use a code like this:
> SqlDataAdapter da = new SqlDataAdapter("Select * From Lab");
>
> SqlCommandBuilder cb = new SqlCommandBuilder(da);
>
> After that your SqlDataAdapter has all the commands.
>
> Have a nice work,
>
> Aytaç ÖZAY
> Software Developer
>
> "serge calderara" <sergecalderara@discussions.microsoft.com> wrote in
> message news:BC0F151D-EEC4-4736-8345-F960F26D9691@microsoft.com...
> > Dear all,
> >
> > I have a function name GetLabData which call a store procedure .
> > For that function I need to create the Adapter.InsertCommand an
> > DeleteCommand
> >
> > My InsertCommand works fine and build as follow :
> >
> > ======================>
> > m_SQl_Insert = "insert into External_Data LINE_ID) "
> > m_SQl_Insert = m_SQl_Insert & "values (@Line_id)"
> >
> > m_ObjSqDeleteCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
> > m_ObjSqDeleteCmd.Parameters.Clear()
> > m_ObjSqDeleteCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar, 16,
> > "LINE_ID")
> >
> > m_ObjSqlInsertCmd = New SqlCommand(m_SQl_Insert, m_sqlConn)
> > m_ObjSqlInsertCmd.Parameters.Clear()
> > m_ObjSqlInsertCmd.Parameters.Add("@Line_id", SqlDbType.NVarChar,
> > 16,
> > "LINE_ID")
> > m_ObjSqlInsertCmd.CommandType = CommandType.Text
> > m_objLabAdapter.InsertCommand = m_ObjSqlInsertCmd
> > <======================
> >
> > As soon as I do the same thing for the DeleteCommand, I get an exception
> > when that command gets call by the Adapter.Update method
> >
> > What is the proper way to define Adapter command (Insert,Delete,Update)
> > ???
> >
> > I do the following for the DeletCommand but does not work :
> >
> > ===========>
> > m_SQl_Insert = "DELETE FROM EXTERNAL_DATA WHERE "
> > m_SQl_Insert = m_SQl_Insert & "Line_ID=' wse30'"
> > m_ObjSqDeleteCmd.CommandType = CommandType.Text
> > m_objLabAdapter.DeleteCommand = m_ObjSqDeleteCmd
> >
> > What I ma doing wrong ? not that I have test those commnd within TSQL and
> > works ok
> >
> > thanks for your help
> > regards
> > serge
>
>
>