0

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.

JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
Lhie
  • 21
  • 4
  • possible duplicate of [Syntax Error in my INSERT INTO statement](http://stackoverflow.com/questions/24988351/syntax-error-in-my-insert-into-statement) – Ňɏssa Pøngjǣrdenlarp Jul 28 '14 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 Jul 28 '14 at 16:51
  • Don't concatenate your SQL like this. You are opening yourself up to [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection). Use parameters instead. – Oded Jul 28 '14 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 Jul 31 '14 at 06:19

4 Answers4

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

Joe Taras
  • 15,166
  • 7
  • 42
  • 55
  • 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.. – Lhie Jul 28 '14 at 07:43
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 & "',";

Periklis Douvitsas
  • 2,431
  • 1
  • 13
  • 14
  • Could you tell us what is the value of sqlStr just before you make the call conConnection.Execute sqlStr – Periklis Douvitsas Jul 28 '14 at 07:55
  • I just assign the sql statement to a string variable which is the sqlStr. That's what the sqlStr is for. – Lhie Jul 28 '14 at 08:06
  • I mean tell us the actual value, from debugging – Periklis Douvitsas Jul 28 '14 at 08:12
  • 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.. – Lhie Jul 28 '14 at 08:17
  • 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 – Periklis Douvitsas Jul 28 '14 at 08:20
  • I've tried inserting into 3 columns (activity_desc, contact_person and revenue) it doesn't give me syntax error. But when I add another column(activity_date) in the insert record, it starts giving me the error. – Lhie Jul 28 '14 at 08:30
  • for `txtAct8am.Text` the value would be "meeting" `txtComp8am.Text` = "Lhie" `Label31.Caption` = "Monday" `Label20.Caption` = "29-June" `txtRev8am.Text` = 1000 `Label9.Caption` = 8 – Lhie Jul 28 '14 at 08:35
  • Most probably you do something wrong with the date(probably a conversion problem from string to date). The activity_date column in the database is it declared as a date or as a string? Also, in your form do you have a textbox or a DateTimePicker for the activvty date ?IF you use textbox you should convert the string of the textbox to date – Periklis Douvitsas Jul 28 '14 at 08:41
  • You shold most probably convert the caption of the Label20 from string to Date – Periklis Douvitsas Jul 28 '14 at 08:46
  • I use Textbox for activity_date and yes it is declared as date/time in the database. How can I convert string to date sir? actually sir when i try inserting for 4 columns, excluding activity_date, it starts giving me the syntax error. so i believe it has nothing to do with the date since i didn't include it in the insert into. – Lhie Jul 28 '14 at 08:48
  • Ok, with 4 columns without the date, can you give the value of sqlStr. In ordeto to convert from string to date in vb6 i think you can use this Dim aDateString, aTimeString As String Dim aDate As Date aDateString = "February 12, 1969" aDate = CDate(aDateString) – Periklis Douvitsas Jul 28 '14 at 08:51
  • `txtAct8am.Text` the value would be "meeting" `txtComp8am.Text` = "Lhie" `Label31.Caption` = "Monday" `txtRev8am.Text` = 1000. I added the day column in the insert statement and it starts giving me the error – Lhie Jul 28 '14 at 08:58
0

I can see three potential problems here:

  1. 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.

  2. 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".

  3. 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?

Richard Hansell
  • 5,315
  • 1
  • 16
  • 35
  • 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 – Lhie Jul 28 '14 at 08:44
  • 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 Jul 28 '14 at 08:47
  • 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 Jul 28 '14 at 08:48
  • This post explains the problem with dates in MS-Access in a lot more detail. http://stackoverflow.com/questions/771861/insert-sql-command-with-datetime-in-ms-access – Richard Hansell Jul 28 '14 at 08:49
  • Sir, I also tried doing number 2 but there's a problem. It is inserting the name of the textbox not its value. – Lhie Jul 28 '14 at 08:54
  • Try reading this? http://bytes.com/topic/access/insights/575414-quotes-double-quotes-where-when-use-them – Richard Hansell Jul 28 '14 at 08:58
  • But i don't think it has something to do with the date sir since i didn't include it in the insert statement.. read my conversation w/ sir Periklis. – Lhie Jul 28 '14 at 09:11
  • Try removing the single quotes from the numeric fields? – Richard Hansell Jul 28 '14 at 09:20
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

Lhie
  • 21
  • 4