select a row in sql where?

[Gildob]

Hi at all, i'm not a programmer, I used some asp code in the past using with dreamwaver.....
Now I'm approaching at vb.net, I've bought some ebooks, and starting to develop a small program.
Now I encounterd a problem in a code...

Created 1 form with 2 textbox (username, password)
I need to select a row in sql where the username is the same and get the value of ID column.
this is the code :

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim Varsusr As String = NameTextBox.ToString
Dim Varpwd As String = PwdTextBox.ToString
Dim dbplug As New SqlClient.SqlConnection()
dbplug.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Gildo\Documents\Visual Studio 2012\Projects\DolphinBeta1\DolphinBeta1\bin\Debug\Dolphinbetadbase.mdf;Integrated Security=True;Connect Timeout=30"
Dim checkusr As New DataTable("userchecktable")

Dim qcheck As New SqlClient.SqlDataAdapter("Select id,name,lvl from utenti where name ='" & Varsusr & "'", dbplug)
dbplug.Open()
qcheck.Fill(checkusr)
dbplug.Close()



End Sub
How I can retrieve the value ID of the column?
Is the right way?, is the only

Sorry if seems a stupid question, but I need to start....

 

[IanRyder]

Hi,

Assuming that the username is unique in your database and your SQL query returns a valid user record then you can use this syntax to check against any field that you have returned in your DataTable:-

YourUserValue = checkusr.Rows(0)(<"YourColumnName"> or <YourColumnIndex>).ToString

To explain the above, all collections in .NET are indexed from zero, so your first and only row will be 0 and then your columns will be indexed from 0 to X so you just need to specify the column that you need to check against.

A couple of other things for you:-

1) If you are using a DataAdapter to populate a data source, as you are, then you do not need to call dbplug.Open and dbplug.Close methods since the DataAdapter takes care of the opening and closing of the connection string for you.

2) When declaring variables do try and use descriptive names to make your code more readable, both for yourself and others who may need to read your code in the future.

3) When posting code in the future, please use the Advanced Button and add code tags where necessary.

Hope that helps.

Cheers,

Ian

 

[Gildob]

many many thanks for your explanation, now I have the value on my variable, but I try to check if tha value of password field is the same on the db, the msgbox display me only the "password not correct"

Where's wrong?

 Dim userid As Integer = 0
        Dim userLVL As Integer = 0
        Dim userpwd As String = ""
        If checkusr.Rows.Count = 0 Then
            MsgBox("utente non presente")
            Me.Close()
        Else
            userid = checkusr.Rows(0)("ID")
            userLVL = checkusr.Rows(0)("lvl")
            userpwd = checkusr.Rows(0)("pwd")
        End If

        If Varpwd = userpwd Then
            MsgBox("password ok")
        Else
            MsgBox("password non ok")

        End If

Many thanks !

 

[IanRyder]

Hi,

Oh, I see the problem. I missed two errors on your original post. These two lines are incorrect:-

Dim Varsusr As String = NameTextBox.ToString
Dim Varpwd As String = PwdTextBox.ToString

To get the value of the text in TextBox's then you have to read the Text property of the control. Therefore these should be:-

Dim Varsusr As String = NameTextBox.Text
Dim Varpwd As String = PwdTextBox.Text

Hope that helps.

Cheers,

Ian

 

[Gildob]

Great IAN
works fine!!!

many thanks again!