Results 1 to 3 of 3

Thread: [RESOLVED] update Query gives a syntax error in vb2017 forms program

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2018

    Resolved [RESOLVED] update Query gives a syntax error in vb2017 forms program

    I know I am being a pest but I have been working on this for last couple of days. scouring the Internet and looking in Visual Basic 2015 book,and SQL for mere mortals.
    Seems I have become a slow learner in my old age.
    I have a VB6 program I am trying to update to in VB Studio 2017, using Access Data Base in Access 2016. On a form with a datagrid I click on a cell and it brings up a edit form and fills the fields with info contained in that row. Then the user can change the info in the text box's and when they click the edit button it sends the info to the table to edit the row. When I run it I get a SYNTAX ERROR IN UPDATE Statement. I have gone over it a number of times looking for missing single quotes and such.

    Here is the code
            Dim strFilePrefix = "NetControl "
            Dim strFileSuffix = ".accdb"
            Dim databaseFile As String = "C:\RRLogger Data\NetControl.accdb"                                                  '& strFilePrefix & strFileSuffix
            Dim conString = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source= " & databaseFile
            con.ConnectionString = conString
            Dim str As String
            str =( "Update TempLog SET [LDate] = '" & txtDate.Text & "',[LTime] = '" & txtTime.Text & "',[HCall] = '" & txtHcall.Text & "',[State] = '" & txtHstate.Text & "',
                                 [County] = '" & txtHcounty.Text & "',[CountyLine] = '" & txtHcntyLine.Text & "',[Freq] = '" & txtFreq.Text & "',[Mode] = '" & txtMode.Text & "',
                                 [MyCall] = '" & txtMycall.Text & "',[HRST] = '" & txtHrst.Text & "',[MRST] = '" & txtMrst.Text & "',[MState] = '" & txtMstate.Text & "',
                                 [MCounty] = '" & txtMcounty.Text & "',[MCountyLine] = '" & txtMcntyLine.Text & "'
                                 WHERE [Id] = '" & txtID.Text & "'")
            Dim cmd As OleDbCommand = New OleDbCommand(str, con)
    Can someone point me in the right direction. I realize parameters are safer, I plan to update the code to that. Trying to get a few screens working to demonstrate the program's concept to a few people.

    I also get a type mismatch for id that is an auto advance field as interger not sure how to over come that.
    Attached Images Attached Images  
    Last edited by MikeDelke; Mar 8th, 2018 at 12:33 PM.

  2. #2
    Fanatic Member
    Join Date
    Nov 2017

    Re: update Query gives a syntax error in vb2017 forms program

    There might be multiple issues, but you mention that Id is an auto-number field, but then in your update statement you are surrounding the [Id] match value in single quotes, which would treat it like a string. If it is an auto-number (or a numeric field at all), then the last line of your string built update query should look like this:

        WHERE [Id] = " & txtID.Text)

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002

    Re: update Query gives a syntax error in vb2017 forms program

    And since [LDate] is a date, it should be treated as one:
    [LDate] = #" & txtDate.Text & "# ...
    As a side note, use a DataTimePicker for dates... then you don't need to worry about invalid dates being entered like 2/29/2017 or 3rd of Mugareth, 5322

    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

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