CODEWITHSUNDEEP
asp.netvb.netvalidationtextbox
jayteezer
jayteezer

Reputation: 115

Validate that textbox has numeric value in vb.net and compare value with database

enter image description hereI'm trying to validate a textbox where users will put an ID. The ID has to be numeric and at the same time compare to a valid ID in the database. When I was just validating for numeric, I didn't have any problems. But now that I have two conditions, my code doesn't work properly. Whenever I type in letters in the textbox and click a button, it gives me an error. The boolean is throwing me off lol. Below is my code:

Thank you in advance.

Protected Sub btnGo_Click(sender As Object, e As EventArgs) Handles btnGo.Click

    Dim dt As DataTable
    Dim dr As DataRow

    Dim Conn As New SqlConnection("Data Source=Computer;Initial Catalog=Catalog;Persist Security Info=True;User ID=userid;Password=password")
    Dim cmd As New SqlCommand("SELECT COUNT(*) FROM [tbl] WHERE [ID]=@Value", Conn)
    cmd.Parameters.Add("@Value", SqlDbType.NVarChar).Value = txtId.Text

    Conn.Open()
    Dim valueExistsInDB As Boolean = CBool(CInt(cmd.ExecuteScalar()) > 0)
    Conn.Close()

    If (IsNumeric(txtId.Text)) AndAlso valueExistsInDB = True AndAlso txtId.Text IsNot Nothing Then
        dt = GetDataTable("SELECT ID, LEFT(SANZ_ID, PATINDEX('%.%', SANZ_ID) -1) AS City, CASE WHEN ST_DIR_ID = 1 THEN 'NB' WHEN ST_DIR_ID = 2 THEN 'SB' WHEN ST_DIR_ID = 3 THEN 'EB' WHEN ST_DIR_ID = 4 THEN 'WB' END AS ST_DIR, STREET_OF_TRAVEL, CROSS_STREET, (SELECT TOP 1 CASE WHEN STATUS_ID = 1 THEN 'F' WHEN STATUS_ID = 2 THEN 'P' WHEN STATUS_ID = 3 THEN 'I' WHEN STATUS_ID = 4 THEN 'N' WHEN STATUS_ID = 5 THEN 'A' END FROM tbl where dbo.tbl.ID=ID) AS STATUS FROM tbl WHERE ID=" & txtId.Text)
        dr = dt.Rows(0)
        labelStreet.Text = dr("street_of_travel")
        labelCrossStreet.Text = dr("cross_street")
        labelCity.Text = dr("city")
        labelDir.Text = dr("st_dir")
        labelAda.Text = dr("STATUS")
        'dropdownStatus.SelectedValue=

        dropdownStatus.Visible = True
        txtNotes.Visible = True
        btnSave.Visible = True

    Else
        MessageBox.Show("ID not found! Please input a valid ID.")
    End If

End Sub

Upvotes: 1

Views: 2869

Answers (1)

Steve
Steve

Reputation: 216263

If ID is a numeric field then you should pass a numeric parameter not an NVarChar one

' First try to convert the input text to an integer '
' this should be done here before acting on the db '
Dim id As Integer
if Not Int32.TryParse(txtId.Text, id) Then
    MessageBox.Show("Error, not a valid number")
    return
End If

Dim cmdText = "SELECT COUNT(*) FROM [tbl] WHERE [ID]=@Value"
Using Conn = New SqlConnection(....)
Using cmd As New SqlCommand(cmdText, Conn)
   cmd.Parameters.Add("@Value", SqlDbType.Int).Value = id
   Conn.Open()
   Dim valueExistsInDB = CBool(CInt(cmd.ExecuteScalar()) > 0)

   ' At this point you don't need anymore to check if the input value'
   ' is numeric and your if is more simple.....'
   if valueExistsInDB Then
   ......
   ... continue with your code ....

   Else
       MessageBox.Show("ID not found! Please input a valid ID.")
   End if
End Using
End Using

Upvotes: 1

Related Questions