[techviking]

Hey guys,

Im new to using databases in my programming and to be honeest i am struggling.

i need to get the program to communicate with my database to allow the user to login

this is the code i have so far as i say i dont know much about it so its a bit bodged.

 Private Sub Connecttodb()

        Dim con As New OleDb.OleDbConnection        'This Allows a Connection To a Database
        Dim dbProvider As String                    'This Will Be The Provider For the Database
        Dim DataSet As New DataSet                  'This Will Effectively Store The Database
        Dim DataAdapter As OleDb.OleDbDataAdapter   'Allows manipulation and access of database
        Dim Sql As String                           'This Is The Query String

        dbProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Ollie\Desktop\Spelling Bee\SpellingBeeDB.accdb;Jet OLEDB:Database" 'Points The Provider to the location of the database

        con.ConnectionString = dbProvider 'this creates a connection to the database provider

        con.Open()                                          'Opens A Connection Stream


        Sql = "SELECT *, FROM Userinfo"

        DataAdapter = New OleDb.OleDbDataAdapter(Sql, con)  'Uses DataAdapater constructor to take the appropriate query and from which connection

        DataAdapter.Fill(DataSet, "UserInfo")               'fill the data set with everything from the dataAdapter and call the table UserInfo

        con.Close()                                         'Close connection stream

        UsernameTextBox = DataSet.Tables("UserInfo").Rows(1).Item(1)

So my question is is there a simpler more efficent approach or can anyone see where im going wrong?

[modi123_1]

"can anyone see where im going wrong? "
What do you mean? Is it giving you an error? If so what? Where?

Is it not functioning as you would like? What is it not doing?

Oh you might want to get in the habit of wrapping everything in a "try catch finally" block. (The finally should be where you dispose of your connection).

[CharlieMay]

Well, other than you left off the .Text property of the UsernameTextBox control I see nothing wrong with the code you've written.

And you need to remove the comma from the sql statement

"SELECT * FROM UserInfo" 

Last edit, I promise

UsernameTextBox.Text = DataSet.Tables("UserInfo").Rows(1).Item(1)

is assuming that you have 2 rows (Rows(1) is the second returned rows).

For a username/password check, it would be better to let the SQL statement do all the work.

SELECT * FROM UserInfo WHERE UserName = 'some users name'"

This would then ideally pull one row for you to extract the password from.

Now there is also the situation where a username doesn't match, so you will need to wrap the UsernameTextBox1.Text = Dataset..... line into an if statement

Something like:

If DataSet.Tables(0).Rows.Count > 0 then 
  UsernameTextBox.Text = DataSet.Tables(0).Rows(0).Item(1)
Else
  Messagebox.Show("User not found in database")
End If.

This post has been edited by CharlieMay: 15 February 2011 - 11:20 AM

[November-06]

Can your program read your database?

I use VB.NET 2008

I have used 2003 access before and the Microsoft.Jet worked there but not when I used 2007.

So when I am using a MS Access database 2007 or 2010, I used this...

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ollie\Desktop\Spelling Bee\SpellingBeeDB.accdb;"

'I already used your file here.

Now you can only use this code when your database has no password.

If there is a password you have to include it like this...


"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ollie\Desktop\Spelling Bee\SpellingBeeDB.accdb;Jet OLEDB:Database Password=yourpassword;"

'replace your password with your password in access

What version of VB.NET are you using anyway?

If you are using 2003 or lower, do not use an accdb access file. Use the access file with the extension .mdb.

This post has been edited by November-06: 15 February 2011 - 08:35 PM