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

[MikeDelke]

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
[/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

        con.Open()
        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)

        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            con.Close()]

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.

[OptionBase1]

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)

[techgnome]

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

-tg