none
Updating Access Database RRS feed

  • Question

  • Hey guys,

     

    I have tryed something new code with updating an access database but the error

    Syntax error in UPDATE statement  occurs every time i try to update. Can some please help me figure out why this problem is happening. Here is the code

     

    Public Class Form1

     

    Dim con As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OleDB.4.0;Data Source = E:\WindowsApplication1\WindowsApplication1\bin\Debug\rl.mdb;")

     

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

     

    updatedata()

     

    End Sub

     

    Public Function updatedata()

     

    Dim str1 As String = "UPDATE playersnames SET Player Name = '" & TextBox1.Text & "' AND Meters = '" & TextBox2.Text & "'"

    Dim cmd As New OleDb.OleDbCommand(str1, con)

    con.Open()

    cmd.ExecuteNonQuery()

    cmd.Cancel()

    con.Close()

    intialization()

    intialization2()

     

    End Function

     

    Public Function intialization()

     

    Dim adp As New OleDb.OleDbDataAdapter("select * from playermeters", con)

    Dim dt As DataTable

    Dim ds As New DataSet

    adp.Fill(ds)

    dt = ds.Tables(0)

     

    End Function

     

    Public Function intialization2()

     

    Dim adp As New OleDb.OleDbDataAdapter("select * from playersmeters", con)

    Dim dr As DataRow

    Dim dt As DataTable

    Dim ds As New DataSet

    adp.Fill(ds)

    dt = ds.Tables(0)

     

    End Function

    Friday, June 29, 2007 12:45 PM

Answers

  • I see the problem!!! I think Smile

    Your column name has a space in  it, BIG no NO.  Try enclosing it in square brackets for tis time.  Next time give the column a name with an underscore to separate words or beeter still use CamelCase. PlayerName.

    Code Snippet

    Dim str1 As String = "UPDATE playersnames SET [Player Name] = '" & TextBox1.Text & "' AND Meters = '" & TextBox2.Text & "'"

     

    Hope I got the bug this time Smile

    Monday, July 2, 2007 8:09 AM

All replies

  • Hi Jayc1,

     

    On which line is the error and what is the error.

     

    Out of curiosity, Why did you put cmd.cancel in the update?

     

    I notice this is a continuation of another thread I tried to help you out on, did you figure the last one?  How come you have playermeters and playersmeters.  Are they two different tables?

     

    Cheers,

    Karl

    Friday, June 29, 2007 3:48 PM
  • Hey Karl Daviss thanks for replying again,

     

    The line of code which the error 'Syntax error in UPDATE statement ' is cmd.ExecuteNonQuery().

     

    I know ive been trying different ways on how to do it with access but it doesnt seem to work. I didnt really understand it, no ive only got one table which holds to columns of data 'Player Name' and 'Meters'. If you could work this out i would be very greatful

    Saturday, June 30, 2007 2:05 AM
  • Hi jayc1,
      I am moving this thread from Visual Basic Express Edition forum to the .NET Framework Data Access and Storage forum, since the issue is related to Access DataBase. There are more DataBase experts in the .NET Framework Data Access and Storage forum.
    Thanks for your understanding!
    Monday, July 2, 2007 3:55 AM
  • I see the problem!!! I think Smile

    Your column name has a space in  it, BIG no NO.  Try enclosing it in square brackets for tis time.  Next time give the column a name with an underscore to separate words or beeter still use CamelCase. PlayerName.

    Code Snippet

    Dim str1 As String = "UPDATE playersnames SET [Player Name] = '" & TextBox1.Text & "' AND Meters = '" & TextBox2.Text & "'"

     

    Hope I got the bug this time Smile

    Monday, July 2, 2007 8:09 AM