Amos Wu Amos Wu - 2 months ago 7
Vb.net Question

How do you restrict what kind of data can be input into a Textbox for Visual Basic?

I have created a simple Windows Forms Application through Visual Studio 2013, with two text boxes and a button, which I linked to a database in MySql through MySql connector. What it does is after I input a product id and status number for a product that is found in table A, clicking the button would transfer certain columns for that product's row from table A to table B, and then the original row in table A after the data is transferred would be deleted. This is done by calling a stored procedure from that MySql database that uses the values inputted in the text boxes as parameters. Here's the code for the app below so far.

**

Imports MySql.Data.MySqlClient
Imports test_mysql_connection

Public Class Form1
Dim cmd As New MySqlCommand
Dim connection As New MySqlConnection("Database=****;" & _
"Data Source=****;" & _
"User Id=****;Password=****")

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
connection.Open()

cmd.Connection = connection
cmd.CommandText = "StoredProcedureC"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@p_product_id", TextBox1.Text)
cmd.Parameters("@p_product_id").Direction = ParameterDirection.Input
cmd.Parameters.AddWithValue("@p_mission_status", TextBox2.Text)
cmd.Parameters("@p_mission_status").Direction = ParameterDirection.Input
cmd.ExecuteNonQuery()

MessageBox.Show("successfully relocated!")
TextBox1.Clear()
TextBox2.Clear()
connection.Close()

End Sub

End Class


**

As the app currently stands though, even though it successfully performs the task I want it to do, I could literally input anything in those boxes, even if they have nothing to do with anything found in table A, and I would still get the message "successfully relocated!" even if nothing from table A is being transferred to table B. I especially don't want to be able to enter the product id and mission number of something from table A that has already been transferred to B and deleted from A. So I'm wondering about how I can restrict what I can input into these text boxes, and perhaps add a few error messages if a product id and mission status that is inputted isn't found on table A. Thanks for any feedback beforehand!

Answer

The simplest way is indicated below. Alternatively you could handle the textbox.Validating event and cancel anything that isn't numeric.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    connection.Open()

    if not IsNumeric(TextBox1.Text) then Msgbox("Must be a number") : return


    cmd.Connection = connection
    cmd.CommandText = "StoredProcedureC"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@p_product_id", TextBox1.Text)
    cmd.Parameters("@p_product_id").Direction = ParameterDirection.Input
    cmd.Parameters.AddWithValue("@p_mission_status", TextBox2.Text)
    cmd.Parameters("@p_mission_status").Direction = ParameterDirection.Input
    dim rows = cmd.ExecuteNonQuery()

    if rows <> 0 then
        MessageBox.Show("successfully relocated!") 
    else
        MessageBox.Show("no matching rows.") 
    end if
    TextBox1.Clear()
    TextBox2.Clear()
    connection.Close()

End Sub