user management in access

[jnash]

im having problems i never thought i would however im trying to add two values from a form (txtAname.text & txtPword.text) in to a access table (tblUsers) using Username & Password as the fields,
when run the code breaks at executeNonQuery

and displays : An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

this is my code so far
thanks in advance:

Dim conn As New OleDb.OleDbConnection(" Provider= Microsoft.JET.OLEDB.4.0; Data Source=db.mdb")


    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        ' Setup Connection Object; con will hold the connection object
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter    ' Data adapter
        Dim sql As String

        Dim cmd As New OleDb.OleDbCommand("INSERT INTO tblusers (Username, Password)VALUES('" & txtAname.Text & "','" & txtApassword.Text & "')", conn)

        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

 

[jmcilhinney]

Your actual is arsing because "password" is an Access reserved word so your column name is being interpreted as something else. Had you examined the the exception more closely it would have told you that you had a syntax error in your SQL code. This may not have been any clearer but it's because the reserved word "password" was found where a column name was expected. Whenever you get a syntax error in what looks like correct SQL you should always start looking for reserved words as column names.

To fix it you need to enclose the column name in square brackets so that it's interpreted as an identifier rather than a reserved word. Many people just put square brackets around every table and column name to make sure this can never be an issue.

Apart from that, you are doing something that is a criminal offence in some countries: building an SQL statement using string concatenation. Instead of this:

Dim cmd As New OleDb.OleDbCommand("INSERT INTO tblusers (Username, Password)VALUES('" & txtAname.Text & "','" & txtApassword.Text & "')", conn)

you should do this:

Dim cmd As New OleDb.OleDbCommand("INSERT INTO tblusers (Username, [Password]) VALUES (@Username, @Password)", conn)

cmd.Parameters.Add("@Username", txtAname.Text)
cmd.Parameters.Add("@Password", txtApassword.Text)

 

[jnash]

ive just been told that im using 2005 i imported it and upgraded and now its depreceated!!!!!

 

[vis781]

jnash, assuming that you don't need your other duplicate thread i shall delete it. Please do not make duplicate threads, if you give us a bit of time then you will get an answer to your query.

 

[jmcilhinney]

If you're using 2005 then please change your profile to reflect that or else specify when posting.

Did you read what the warning message said? It says use AddWithValue instead because that overload of Add is obsolete. If the IDE tells you how to fix your issues then there's no need to look any further.

 

[jnash]

thank you it works now!