N> Help in Visual Basic [Database Connections]

[pranavrules2007]

Hi there, I am a computer engineering student in 3rd year of my diploma. I am in great need of help in visual basic as it's the main base of my project work. I am working on an autobased company statistics program, which keeps all the records of the industry like employees, products made, etc. I am stuck on the very basic form of the project, that is, the login screen.


I created a basic login form with a username and password fields as text boxes. Here are the steps that i carried out.

1. Created a New Form
2. Added a Database via MS-Access 2003
3. Created a table named login with fields "id" and "paswd"
4. Using the Microsoft ADO Data Control 6.0 i added the data control on the form
5. Used a connection string to connect to the database by simply right clicking on the data control on the form and selecting the connection string as the database file [login.mdb] which was on the desktop within a folder.
6. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\Desktop\pranav\login.mdb;Pe rsist Security Info=False This was the basic connection string that was generated by the control.
7. Then, i clicked on the RecordSet tab on the data control's properties and chose adCmdText under the command type drop down list and i used a SQL command select * from login; for the command text.
8. Then, i went to the login text box and chose the datasource as ado1 [the name of my control for the database connection] and the data field as id and similarly for the password field as paswd for text2

Now, i am stuck at the queries section. I want to verify the users information that he enters in the text fields and check with the records in the database. Such that, the existing records are matched with the data entered in the "ID" and "Password" fields in the form. I have absolutely no idea how to do that.

Like, do i go to the command1_click() and type something there? how do i search for a string in the records of the database? see this for example..

text1.text = "admin" [that's the id in the form]
text2.text = "asd123" [that's the password in the form]

How do i search for these strings in the database?
is it like
ado1.recordset.find (text1.text) or something? please help me here.. tell me the exact queries that i should place inside the text1_click() event in order to connect to the database, look inside the login table and search for the records.. Thanks for helping me. I need this urgently.. Thanks again..

Please do reply someone.. I am need of serious help, my project is held up because of this database.. Please do reply ASAP.. thanks..

Pranav..

[Charan]

oops I thought I had replied to this thread.

use the query like "SELECT pass FROM login WHERE userID='" + text1.text + "'"
executing the query will return just a single password string for the particular user. you can now compare this to the entered value.

[pranavrules2007]

please throw a bit more light on this... where and how should i use this query? I am new to this stuff.. Not the SQL part, but the implementation of the code.. I mean.. where should i put this query? please help.. sob..

[Charan]

use this code, paste it in the command click event. this is fully runtime based SQL querying. you need to add refference to "Microsoft ActiveX Data Objects 2.0 Library".

Private Sub Command1_Click()
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim connectString As String
Dim Username As String
Dim Password As String


Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset


connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=test.mdb"
On Error GoTo HELL
adoConnection.Open connectString

Set adoRecordset = adoConnection.Execute("SELECT Password FROM Login WHERE UserName = '" & txtUserName.Text & "' ")

If adoRecordset.BOF Or adoRecordset.EOF Then
    MsgBox "Password Incorrect"
    Else
    If adoRecordset(0) = txtPassword.Text Then
        MsgBox "Password Correct"
    Else
        MsgBox "Password Incorrect"
    End If


    adoRecordset.Close
    adoConnection.Close

HELL:
If Err <> 0 Then
MsgBox "Error: " & Err.Description
End If
    Set adoRecordset = Nothing
    Set adoConnection = Nothing
    
End If
End Sub

attached a small program check it out. the database is test.mdb ( access 2000) , table is login , fields are UserName and Password. Hope this helps you

[pranavrules2007]

thanks for the quick reply mate.. i'll try it soon...

Edit:

Excellent Work!! Thanks for all the help man.. I really needed that.. I am off to designing my entire project now.. I was thinking of using RDO.. but looking at your program, i think i will use ADODB.. it's much more useful..

Thanks,
Pranav

[Charan]

^^^ yes using ADODB gives you more control on your program and the queries . its much better than RDO or DAO.

BTW just asking.... why dont you use VB.NET instead?