Syntax error on insert into statement

Question

0.00/5 (No votes)

See more:

I am getting a syntax error when I try to update my table. I am new to Access and can't seem to find the problem with a lot of research and book reading. Access 2016 and Visual studio 2015 , using visual basic.

What I have tried:

VB

Access.ExecQuery("INSERT INTO Log (LDate, LTime, HCall, " &
            "State, County, Band, Freq, Mode, MCall, HRST, MRST, " &
           "HOper, MOper, RunStart, RunEnd, NetDuration, HomeCounty, CountyLine) " &
          "VALUES(@LDate,@LTime,@HCall,@State,@County,@Band,@Freq,@Mode,@MCall,@HRST,@MRST,@Hoper,@Moper,@RunStart,@RunEnd,@NetDuration,@HomeCounty,@CountyLine);")

this is the code I have that takes the parameter and places it into the column. Please help.

Posted 27-Jan-17 7:29am

medic63

Updated 27-Jan-17 20:42pm

CHill60

v2

Add a Solution

Comments

CHill60 27-Jan-17 13:42pm

What is the exact error?

Richard Deeming 27-Jan-17 13:42pm

What's the full error message?

medic63 27-Jan-17 15:19pm

syntax error in INSERT INTO statement

this si the whole error message

[no name] 27-Jan-17 13:48pm

I don't get any syntax error but I used Access 2013.

Michael_Davies 27-Jan-17 14:18pm

Does Access.ExecQuery actually execute the query? If it does then normally you build and SQL parameter string with @<name> placeholders for parameter values then use a parameter Add method to submit the values using the @<names> and only then you execute the query.

Not seen this method, usually use ADO to access Access.

medic63 27-Jan-17 15:22pm

Here is the whole sub routine code

Private Sub AddLogEntry()
Dim DBDA As New OleDbDataAdapter
Dim DBTA As New DataTable
Dim DBCon As New OleDbConnection
Dim DBCmd As New OleDbCommand
Dim bsource As New BindingSource

'ADD PARAMETERS

Access.Addparam("@LDate", lblDate.Text)
Access.Addparam("@LTime", lblClock.Text)
Access.Addparam("@HCall", txtCall.Text)
Access.Addparam("@State", cbxState.Text)
Access.Addparam("@County", cbxCounty.Text)
Access.Addparam("@Band", cbxBand.Text)
Access.Addparam("@Freq", cbxFreq.Text)
Access.Addparam("@Mode", cbxMode.Text)
Access.Addparam("@Mcall", lblMCall.Text)
Access.Addparam("@HRST", cbxHRst.Text)
Access.Addparam("@MRST", cbxMRst.Text)
Access.Addparam("@Hoper", cbxHOper.Text)
Access.Addparam("@Moper", cbxMOper.Text)
Access.Addparam("@RunStart", txtRStart.Text)
Access.Addparam("@RunEnd", txtREnd.Text)
Access.Addparam("@NetDuration", lblNetDur.Text)
Access.Addparam("@HomeCounty", lblHomeCounty.Text)
Access.Addparam("@CountyLine", cbxCountyL.Text)

'EXECUTE INSERT COMMAND
Access.ExecQuery("INSERT INTO Log (LDate, LTime, HCall, " &
"State, County, Band, Freq, Mode, MCall, HRST, MRST, " &
"HOper, MOper, RunStart, RunEnd, NetDuration, HomeCounty, CountyLine) " &
"VALUES(@LDate,@LTime,@HCall,@State,@County,@Band,@Freq,@Mode,@MCall,@HRST,@MRST,@Hoper,@Moper,@RunStart,@RunEnd,@NetDuration,@HomeCounty,@CountyLine);")

'Report and abort on errors
If Not String.IsNullOrEmpty(Access.exception) Then

'SUCCESS MESSAGE
MsgBox("Row was added SUCCESFULLY.")

Me.RefreshGrid()

End If

End Sub

Michael_Davies 27-Jan-17 15:52pm

The addparam's normally come after the command has been given the SQL, your adding the params then giving the SQL text with the param holders in it.

Where is Access defined and does it have a commandtext method?

medic63 27-Jan-17 17:50pm

Michael,

Thank you for your questions and suggestions. This is all very difficult to grasp as I am learning programming. I watch a lot of videos on U-Tube and have my face in books trying to figure this out. Simple programs I have written seem to work ok, but working with a database is a whole other story. I have combobox's in this program and fill them from a table that works. I can connect to the database and view its contents in a datagrid view that works. I guess I don't get the nuances required to update the table. If you know of someplace to view this info I would appreciate it. I have learned programming on my own and U-Tube, seem to learn faster by watching and then doing. The biggest problem is having a different version of the programs causes problems as their's work and mine won't.
Again thank's for your attempts to help.

I don't understand your last question.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Michael_Davies · Accepted Answer · 2017-01-27T20:42:00

Solution 1

Try this:

VB

Private Sub AddLogEntry()
Using DBCon As New OleDbConnection(<PUT ACCESS DB CONNECTION STRING HERE>),
DBCmd As New OleDbCommand("",DBCon)

 DBCmd.CommandText = "INSERT INTO Log (LDate, LTime, HCall, " &
"State, County, Band, Freq, Mode, MCall, HRST, MRST, " &
"HOper, MOper, RunStart, RunEnd, NetDuration, HomeCounty, CountyLine) " &
"VALUES(@LDate,@LTime,@HCall,@State,@County,@Band,@Freq,@Mode,@MCall,@HRST,@MRST,@Hoper,@Moper,@RunStart,@RunEnd,@NetDuration,@HomeCounty,@CountyLine);"

 With DBCmd
 .Parameters.AddWithValue("@LDate", lblDate.Text)
 .Parameters.AddWithValue("@LTime", lblClock.Text)
 .Parameters.AddWithValue("@HCall", txtCall.Text)
 .Parameters.AddWithValue("@State", cbxState.Text)
 .Parameters.AddWithValue("@County", cbxCounty.Text)
 .Parameters.AddWithValue("@Band", cbxBand.Text)
 .Parameters.AddWithValue("@Freq", cbxFreq.Text)
 .Parameters.AddWithValue("@Mode", cbxMode.Text)
 .Parameters.AddWithValue("@Mcall", lblMCall.Text)
 .Parameters.AddWithValue("@HRST", cbxHRst.Text)
 .Parameters.AddWithValue("@MRST", cbxMRst.Text)
 .Parameters.AddWithValue("@Hoper", cbxHOper.Text)
 .Parameters.AddWithValue("@Moper", cbxMOper.Text)
 .Parameters.AddWithValue("@RunStart", txtRStart.Text)
 .Parameters.AddWithValue("@RunEnd", txtREnd.Text)
 .Parameters.AddWithValue("@NetDuration", lblNetDur.Text)
 .Parameters.AddWithValue("@HomeCounty", lblHomeCounty.Text)
 .Parameters.AddWithValue("@CountyLine", cbxCountyL.Text)
 End with

 DBCon.Open()
 if DBCmd.ExecuteNonQuery() = 1 Then
  MsgBox("Row was added SUCCESFULLY.")

  Me.RefreshGrid()
 Else
  MsgBox("Insert FAILED.")
 Endif
 DBCon.Close()
End Using

End Sub

Posted 27-Jan-17 20:42pm

Michael_Davies

Updated 28-Jan-17 6:15am

v2

Comments

medic63 28-Jan-17 11:56am

Michael_Davies,

That did eliminate the error message, however the file is not added to the database, and don't see the MsgBox. I do see now what you were asking in the previous question. I also see where my formatting of the AddWithValue statement was wrong.

I appreciate your answer it makes the process clearer.

Michael_Davies 28-Jan-17 12:10pm

I've added an else condition to display failure.

Most problems I had using an Access DB occurred around date and time fields, they must be in the correct format or the whole insert is rejected. Notice you have date and time fields and you get their content from labels if your naming convention reads right, check them for correct format.

You also have what looks like ComboBoxes, have you made sure that an item has been selected on the comboboxes?

Are any of your tables fields required and cannot be empty?

medic63 28-Jan-17 14:05pm

I added the failure message it doesn't show either. So I am assuming the error is in my refreshGrid sub ? there are 3 clock fields, and a stop watch running to keep track of how long you are on the air. They are all set by UTCNow and the Date field is also set by UTCNow, except the stop watch which is just a counting timer. When I run the program they all appear to be in the correct format. The combo box's are selected as state must be picked to show the county's in that state to be picked from and then a county is picked show it is showing in the combo box. The only field that cannot be empty is the ID field which is set to integer and auto increment it is the very first field and was set by Access.
Hope this answer's your question.

Michael_Davies 28-Jan-17 14:10pm

If your RefreshGrid is called then you should see the msgbox, use the debugger to walk through and see what is happening.

Is the date field a date field or a string to store a date?

Show me the date you are storing as it appears in lbldate.text.

medic63 28-Jan-17 15:49pm

The Date field is a string storing a date, and the lbldate.text displays 01/28/2017. Will go thru the debugger after I figure it out I am reading in Visual basic 2015 book. The Times are the same saved as string's like 00:00:00.

medic63 30-Jan-17 12:21pm

Michael_Davies

I went thru the debug and I couldn't find anything. I was thinking and looking at the program and thought " jeez I am scatter brained " I never mentioned that the tutorial I used added a DB control class so maybe it is trying to do the same job twice.
So here is the class code

Public Class DBControl

'CREATE FIRST CONNECTION
Public DBCon As New OleDbConnection("provider = microsoft.Ace.OleDB.12.0;" & ' Private
"Data Source=Database11.accdb;")

'PREPARE DATABASE COMMAND DBCmd
Private DBCmd As OleDbCommand

'DATABASE DATA
Public DBDA As OleDbDataAdapter
Public DBTA As DataTable

'QUERY PARAMETERS
Public Params As New List(Of OleDbParameter)

'QUERY STATISTICS
Public RecordCount As Integer
Public exception As String

Public Sub ExecQuery(query As String)

'RESET QUERY STATISTICS
RecordCount = 0
exception = ""

Try
'OPEN CONNECTION
DBCon.Open()

'CREATE DATABASE COMMAND
DBCmd = New OleDbCommand(query, DBCon)

'LOAD PARAMS INTO DB COMMAND
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

'CLEAR PARAMS LIST
' Params.Clear()

' EXECUTE COMMAND AND FILL DATATABLE
DBTA = New DataTable
DBDA = New OleDbDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBTA)

Catch ex As Exception

exception = ex.Message

End Try

'CLOSE CONNECTION
If DBCon.State = ConnectionState.Open Then DBCon.Close()

End Sub

'INCLUDE QUERY AND COMMAND PARAMETERS
Public Sub Addparam(Name As String, Value As Object)

Dim newParam As New OleDbParameter(Name, Value)
Params.Add(newParam)

End Sub

End Class

this should help get to the problem I hope. Sorry for any inconvenience I have caused.

Michael_Davies 30-Jan-17 13:02pm

That explains what Access is in your code, you must declare Access as a new DBControl somewhere.

In which case you can use it as it store the parameters in an array then adds them to the query before execution.

It is not the neatest or best DB access class I've seen, the NonQuery sub does not return a value, you can read the results from the .Record which will contain a count of records in the table returned by the query string which is quite probably 1 row with 1 column that is an integer and it contains either 1 for row inserted or 0 nothing inserted so you'll have to use that to test success or not.

Also the connector specifies a hard coded database named Database11.accdb, which must be in your working directory, presume this is your database that you are working on.

medic63 30-Jan-17 13:32pm

it is in the working directory. This is a stand alone desktop program that I want to use in my ham radio hobby.

So would it be better to make public subs in the class for add edit delete and then just call them from the program, or just write those subs in the program itself ? I guess first I need to get the add to work that would be a big step for me, and the program as it is now is useless if I can't add records

Michael_Davies 30-Jan-17 13:38pm

You probably are adding records, so long as the are no rules preventing duplicates, you just need to test the right place for the result of your insert.

If you write everything inline it will teach you more about databases than relying on something you do not understand written by someone else.

Good luck with whatever you decide.

medic63 30-Jan-17 13:52pm

Thanks

I will keep after it!