a different case of retrieving SQL DB data for login form

[ssfftt]

[RESOLVED]a different case of retrieving SQL DB data for login form

hi all:I am using VB.NET 2003i have a login form that contains btnLogin, txtLoginName.Text and txtLoginPW.Textalso have a SQL database table contains 2 columns : user and pwon the login frm, i have visually setup SqlDataAdapterUserLogin, SqlConnectionUserLogin and DataSetUserLogin, dont know if the settings are all correct though.I know somehow when i trigger btnLogin.click, i need to check whether the inputs match up database record, and then i either give access to valid user or deny access. However, i have no idea how to implement this by code.because i am new to VB.NET, can anyone please be more specific on code while trying to help?thanks

 

[jmcilhinney]

You need to execute a query to see if there are any records that contain the supplied values in the correct fields. You don't need a Data Adapter but just a Command object. Depending on what you want to do you can either call ExecuteScalar or ExecuteReader on the Command. Here's an example if you want to use ExecuteScalar:

Dim myCommand As New SqlCommand("SELECT COUNT(*) FROM Users WHERE UserID = @UserID AND Password = @Password", myConnection)

 myCommand.Parameters.Add("@UserID", txtLoginName.Text)
 myCommand.Parameters.Add("@Password", txtLoginPW.Text)

myConnection.Open()

If CInt(myCommand.ExecuteScalar()) = 0 Then
    MessageBox.Show("Login failed.")
Else
    MessageBox.Show("Login successful")
End If

myConnection.Close()

 

[jmcilhinney]

I'd actually recommend that you create the data access objects in the designer, but I had to do it manually to show the entire process.

 

[ssfftt]

thank you jmcilhinney

hi jmcilhinney, thanks for helping me out, however, I tried to implement the code, it didnt work, I know it must be my own problem.

firstly, i tried your way doing it all manually, then the system give error message: Type 'SqlCommand is not defined'.

and then i tried to put all the components visually (create the data access objects in the designer). anyway, i think i did something wrong, no matter what i type in to the textboxes, it gives "login failed" all time.

Can you plz provide an instruction on the steps that i need to do for the whole thing? i mean create the data access objects in the designer, set the properties and make them work in code.

If you cant be bothered doing this whole lot, could u plz introduce me some online materials that will guide me to complete this? I am a starter and need help.

thx

 

[sevenhalo]

If you don't import the parent, you need to reference it by it's long name.

system.Data.SqlClient.SqlCommand

That's probably longer then you need, the default imports in VS.Net should allow for you to type: "SqlClient.SqlCommand"

Edit: Changed "namespace" to "parent." It's more purist friendly

 

[ssfftt]

On parameter property of myCommand, i put "txtLoginName.Text" and "txtLoginPW.Text" into those two parameters' Value fields, it didnt work. then i changed paramters' values from "txtLoginName.Text and txtLoginPW.Text" to "one" and "one" (just one correct record in the database), it worked.

why is that?
looks like it's not reading data from the db?

 

[sevenhalo]

When you tried it initially, did you put one and one in the two textboxes?

 

[ssfftt]

no, one and one are user name and password for the only one record in my DB.
textboxes all empty, i run the app and type one and one in, clicked on login button, it didnt work

 

[sevenhalo]

can you post the code that handles the login.click?

 

[ssfftt]

    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        SqlConnectionUserLogin.Open()
        If CInt(SqlCommandUserLogin.ExecuteScalar()) = 0 Then
            MessageBox.Show("Login failed.")
        Else
            MessageBox.Show("Login successful")
        End If
        SqlConnectionUserLogin.Close()
    End Sub

thx sevenhalo

Thanks

 

[sevenhalo]

Ok, I might need more code.

When are you setting up the parameters and command? If it's onLoad, that's your problem. The two textboxes are empty, so the parameters are passing an emptystring to the database. You need to construct the parameters after they populate the two textboxes.

 

[ssfftt]

the sql that i put in SqlCommandUserLogin component is:

SELECT     COUNT(*) AS Expr1
FROM         CYMUSER
WHERE     (UNAME = @UNAME) AND (PW = @PW)

and the paramaters are UNAME and PW

maybe i did something wrong with the properties of the parameters?

 

[sevenhalo]

I think you added them too soon. When are you setting up the parameters for the command?

myCommand.Parameters.Add("@UserID", txtLoginName.Text)
myCommand.Parameters.Add("@Password", txtLoginPW.Text)

That needs to happen 'after' they populated the form. Prefferably login.click since thats where everything else is happening. And to be honest, it wouldn't hurt to keep all of it together.

 

[ssfftt]

i dont think so, i set the command the connection in the designer visually by simply dropping the components onto the form and set their properties.

 

[ssfftt]

I dont know when i am setting up the parameters for the command, i did the visually in the properties section visually without coding them.