8 Replies - 2399 Views - Last Post: 29 February 2012 - 05:35 AM Rate Topic: -----

#1 Sgt. Cookie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 28-February 12

Issue: Syntax error in INSERT INTO statement

Posted 28 February 2012 - 03:12 AM

Hi. I am trying to use Visual Basic 2008 to create a game. So far, everything has been working well. I tried to sync up a database to the game but all I am coming up with is Syntax error in INSERT INTO statement.

Here is the code in total:

Public Class frmRegister

    Dim intRegCnt

    Private Sub Check_username()
        Dim Con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String


        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data source = Stock_database.mdb"

        Con.ConnectionString = dbProvider & dbSource

        Con.Open()
        sql = "SELECT * FROM Login_Form where User_ID = '" & txtUsername.Text & "'"
        da = New OleDb.OleDbDataAdapter(sql, Con)
        da.Fill(ds, "UserRegister")

        Con.Close()
        intRegCnt = ds.Tables("UserRegister").Rows.Count

        If intRegCnt > 0 Then
            MsgBox("The username " & txtUsername.Text & " already exists")
        Else
            dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
            dbSource = "Data source = Stock_database.mdb"

            Con.ConnectionString = dbProvider & dbSource


            Con.Open()
            sql = "SELECT * FROM Login_Form"
            da = New OleDb.OleDbDataAdapter(sql, Con)
            da.Fill(ds, "UserRegister")

            Con.Close()


           

            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

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

            dsNewRow.Item("User_ID") = txtUsername.Text
            dsNewRow.Item("Password") = txtPassword.Text

            ds.Tables("UserRegister").Rows.Add(dsNewRow)

            da.Update(ds, "UserRegister")

            

        End If

    End Sub

    Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegister.Click
        Check_username()
    End Sub
End Class


The exact line that is throwing the error is:

da.Update(ds, "UserRegister")


What is the issue here? I have tried searching for information, but have so far come up blank.

Is This A Good Question/Topic? 0
  • +

Replies To: Issue: Syntax error in INSERT INTO statement

#2 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Issue: Syntax error in INSERT INTO statement

Posted 28 February 2012 - 03:26 AM

so where is the INSERT INTO statement in your code?
Was This Post Helpful? 0
  • +
  • -

#3 Sgt. Cookie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 28-February 12

Re: Issue: Syntax error in INSERT INTO statement

Posted 28 February 2012 - 03:34 AM

I was following a tutorial that didn't use one. So, er, there isn't one. That was the name of the error VB was throwing at me.

I am guessing that that is where my issues lie?
Was This Post Helpful? 0
  • +
  • -

#4 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Issue: Syntax error in INSERT INTO statement

Posted 28 February 2012 - 04:17 AM

Few things to note here:
- There is no need everytime to create the connection string. You have given them in the first place, no need again.

- The sql Adapter creates the INSERT, UPDATE and DELETE statements for you according to the SELECT statement you passed to it. And those statements are the one used when you call Update() method.
But those statements are only created correctly if the SELECT Statement shows what is the primary key of the table, otherwise you will get errors or bad queries. So your SELECT statement is:
sql = "SELECT * FROM Login_Form"

Doesnt say what is the primary key of the table. It expects something like:
sql = "SELECT * FROM Login_Form where User_ID = ''"

Even for something that will match everyting....

- Dont use "*" to return things, especially for tables with sensitive data like password, specify columns and use parameters for your query.
Was This Post Helpful? 1
  • +
  • -

#5 Sgt. Cookie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 28-February 12

Re: Issue: Syntax error in INSERT INTO statement

Posted 28 February 2012 - 04:27 AM

I think that makes sense. Thanks.


Though, how would that interfere with adding new data to the table via VB?
Was This Post Helpful? 0
  • +
  • -

#6 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

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

Re: Issue: Syntax error in INSERT INTO statement

Posted 28 February 2012 - 08:52 AM

What is your table schema? Does it have only 2 columns? the primary key is autoincrement/identity? If there are other columns, do they allow null? If you use, "*" make sure you put values for all columns(not just for those two)
Was This Post Helpful? 0
  • +
  • -

#7 Sgt. Cookie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 28-February 12

Re: Issue: Syntax error in INSERT INTO statement

Posted 29 February 2012 - 02:26 AM

The primary key is not an auto number, no. It is more akain to a username.
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

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

Re: Issue: Syntax error in INSERT INTO statement

Posted 29 February 2012 - 05:11 AM

OK this generic syntax error message usually appears when using the command builder when a reserved word is being used as a column name. (ie, Password). Before you make a bunch of changes, go into your database and rename password column to UserPassword, fix all occurrences of Password to UserPassword in your code and see if still receive the same generic "Syntax Error In Insert Statement" error.
Was This Post Helpful? 1
  • +
  • -

#9 Sgt. Cookie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 28-February 12

Re: Issue: Syntax error in INSERT INTO statement

Posted 29 February 2012 - 05:35 AM

That worked perfectly, thank you.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1