[SupriyaB]

I am trying to insert data into access table using VB.Net 2008. But each time I am getting error: "Syntax error in INSERT INTO statement".
I typed following code. Please suggest me if anyone knows the solution.

On Error GoTo errores
        Dim cn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim str As String
       
        cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath & "\EventsTable1.mdb")

        cn.Open()

        str = "insert into EventsTab1(Id, EnglishDate, MarathiDate, Time) values(@Id, @Date1, @Date2, @Time)" 

        cmd = New OleDbCommand
        cmd.CommandText = str
        cmd.Parameters.AddWithValue("@Id", "5")
        cmd.Parameters.AddWithValue("@Date1", "6/6/2011")
        cmd.Parameters.AddWithValue("@Date2", "Magh Shukla 6")
        cmd.Parameters.AddWithValue("@Time", "5:00 AM")
        

        cmd.Connection = cn
        cmd.ExecuteNonQuery()

errores:
       
        MsgBox("Error #" & "" & Err.Number & " " & Err.Description)
       
        cn.Close()

[raziel_]

put space between EventsTab1 and (ID, ... Also avoid using labels in VB.NET its poor style use Try-catch blocks:

        Dim cn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim str As String
       
        cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath & "\EventsTable1.mdb")

        cn.Open()

        str = "insert into EventsTab1 (Id, EnglishDate, MarathiDate, Time) values(@Id, @Date1, @Date2, @Time)" 

        cmd = New OleDbCommand
        cmd.CommandText = str
        cmd.Parameters.AddWithValue("@Id", "5")
        cmd.Parameters.AddWithValue("@Date1", "6/6/2011")
        cmd.Parameters.AddWithValue("@Date2", "Magh Shukla 6")
        cmd.Parameters.AddWithValue("@Time", "5:00 AM")
        
        Try
        cmd.Connection = cn
        cmd.ExecuteNonQuery()
        Catch ex As OledbException
        MessageBox.Show(ex.Message)
        End Try

This post has been edited by NoBrain: 13 March 2011 - 05:18 AM

[Ionut]

Can you post the datatype of every column?

[CharlieMay]

Try enclosing time in square brackets

str = "insert into EventsTab1(Id, EnglishDate, MarathiDate, [Time]) values(@Id, @Date1, @Date2, @Time)"

Time is a reserved keyword and it's confused by it thinking you're trying to perform some function that it doesn't have all the information for. By wrapping it in square brackets you are denoting it as a field.

This post has been edited by CharlieMay: 13 March 2011 - 06:11 AM

[SupriyaB]

Thanks all for replying.

I changed some of my code, & it's working.

        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
       
        cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath & "\EventsTable1.mdb")

        
        cn.Open()

        str = "INSERT INTO EventsTab1 VALUES ('3', '4/5/2011', 'Chaitra Shukla 5', '9:00 AM')"
        
        da = New OleDbDataAdapter(str, cn)
        da.Fill(ds, "EventsTab1")

        cn.Close()

When I was trying Insert statement like :
str = "insert into EventsTab1 (Id, EnglishDate, MarathiDate, Time) values ('3', '4/5/2011', 'Chaitra Shukla 5', '9:00 AM')"
I was getting error : Syntax error in Insert Into statement.
When I changed it to: str = "INSERT INTO EventsTab1 VALUES ('3', '4/5/2011', 'Chaitra Shukla 5', '9:00 AM')" , it is working.
I don't understand why.

NoBrain, I will remember about try-catch, thanks.

[CharlieMay]

It is because Time is a keyword and the sql statment thinks you are trying to use the time function. When you wrap your Fields in square brackets you are telling the interpreter that these are fields.

If you read my post above the solution was there. The reason your code is working is because you are no longer specifying fields and letting the interpreter do it for you which it handles by defaulting to all fields in the database being wrapped with square brackets.

Re-read my original post, the solution is there.