Resolved INSERT INTO, Error?

[DTruth]

I searched the forum looking for a answer this question sorry if this was asked to many times. I'm trying to create a new record in the database and have it same, pretty simple I thought.
The program states that the problem is a INSERT INTO error but points toward insertCommand.ExecuteNonQuery() line. I'm not completely sure where I went wrong within that INSERT INTO line. I first thought it might be the variables User and Password in the Values line but changing it gets me nowhere.

Dim Connection As New OleDb.OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0; Data Source = C:\Password.accdb")
        Dim insertCommand As New OleDb.OleDbCommand("INSERT INTO Password (User ID, Password) VALUES (User, Password)", Connection)


        Try
            Connection.Open()
            insertCommand.ExecuteNonQuery()


            Me.Validate()
            Me.PasswordBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.PasswordDataSet)
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        Finally
            Connection.Close()
        End Try

 

[JuggaloBrotha]

I searched the forum looking for a answer this question sorry if this was asked to many times. I'm trying to create a new record in the database and have it same, pretty simple I thought.
The program states that the problem is a INSERT INTO error but points toward insertCommand.ExecuteNonQuery() line. I'm not completely sure where I went wrong within that INSERT INTO line. I first thought it might be the variables User and Password in the Values line but changing it gets me nowhere.

Dim Connection As New OleDb.OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0; Data Source = C:\Password.accdb")
        Dim insertCommand As New OleDb.OleDbCommand("INSERT INTO Password (User ID, Password) VALUES (User, Password)", Connection)


        Try
            Connection.Open()
            insertCommand.ExecuteNonQuery()


            Me.Validate()
            Me.PasswordBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.PasswordDataSet)
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        Finally
            Connection.Close()
        End Try

This part: VALUES (User, Password)
should be either: VALUES ('User', 'Password')
or: VALUES (@User, @Password)

If you're simply hard coding the values for the two fields then you need the single quotes around the user and the password

If you're going for a parametrized query then the @User is the name of the parameter that you'll be getting and setting before actually inserting the record.

 

[thirteentwenty]

I was having similar issues with my dbase, this tread on Parameterized queries helped tons.

 

[DTruth]

This part: VALUES (User, Password)
should be either: VALUES ('User', 'Password')
or: VALUES (@User, @Password)

I add in the (@User, @Password) to access the predefined variables but I am still gaining the same errors.

 

[JuggaloBrotha]

Here's an example of how I do it:

Dim Connection As New OleDb.OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0; Data Source = C:\Password.accdb")
Dim insertCommand As New OleDb.OleDbCommand("INSERT INTO Password (User ID, Password) VALUES (@User, @Password);", Connection)
Try
    Connection.Open()

    With insertCommand
        .Parameters.Add("@User", System.Data.OleDb.OleDbType.VarWChar)
        .Parameters.Add("@Password", System.Data.OleDb.OleDbType.VarWChar)

        .Parameters("@User").Value = UserTextBox.Text.Trim
        .Parameters("@Password").Value = PassWordTextBox.Text.Trim

        .ExecuteNonQuery()
    End With

    Me.Validate()
    Me.PasswordBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.PasswordDataSet)
Catch ex As Exception
    MessageBox.Show(ex.ToString())
Finally
    Connection.Close()
End Try

 

[DTruth]

No matter what I am still receiving the error saying that it is a syntax error in the Insert Into statement even with the code you provided.

 

[JuggaloBrotha]

Try this: "INSERT INTO Password ([User ID], Password) VALUES (@User, @Password);" if that works then here's a tip, don't put spaces in field or table names.

If that doesn't work then check your table names, if you don't have a table named 'Password' there's a problem. Also check the fields inside the table too.

 

[DTruth]

Thank you, I got it to work after making a new database with a couple corrections.

 

[JuggaloBrotha]

Thank you, I got it to work after making a new database with a couple corrections.

Glad to hear it, so what was causing the error anyways?

 

[DTruth]

Not completely sure but I believe it was due to the name of database matched the name of the table.

 

[cjard]

In Oracle, leaving a ; in a statement breaks it..