9 Replies - 599 Views - Last Post: 24 August 2013 - 02:33 AM Rate Topic: -----

#1 cbu007zn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 20-August 13

I am getting an error "Syntax error in Update statement" code

Posted 20 August 2013 - 05:20 AM

Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter
Dim sql As String
Dim inc As Integer
Dim MaxRows As Integer


Private Sub Emp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    dbProvider = "Provider=Microsoft.Ace.OLEDB.12.0;"
    dbSource = "Data Source = C:\Users\Blessing\Documents\IBCARIP.accdb"
    con.ConnectionString = dbProvider & dbSource
    con.Open()
    sql = "SELECT * From Employees"
    da = New OleDb.OleDbDataAdapter(sql, con)
    da.Fill(ds, "IBCARIP")
    'con.Close()
    txtID.Text = ds.Tables("IBCARIP").Rows(0).Item(0)
    txtName.Text = ds.Tables("IBCARIP").Rows(0).Item(1)
    MaxRows = ds.Tables("IBCARIP").Rows.Count
    inc = -1
End Sub

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    If InputBox("Please enter Clearance Level 1 Code", ) <> "me" Then
        MsgBox("Invalid clearence code entered. Please make sure you have enough previlegies to perfom this operation..!", MsgBoxStyle.OkOnly)
    Else
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("IBCARIP").Rows(inc).Item(0) = txtID.Text
        ds.Tables("IBCARIP").Rows(inc).Item(1) = txtName.Text
        da.Update(ds, "IBCARIP")   <<<<<<<<<<<<<<<<<<<<<<<<<<<<SYNTAX ERROR IN UPDATE STATEMENT
    End If
End Sub

This post has been edited by JackOfAllTrades: 23 August 2013 - 04:56 AM
Reason for edit:: Added code tags


Is This A Good Question/Topic? 0
  • +

Replies To: I am getting an error "Syntax error in Update statement" code

#2 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 2,998
  • Joined: 12-January 10

Re: I am getting an error "Syntax error in Update statement" code

Posted 20 August 2013 - 05:32 AM

a) use [code] tags
B) list your entire error
Was This Post Helpful? 0
  • +
  • -

#3 deery5000  Icon User is offline

  • D.I.C Addict

Reputation: 78
  • View blog
  • Posts: 966
  • Joined: 09-May 09

Re: I am getting an error "Syntax error in Update statement" code

Posted 20 August 2013 - 09:17 AM

Dim ds As New DataSet
 Dim da As New OleDb.OleDbDataAdapter
 Dim sql As String
 Dim inc As Integer
 Dim MaxRows As Integer
 

Private Sub Emp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
dbProvider = "Provider=Microsoft.Ace.OLEDB.12.0;"
 dbSource = "Data Source = C:\Users\Blessing\Documents\IBCARIP.accdb"
 con.ConnectionString = dbProvider & dbSource
 con.Open()
 sql = "SELECT * From Employees"
 da = New OleDb.OleDbDataAdapter(sql, con)
 da.Fill(ds, "IBCARIP")
 'con.Close()
 txtID.Text = ds.Tables("IBCARIP").Rows(0).Item(0)
 txtName.Text = ds.Tables("IBCARIP").Rows(0).Item(1)
 MaxRows = ds.Tables("IBCARIP").Rows.Count
 inc = -1
 End Sub
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
If InputBox("Please enter Clearance Level 1 Code", ) <> "me" Then
 MsgBox("Invalid clearence code entered. Please make sure you have enough previlegies to perfom this operation..!", MsgBoxStyle.OkOnly)
 Else
 Dim cb As New OleDb.OleDbCommandBuilder(da)
 ds.Tables("IBCARIP").Rows(inc).Item(0) = txtID.Text
 ds.Tables("IBCARIP").Rows(inc).Item(1) = txtName.Text
 da.Update(ds, "IBCARIP") <<<<<<<<<<<<<<<<<<<<<<<<<<<<SYNTAX ERROR IN UPDATE STATEMENT
 End If
 

Was This Post Helpful? 0
  • +
  • -

#4 jimzcoder  Icon User is online

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 331
  • Joined: 14-November 12

Re: I am getting an error "Syntax error in Update statement" code

Posted 20 August 2013 - 05:24 PM

check the value of your inc variable..
as i can see "inc" is still=-1.
that might help.

This post has been edited by andrewsw: 24 August 2013 - 02:38 AM
Reason for edit:: Removed previous quote

Was This Post Helpful? 1
  • +
  • -

#5 _ELement_8215  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 34
  • Joined: 14-December 11

Re: I am getting an error "Syntax error in Update statement" code

Posted 21 August 2013 - 08:19 AM

Did you mean to increment "inc" in some sort of loop to keep track of rows? Because I don't see that anywhere in the posted code.
Was This Post Helpful? 0
  • +
  • -

#6 deery5000  Icon User is offline

  • D.I.C Addict

Reputation: 78
  • View blog
  • Posts: 966
  • Joined: 09-May 09

Re: I am getting an error "Syntax error in Update statement" code

Posted 21 August 2013 - 08:51 AM

line 21

inc = -1




Have a look at these lines

ds.Tables("IBCARIP").Rows(inc).Item(0) = txtID.Text
ds.Tables("IBCARIP").Rows(inc).Item(1) = txtName.Text




I believe this is what jimzcoder is getting at :)

This post has been edited by deery5000: 21 August 2013 - 08:54 AM

Was This Post Helpful? 0
  • +
  • -

#7 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

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

Re: I am getting an error "Syntax error in Update statement" code

Posted 21 August 2013 - 01:42 PM

Make sure your employee table doesn't contain any fields with spaces and non are considered keywords. (ie, Password is a keyword) and will throw the generic "Syntax Error in Update Statement" when the command builder builds the update statement.
Was This Post Helpful? 0
  • +
  • -

#8 jimzcoder  Icon User is online

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 331
  • Joined: 14-November 12

Re: I am getting an error "Syntax error in Update statement" code

Posted 21 August 2013 - 07:12 PM

as i can see. you assigned your "inc" variable to -1 and used it on below code.
 ds.Tables("IBCARIP").Rows(inc).Item(0) = txtID.Text


this is how it will look like then:
ds.Tables("IBCARIP").Rows(-1).Item(0) = txtID.Text


 ds.Tables("IBCARIP").Rows(inc).Item(1) = txtName.Text



but i dont see any line where the value of your "inc" could/should be changed.
so i assume, the value is still -1.
since the index is as of "inc" = -1 then your update statement will be applied to the rowIndex -1 which i think is not valid.

I hope you got my point. im just tryin to help.
:)/>/>

This post has been edited by jimzcoder: 21 August 2013 - 07:17 PM

Was This Post Helpful? 0
  • +
  • -

#9 cbu007zn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 20-August 13

Re: I am getting an error "Syntax error in Update statement" code

Posted 23 August 2013 - 04:53 AM

thanks a lot jimz coder

i applied ur fix
Was This Post Helpful? 0
  • +
  • -

#10 jimzcoder  Icon User is online

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 331
  • Joined: 14-November 12

Re: I am getting an error "Syntax error in Update statement" code

Posted 24 August 2013 - 02:33 AM

you're welcome cbu007zn. im glad i was able to help.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1