SqlAdapter (Update) problems

[Stan Sainte-Rose]

Hi

I change my oledb namespace to sql namespace, and I have several problems
with the update command.
It worked fine with oledb but now, I drive me crazy.
And it happens for each of my update datatables.

By the way, I thank you for your help on my previous posts.

Stan

Here's a bit of my code :

strSQL = "Select code,ncdeclient FROM A_PARAM WHERE CODE='" &
stID + & "'"
daParam = New SqlDataAdapter(strSQL, cn)
daParam.Fill(Ds, "Param")
RowParam = Ds.Tables("Param").Rows.Find(stID)
If RowParam Is Nothing Then
intNumeroCde = 100
RowParam = Ds.Tables("Param").NewRow
RowParam("Code") = stID
RowParam("ncdeclient") = intNumeroCde
Ds.Tables("Param").Rows.Add(RowParam)
daParam.InsertCommand = ParamInsert(RowParam)
Else
NumeroCde = RowParam("ncdeclient")
RowParam.BeginEdit()
RowParam("ncdeclient") = intNumeroCde + 1
RowParam.EndEdit()
daParam.UpdateCommand = ParamUpdate()
End If
daParam.Update(Ds.Tables("Param"))


Private Function ParamUpdate() As SqlCommand
Dim cmd As SqlCommand
strSQL = "UPDATE A_PARAM SET NCDECLIENT = ? WHERE CODE = ? "
cmd = New SqlCommand(strSQL, cn)
cmd.Parameters.Add("ncdeclient", SqlDbType.Int, 0, "ncdeclient")
cmd.Parameters.Add("code", SqlDbType.VarChar, 50, "code")
Return cmd
End Function

 

[David Browne]

Hi

I change my oledb namespace to sql namespace, and I have several problems
with the update command.
It worked fine with oledb but now, I drive me crazy.
And it happens for each of my update datatables.

By the way, I thank you for your help on my previous posts.

Stan

Here's a bit of my code :

Try this.

David


strSQL = "Select code,ncdeclient FROM A_PARAM WHERE CODE=@code"
dim cmdSelect as New SqlCommand(strSQL,cn)
cmdSelect.Parameters.add(new SqlParameter("@code",Integer.Parse(strID)))
daParam = New SqlDataAdapter(cmdSelect)
daParam.InsertCommand = ParamInsert()
daParam.UpdateCommand = ParamUpdate()

daParam.Fill(Ds, "Param")
dim dtParam as DataTable = Ds.Tables("Param")
RowParam = dtParam.Rows.Find(stID)

If RowParam Is Nothing Then
intNumeroCde = 100
RowParam = dtParam.NewRow
RowParam("Code") = stID
RowParam("ncdeclient") = intNumeroCde
dtParam.Rows.Add(RowParam)
Else
NumeroCde = RowParam("ncdeclient")
RowParam.BeginEdit()
RowParam("ncdeclient") = intNumeroCde + 1
RowParam.EndEdit()
End If
daParam.Update(dtParam)


. . .





Private Function ParamUpdate() As SqlCommand
strSQL = "UPDATE A_PARAM SET NCDECLIENT = @ncdeclient WHERE CODE =
@code "
Dim cmd as New SqlCommand(strSQL, cn)
cmd.Parameters.Add("@ncdeclient", SqlDbType.Int, 0, "ncdeclient")
cmd.Parameters.Add("@code", SqlDbType.VarChar, 50, "code")
Return cmd
End Function

Private Function ParamInsert() As SqlCommand
strSQL = "insert into A_PARAM(ncdeclient,code) values
(@ncdeclient,@code) "
Dim cmd As new SqlCommand(strSQL, cn)
cmd.Parameters.Add("@ncdeclient", SqlDbType.Int, 0, "ncdeclient")
cmd.Parameters.Add("@code", SqlDbType.VarChar, 50, "code")
Return cmd
End Function

 

[bruce barker]

the SqlCommand object doesn't support "?" for parameter tokens. you must use
@paramname instead.

Private Function ParamUpdate() As SqlCommand
Dim cmd As SqlCommand
strSQL = "UPDATE A_PARAM SET NCDECLIENT = @ncdeclient WHERE CODE =
@code"
cmd = New SqlCommand(strSQL, cn)
cmd.Parameters.Add("@ncdeclient", SqlDbType.Int, 0, "ncdeclient")
cmd.Parameters.Add("@code", SqlDbType.VarChar, 50, "code")
Return cmd
End Function