-
Dec 15th, 2014, 03:48 AM
#1
Thread Starter
New Member
Updating a table in Ms Access using OLEDB in VB.net
Dear Friends I am new to this site but I can see you help each other very much and I liked it.
Please help me about the following.
I need to select data from ms access table using OLEDBDATAREADER and then put it into another table according to my criteria using update and insert statements as follows. Put it gives me "Syntax error in UPDATE statement." error. I have tried many syntax of updating using parametric and non parametric update but I couldn't find the problem.
Here is the code:
dim strupdate as string
strupdate = "UPDATE " + tbl + StrAircraft + StrCatID + StrExamPhase + generated + " SET"
strupdate += "QUESTION =?, A =?, B =?, C =?, ANSWER =?, ATA =?, TOPIC =?, SECTION =?, LEVEL =?, SPECIFICS =?, AC_SERIES =?, DATE_OF_ENTRY =?, DATE_OF_UPDATE =?, VALIDATOR_NAME =?, VALIDATOR_ID =?"
strupdate += " WHERE QID =?"
Dim cmdU As OleDbCommand = New OleDbCommand(strupdate, myConnection)
cmdU.Parameters.AddWithValue("QUESTION", Question)
cmdU.Parameters.AddWithValue("A", ChA)
cmdU.Parameters.AddWithValue("B", ChB)
cmdU.Parameters.AddWithValue("C", ChC)
cmdU.Parameters.AddWithValue("ANSWER", Answer)
cmdU.Parameters.AddWithValue("ATA", ATA)
cmdU.Parameters.AddWithValue("TOPIC", Topic)
cmdU.Parameters.AddWithValue("SECTION", Section)
cmdU.Parameters.AddWithValue("LEVEL", Level)
cmdU.Parameters.AddWithValue("SPECIFICS", Specifics)
cmdU.Parameters.AddWithValue("AC_SERIES", Series)
cmdU.Parameters.AddWithValue("DATE_OF_ENTRY", DateOfEntry)
cmdU.Parameters.AddWithValue("DATE_OF_UPDATE", DateOfUpdate)
cmdU.Parameters.AddWithValue("VALIDATOR_NAME", ValidatorN)
cmdU.Parameters.AddWithValue("VALIDATOR_ID", ValidatorID)
cmdU.Parameters.AddWithValue("QID", QID)
'cmdU.Transaction = tran
If cmdU.ExecuteNonQuery() Then
i = i - 1
Else
strinsert = "INSERT INTO " + tbl + StrAircraft + StrCatID + StrExamPhase + generated + "([QID], [QUESTION], [A], [B], [C], [ANSWER], [ATA], [TOPIC], [SECTION], [LEVEL], [SPECIFICS], [A/C SERIES], [DATE-OF-ENTRY], [DATE-OF-UPDATE], [VALIDATOR NAME], [VALIDATOR ID]) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
Dim cmdI As OleDbCommand = New OleDbCommand(strinsert, myConnection)
cmdI.Parameters.Add(New OleDbParameter("QID", QID))
cmdI.Parameters.Add(New OleDbParameter("QUESTION", Question))
cmdI.Parameters.Add(New OleDbParameter("A", ChA))
cmdI.Parameters.Add(New OleDbParameter("B", ChB))
cmdI.Parameters.Add(New OleDbParameter("C", ChC))
cmdI.Parameters.Add(New OleDbParameter("ANSWER", Answer))
cmdI.Parameters.Add(New OleDbParameter("ATA", ATA))
cmdI.Parameters.Add(New OleDbParameter("TOPIC", Topic))
cmdI.Parameters.Add(New OleDbParameter("SECTION", Section))
cmdI.Parameters.Add(New OleDbParameter("LEVEL", Level))
cmdI.Parameters.Add(New OleDbParameter("SPECIFICS", Specifics))
cmdI.Parameters.Add(New OleDbParameter("AC_SERIES", Series))
cmdI.Parameters.Add(New OleDbParameter("DATE_OF_ENTRY", DateOfEntry))
cmdI.Parameters.Add(New OleDbParameter("DATE_OF_UPDATE", DateOfUpdate))
cmdI.Parameters.Add(New OleDbParameter("VALIDATOR_NAME", ValidatorN))
cmdI.Parameters.Add(New OleDbParameter("VALIDATOR_ID", ValidatorID))
cmdI.ExecuteNonQuery()
End If
End While
-
Dec 15th, 2014, 09:12 AM
#2
Re: Updating a table in Ms Access using OLEDB in VB.net
Hello,
It is easy to make small mistakes here when using string concatenation. The following is an example of using XML literals and embedded expressions to assist with writing a SQL statement w/o string concatenation which is easier to read. If you run this code the output goes to the IDE output window while running in the IDE and you can examine the statement along with seeing the problem right before the WHERE condition
Code:
Public Class Form1
Private tbl As String = "A"
Private StrAircraft As String = "B"
Private StrCatID As String = "C"
Private generated As String = "D"
Private StrExamPhase As String = "E"
Private strupdate As String = "F"
Private TableName As String = "G"
Private UpdateStatement As String = ""
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
TableName = String.Concat(tbl, StrAircraft, StrCatID, StrExamPhase, generated)
UpdateStatement =
<SQL>
UPDATE <%= TableName %>
SET
Question = ?,
A = ?,
B = ?,
C = ?,
ANSWER =?,
SECTION = ?,
Level = ?,
SPECIFICS = ?,
AC_SERIES =?,
DATE_OF_ENTRY =?,
DATE_OF_UPDATE =?,
VALIDATOR_NAME =?,
VALIDATOR_ID =?
<%= strupdate %>
WHERE QID = ?
</SQL>.Value
Console.WriteLine(UpdateStatement)
End Sub
End Class
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|