CODEWITHSUNDEEP
sqlvb.net
Randy
Randy

Reputation: 127

Compare db value with textbox value VB

My Table **PersonName** values Jim,Dan **Department** values Manager,User

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

Upvotes: 1

Views: 1232

Answers (1)

Mary
Mary

Reputation: 15081

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

Upvotes: 1

Related Questions