I'm having some problem in my query.
I'm trying to update a dataset if it exists in another datatable.
I'm having a datatable ds1.table(0)
with 18 columns with 2 primary keys, and datatable ds2.table(0)
with a flexible number of rows.
If the column name of ds1.table(0)
exists in the row of ds2.table(0)
I would like to update my database.
If rc1 > 0 Then
For Each co As DataColumn In ds1.Tables(0).Columns
Dim ColName As String = co.ColumnName
If rc2 > 0 Then
For Each ro As DataRow In ds2.Tables(0).Rows
Dim RoName As String = ro(0).ToString
If RoName.Contains(ColName) Then
Dim cmnd1 As SqlCeCommand
Try
con.Open()
Dim cry As String = "UPDATE serdate SET ['" & ColName & "'] = @date WHERE ((company = '" & Company & "') AND (number = '" & number & "'))"
cmnd1 = New SqlCeCommand(cry, con)
cmnd1.Parameters.Add(New SqlCeParameter("@date", Now))
cmnd1.ExecuteNonQuery()
MsgBox("Update Success")
Catch ex As Exception
MsgBox("Query Error! " & ex.Message)
Finally
con.Close()
End Try
End If
Next
End If
Next
End If
And I'm having some error.
The error is
There was an error in parsing query: Column Name is not valid
The error is being caused by you trying to update a column which does not exist in the database. Have a look in the table definition to ensure the column actually exists. However, you're putting '
around the column name which is not necessary when referring to a column and is most likely causing the error. Remove the '
and use a valid column name to solve this.
On a bit of a unrelated note, your SQL query is vulnerable to SQL injection still. You should be using parameters for every variable you're putting into the query, like how you're putting in the date.