Results 1 to 2 of 2

Thread: Updating a table in Ms Access using OLEDB in VB.net

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2014
    Posts
    1

    Post 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

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    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
  •  



Click Here to Expand Forum to Full Width