• Welcome to TechPowerUp Forums, Guest! Please check out our forum guidelines for info related to our community.

User accounts in VB2005 from MS Access

Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
Hello everyone

I have an Access database with 2 fields (username, password). I want a simple VB form with 2 textboxes and a button that can log users in. what is the easiest way to do this?

Thanks in advance...

Akumos
 
Joined
May 20, 2004
Messages
10,487 (1.45/day)
Something along the lines of

docmd.runsql "select password from usertablethingy where username = '" & textbox1.value & "';"

Though docmd.runsql can't do select queries I believe, just write, use the reading one and read out the value and verify it's the same as textbox2.value. Google will tell you.


Though holding shift during startup still gets around your password box.
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
HUH? Sorry, should of mentioned I've not worked with VB too much.

So, I've built my form and added the database connection using 'Tools', 'Connect to database'. So I now have Users.mdb and UsersDataSet.xds in the solution explorer. What code goes here:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click

???????

End Sub
 

iStink

New Member
Joined
Sep 22, 2008
Messages
648 (0.11/day)
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
VB2005 is VB.Net correct? What you'll need to do is first import system.data.oleDB

just put "Imports System.Data.OleDB" at the top of your form's code above where it says "Public class " and your formname. Usually "Public class Form1"


'Next, you'll need to create some string variables:

Dim usernameString as string = usernameTextBox.text 'or whatever your username textbox is called.
Dim passwordString as string = passwordTextBox.text 'or whatever your password textbox is called.


'now we need to make sure people can't break the sql command with apostrophes.

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

'All we're doing is replacing a single apostrophe with a double apostrophe. Double apostrophe's in an sql statement will be treated as one, so if someone changes their password to include an apostrophe, it won't mess up the sql statement.


'Now to create the SQL string:

Dim SQLString as string = "Select * from USERS where USERNAME LIKE '" & usernameString & "'"

'Now here's the thing. If you want the username and password to be case sensitive, leave it the way I've shown you. Otherwise you'll need to change it to something like this:

Dim SQLString as string = "Select * from USERS where UPPER(USERNAME) LIKE '" & UCase(usernameString) & "'"



Dim pwReader As String 'We'll use this to grab the password from the db, and check it against our pw string reflecting the pw textbox. You'll see...



'Now that you've established a secure username and password string, and a generic select statement, you'll need to setup your connection:

Dim dbPath = "C:\Whatever\Whatever.mdb" ' location of your database including filename. .mdb is access 2000, but new access databases have a different file extension.

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbPath 'Here I've used a connection string suitable for access 2000. If you're working with the most recent version of access, you'll need to update this (generally just changes 4.0 to some other higher number.) Google this for the version of access you are using, it's not hard to find.


'Now you'll need to setup the OleDBCommand objects...

Dim dbCon As New OleDbConnection(connectionString) 'Setting up the oleDB connection using your database.

'Now for the code to grab what you need and read it:

Try
dbCon.Open() 'opening the connection.

Dim cmd As New OleDbCommand(SQLString, dbCon)

Dim data_reader As OleDbDataReader = cmd.ExecuteReader()

Do While data_reader.Read()

pwReader = (data_reader.Item("PASSWORD").ToString) 'PASSWORD is the field name within the table you are looking at.

Loop

dbCon.Close()

Catch ex As System.Exception 'general catch statement in case something goes wrong with your connection...
MessageBox.Show("Error: " & vbNewLine & ex.Message)
Exit Sub

End Try



'This last part is simple enough to understand:

If passwordString = pwReader then
'Let in
Else
'Don't let in
End if


'Now again, if you don't want passwords to be case sensitive, just wrap ucase around what you are looking at:
If ucase(passwordString) = ucase(pwReader) then
'Let in
Else
'Don't let in
End if


Good luck
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
Wow, I had no idea it would involve so much code. Thank you for taking the time to type all that out, I really appreciate it. Will try it out and let you know how I get on. Thanks again.
 

iStink

New Member
Joined
Sep 22, 2008
Messages
648 (0.11/day)
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
Yeah it's a pain in the butt, even in 2008. LINQ helps eliminate ALOT of that code, but its only for SQL databases. The last I checked, LINQ didn't work with access databases.
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
Hey 'iStink'

It worked! Thank you ever so much for all the effort you put it in to make that IDIOT proof. I'm glad I've got this working! Few extra questions if you don't mine.

1) Can I make the password textbox show hidden text when typed in to? ie. ***** for chracters.


Now totally off topic, where is the best place to decalre forms? As in:

Dim formMain As New form1

Thanks again!!!
 

iStink

New Member
Joined
Sep 22, 2008
Messages
648 (0.11/day)
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
To make a password field like you described, click on the text box, then go to the properties window. There should be a property called "PasswordChar". Just type in whatever character you want to mask it with.

To create a new form like you describe, you could declare it anywhere really. I prefer to put them within if then statements or try catch statements. Basically, put it in right before you call the form.

So instead of having it like:

Dim x as new form1
if blah = blah then
x.show
else
end
end if

it would be:
if blah=blah then
dim x as new form1
x.show
else
end
end if

Unless of course you have it in some sort of large loop, in which case you'd want to declare all variables outside the loop to avoid lag.
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
Ok got it, thanks again!
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
Just to help me understand, exactly what part does the import statement allow for? Is it the whole database connection?

Also, what does vbNewLine and ex.Message actually show?

Thanks
 

iStink

New Member
Joined
Sep 22, 2008
Messages
648 (0.11/day)
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
the Imports line brings in the system code necessary to work with OleDBCommands (at least thats my understanding of it.)

vbnewline is a break in the string to start a new line. Like hitting enter in a multi line text box.

ex.Message converts any errors the try catch statement picks up into an understandable message. Ex.ToString shows the complete error, but with a bunch of stuff even most developers don't understand.

Try catch statements are great. Back in the old VB days you'd have On Error Resume Next or On Error Goto Blah. Now you can run a little bit of code, wrap it in a try catch statement, and not worry about having your program crash if something goes wrong.

For instance:

Dim x as integer = 1
dim y as integer = 0
dim x as integer = 0

Try

z = val(x / y) 'Divide by zero.

Catch EX as Exception
Msgbox(ex.toString) ' will give a bunch of stuff in a message box relating to the error.

MsgBox(Ex.Message) ' Will give a better, shorter message, like "Can not divide by zero."

End Try

You don't always need to present the error within a try catch statement either. You could do anything u want pretty much after you've caught the exception. End the program, exit the sub, execute a public function, you name it. When an error occurs, you don't have to crash.
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
That's good, will be able to include that alot. Do you mind if I keep going with my questions??

Now I have that working, I want a really simple form to add new users to the same database. So just 3 fields, username, password and confirm password. I imagine alot of the code is similar (ie. opening the connection) just a variation of the SQL. Do you mind teaching me?

By all means, leave it for someone else if you wish. You have given more than your fair share of information to me lol.
 
Joined
May 20, 2004
Messages
10,487 (1.45/day)
Why use an access db for such a simple table when not using access? The security is close to non-existent this way so why not just use a text file to save passwords? Saves you from having to install oledb components and is easier to read from.
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
I'm trying to learn whilst on my placement year from uni intime for my final year project next christmas. I have to use databases!
 
Joined
May 20, 2004
Messages
10,487 (1.45/day)
I'm trying to learn whilst on my placement year from uni intime for my final year project next christmas. I have to use databases!

Fair enough, it's the reason I do things as well. I recently made an MP3 player when being busy with that. Secretly it uses a WMP control for playback but other than that it can scan a folder+subfolders for MP3's, read ID3 tags (though my library seems ancient and doesn't read them all :)) and drops it into a database. From there it can query the database and fill a listbox, from that listbox I can play or add to my playlist, which is just another listbox. It worked better than I imagined.
Point is, start with something simple like you are doing right now, and all kinds of mad ideas come to mind. Eventually you made something that works. I find it the best way to learn, keeps me interested in what I'm doing.
In the beginning you steal a lot of code form which you can learn, eventually you write more and more yourself.
Though I wouldn't start with a database, start with the basics. Manipulating data (strings/numbers) you'd come to reading/writing to sources pretty soon ending up at some form of database.
 

iStink

New Member
Joined
Sep 22, 2008
Messages
648 (0.11/day)
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
Dan I'd like to see that. Are you weird about your source code? What I'm thinking is, I could modify it to run on my windows mobile phone, and simplify finding my music to play in my car! :D

Also - Using an access database to login is stupid, but trust me it's used. I'm working on something at work that does this. The db file is password protected but other than that security SUCKS. I always mention how I'm concerned but everyone always reassures me our software is being used in secure facilities and the db is just to keep track of everyone's actions.
 
Joined
May 20, 2004
Messages
10,487 (1.45/day)
Dan I'd like to see that. Are you weird about your source code? What I'm thinking is, I could modify it to run on my windows mobile phone, and simplify finding my music to play in my car! :D

No, the code itself is weird :)

That's not even complete bullshit, when I'm messing with new stuff I tend to edit things alot and retry things other ways, leaving some useless code or illogical things because I'm too lazy to rewrite it. Also string and function naming should piss off some programmers :) Besides, all subs, functions, etc are right under each other, no reasonable way to find them :)

Do remember this was basically the result of just some testing with databases. It might seem completely illogical, things were just added when they came to mind or when I was wondering something. Besides, I'm really bad at UI design. The ID3 tag thing seems limited to V1, so if it doesn't show file info that's probably the issue.

Anyway, basic usage so far:
Go to import, select one or more directories (just click browse another time to add another), when you're happy click scan on the bottom. In the log screen it will log some things, like when it started scanning, if it can't read files and whatnot.
Once it's done scanning you can go back to the player and query the scanned files. Empty query = all files. Double click files to play, rightclick to add a file to the playlist or remove it from the list. Playlist tab is similar. It will play through the playlist until it's empty, if it's empty itll randomly play a file in the player list.

Enabling the MSN now playing thing works partly so far, it works the first time so I probably did something silly somewhere. The code for that is stolen from the interwebz anyway. The wmp status change list is stolen as well, you can tell by it having comments :)

The file watching thing actually works as well, select some directory like with the scanning and press monitor, any mp3 file you create, delete, rename or edit will be rescanned. For some reason it triggers multiple times and gives errors, but works fine otherwise.

As for converting it to something, no clue. It depends on several libraries which you might not have, you also require the data access components to use access 2007 files, having access 2007 works too :) Those don't exist in 64 bit flavour > crash. You have to compile for 32 bit specifically. The batchfile in the release directory changes it for you (vb express doesn't have the option)


Linky
Ding means thingy :) Execute the exe in the bin/release dir helps if you want to know if you have all dependencies. Also, references seem to be linked to a networkdrive here, might have to re-reference if you plan on editing, just reference the dll's in the release folder and wmplib.dll in your windows folder somewhere and you're fine.



PS, if your computer explodes blame random people.
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
Anyone know how to add new users to the database?
 
Joined
May 20, 2004
Messages
10,487 (1.45/day)
Insert into tablename (username, password) values ('newuser','newpassword');

That's the SQL part, read the crappy code I posted, it has these things in it.
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
Which post? I can't see it!!
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
Hmmm, I thought that was just for 'iStink'. I'll read through it again.
 
Joined
May 20, 2004
Messages
10,487 (1.45/day)
Hmmm, I thought that was just for 'iStink'. I'll read through it again.

It was a reply to him, though the linked code uses a simple Access database. Which is the reason I mentioned it.
Apart from that it's far from exemplary, it's quite messy actually. It's just some testing and self education, ie no focus on making it look good.
 
Joined
Nov 30, 2008
Messages
555 (0.10/day)
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
Right here is what I came up with. Can someone tell me what I have missed out or where I have gone wrong? Thanks in advance!!

Imports System.Data.OleDB

Public Class Form1

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

Dim usernameString As String = txtUsername.Text
Dim passwordString As String = txtPassword.Text

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



Dim SQLString As String = "INSERT INTO Users (Username, Password) VALUES ('usernameSring.Text','passwordString.Text'"

Dim dbPath = "C:\Documents and Settings\Technicians\My Documents\Users.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)

dbCon.Close()

Catch ex As System.Exception
MessageBox.Show("Error: " & vbNewLine & ex.Message)
Exit Sub

End Try

End Sub
End Class
 
Joined
May 20, 2004
Messages
10,487 (1.45/day)
First of all, if something doesn't work please give error messages. Second, learn to work with strings before you mess with databases.


Dim SQLString As String = "INSERT INTO Users (Username, Password) VALUES ('usernameSring.Text','passwordString.Text'"

You want that string to contain the values of another string, so you have to add those values to the string instead of the names of those strings. So:

Dim SQLString As String = "INSERT INTO Users (Username, Password) VALUES ('" & usernameString & "','" & passwordString & "'"

.text is a property of a textbox (and other controls), you don't use it with a string. Understand such basics first.

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