Syntax error in my SQL statement "INSERT INTO"

Question

I have a database with two different tables.

**call_code**                      activity
call_id                            activity_id
maintCall_plan                     activity_desc
maintCall_unplanned                contact_person
creditCalls                        day
newBussCalls                       activity_date
phoneCalls                         revenue
                                   time

I am having trouble debugging my code. The problem is syntax error in insert into statement and conConnection.Execute is highlighted. Here's my code snippet:

Private Sub Command1_Click()
Dim conConnection As ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim strSql As String

Set conConnection = New ADODB.Connection

conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
  App.Path & "\" & "db_weekActRep.mdb;Mode=Read|Write"
conConnection.CursorLocation = adUseClient
conConnection.Open

sqlStr = "INSERT INTO activity(activity_desc, contact_person, day, activity_date, revenue, time) VALUES ("
sqlStr = sqlStr & "'" & txtAct8am.Text & "',"
sqlStr = sqlStr & "'" & txtComp8am.Text & "',"
sqlStr = sqlStr & "'" & Label31.Caption & "',"
sqlStr = sqlStr & "'" & Label20.Caption & "',"
sqlStr = sqlStr & "'" & txtRev8am.Text & "',"
sqlStr = sqlStr & "'" & Label9.Caption & "')"
    conConnection.Execute sqlStr

 Select Case Combo1.ListIndex
    Case 0

        sqlStr = "INSERT INTO call_code(maintCall_plan) VALUES ("
        sqlStr = sqlStr & "'" & "1" & "')"
        conConnection.Execute sqlStr

    Case 1
        sqlStr = "INSERT INTO call_code(maintCall_unplanned) VALUES ("
        sqlStr = sqlStr & "'" & "2" & "')"
        conConnection.Execute sqlStr

    Case 2
        sqlStr = "INSERT INTO call_code(creditCalls) VALUES ("
        sqlStr = sqlStr & "'" & "3" & "')"
        conConnection.Execute sqlStr

    Case 3
        sqlStr = "INSERT INTO call_code(newBussCalls) VALUES ("
        sqlStr = sqlStr & "'" & "4" & "')"
        conConnection.Execute sqlStr

    Case 4
        sqlStr = "INSERT INTO call_code(phoneCalls) VALUES ("
        sqlStr = sqlStr & "'" & "5" & "')"
        conConnection.Execute sqlStr

End Select
End Sub

Also, I wanted to know if is it possible to insert data into two different tables using one click-event or button? If it is, am I doing it right?

By the way, I didn't include the activity_id for the activity table because it is auto-incrementing. Same with the call_id for the call_code table. Any help would be much appreciated.

possible duplicate of Syntax Error in my INSERT INTO statement — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jul 28, 2014 at 11:17
Please do not re-post questions. You can make edits to your existing question if you have changes or new information. — JasonMArcher
– JasonMArcher, Commented Jul 28, 2014 at 16:51
Don't concatenate your SQL like this. You are opening yourself up to SQL Injection. Use parameters instead. — Oded
– Oded, Commented Jul 28, 2014 at 17:01
what do you mean by "don't concatenate" sir @Oded? Is it possible to have multiple insert statement in one click-event? — Lhie
– Lhie, Commented Jul 31, 2014 at 6:19

Joe Taras · Accepted Answer · 2014-07-28 07:36:05Z

0

Your first INSERT statement has declared in this wrong way:

You have two field activity_desc, contact_person but you try to insert more than two fields (six for precision). So you must add the other 4 missing fields

sqlStr = "INSERT INTO activity(activity_desc, contact_person) VALUES ("
sqlStr = sqlStr & "'" & txtAct8am.Text & "',"
sqlStr = sqlStr & "'" & txtComp8am.Text & "',"
sqlStr = sqlStr & "'" & Label31.Caption & "',"
sqlStr = sqlStr & "'" & Label20.Caption & "',"
sqlStr = sqlStr & "'" & txtRev8am.Text & "',"
sqlStr = sqlStr & "'" & Label9.Caption & "')"

Add 4 missing fields in the INSERT INTO statement.

Two second INSERT (in the CASE statement) don't fill the primary key. If you use an autoincremental field it's ok, else it's wrong because a primary key can't be NULL

answered Jul 28, 2014 at 7:36

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lhie Over a year ago

ohhh.. my bad.. even though i added the missing fields, it still gives me same error.. by the way, i didn't include the activity_id for the activity table because it is autoincrement. same with the call_id for the call_code table.. i'll edit my question..

Periklis Douvitsas · Accepted Answer · 2014-07-28 07:41:26Z

0

First of all, in INSERT INTO activity(activity_desc, contact_person) you have two columns but 6 values. Either put 2 values or add 4 columns. ie. INSERT INTO activity(activity_desc, contact_person,day, activity_date, revenue, time ) VALUES if these were the columns that you wanted to add or sqlStr = "INSERT INTO activity(activity_desc, contact_person) VALUES (" sqlStr = sqlStr & "'" & txtAct8am.Text & "'," sqlStr = sqlStr & "'" & txtComp8am.Text & "',";

answered Jul 28, 2014 at 7:41

Periklis Douvitsas

2,5011 gold badge16 silver badges16 bronze badges

12 Comments

Periklis Douvitsas Over a year ago

Could you tell us what is the value of sqlStr just before you make the call conConnection.Execute sqlStr

Lhie Over a year ago

I just assign the sql statement to a string variable which is the sqlStr. That's what the sqlStr is for.

Periklis Douvitsas Over a year ago

I mean tell us the actual value, from debugging

Lhie Over a year ago

I don't get your point sir.. In my own understanding the value of the sqlStr would be the value the user inputs or what the label's caption is..

Periklis Douvitsas Over a year ago

The value of sqlStr depends on your input, correct. Just put some values on your text boxes, on the label's caption etc and tell me the value of sqlStr to see why there is a syntax error

|

Richard Hansell · Accepted Answer · 2014-07-28 08:36:43Z

0

I can see three potential problems here:

You are passing what looks like it would be a date field as a string. You will probably need to format that date so that MS-Access recognises it, e.g. into "YYYYMMDD" or "DD/MM/YYYY" format. I can't remember how Access likes dates but I seem to remember it can be fussy.
Your text fields could potentially have single quotes in them so you may need to REPLACE single quotes with 2 x single quotes, i.e. "can't" becomes "can''t".
You are also passing what seems to be a numeric as a quoted string. If it doesn't resolve to a number then this would throw an error.

To debug start your program and then break it before the execute statement. Examine the contents of sqlStr and then copy/ paste this into your database. When you execute the INSERT directly from MS-Access it will probably give you a more helpful error?

answered Jul 28, 2014 at 8:36

Richard Hansell

5,4131 gold badge20 silver badges40 bronze badges

8 Comments

Lhie Over a year ago

sir @Hansell how can i format the date before inserting it? I know the syntax in formatting the date in displaying it in a label or textbox but i don't know how to format it in an insert statement. This is the syntax right? Label2.Caption = Format(Label2.Caption, "d-mmm") correct me if I'm wrong

Richard Hansell Over a year ago

tbh it depends on where you are, sometimes MS-Access likes really weird dates, e.g. '#2014-12-13#' for INSERT statements and sometimes it only needs those weird hashes when using a date in a WHERE clause. The best bet is to write the SQL in Access until it does what you want and then copy that format into your code.

Richard Hansell Over a year ago

Also, you don't need to format the contents of the caption, you would apply the formatting when building your string up, e.g. sqlStr = sqlStr & "'#" & Format(Label20.Caption, "yyyy-mm-dd") & "#'".

Richard Hansell Over a year ago

This post explains the problem with dates in MS-Access in a lot more detail. stackoverflow.com/questions/771861/…

Lhie Over a year ago

Sir, I also tried doing number 2 but there's a problem. It is inserting the name of the textbox not its value.

|

Lhie · Accepted Answer · 2014-08-01 01:24:17Z

0

I get it now guys.. I'm just missing a bracket in some of my fields. Here's the code snippet..

sqlStr = "INSERT INTO activity(activity_desc, contact_person, revenue, [day], activity_date, [time]) VALUES ("
sqlStr = sqlStr & "'" & txtAct8am.Text & "',"
sqlStr = sqlStr & "'" & txtComp8am.Text & "',"
sqlStr = sqlStr & "'" & txtRev8am.Text & "',"
sqlStr = sqlStr & "'" & Label31.Caption & "',"
sqlStr = sqlStr & "'" & Label20.Caption & "',"
sqlStr = sqlStr & "'" & Label9.Caption & "')"
conConnection.Execute sqlStr

I just need to enclose day and time field in a bracket.. Thanks for your responce I really appreciate it.. My program is now running. Thanks again

answered Aug 1, 2014 at 1:24

Lhie

215 bronze badges

Collectives™ on Stack Overflow

Syntax error in my SQL statement "INSERT INTO"

4 Answers 4

1 Comment

12 Comments

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

12 Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related