Change data in textbox according a DB result VB.net

Question

I've designed a form in VB.net based on ODBC connection.

My user need to insert a product number in textbox1 and the description pulled from the DB is automatically shown in textbox2 after the user presses the ENTER key.

After seeking on the net, I wrote something that doesn't work.

Private Sub Product_TextChanged(sender As Object, e As EventArgs) Handles Product.TextChanged
    Dim P, SQL As String
    Dim cn As OdbcConnection
    Dim cmd As OdbcCommand

    P = Product.Text

    If P = "" Then
        Exit Sub
    Else
        cn = New OdbcConnection("Driver={Microsoft ODBC for Oracle}; Server=*****; uid=*****;pwd=*****")
        SQL = "select part_long_name from dbtrans.parts where part_code='" & P & "'"
        cn.Open()
        cmd = New OdbcCommand(SQL)
        Description.Text = cmd.ToString
    End If
End Sub

When I run the form, I receive this output

Curious. What DB are you using that requires ODBC. No .net provider? — Mary, Jan 15 '19 at 22:44

score 0 · Answer 1 · answered Jan 15 '19 at 13:50

You have to use Using for database objects.
You have to use parameters instead of string concatenation.
You have to link your Command to the Connection object you have created.
Finally, you have to actually execute your command instead of just creating it.

Using cn = New OdbcConnection("Driver={Microsoft ODBC for Oracle}; Server=*****; uid=*****;pwd=*****")
    cn.Open()
    Using cmd = New OdbcCommand("select part_long_name from dbtrans.parts where part_code=?", cn)
        cmd.Parameters.Add(Nothing, OdbcType.VarChar, 255).Value = P

        Description.Text = CType(cmd.ExecuteScalar(), String)
    End Using
End Using

Moiyd · Accepted Answer · 2019-01-15T16:48:46.453

-1

To perform an Enter Keypress Event. Try this:

1) Declare connection variables somewhere in the top of your form.

Dim Str As String = "Driver={Microsoft ODBC for Oracle}; Server=*****; uid=*****;pwd=*****"
Dim con As New Odbc.OdbcConnection(Str)
Dim CMD As New Odbc.OdbcCommand

2) Add Your KeyPress Event to your TextBox Control

Private Sub Product_KeyPress(sender As Object, e As KeyPressEventArgs) Handles Product.KeyPress
   Select Case e.KeyChar
       Case ChrW(Keys.Enter) 'This when End-User presses Enter Key
        P = Product.Text
        If P = "" Then
           Exit Sub
        Else
           CMD = New Odbc.OdbcCommand("select part_long_name from dbtrans.parts where part_code='" & P & "'", con)
          If con.State = ConnectionState.Open Then con.Close()
            con.Open()
            Description.Text = CType(cmd.ExecuteScalar(), String)
            con.Close()
          End If
        End If
  End Select
End Sub

edited Jan 15 '19 at 16:48

answered Jan 15 '19 at 14:14

Moiyd

55
2
7

1

Its work good!! just one correct "Private Sub Product_KeyPress(sender As Object, e As KeyPressEventArgs) Handles Product.KeyPress" THANKS!!!!!! – user3306637 Jan 15 '19 at 15:06
Oh, that typo, you are right, it should be KeyPressEventArgs – Moiyd Jan 15 '19 at 16:48
I used to have a Form level connection but that is really frowned upon. A connection object needs to be disposed not just closed. A Using block will take care of that for you. It can be very bad to concatenate strings for Sql statements. Use parameters. Why a Select Case when there is only one case? – Mary Jan 15 '19 at 22:43
With regards select case, that was an example if you want to implement more than KeyPress action. For select statement, I would have done it inside a class, and call it within the function. – Moiyd Jan 16 '19 at 06:04

Change data in textbox according a DB result VB.net

2 Answers2