• 0

[VB.NET] getting data from a DB


Question

I am using Visual Studio .NET 2002

I need to know how to get 2 fields from a DB (user name and password), then use those to check to see that when someone types their user name and password into text boxes then hit a login button, it will log them in, and if not, display an error message.

The database is an MS Access file that has a special table just for this purpose. It has a user name field and a password field.

I don't want to use the "Data" tools on the toolbox. I need to just write all the code to connect to the DB and stuff.

Any help will be greatly appreciated!

Link to comment
https://www.neowin.net/forum/topic/142501-vbnet-getting-data-from-a-db/
Share on other sites

25 answers to this question

Recommended Posts

  • 0

Arckon,

As I so rarely use MS Access anymore I can't be specific however I do have several points to mention here. What you are talking about doing is called Application Security. (for the sake of knowledge assume this is a SQL Server or Oracle app) There would be a server login that you would pass with your ADO connection. Then you would query your table for those two values (UserID and Password) If you got a result back then you would know if the login was correct.

Because MS Access is relatively easy to hack I have attached a class module that uses MS's crypto API - I would highly recommend using this to encrypt the password. It's easy to use:

With objCrypto

.InBuffer = txtPassword.Text

.Encrypt

sEncryptedPass = .OutBuffer

End With

Then you query the database for the UserID row, and compare the password in the database to the value the user just entered.

Now, since I don't have .NET installed here at work I can't run up the exact example of the connection to MS Access. However I will post my SQL Server code and you'll see how easy this is

Dim sConnection as String

dim cnConn as ADODB.Connection

'You'll want to get the MS Access connection string. Google this as it's easy to find!

sConnection = "uid=" & sUserID & ";pwd=" & sPass & ";driver={SQL Server};Server=" & sDBServer & ";Database=" & sDBName & ";DSN=,,Connection=adConnectAsync"

Set cnConn = New ADODB.Connection

With cnConn

.ConnectionString = sConnection

.ConnectionTimeout = sTimeout

.Open

End With

'That get's you a connection. This will get you a recordset, or in ADO.NET a Resultset

Dim cmGet As ADODB.Command

Dim rsGet as ADODB.Recordset

Dim sPassword as string

Set cmGet = New ADODB.Command

Set rsGet = New ADODB.Recordset

Set cmGet.ActiveConnection = gcnAMS

cmGet.CommandText = "SELECT * from USERTABLE WHERE UserID = '" & sUserID & "'"

With rsGet

.CursorLocation = adUseClient

.Open cmGet, , adOpenDynamic, adLockOptimistic

sPassword = .Fields("Password").Value

.Close

End With

'Now compare the two items

Hope this helps... take some time to look thought it all.

Crypto.clsFetching info...

  • 0

jameswjrose: Thank you for your imput! I don't have time to mess with this stuff right now, but I will when I get back from the weekend.

One quick question though, how do I add that class file to my project and how do I get the project to actually use it? Do I add that somewhere in the code for the form? Like add another class in there?

Also, where do it put the with...end with stuff at? inside the public class? when the form loads?

I never used stuff like that before.

  • 0

Ah, you really are a beginner (no offense.. just stating a precieved fact) I've used VB since version 2.0 ...yes I'm that old - f you! ;)

Anywho... IN VB6 (like I said I've got .NET at home only - damn job) and I believe it's basically the same thing (ever notice you forget how to do the basics... anywhoo)

Select menu: PROJECT, ADD CLASS MODULE then find it on your file system

A class is just like any other object, ie a form, a button etc. In fact a form is just a class too. (another story) get to know the ideas behind classes as since you are starting with VB.NET (which is fine, I make a good living in NYC doing VB/Client server work) as opposed to VB6 classes/objects are a key element.

Using the Class is easy (yea yea yea, I know, it's easy if you know how)

Private Sub Form_Load()

Dim objCrypto As clsCrypto

Dim sInput As String

On Error GoTo ProcError

Set objCrypto = New clsCrypto

sInput = InputBox("Enter the item to encrypt", "Encryt")

'Encrypt it, then show the value

With objCrypto

.InBuffer = sInput

.Encrypt

sInput = .OutBuffer

End With

MsgBox sInput

'Now unencrypt it, then show the value

With objCrypto

.InBuffer = sInput

.Decrypt

sInput = .OutBuffer

End With

MsgBox sInput

ProcExit:

On Error Resume Next

Set objCrypto = Nothing

Exit Sub

ProcError:

MsgBox Err.Number & " " & Err.Description

End Sub

Now, there is a Password property to the class - and during Class_Initizalize I've set this password, you can use whatever password you wish - HOWEVER to decrypt the data it has to be the same password. This is your KEY. Be aware of this.

  • 0

Arckon,

I just pulled your other code and I should have some time tonight to look it over. However since I started on encyrpting the password issue I thought I should pass this portion on to you.

I had to redo the Crypto code for .NET, and like so many items in .NET - SO easy. I've attached the class and here's how to call it

Dim objCrypto As clsCrypto

Dim sInfo As String

On Error GoTo ProcError

objCrypto = New clsCrypto

sInfo = InputBox("Enter the value you wish to encrypt")

With objCrypto

.Key = "123"

sInfo = .EncryptString128Bit(sInfo, .Key)

MessageBox.Show(sInfo, "Encrypted")

sInfo = (.DecryptString128Bit(sInfo, .Key))

MessageBox.Show(sInfo, "Unencrypted")

End With

ProcExit:

On Error Resume Next

Exit Sub

ProcError:

MsgBox(Err.Number & " " & Err.Description)

Crypto.vbFetching info...

  • 0

I finally got my program to connect to the DB, but there are a few things that I'm not sure about yet. Most of the stuff in the "with rsGet" section doesn't work for me...mostly because I don't know what it all does anyway.

Also, I have no clue what the "Set cmGet.ActiveConnection = gcnAMS" line does.

I think I need to be putting this stuff in button_click section, but later, I want to use most of that code in other forms to connect to the DB, retrieve, insert, update, and delete records in it. How can I accomplish this?

I have tried using a data reader and other things to try to get the program to compare what is in the text boxes to what is in the database, but I haven't really gotten anything to actually work yet.

  • 0

EDITED: oops... i thought this was in Web Developers Corner... modified for winforms... i think ;) spend most of my time doing web dev.

Arckon...

try using the DataReader class/object in .NET, and since you're using access look into using the OleDb methods. You may find things a bit easier. Also, in .NET [/b]On Error Goto[/b] has been done away with i think... If you want Error Trapping, use your Try...Catch...Finally...EndTry block.

If you have any questions, i'll be MORE than happy to help you also. I've been working with VB.NET for the past 2 1/2 years and LOVE it... my current website is coded in ASP.NET(VB) and I use an Access Database for the backend.

http://www.JoshAndBrandi.com

btw, here's my login routine... nothing fancy. I have put everything in as if it were not codebehind, and included a sample reusable ErrorHandling routine that i've actually compiled into a class with a bit of modification... but here you go:

Imports System.Data
Imports System.Data.OleDb

dim Conn as New OledbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MapPath("/***/***.mdb" & ";")

Login_Clicked(ByVal Sender As Object, ByVal e As EventArgs)
	dim strSQL as string = "SELECT [User].UserID, [Perm], [Active] " & _
         "FROM [User] " & _
         "INNER JOIN [Perm] ON [User].UserID = [Perm].UserID " & _
         "WHERE [Username]=@Username " & _
         "AND [Password]=@Password"
	dim C as New OleDbCommand(strSQL, Conn)
	With C.Parameters
  .Add(New OleDbParameter("@Username", txtUser.Text))
  .Add(New OleDbParameter("@Password", txtPass.Text))
	End With
	dim DR as OleDbDataReader
	Conn.Open()
	Try
  DR = C.ExecuteReader()
  Do While DR.Read()
 	 If DR("Active") then
    If NOT(DR("UserID") is DBNull.Value) then ...
    If NOT(DR("Perm") is DBNull.Value) then ...
 	 End If
  Loop
  DR.Close()
	Catch Exc As Exception
  ErrorAlert(Exc, strSQL)
	End Try
	Conn.Close()
End Sub

Sub ErrorAlert(ByVal Exc As Object, Optional ByVal strSQL as string = "n/a")
	dim M as string
	M = "ERROR:" & Environment.NewLine() & Environment.NewLine() & _
    Exc.ToString() & Environment.NewLine() & Environment.NewLine() & Environment.NewLine() & _
    "SQL: " & strSQL & Environment.NewLine() & _
    "Date: " & DateTime.Now
    MsgBox(M, vbOK, "ERROR:")
End Sub

Edited by best_uv_d_best
  • 0

Just to let everyone know that I had a web page that used a DB and a login type thing similiar to what I'm doing here. That was written in ASP.NET and VB script w/ that. So the first thing I tried to do was open up my asp file and try to do the code as close to that as I could. But for some reason I've been unsucessful at converting the syntax to normal VB.NET. I tried the "while datareader.read" stuff and I kept getting some type of unhandled exception error.

I've been getting pretty frustrated w/ this thing because things worked fine in the asp file and when I tried "converting" the syntax, everything seems all screwy.

  • 0

best_uv_d_best:

Would you please explain what all the items in your sql statement do? For example, what does [user].UserID do?

and what is [Perm]? and what is [userName] = @UserName?

I haven't seen anything like that before and I'm trying to relate all of this to what is in my DB.

Thank you for your time!

  • 0

Gladly... :D

Okay, you've seen SQL statements that resemble SELECT Name, Status FROM Employees. Well, in the query you see I'm joining tables. let me try to illustrate this alittle bit...

_____________

|TABLE: User |

|============

| UserID | Active |

|============

_____________

|TABLE: Perm |

|============

| UserID | Perm |

|============

Okay, so now i have two tables, and i want to get values from both of them, but i want the values to be related by UserID and appear to be ONE table, and to join the results into Records based on the JOIN to UserID.

Now, when you join tables, if you have Column names that are the same in both tables, when you specify that request in your query you must also relate the column to the table; hence the User.UserID you see in my query. I'm requesting UserID fromt he User table. :) Simple enough i hope...

As far as the square brackets are concerned... Databases have a thing called "Reserved Words". By this, it means that you're not supposed to use those words for naming columns or tables because that database is recognizing them as something else. HOWEVER... you CAN use them, you just have to wrap them in square brackets to tell the DB not to process these Reserved Words as it normally would, but as you want it to.

I have gotten in the habit of wrapping ALL of my query elements in square brackets because it just makes my life easier. So since User and Username are reserved words and i couldn't normally use them in my db design, i DID use them and now im just telling the DB to treat those like queryable items by wrapping them in [...] - square brackets.

For the last item... i have Parameters specifed... @Username and @Password. As you can see i've also included a With...End With statement on my C.Parameters (Command Object Parameters). You can create a SQL statement that retrieves values from user input objects and controls (Textbox, DropDownList, etc) and make the SQL Statement like this...

dim strSQL as string = "SELECT [username] FROM [user] WHERE Password='" & txtPassword.Text & "'"

Now, i know that doesn't seem hard to read, but imagine having 20 or so fields of different data types being requested... this could get confusing. So instead i go...

dim strSQL as string = "SELECT [username] FROM [user] WHERE Password=@Password"

dim C as New OleDbCommand(strSQL, Conn)

With C.Parameters

.Add(New OleDbParameter("@Password", txtPassword.Text))

End With

When you get to having alot of information pulled, this will save you from too much :argh: or :s

You can research the INNER JOIN and OUTER JOIN methods on Google... there's plenty of tutorials and explanations out there. Just type "SQL Tutorial" in google and go with it.

So... That's how my query ended up looking like:

SELECT [user].UserID, [Active], [Perm]

FROM User

INNER JOIN Perm

ON [user].UserID = [Perm].UserID

WHERE [username]=@Username AND [Password]=@Password

and the rest SHOULD be pretty self explanatory.

The only other advice i can give at this point, if you have the money, is to buy some WROX Press books like these:

http://www.amazon.com/exec/obidos/tg/detai...=books&n=507846

They're the BEST :D

Also, i'm more than happy to help you, but theres a great set of forums on ASP.NET stuff over at http://www.asp.net/forums - i use them when i get stuck.

===================

EDIT:

Okay, i know you guys don't allow linking to some other sites, but theres a good tutorial that explains some of this ... Just remove the [] (square brackets...)

http://www.[slip]stream[x].net/viewthread.php?tid=2614

Edited by best_uv_d_best
  • 0

good stuff best_uv_d_best, least you put him in the right direction considering .net does away with the ADODB ****.

i quite like to write all my queries in my database because its just so easy in access

you can then access them in sorta the same way in your code

OleDbCommand.CommandText = "[nameofquery]"
OleDbCommand.CommandType = Something.StoredProcedure
OleDbCommand.Parameter.Add(New System.Data.OleDb.OleDbParameter("PasswordSySystem.Data.OleDb.OleDbType.VarWChar, 50, "Password"))

With C.Parameters

.Add(New OleDbParameter("@Password", txtPassword.Text))

End With

  • 0

Thanks.

I hate ADODB... If given the option, always go SQL (not MySQL if you have a choice of that, too), and if not SQL, MSDE (which is basically the same thing) or Access as last resort or for small end stuff.

Bender - that works too... but if you're not as familiar with Access, then this is the "easy" way. I've used both, but prefer not to have to goto my DB to add/edit a query.

  • 0

Bender: I am working on this VB project for a class and it is being written in a team. One of the members already created all of the queries in access, and I would like to know more about implementing that stuff in our VB program. That's really sweet since most of those queries are already done!

  • 0

I was working w/ best_uv_d_best's pieces of code and I want to know what I need to put in the following section of code

Try
            DR = C.ExecuteReader()
            Do While DR.Read()
                If DR("slsPerID") Then
                    If Not (DR("slsperID") Is DBNull.Value) Then
                        MsgBox("User Name Error!")
                    End If
                    If Not (DR("slsperPassword") Is DBNull.Value) Then
                        MsgBox("Password Error!")
                    End If
                End If
            Loop
            DR.Close()
        Catch Exc As Exception
            ErrorAlert(Exc, strSQL)
        End Try

I have a table called UserName-Password that contains these fields: ID (as primary key), slsperID, slsperPassword, and Active (check box used for logical deletion of records). I'm not sure what I need in that do loop.

  • 0

Try this... you might wanna read up on the Logic and how it works.

Try
     DR = C.ExecuteReader()
     Do While DR.Read()

          If DR("slsperID") Is DBNull.Value Then
               MsgBox("User ID Is a NULL Value!")
          Else
               lblID.Text = DR("slsperID")
          End If

          If DR("slsperPassword") Is DBNull.Value Then
               MsgBox("User Password Is a NULL Value!")
          Else
               lblPassword.Text = DR("slsperPassword")
          End If
     Loop
     DR.Close()
Catch Exc as Exception
     ErrorAlert(Exc, strSQL)
End Try

  • 0

Here is the latest version of the project I'm working on so far. The problem I am having now is that When the 2 text boxes are blank or even have 1 of the items correct, nothing happens at all. And I get an error when both of them are correct.

iviewcapture_date_26_02_2004_time_23_36_06.jpg

Login.zipFetching info...

  • 0
Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
 ? ? ? ?If txtUserID.Text <> String.Empty And txtPassword.Text <> String.Empty Then
 ? ? ? ? ? ?Dim CMD As New OleDbCommand
 ? ? ? ? ? ?Dim DA As OleDbDataAdapter
 ? ? ? ? ? ?Dim DS As UserName_Password
 ? ? ? ? ? ?CMD.CommandText = "SELECT slsperID, slsperPassword FROM [UserName-Password]"
 ? ? ? ? ? ?CMD.Connection = OleDbConnection1
 ? ? ? ? ? ?DA.SelectCommand = CMD
 ? ? ? ? ? ?OleDbConnection1.Open()
 ? ? ? ? ? ?Try
 ? ? ? ? ? ? ? ?DA.Fill(DS)
 ? ? ? ? ? ?Catch
 ? ? ? ? ? ? ? ?MsgBox(Err.Description, MsgBoxStyle.Exclamation, "Error")
 ? ? ? ? ? ?End Try
 ? ? ? ? ? ?Try
 ? ? ? ? ? ? ? ?'first we want to filter the dataset
 ? ? ? ? ? ? ? ?Dim DRs() As DataRow = DS._UserName_Password.Select("[SlsperID] = " & txtUserID.Text)
 ? ? ? ? ? ? ? ?Select Case DRs.GetUpperBound(0)
 ? ? ? ? ? ? ? ? ? ?Case Is > 1
 ? ? ? ? ? ? ? ? ? ? ? ?MsgBox("Login Failed - UserID is not Unique")
 ? ? ? ? ? ? ? ? ? ? ? ?txtUserID.Clear()
 ? ? ? ? ? ? ? ? ? ? ? ?txtUserID.Focus()
 ? ? ? ? ? ? ? ? ? ? ? ?txtPassword.Clear()
 ? ? ? ? ? ? ? ? ? ?Case 1
 ? ? ? ? ? ? ? ? ? ? ? ?'user exists now check pass
 ? ? ? ? ? ? ? ? ? ? ? ?'username should always be unique, therefore 1 result will be returned
 ? ? ? ? ? ? ? ? ? ? ? ?'so no looping is required
 ? ? ? ? ? ? ? ? ? ? ? ?Dim dr As DataRow = DRs(0)
 ? ? ? ? ? ? ? ? ? ? ? ?If dr("slsperPassword") = txtPassword.Text Then
 ? ? ? ? ? ? ? ? ? ? ? ? ? ?MsgBox("Login Accepted")
 ? ? ? ? ? ? ? ? ? ? ? ?Else
 ? ? ? ? ? ? ? ? ? ? ? ? ? ?'the stored password differs from the one they are trying
 ? ? ? ? ? ? ? ? ? ? ? ? ? ?MsgBox("Login Failed - Bad Password")
 ? ? ? ? ? ? ? ? ? ? ? ? ? ?txtPassword.Clear()
 ? ? ? ? ? ? ? ? ? ? ? ? ? ?txtPassword.Focus()
 ? ? ? ? ? ? ? ? ? ? ? ?End If
 ? ? ? ? ? ? ? ? ? ?Case 0
 ? ? ? ? ? ? ? ? ? ? ? ?'User does not exist as no records were returned
 ? ? ? ? ? ? ? ? ? ? ? ?MsgBox("Login Failed - Bad Username")
 ? ? ? ? ? ? ? ? ? ? ? ?txtUserID.Clear()
 ? ? ? ? ? ? ? ? ? ? ? ?txtUserID.Focus()
 ? ? ? ? ? ? ? ? ? ? ? ?txtPassword.Clear()
 ? ? ? ? ? ? ? ?End Select
 ? ? ? ? ? ?Catch
 ? ? ? ? ? ?Finally
 ? ? ? ? ? ? ? ?'put further processing of a correct login here
 ? ? ? ? ? ?End Try
 ? ? ? ?End If
 ? ?End Sub

  • 0

I just copied and pasted the above code into my project again, and I still can't get it to work.

When both UserName and Password are wrong I get this error:

An unhandled exception of type 'System.NullReferenceException' occurred in Login.exe

Additional information: Object reference not set to an instance of an object.

It then points to the line that says: DA.SelectCommand = CMD

And if one or the other are incorrect, I get the same thing

And if both are correct, I get an error box w/ this in the details section:

************** Exception Text **************

System.NullReferenceException: Object reference not set to an instance of an object.

at Login.Test_Form_3.btnLogin_Click(Object sender, EventArgs e) in C:\MyFiles\IFMG 470\Group 1\Sales-Program\Login\Test Form 3.vb:line 197

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.PerformClick()

at System.Windows.Forms.Button.ProcessMnemonic(Char charCode)

at System.Windows.Forms.Control._ProcessMnemonic(Char charCode)

at System.Windows.Forms.ContainerControl.ProcessMnemonic(Char charCode)

at System.Windows.Forms.ContainerControl.ProcessDialogChar(Char charCode)

at System.Windows.Forms.Control.ProcessDialogChar(Char charCode)

at System.Windows.Forms.Control.PreProcessMessage(Message& msg)

at System.Windows.Forms.ThreadContext.System.Windows.Forms.UnsafeNativeMethods+IMsoComponent.FPreTranslateMessage(MSG& msg)

  • 0

I tried that, and I still can't get it to work. The button seems to be non functional unless I add something to the Finally...End Try section.

I tried to put a msgbox in there to see if anything would happen. the msgbox will only come up when both text boxes have something in them (no matter if one or the other, or both are correct).

I tried to debug it a little to see if I could find where the problem might be. I put a watch on the DS and on the DRs. The DS seems to be ok, but the DRs always has a {length=0} in the value for it in the watch pane.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Cortana's real value was that early on it let you connect assistants like Alexa to it so you could use Cortana while at your PC to control other assistants. That was what MS envisioned for it, a sort of assistant hub that let you manage the others. Then Amazon killed the ability in Alexa which ended that. MS just never brought out any new ideas to make up for it. It was then just "yet another" assistant, which who is going to use an assistant while at their PC then Alexa in the kitchen then also Google Nest when in another room?
    • Swallowed Pride? Try more "they got tired of making YT work, just for Google to break the site on purpose again to SPECIFICALLY not work in Edge."
    • You joke, but BSOD is an important feature of Windows, one that Linux doesn't exactly have, which can lead to some wasted time troubleshooting issues. What I mean by that is that when Linux does a kernel panic, it is far from guaranteed that the normal kernel panic text actually gets shown on the screen, depending on what mode you are in. Sometimes a kernel panic will just result in your desktop manager seeming to go unresponsive, which may lead you down the wrong troubleshooting path.
    • Great write up. Microsoft had much from developers when it comes to new features and frameworks. That fortune seems only Apple appears to have. The day they announce new visuals or API’s, almost all developers jump immediately on implementing it when the first beta is out, so it can be released asap. And when a developer is slacking, Apple users actively call them out.
    • Tiles on 10 was great for touch devices, just few people use WIndwos devices in an actual Tablet way. Groove as GREAT it seamlessly pulled my music to stream from my OneDrive even on the mobile app. Legacy Edge was also great it was only failed because Google kept screwing with their sites to break them for non-Chromium browsers. Cortana also had great possibilities and was just never fully realized. Kind of like Siri. IMO they should have just revamped it to be what Copilot is.
  • Recent Achievements

    • Week One Done
      SmileWorks Dental earned a badge
      Week One Done
    • Community Regular
      vZeroG went up a rank
      Community Regular
    • Collaborator
      Snake Doc earned a badge
      Collaborator
    • Week One Done
      Snake Doc earned a badge
      Week One Done
    • One Month Later
      Johnny Mrkvička earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      589
    2. 2
      Michael Scrip
      197
    3. 3
      ATLien_0
      195
    4. 4
      +FloatingFatMan
      131
    5. 5
      Xenon
      122
  • Tell a friend

    Love Neowin? Tell a friend!