not getting the 'tic' marks correct on INSERT INTO statement

Question

Hello.  Below is the sql statement for the INSERT INTO.  
Some background on issue;  
1)  backend is sql server, 
2)  frontend is Access2013, 
3)  the tables are linked, 
4)  using ADO for project, 
5)  issue is in the VALUES part, 
6)  debug.print does not show problem,
7)  msgbox showing sqlInsert does not show issue,
8)  the cbo are datatype int in table,
9)  the txt are datatype varchar in table,
10) the chk are datatype bit in table,
11) the txtDateAdded is datatype datetime in table,
12) those ending in Staff are datatype decimal in the table,
13) the txtComment is datatype varchar in table.

The error that I get is 'Error -2147217900 ([Microsoft SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ','.)'  

I have changed the sqlInsert (below) so many times I don't remember what the original looked like anymore.  This is getting frustrating.  I have taken sqlInsert and used the management studio and plugged in info and it inserted a new record.  I even strung out the entire VALUES part in one line rather than breaking it up like you see below.  Still got the error.

So, I am asking for help on this for another pair of eyes to look this over and mark the fields with the proper tic/quote marks.  

Thanks...John

        sqlInsert = "INSERT INTO tblCompany (StatusID, ShippingMethodID, ParentID, " & _
                                            "Name, Telephone, Fax, Website, EmailAddress, " & _
                                            "IsCorporate, IsLocation, IsHeadquarters, IsSubsidiary, IsRemove, " & _
                                            "DateAdded, FTStaff, PTStaff, TempStaff, InternStaff, ContractStaff, " & _
                                            "Comment) " & _
                    "VALUES (" & cboStatus & ", " & cboShippingMethod & ", " & cboParent & ", " & _
                            "'" & txtName & "', '" & txtTelephone & "', '" & txtFax & "', '" & txtWebsite & "', '" & txtEmail & "', " & _
                            "" & chkIsCorporate & ", " & chkIsLocation & ", " & chkIsHeadquarters & ", " & chkIsSubsidiary & ", " & chkIsRemove & ", " & _
                            "#" & txtDateAdded & "#, " & txtFTStaff & ", " & txtPTStaff & ", " & txtTempStaff & ", " & txtInternStaff & ", " & txtContractStaff & ", " & _
                            "'" & txtComment & "')"

Answer 1

Hi. If you're doing this in Access using VBA, it's a good idea to do a Debug.Print sqlInsert to see exactly what your code is passing to the engine.

The first thing I noticed is the use of the reserved word "Name." Try enclosing it in brackets. For example, [Name].

Hope that helps...

Answer 2

Thanks for the response.  I have been debug.print everytime.  Here is what I get.

INSERT INTO tblCompany (StatusID, ShippingMethodID, ParentID, [Name], Telephone, Fax, Website, EmailAddress, IsCorporate, IsLocation, IsHeadquarters, IsSubsidiary, IsRemove, DateAdded, FTStaff, PTStaff, TempStaff, InternStaff, ContractStaff, Comment) VALUES (1, 7, 105, 'Wonder Woman Gadgets', '', '', '', '', , -1, -1, 0, , 1/6/2016, , , , , , '')

And I still get the error.  But where?  That's what is puzzling.

...John

Answer 3

Hi John,

If that's exactly what you're getting in the Immediate Window, then I think the problem is with those empty values (the ones where there are no values between the commas). If there is no value to insert, try using Null or ZLS.

Hope that helps...

Answer 4

Yep.  The empty controls were it.  I went thru one control at a time until they all had something to save.  Even the checkboxes had to be checked and then unchecked, which put a 0 in the field.  The db table for a bit datatype defaults to 0.  Only the autonumber field is required and not null.  All other fields do not need to have data in them if not appropriate.  But, hey, at least the autonumber comes back to the form.

At least the INSERT INTO got resolved.  Thanks for the help and insight.  Now its time to go to my online truck driving lesson.

...John