• We've upgraded our forums. Please post any issues/requests in this thread.

User accounts in VB2005 from MS Access

Joined
Nov 30, 2008
Messages
555 (0.17/day)
Likes
28
Location
Birmingham, England...
Processor Intel Core 2 Quad Q6600 @ 2.8GHz
Motherboard Gigabyte X48T-DQ6
Cooling Zalman 9500 LED CPU Cooler
Memory 2x 2GB Corsair DDR3 XMS3 DHX - 1600MH/PC3-12800
Video Card(s) Gigabyte HD4870 1GB
Storage 2x Seagate 320GB Barracuda (RAID 0) 3x 1TB Samsung F3, 140GB WD Maxtor (10,000rpm)
Display(s) 2x 20" LG Flatron L204WS
Power Supply Powercool 850W
Software Windows 7 Ultimate x64
#26
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

New Member
Joined
Sep 22, 2008
Messages
648 (0.19/day)
Likes
49
System Name The beast
Processor E6600@ 3.4
Motherboard P5W DH Deluxe
Cooling Tuniq Tower 120
Memory 3GB G.Sill DDR2-800
Video Card(s) 8800GT @ 700/975
Storage (1)80GB With XP, (1) 320GB storage
Display(s) 205BW & 710N
Case XClio Windtunnel (I love it)
Audio Device(s) X-Fi Platinum
Power Supply OCZ gamerXStream 700W
#27
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.
 
Last edited:
Joined
Nov 30, 2008
Messages
555 (0.17/day)
Likes
28
Location
Birmingham, England...
Processor Intel Core 2 Quad Q6600 @ 2.8GHz
Motherboard Gigabyte X48T-DQ6
Cooling Zalman 9500 LED CPU Cooler
Memory 2x 2GB Corsair DDR3 XMS3 DHX - 1600MH/PC3-12800
Video Card(s) Gigabyte HD4870 1GB
Storage 2x Seagate 320GB Barracuda (RAID 0) 3x 1TB Samsung F3, 140GB WD Maxtor (10,000rpm)
Display(s) 2x 20" LG Flatron L204WS
Power Supply Powercool 850W
Software Windows 7 Ultimate x64
#28
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

New Member
Joined
Sep 22, 2008
Messages
648 (0.19/day)
Likes
49
System Name The beast
Processor E6600@ 3.4
Motherboard P5W DH Deluxe
Cooling Tuniq Tower 120
Memory 3GB G.Sill DDR2-800
Video Card(s) 8800GT @ 700/975
Storage (1)80GB With XP, (1) 320GB storage
Display(s) 205BW & 710N
Case XClio Windtunnel (I love it)
Audio Device(s) X-Fi Platinum
Power Supply OCZ gamerXStream 700W
#29
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.
 
Joined
Nov 30, 2008
Messages
555 (0.17/day)
Likes
28
Location
Birmingham, England...
Processor Intel Core 2 Quad Q6600 @ 2.8GHz
Motherboard Gigabyte X48T-DQ6
Cooling Zalman 9500 LED CPU Cooler
Memory 2x 2GB Corsair DDR3 XMS3 DHX - 1600MH/PC3-12800
Video Card(s) Gigabyte HD4870 1GB
Storage 2x Seagate 320GB Barracuda (RAID 0) 3x 1TB Samsung F3, 140GB WD Maxtor (10,000rpm)
Display(s) 2x 20" LG Flatron L204WS
Power Supply Powercool 850W
Software Windows 7 Ultimate x64
#30
Brillitant, that line worked perfectly Thanks again iStink.
 

DanTheBanjoman

Señor Moderator
Joined
May 20, 2004
Messages
10,488 (2.12/day)
Likes
1,331
#31
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

New Member
Joined
Sep 22, 2008
Messages
648 (0.19/day)
Likes
49
System Name The beast
Processor E6600@ 3.4
Motherboard P5W DH Deluxe
Cooling Tuniq Tower 120
Memory 3GB G.Sill DDR2-800
Video Card(s) 8800GT @ 700/975
Storage (1)80GB With XP, (1) 320GB storage
Display(s) 205BW & 710N
Case XClio Windtunnel (I love it)
Audio Device(s) X-Fi Platinum
Power Supply OCZ gamerXStream 700W
#32
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