Compare db value with textbox value VB

Question

My Table

I load the windows user name to textbox1.text using 'System.Environment' After that I query this and compare the textbox value to the PersonName in db if it matches, I want to get the relevant Department name ie if it's 'manager' then I want to display a form from menuitem_click event. My code is below it dosent work can some one please help with this.

Private Sub MySamplesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles MySamplesToolStripMenuItem.Click
    Dim cn As New SqlClient.SqlConnection("Data Source=ffff;Initial Catalog=ffff;User ID=****;Password=****;")
    Dim cmd As New SqlClient.SqlCommand
    Dim tbl As New DataTable
    Dim da As New SqlClient.SqlDataAdapter
    Dim reader As SqlClient.SqlDataReader
    Dim ta As String
    Try
        cn.Open()
        Dim sql As String
        sql = "select * from dbo.Person where [PersonName] ='" + TextBox1.Text + "'"
        cmd = New SqlClient.SqlCommand(sql, cn)
        reader = cmd.ExecuteReader
        While reader.Read
            ta = reader.Item("Department")
            If ta = 'Maneger' Then
                Form2.Show()
            End If
            ' TextBox2.Text = reader.Item("Department")
            'TextBox2.Text = reader.Item("dob")

        End While
        cn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

End Sub

Better learn to use sql parameters to avoid sql injection and formatting errors. Also use the Using-End Using block on your disposable objects. Don't know if this is an English issue or not, but it's spelled "Manager" normally. We don't know what "doesn't work" means. What happens when you debug this code line by line? — LarsTech
– LarsTech, Commented Mar 11, 2019 at 15:57
Thanks for the reply "If ta = 'Manager' Then" getting the error near the equal sign saying 'expression expected' — Randy
– Randy, Commented Mar 11, 2019 at 16:04
Your data table image shows "Maneger" though. I was just pointed that out in case it was spelled wrong. Use double quotes, not single quotes. — LarsTech
– LarsTech, Commented Mar 11, 2019 at 16:08
Hi I did try that no errors now but form2.show command docent display the form2 — Randy
– Randy, Commented Mar 11, 2019 at 16:23
This is where you debug the program. Put a break point on While reader.Read and then step through your code line by line and hover your mouse over the variables to see what they are. — LarsTech
– LarsTech, Commented Mar 11, 2019 at 16:26

Mary · Accepted Answer · 2019-03-11 21:16:44Z

1

No matter how you spell it, Manager or Maneger, just make sure what is in the database matches what is in your If statement. I think I would use a drop down box for you to select the Department wherever you are inserting the Person so the Department name would match.

The Using...End Using blocks ensure that you database objects are closed and disposed even if there is an error.

You can pass your Sql statement and the connection directly to the constructor of the command. If all you need is the Department then don't drag down all the date with "*".

Never concatenate strings to build Sql statements. A hacker could type in TextBox1 "Joe; Drop Table dbo.Person;" Using parameters stops this hole because the .Value of the parameter is treated as only a value not executable code.

You are only expecting one value in return so you can use .ExecuteScalar which returns the first column of the first row in the result set.

Your code is very fragile because I suspect you could have duplicate names unless you require unique user names.

    Private Sub MySamplesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles MySamplesToolStripMenuItem.Click
    Try
        Using cn As New SqlClient.SqlConnection("Data Source=ffff;Initial Catalog=ffff;User ID=****;Password=****;")
            Using cmd As New SqlClient.SqlCommand("Select Department From dbo.Person Where PersonName = @Name;", cn)
                cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = TextBox1.Text
                cn.Open()
                Dim ta As String = cmd.ExecuteScalar.ToString
                If ta = "Maneger" Then
                    Form2.Show()
                End If
                TextBox2.Text = ta
            End Using
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

edited Mar 11, 2019 at 21:16

answered Mar 11, 2019 at 19:31

Mary

15.1k3 gold badges20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LarsTech Over a year ago

You don't need that cn.Close() line because it will close automatically from the End-Using block, even if an exception is raised.

Mary Over a year ago

@LarsTech Corrected the code. I also move the Try...End Try so if there was an error, the MessageBox wouldn't hold up the End Using.

Collectives™ on Stack Overflow

Compare db value with textbox value VB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related