User accounts in VB2005 from MS Access

[Akumos]

No error message, just doesn't add to my database which is how I knew it didn't work.

Will try your ammendments... and study the basics.

 

[iStink]

Also, instead of using those strings you can just directly point at the textboxes.

The reason I convert the textbox's contents into a string variable is to work with them easier and to give the user a sense of stability. We run the replace command against the variable instead of the string so we don't interfere with what the user entered (in case they mess up the password, we could accidentally throw them off even more.) For instance, if someone entered a password which contained an apostrophe, it would mess up the SQL statement if you pointed directly to the textbox. The SQL statement would cut off the password string prematurely because it uses apostrophes, not quotes, to wrap around text and indicate a string.

Saying textbox1.text = replace(textbox1.text, "'", "''") will actually change text within the textbox, while saying textVar = replace(textbox1.text, "'", "''") will simply adjust the variable and the user won't see it.

If you enter '' (double apostrophe) into an SQL statement, it gets treated as a single apostrophe within a string, instead of the single apostrophe used to wrap around strings. Know what I mean?

As far as his error, it has to do with how he's executing the command. The code I posted is for a reader, now you need to use ExecuteNonQuery.

The code should look something like this:

Dim usernameString as string = usernameTextBox.text
Dim passwordString as string = passwordTextBox.text

usernameString = replace(usernameString, "'", "''")
passwordString = replace(passwordString, "'", "''")

Dim SQLString as string = "INSERT INTO USERS (USERNAME, PASSWORD) VALUES ('" & usernameString & '", '" & passwordString & "')"

Dim dbPath = "C:\Whatever\Whatever.mdb"

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbPath

Dim dbCon As New OleDbConnection(connectionString)

Try

dbCon.Open()

Dim cmd As New OleDbCommand(SQLString, dbCon)
cmd.ExecuteNonQuery() '<This is what you were missing.

dbCon.Close()

Catch ex As System.Exception

MessageBox.Show("Error: " & vbNewLine & ex.Message)
Exit Sub

End Try


Notice how here we don't need a data reader since we're simply inserting data into the database. There's nothing we are grabbing, just sending a command out to the database.

 

[Akumos]

Thank you both for your help. I am getting a 'Syntax error in INSERT INTO statement. Can you help?

Dim sqlString As String = "INSERT INTO USERS (USERNAME, PASSWORD) VALUES ('" & usernameString & "', '" & passwordString & "')"

 

[iStink]

Access has a list of words not to use as column names, but if you must, you'll need to use brackets in your sql statement. I'm unsure of the list of words, but the line of code appears valid. Try using this:

Dim sqlString As String = "INSERT INTO USERS ([USERNAME], [PASSWORD]) VALUES ('" & usernameString & "', '" & passwordString & "')"

Perhaps its password that's causing the issue.

You could also try renaming the columns within the access database. I ran into this issue when I used the word time as a column name.

 

[Akumos]

Brillitant, that line worked perfectly Thanks again iStink.

 

[Deleted member 3]

I wasn't suggesting moving the result of the replace back to the textbox.

More like Dim sqlString As String = "INSERT INTO USERS ([USERNAME], [PASSWORD]) VALUES ('" & replace(textbox1.text, "'", "''") & "', '" & replace(textbox2.text, "'", "''") & "')".

And I think the whole issue was the closing bracket I forgot At least with Accdb (2007) files you don't have to use those [Access brackets] when accessing data from .NET. Not sure if that's forced with mdb files though.

 

[iStink]

Oh, yeah you're right Dan, you can do it that way just fine. In code like this when you aren't calling on the text a bunch of times, using it like that probably makes more sense. I get into the habit of using variables just so later on in the code it's easier to use them again. Such is the freedom and creativity of programming! lol