5 Replies - 3275 Views - Last Post: 14 March 2011 - 04:22 AM Rate Topic: -----

#1 SupriyaB  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-March 11

Not able able to insert data into access table

Posted 13 March 2011 - 04:17 AM

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()



Is This A Good Question/Topic? 0
  • +

Replies To: Not able able to insert data into access table

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Not able able to insert data into access table

Posted 13 March 2011 - 05:18 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Not able able to insert data into access table

Posted 13 March 2011 - 05:25 AM

Can you post the datatype of every column?
Was This Post Helpful? 1
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Not able able to insert data into access table

Posted 13 March 2011 - 06:05 AM

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

Was This Post Helpful? 1
  • +
  • -

#5 SupriyaB  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-March 11

Re: Not able able to insert data into access table

Posted 13 March 2011 - 11:57 PM

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.
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Not able able to insert data into access table

Posted 14 March 2011 - 04:22 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1