[Sgt. Cookie]

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.

[nK0de]

so where is the INSERT INTO statement in your code?

[Sgt. Cookie]

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?

[smohd]

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.

[Sgt. Cookie]

I think that makes sense. Thanks.


Though, how would that interfere with adding new data to the table via VB?

[Ionut]

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)

[Sgt. Cookie]

The primary key is not an auto number, no. It is more akain to a username.

[CharlieMay]

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.

[Sgt. Cookie]

That worked perfectly, thank you.