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

Show quoteHide quote

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

Show quoteHide quote

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

Show quoteHide quote

Data Access Problem
Storing filepath in database field ??
Seemingly random Concurrency violations updating Access database
What transaction IsolationLevel for?
Help: Login failed for User ???