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

User accounts in VB2005 from MS Access

Discussion in 'Programming & Webmastering' started by Akumos, Jan 26, 2009.

  1. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    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
     
  2. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.74/day)
    Thanks Received:
    1,383
    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.
     
  3. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    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
     
  4. iStink

    iStink New Member

    Joined:
    Sep 22, 2008
    Messages:
    648 (0.29/day)
    Thanks Received:
    49
    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 says thanks.
  5. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    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.
     
  6. iStink

    iStink New Member

    Joined:
    Sep 22, 2008
    Messages:
    648 (0.29/day)
    Thanks Received:
    49
    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.
     
  7. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    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!!!
     
  8. iStink

    iStink New Member

    Joined:
    Sep 22, 2008
    Messages:
    648 (0.29/day)
    Thanks Received:
    49
    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 says thanks.
  9. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    Ok got it, thanks again!
     
  10. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    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
     
  11. iStink

    iStink New Member

    Joined:
    Sep 22, 2008
    Messages:
    648 (0.29/day)
    Thanks Received:
    49
    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.
     
  12. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    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.
     
  13. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.74/day)
    Thanks Received:
    1,383
    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.
     
  14. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    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!
     
  15. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.74/day)
    Thanks Received:
    1,383
    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.
     
  16. iStink

    iStink New Member

    Joined:
    Sep 22, 2008
    Messages:
    648 (0.29/day)
    Thanks Received:
    49
    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.
     
  17. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.74/day)
    Thanks Received:
    1,383
    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.
     
  18. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    Anyone know how to add new users to the database?
     
  19. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.74/day)
    Thanks Received:
    1,383
    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.
     
  20. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    Which post? I can't see it!!
     
  21. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.74/day)
    Thanks Received:
    1,383
    17
     
  22. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    Hmmm, I thought that was just for 'iStink'. I'll read through it again.
     
  23. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.74/day)
    Thanks Received:
    1,383
    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.
     
  24. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    552 (0.25/day)
    Thanks Received:
    28
    Location:
    Birmingham, England...
    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
     
  25. DanTheBanjoman Señor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.74/day)
    Thanks Received:
    1,383
    First of all, if something doesn't work please give error messages. Second, learn to work with strings before you mess with databases.


    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:

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

Currently Active Users Viewing This Thread: 1 (0 members and 1 guest)

Share This Page