[RESOLVED] SQL help! What am i doing wrong?

[dinosaur_uk]

Hi all,

The following code

    Function AddToDatabase(ByVal TAG As String, ByVal EquipmentType As String, ByVal Description As String, ByVal Status1 As String, ByVal Status2 As String, ByVal DateChanged As Date, ByVal Comments As String, ByVal UserID As String) As Boolean
        'Write to the Database
        Try
            Dim connectionSQL As New OleDbConnection(strSQLconn & DataSourcePath)
            Dim command As New OleDbCommand("INSERT INTO PMTStatus (EquipmentType, TAG, Descriptor, Status1, Status2, DateChanged, Comments, UserID) VALUES (@EquipmentType, @TAG, @Descriptor, @Status1, @Status2, @DateChanged, @Comments, @UserID", connectionSQL)
            connectionSQL.Open()
            MsgBox(connectionSQL.State.ToString)

            With command.Parameters
                .AddWithValue("@EquipmentType", EquipmentType)
                .AddWithValue("@TAG", TAG)
                .AddWithValue("@Descriptor", Description)
                .AddWithValue("@Status1", Status1)
                .AddWithValue("@Status2", Status2)
                .AddWithValue("@DateChanged", "01/01/2010")
                .AddWithValue("@Comments", Comments)
                .AddWithValue("@Userid", UserID)
            End With
            command.ExecuteNonQuery()
            'command.Dispose()
            'Need to reset the form for the next one
            Return True
        Catch ex As Exception
            MsgBox(ex.ToString)
            Clipboard.SetText(ex.ToString)

            Return False
        End Try
    End Function

Generates this error

System.Data.OleDb.OleDbException was caught
  ErrorCode=-2147217900
  Message="Syntax error in INSERT INTO statement."
  Source="Microsoft JET Database Engine"
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at Tool.clsSQL.AddToDatabase(String TAG, String EquipmentType, String Description, String Status1, String Status2, DateTime DateChanged, String Comments, String UserID) in C:\Documents and Settings\dinoken\My Documents\Visual Studio 2005\Projects\Tool\clsSQL.vb:line 37

Any ideas folks?

[ForumAccount]

You forgot the closing bracket at the end of the SQL string (your parameter list).

[stanav]

What database are you using?

[stateofidleness]

As ForumAccount stated, you are missing the closing parenthesis in your Command string:

Dim command As New OleDbCommand("INSERT INTO PMTStatus (EquipmentType, TAG, Descriptor, Status1, Status2, DateChanged, Comments, UserID) VALUES (@EquipmentType, @TAG, @Descriptor, @Status1, @Status2, @DateChanged, @Comments, @UserID)", connectionSQL)

[ForumAccount]

As ForumAccount stated, you are missing the closing parenthesis in your Command string:

Dim command As New OleDbCommand("INSERT INTO PMTStatus (EquipmentType, TAG, Descriptor, Status1, Status2, DateChanged, Comments, UserID) VALUES (@EquipmentType, @TAG, @Descriptor, @Status1, @Status2, @DateChanged, @Comments, @UserID", connectionSQL))

Wrong place, it's after @UserID that he is missing it.

[stateofidleness]

*sung like Jack Black*

yesss but I was just testing you... ittttt''sssss 999999

heh.

I edited it.

[ForumAccount]

"And that's a magic numberrrr?"

You're not going to like this but its still wrong

Dim command As New OleDbCommand("INSERT INTO PMTStatus (EquipmentType, TAG, Descriptor, Status1, Status2, DateChanged, Comments, UserID) VALUES (@EquipmentType, @TAG, @Descriptor, @Status1, @Status2, @DateChanged, @Comments, @UserID)", connectionSQL)

[stateofidleness]

well maybe I just like talking to you.

/hate_sql 1

[stanav]

The problem could also be the use of database reserved words for column names... For example, "descriptor" is a reserved word in DB2, ODBC, PostgreSQL 8 and some other DB's. That's why besides the obvious syntax error, you also need to check the field names.

[dinosaur_uk]

Ahhh all resolved guys!

Thanks, it was the )