Question Inserting record Error

[mparker1061]

When I try to insert a record into an Access 2010 database, I get the error "Syntax error in INSERT INTO statement". If I copy the SQL and run it using a query in Access, the record inserts fine. The SQL statement is:
INSERT INTO tbDevices(DeviceName, DeviceType, ClosedLS, OpenLS, Output, Route, Dest0) VALUES('A1004', 'S', 'B3/9', 'B3/10', 'B3/11', 'N7:3', 'A1005')

The following code:
PrivateSub cmdNew_Click(ByVal sender AsObject, ByVal e As System.EventArgs) Handles cmdNew.Click
Dim cnCon AsOleDbCommand
Dim strSql AsString
Dim strInsert AsString
Dim strValues AsString
If Trim$(txtDeviceName.Text) = ""Then
MsgBox("Please enter a valid device name.", MsgBoxStyle.Exclamation)
Exit Sub
ElseIf cbType.SelectedIndex < 0 Then
MsgBox("Please select a valid device type.", MsgBoxStyle.Exclamation)
Exit Sub
ElseIf Trim$(txtOutput.Text) = ""Then
MsgBox("Please enter a valid output.", MsgBoxStyle.Exclamation)
Exit Sub
ElseIf Trim$(txtRoute.Text) = ""Then
MsgBox("Please enter a valid route.", MsgBoxStyle.Exclamation)
Exit Sub
ElseIf cbType.Text <> "D"And Trim$(txtDest0.Text) = ""Then
MsgBox("Please enter a valid destination 0.", MsgBoxStyle.Exclamation)
Exit Sub
EndIf
Try
strInsert = "INSERT INTO tbDevices(DeviceName, DeviceType, "
strValues = "VALUES('" & UCase$(txtDeviceName.Text) & "', '" & cbType.Text & "', '"
If Trim$(txtCLS.Text) <> ""Then
strInsert += "ClosedLS, "
strValues += UCase$(txtCLS.Text) & "', '"
EndIf
If Trim$(txtOLS.Text) <> ""Then
strInsert += "OpenLS, "
strValues += UCase$(txtOLS.Text) & "', '"
EndIf
strInsert += "Output, Route"
strValues += UCase$(txtOutput.Text) & "', '" & UCase$(txtRoute.Text) & "'"
If Trim$(txtDest0.Text) <> ""Then
strInsert += ", Dest0"
strValues += ", '" & UCase$(txtDest0.Text) & "'"
EndIf
If Trim$(txtDest1.Text) <> ""Then
strInsert += ", Dest1"
strValues += ", '" & UCase$(txtDest1.Text) & "'"
EndIf
strInsert += ") "
strValues += ")"
strSql = strInsert & strValues
gsCon.Open()
cnCon = NewOleDbCommand(strSql, gsCon)
cnCon.ExecuteNonQuery()
gsCon.Close()
Call ClearForm()
Catch ex AsException
gsCon.Close()
MsgBox(Err.Description)
EndTry
EndSub

gsCon is defined:
Private gsCon AsOleDbConnection

gsCon = NewOleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Environment.CurrentDirectory & "\Devices.mdb;Persist Security Info=False")


Any suggestions?

 

[InertiaM]

Any suggestions?

Several.

1. Use parameters for your queries - do NOT compile SQL statements as you have been doing. Follow the link in my signature for more information.

2. Try and use more .NET orientated functions, rather than the old VB6 (and earlier) versions. You will be amazed at how much faster you can code with the Intellisense AutoComplete. For example :-

UCase$(txtDeviceName.Text)

is much faster when typed as

txtDeviceName.Text.ToUpper

and also you can use

txtDeviceName.Text.Trim.ToUpper

IMHO it also looks better


3. Not sure on the answer to your problem, but should it be "tbDevices (" ie with a space rather than "tbDevices("

 

[mparker1061]

I modified the code and got the same error.

Private Sub cmdNew_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdNew.Click
Dim cnCon As OleDbCommand
Dim strSql As String
Dim strInsert As String
Dim strValues As String
If txtDeviceName.Text.Length = 0 Then
MsgBox("Please enter a valid device name.", MsgBoxStyle.Exclamation)
Exit Sub
ElseIf cbType.SelectedIndex < 0 Then
MsgBox("Please select a valid device type.", MsgBoxStyle.Exclamation)
Exit Sub
ElseIf txtOutput.Text.Length = 0 Then
MsgBox("Please enter a valid output.", MsgBoxStyle.Exclamation)
Exit Sub
ElseIf txtRoute.Text.Length = 0 Then
MsgBox("Please enter a valid route.", MsgBoxStyle.Exclamation)
Exit Sub
ElseIf cbType.Text <> "D" And txtDest0.Text.Length = 0 Then
MsgBox("Please enter a valid destination 0.", MsgBoxStyle.Exclamation)
Exit Sub
End If
Try
strInsert = "INSERT INTO tbDevices(DeviceName, DeviceType, "
strValues = "VALUES(?, ?"
If txtCLS.Text.Length <> 0 Then
strInsert += "ClosedLS, "
strValues += txtCLS.Text.ToUpper & "', '"
End If
If txtOLS.Text.Length <> 0 Then
strInsert += "OpenLS, "
strValues += ", ?"
End If
strInsert += "Output, Route"
strValues += ", ?, ?"
If txtDest0.Text.Length <> 0 Then
strInsert += ", Dest0"
strValues += ", ?"
End If
If txtDest1.Text.Length <> 0 Then
strInsert += ", Dest1"
strValues += ", ?"
End If
strInsert += ") "
strValues += ")"
strSql = strInsert & strValues
gsCon.Open()
cnCon = New OleDbCommand(strSql, gsCon)
cnCon.Parameters.Add(New OleDb.OleDbParameter("DeviceName", txtDeviceName.Text.ToUpper))
cnCon.Parameters.Add(New OleDb.OleDbParameter("DeviceType", cbType.Text))
If txtCLS.Text.Length <> 0 Then
cnCon.Parameters.Add(New OleDb.OleDbParameter("ClosedLS", txtCLS.Text.ToUpper))
End If
If txtOLS.Text.Length <> 0 Then
cnCon.Parameters.Add(New OleDb.OleDbParameter("OpenLS", txtOLS.Text.ToUpper))
End If
cnCon.Parameters.Add(New OleDb.OleDbParameter("Output", txtOutput.Text.ToUpper))
cnCon.Parameters.Add(New OleDb.OleDbParameter("Route", txtRoute.Text.ToUpper))
If txtDest0.Text.Length <> 0 Then
cnCon.Parameters.Add(New OleDb.OleDbParameter("Dest0", txtDest0.Text.ToUpper))
End If
If txtDest1.Text.Length <> 0 Then
cnCon.Parameters.Add(New OleDb.OleDbParameter("Dest1", txtDest1.Text.ToUpper))
End If
cnCon.ExecuteNonQuery()
gsCon.Close()
Call ClearForm()
Catch ex As Exception
gsCon.Close()
MsgBox(Err.Description)
End Try
End Sub

 

[JohnH]

Debug the value of strSql before you run the command to validate its syntax. Post the value here if you can't see why yourself and someone can probably see to it.

 

[jmcilhinney]

Your method of building SQL is very error prone. This is cleaner:

Dim sql As String = "SELECT * FROM SomeTable WHERE (? IS NULL OR Column1 = ?) AND (? IS NULL OR Column2 = ?)"

Using connection As New OleDbConnection("connection string here"),
      adapter As New OleDbDataAdapter(sql, connection)
    Dim column1Value As Object = If(Me.TextBox1.TextLength = 0, CObj(DBNull.Value), Me.TextBox1.Text)
    Dim column2Value As Object = If(Me.TextBox2.TextLength = 0, CObj(DBNull.Value), Me.TextBox2.Text)

    With adapter.SelectCommand.Parameters
        .AddWithValue("p1_1", column1Value)
        .AddWithValue("p1_2", column1Value)
        .AddWithValue("p2_1", column2Value)
        .AddWithValue("p2_2", column2Value)
    End With
End Using