# User accounts in VB2005 from MS Access



## Akumos (Jan 26, 2009)

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


----------



## Deleted member 3 (Jan 26, 2009)

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.


----------



## Akumos (Jan 26, 2009)

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 (Jan 26, 2009)

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


----------



## Akumos (Jan 26, 2009)

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 (Jan 26, 2009)

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.


----------



## Akumos (Jan 26, 2009)

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 (Jan 26, 2009)

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.


----------



## Akumos (Jan 26, 2009)

Ok got it, thanks again!


----------



## Akumos (Jan 26, 2009)

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 (Jan 26, 2009)

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.


----------



## Akumos (Jan 26, 2009)

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.


----------



## Deleted member 3 (Jan 26, 2009)

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.


----------



## Akumos (Jan 26, 2009)

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!


----------



## Deleted member 3 (Jan 26, 2009)

Akumos said:


> 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 (Jan 26, 2009)

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! 

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.


----------



## Deleted member 3 (Jan 26, 2009)

iStink said:


> 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!



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.


----------



## Akumos (Jan 27, 2009)

Anyone know how to add new users to the database?


----------



## Deleted member 3 (Jan 27, 2009)

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.


----------



## Akumos (Jan 27, 2009)

Which post? I can't see it!!


----------



## Deleted member 3 (Jan 27, 2009)

Akumos said:


> Which post? I can't see it!!



17


----------



## Akumos (Jan 27, 2009)

Hmmm, I thought that was just for 'iStink'. I'll read through it again.


----------



## Deleted member 3 (Jan 27, 2009)

Akumos said:


> 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.


----------



## Akumos (Jan 27, 2009)

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


----------



## Deleted member 3 (Jan 27, 2009)

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.


----------



## Akumos (Jan 27, 2009)

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 (Jan 27, 2009)

DanTheBanjoman said:


> 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 (Jan 27, 2009)

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 (Jan 27, 2009)

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 (Jan 27, 2009)

Brillitant, that line worked perfectly Thanks again iStink.


----------



## Deleted member 3 (Jan 27, 2009)

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 (Jan 27, 2009)

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


----------

