Using executeNonQuery to execute an INSERT stmt [RESOLVED]

[ERage]

I am attempting to use the executeNonQuery method of a
command object to execute an insert SQL statement on a table in
an Access database. I have successfully used executeNonQuery
to perform an Update to a table but I can't seem to get this one
to work. Here is my code, I have printed the generated query
and tested it in access and the query works fine, must be
something with my VB code. I'm new to .NET so I'm not seeing
the problem. Any help is appreciated. Here is my code.

Private Sub Insert_Row()
        Dim strSQL As String
 
        Dim strconn As String
        Dim connection As New OleDb.OleDbConnection
 
        Dim a() As String = cmbExpenseAccount.SelectedItem.Split()
 
        strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = S:\20YRLongRangePlan\CapitalLogistics.mdb"
 
        strSQL = "INSERT INTO tblProjects VALUES(" & projectID 
& ", '', '', '" & a(0) & "', " & Chr(34) & txtDescription.Text & Chr(34) 
& ", " & Chr(34) & txtManufacturer.Text & Chr(34) & ", '', '', " & Chr
(34) & txtProjectLife.Text & Chr(34) & ", " & Chr(34) & 
cmbReviewYear.SelectedItem & Chr(34) & ", " & Chr(34) & 
cmbReplacementYear.SelectedItem & Chr(34) & ", " & Chr(34) & 
txtArchitectComments.Text & Chr(34) & ", " & Chr(34) & 
cmbArchitect.SelectedItem & Chr(34) & ", '', '', '', '', " & Chr(34) & 
cmbGrant.SelectedItem & Chr(34) & ", '', '', '', '', '', '', '', '', " & 
Me.projDesc & ");"
 
 
 
        connection.ConnectionString = strconn
        connection.Open()
 
 
        Dim command As New OleDb.OleDbCommand
 
 
        command.CommandText = strSQL
 
        command.Connection = connection
 
        command.ExecuteNonQuery()
 
        MsgBox("Project Information Inserted Successfully.", MsgBoxStyle.Information)
 
End Sub

With an error occuring on the command.ExecuteNonQuery() line.

Thanks in advance for any help.

[Mike Hildner]

Just a guess, but there might be something bad with your statement. It's hard to read, anyway. A couple things that might help:

- Use try...catch - catching a SqlException. Then use the Message property of the SqlExcepton to get some useful message on what's wrong.

- Use parameters instead of "sql string" & Text1.Text & "more sql stuff" - much easier to read and maintain.

- Not sure about Access as I don't use it, but do you need the semi-colon at the end of the statement when using a OleDBCommand?

[salvelinus]

Couple possibilities:
Are you inserting into every field in the table? I'm guessing that's what the blanks between single quotes indicate. It might be easier to specify the fields. You have to if you're not putting values in all the fields.
Make sure the values you insert match the datatypes in the Access table. For example, when inserting a combo.selecteditem, you may want to cstr() it first, if the Access ifield is text.
Put in a msgbox to view your SQL before executing it, see if it's ok.

[ERage]

Thanks for the comments. Used the Try Catch method to figure out that one of my datatypes was incorrect and changed my SQL string to include just the fields which were being used which ultimately fixed the problem. Thanks all for helping out a newbie.