I need help updating a SQL Server using VB.Net and ADO.Net

[rarubio1]

I need help updating a record using ADO.Net. I get the following error whenever I attempt to update:

An unhandled exception of type 'System.InvalidOperationException'
Occurred in System.Data.Common.dll
Additional information: Update unable to find TableMapping['Table'] or
DataTable 'Table'

Here is the code I am using:

Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlDataAdapter
Imports System.Data.DataSet

Public Function SaveUpdate(ByVal strWO As String, ByVal strProcess As String, _
ByVal intPTI As Integer, ByVal intQty As Integer) As Integer

Dim cn3 As New SqlConnection
'Initialize Objects
cn3 = New SqlConnection("Data Source = DAM3;" & _
"Initial Catalog=Scheduling;UserID=User1; PassWord=IT100;")
Dim strSQL As String
Dim cmdUpdate As New SqlCommand
cmdUpdate = New SqlCommand

'Set Up Command
strSQL = "UPDATE tblWorkOrderDtl " _
& "SET ActualQty = '" & intQty & "'," _
& "PTI = '" & intPTI & "' " _
& "WHERE WorkOrderNo = '" & strWO & "' " _
& "AND ProcessStep = '" & strProcess & "'"
cmdUpdate.CommandText = strSQL
cmdUpdate.CommandType = CommandType.Text
cmdUpdate.Connection = cn3

'Dim cn As New SqlConnection
Dim dResults As DataSet
dResults = New DataSet

Dim adPerson As SqlDataAdapter
adPerson = New SqlDataAdapter
adPerson.UpdateCommand = cmdUpdate

cn3.Open()
adPerson.Update(dResults, "tblWorkOrderDtl")
cn3.Close()
End Function


Am I not setting the right references. Please help.

Thank you,
RR

 

[chrissie1]

firts your dataset is not being filled by the dataadapter so it can not update. Second it holds no table because it is not filled.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[russellbcopeland]

try skipping the data adapter for this and use cmdUpdate.ExecuteNonQuery

 

[rarubio1]

I made the changes to the code. Here is what it looks like now:

Public Function SaveUpdate(ByVal strWO As String, ByVal strProcess As String, _
ByVal intPTI As Integer, ByVal intQty As Integer) As Integer

Dim cn3 As New SqlConnection
'Initialize Objects
cn3 = New SqlConnection("Data Source = DAMERON3;" & _
"Initial Catalog = Scheduling;User ID=SA; PassWord=DAF90224;")
Dim strSQL As String
Dim cmdUpdate As New SqlCommand
cmdUpdate = New SqlCommand

'Set Up Command
strSQL = "UPDATE tblWorkOrderDtl " _
& "SET ActualQty = '" & intQty & "'," _
& "PTI = '" & intPTI & "' " _
& "WHERE WorkOrderNo = '" & strWO & "' " _
& "AND ProcessStep = '" & strProcess & "'"
cmdUpdate.CommandText = strSQL
cmdUpdate.CommandType = CommandType.Text
cmdUpdate.Connection = cn3

cn3.Open()
cmdUpdate.ExecuteNonQuery()
cn3.Close()
End Function


I now get the following error message:
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.SqlClient.dll

Additional information: SqlException

Can you please tell me what's wrong with the syntax now.

Thank you,
RR

 

[russellbcopeland]

put a breakpoint at the ExecuteNonQuery statement and use the command window to find out the value of strSQL. The problem is probably in that string.

 

[jebenson]

This error means that there is something wrong with your SQL code.

strSQL = "UPDATE tblWorkOrderDtl " _
& "SET ActualQty = '" & intQty & "'," _
& "PTI = '" & intPTI & "' " _
& "WHERE WorkOrderNo = '" & strWO & "' " _
& "AND ProcessStep = '" & strProcess & "'"

Are the fields ActualQty and PTI numeric or character fields. If they are numeric, you need to remove the single quotes from around the data (intQty and intPTI).


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[rarubio1]

You were right jebenson, there was something wrong with my sql query. I had mispelled one of the columns wrong. My code works fine now.

Thank you all.
RR