variable referencing value in a table

[atticusr5]

hello all,

I have a question about assigning variables values based on values stored inside an sql database. I have the connection part down, but here is my basic set-up.

I have a table with the values {username, password, isAdmin}, where username and password are strings and "isAdmin" is a bit variable. I am able to read in the username and password entered by the user and match it to the database fine, however I would like to also read in the "isAdmin" and set it to a local Boolean variable that will then direct the user to the proper form.

Can anyone help me with this? I can post what code I have if it will be any help.

Many thanks!

[Reverend Jim]

Go ahead and post the code. It would also help to see the structure of the table. How you set the value of a local boolean for isAdmin will depend on the field type. For example, if isAdmin is a single character field containing "Y" or "N" (and assuming you are using ADO) it would look something like

rset.Open("select isAdmin from mytable where username = '" & username & "'",...

dim admin as Boolean = False

if not rset.EOF then
    admin = rset("isAdmin").Value = "Y"
end if

rset.Close()

[atticusr5]

Ok so i got my select to work, any clues on why the following insert code would not be work?

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If tbox_password.Text = tbox_confirmpass.Text Then
            Dim connection As New SqlClient.SqlConnection
            Dim command As New SqlClient.SqlCommand
            Dim adaptor As New SqlClient.SqlDataAdapter
            Dim dataset As New DataSet

            connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")
            'connection.ConnectionString = ("Data Source=prodsql2008;Persist Security Info=True;User ID=writingcenteraccess;Password=pacman79")
            command.CommandText = "SELECT * FROM [login] WHERE login_username= '" & tbox_username.Text & "';"
            connection.Open()
            command.Connection = connection
            adaptor.SelectCommand = command
            adaptor.Fill(dataset, "0")

            Dim count = dataset.Tables(0).Rows.Count

            If count > 0 Then
                lbl_message.Text = "That username already exsits"
            Else
                Dim command2 As New SqlClient.SqlCommand
                Dim adaptor2 As New SqlClient.SqlDataAdapter
                Dim dataset2 As New DataSet
                'Set the admin variable
                Dim isAdmin As Boolean

                If chbox_admin.Checked = True Then
                    isAdmin = True
                Else
                    isAdmin = False
                End If

                command2.CommandText = "INSERT INTO [login] VALUES('" & tbox_username.Text & "','" & tbox_password.Text & "','" & isAdmin & "');"
                command2.Connection = connection
                command2.ExecuteNonQuery()
                ' adaptor2.InsertCommand = New SqlClient.SqlCommand("INSERT INTO [login] (login_username, login_password, login_isAdmin) VALUES(" & tbox_username.Text & "," & tbox_password.Text & "," & isAdmin & ")", connection)

                'adaptor2.InsertCommand = command2
                'adaptor2.InsertCommand.ExecuteNonQuery()


                'Dim count2 = dataset2.Tables(0).Rows.Count


            End If
        Else

            lbl_message.Text = "The passwords do not match"
            lbl_message.Show()
            tbox_password.Clear()
            tbox_confirmpass.Clear()
            lbl_password.ForeColor = Color.Red
            lbl_confirmpass.ForeColor = Color.Red
        End If
    End Sub

[Reverend Jim]

In what way does it not work? Are you getting an error message? Is the resulting data not what you expect? What are the values of

tbox_username.Text
tbox_password.Text
isAdmin

What is the structure of [login]? I suspect that isAdmin is going into the query as the string "True" or "False". If isAdmin (in the table) is abit value then you will have to translate true/false into 1/0 in the query.

Can't provide definitive help without complete information.

[atticusr5]

ah sorry for not clarifying.

the table login has three values, with username and password stored as varchars and isAdmin as a bit value.

the error is a runtime error, in that the debugger does not sound a warning, but on submit of the data, nothing happens, no data is added. Perhaps it is the failure of not converting the true/false into a 1/0. I will try that and see if the problem is fixed.

thanks for the insight so far.

[atticusr5]

here is what i have tried to throw together so far. there are now errors, but the code does not insert any data into the table (i am using typed input just to test)

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If tbox_password.Text = tbox_confirmpass.Text Then
            Dim connection As New SqlClient.SqlConnection
            Dim command As New SqlClient.SqlCommand
            Dim adaptor As New SqlClient.SqlDataAdapter
            Dim dataset As New DataSet



            connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")
            'connection.ConnectionString = ("Data Source=prodsql2008;Persist Security Info=True;User ID=writingcenteraccess;Password=pacman79")
            command.CommandText = "SELECT * FROM [login] WHERE login_username= '" & tbox_username.Text & "';"
            connection.Open()
            command.Connection = connection
            adaptor.SelectCommand = command
            adaptor.Fill(dataset, "0")

            Dim count = dataset.Tables(0).Rows.Count

            If count > 0 Then
                lbl_message.Text = "That username already exsits"
            Else
                'Dim command2 As New SqlClient.SqlCommand
                'Dim adaptor2 As New SqlClient.SqlDataAdapter

                ' Dim dataset2 As New DataSet
                '''''''''''''''''''''''''''''''''''''''''''''''''''                
                'Set the admin variable
                Dim isAdmin As Boolean

                If chbox_admin.Checked = True Then
                    isAdmin = True
                Else
                    isAdmin = False
                End If

                Dim queryString As String = _
                    "INSERT INTO login (login_username,login_password,login_isAdmin) VALUES ('panda','panda',0);"

                Dim command3 As New SqlClient.SqlCommand(queryString, connection)
                command3.ExecuteNonQuery()


            End If
        Else

            lbl_message.Text = "The passwords do not match"
            lbl_message.Show()
            tbox_password.Clear()
            tbox_confirmpass.Clear()
            lbl_password.ForeColor = Color.Red
            lbl_confirmpass.ForeColor = Color.Red
        End If
    End Sub

[Reverend Jim]

I tried to recreate your database here and ran the insert. It worked. How do you know the data has not been inserted? Do you have SQL Server Management Studio installed? I should disclose that I prefer ADO.Net over OLEDB. I find that fewer layers between my code and the data means less time debugging.

I see your connection string contains.

AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")

I suggest you use

Initial Catalog=Database1;Integrated Security=True;"