3 Replies - 3879 Views - Last Post: 11 June 2012 - 10:01 PM Rate Topic: -----

#1 purvabhilare1587  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-May 12

Syntax error in insert into statement

Posted 09 June 2012 - 03:37 AM

I am trying to add a new record in a database.When I click the submit button I get an OleDbException Unhandled error.
It points to the Update() function in the code and says Syntax Error in Insert Into Statement.FOllowing is my code to add a record.

 Private Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click

 Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim dsNewRow As DataRow

       
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=C:\Users\technowin\Documents\Candidates.accdb"
        con.Open()

        sql = "SELECT * FROM Infotable"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Candidate")
       
        Dim cb As New OleDb.OleDbCommandBuilder(da)


        dsNewRow = ds.Tables("Candidate").NewRow()

        dsNewRow.Item("LastName") = LastNameTextBox.Text
        dsNewRow.Item("FirstName") = FirstNameTextBox.Text
        dsNewRow.Item("MiddleName") = MiddleNameTextBox.Text
        ds.Tables("Candidate").Rows.Add(dsNewRow)

        da.Update(ds, "Candidate")
        MsgBox("New record added to the database")

        MsgBox("Database is now open")
        con.Close()
        MsgBox("Database is now closed")
        ' sql = "INSERT INTO Infotable(LastName,PhoneNo) VALUES('" & LastNameTextBox.Text & "', '" & PhoneNoTextBox.Text & "')"

    End Sub





Is This A Good Question/Topic? 0
  • +

Replies To: Syntax error in insert into statement

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9091
  • View blog
  • Posts: 34,142
  • Joined: 12-June 08

Re: Syntax error in insert into statement

Posted 09 June 2012 - 05:44 AM

You need to actually specify a oledb command object with an UPDATE statement and execute it.. similar to how your SELECT statement works.


OleDb Basics in VB.Net Rate Topic

A Really Simple Database Create a Database using Access & VB.net Express 2008
Was This Post Helpful? 0
  • +
  • -

#3 purvabhilare1587  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-May 12

Re: Syntax error in insert into statement

Posted 11 June 2012 - 01:44 AM

View Postmodi123_1, on 09 June 2012 - 05:44 AM, said:

You need to actually specify a oledb command object with an UPDATE statement and execute it.. similar to how your SELECT statement works.


OleDb Basics in VB.Net Rate Topic

A Really Simple Database Create a Database using Access & VB.net Express 2008


View Postmodi123_1, on 09 June 2012 - 05:44 AM, said:

You need to actually specify a oledb command object with an UPDATE statement and execute it.. similar to how your SELECT statement works.


OleDb Basics in VB.Net Rate Topic

A Really Simple Database Create a Database using Access & VB.net Express 2008



But I am using an oledb commandbuilder object.
Was This Post Helpful? 0
  • +
  • -

#4 purvabhilare1587  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-May 12

Re: Syntax error in insert into statement

Posted 11 June 2012 - 10:01 PM

View Postmodi123_1, on 09 June 2012 - 05:44 AM, said:

You need to actually specify a oledb command object with an UPDATE statement and execute it.. similar to how your SELECT statement works.


OleDb Basics in VB.Net Rate Topic

A Really Simple Database Create a Database using Access & VB.net Express 2008



Hey thanks for your time.But my problem is solved.Instead of the commandbuilder I used a parameterized query.My code is as
follows:


 Private Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click

 Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=C:\Users\technowin\Documents\Candidates.accdb"
        con.Open()
 Dim cmdText As String = "INSERT INTO Infotable (LastName, FirstName, MiddleName, Address,PhoneNo, AlternativeNumber, LanguagesKnown)" & "Values(?,?,?,?,?,?,?)"
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con)
        cmd.CommandType = CommandType.Text

        With cmd.Parameters
            .Add("@LastName", OleDb.OleDbType.VarChar).Value = LastNameTextBox.Text
            .Add("@FirstName", OleDb.OleDbType.VarChar).Value = FirstNameTextBox.Text
            .Add("@MiddleName", OleDb.OleDbType.VarChar).Value = MiddleNameTextBox.Text
            .Add("@Address", OleDb.OleDbType.VarChar).Value = AddressRichTextBox.Text
            .Add("@PhoneNo", OleDb.OleDbType.Numeric).Value = PhoneNoTextBox.Text
            .Add("@AlternativeNumber", OleDb.OleDbType.Numeric).Value = AlternativePhoneNoTextBox.Text

        End With
        cmd.ExecuteNonQuery()
        MsgBox("New record added to the database")
 End Sub


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1