Get Record Based on ID from User Input

[Newander]

Hey everyone,

I'm new to VB/ADO.net and I've got what I hope is a pretty simple question.

I have a PgSQL database, with a primary key of employee ID.

I've created the form to edit the employee ID, however, I'd like to create an on enter event in that field.

The purpose of this event would be to allow my users to type in a specific employee ID, hit enter, and go to that record.

Can somebody point me in the right direction here, either an article or some example code?

Thx

Newander

 

[jmcilhinney]

Please post in the most appropriate forum for the topic of your thread, not just the first one you come to. The VS.NET is for IDE issues, not coding issues, and the General forums are for topics that don't fit into any of the more specific forums. Moved.

Also, please provide a title that describes the topic of the thread. Renamed.

Handle the KeyDown event of your TextBox and trap the Enter key. Use a DataReader to get the data from the database for that ID.

Private Sub TextBox1_KeyDown(ByVal sender As Object, _
                             ByVal e As KeyEventArgs) Handles TextBox1.KeyDown
    If e.KeyCode = Keys.Enter Then
        Using connection As New OleDbConnection("connection string here")
            Using command As New OleDbCommand("SELECT * FROM Employee WHERE EmployeeID = @EmployeeID", _
                                              connection)
                connection.Open()

                Using reader As OleDbDataReader = command.ExecuteReader(CommandBehavior.SingleRow)
                    If reader.HasRows Then
                        'Get data from reader, e.g.
                        Dim name As String = CStr(reader("Name"))
                    Else
                        MessageBox.Show("No such employee.")
                    End If
                End Using
            End Using
        End Using
    End If
End Sub

 

[Newander]

Thanks for the quick response,

It was helpful, though I've changed it some to meet some new needs.

Private Sub IdTextBox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles IdTextBox.KeyDown
        If e.KeyCode = Keys.Enter Then
            Using connection As New Odbc.OdbcConnection("Driver=PostgreSQL35W;Server=myserver;Port=5432;Database=mine;Uid=mine;Pwd=mine;")
                Using command As New Odbc.OdbcCommand("SELECT * FROM Jobs WHERE id = @id", _
                                                  connection)
                    connection.Open()

                    Using reader As Odbc.OdbcDataReader = command.ExecuteReader(CommandBehavior.SingleRow)
                        If reader.HasRows Then
                            'Get data from reader, e.g.
                            Dim name As String = CStr(reader("id"))
                        Else
                            MessageBox.Show("No Such Job.")
                        End If
                    End Using
                End Using
            End Using
        End If
    End Sub

Ok, so now as you can see I'm searching through Job ID's now instead of employee ID's. The code debugs fine, however, it does not have the desired effect.

When I enter a job number, say I have it starting on job number 1 and put job number 2 into the box then hit enter it just beeps.

Any Ideas my friend?

Thx
Newander

 

[cjard]

Enter, or Return? They are different keys..

 

[jmcilhinney]

Enter, or Return? They are different keys..

Actually, they're not. Try this:

MessageBox.Show(CInt(Keys.Enter).ToString(), Keys.Enter.ToString())
MessageBox.Show(CInt(Keys.Return).ToString(), Keys.Return.ToString()

 

[jmcilhinney]

Thanks for the quick response,

It was helpful, though I've changed it some to meet some new needs.

Private Sub IdTextBox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles IdTextBox.KeyDown
        If e.KeyCode = Keys.Enter Then
            Using connection As New Odbc.OdbcConnection("Driver=PostgreSQL35W;Server=myserver;Port=5432;Database=mine;Uid=mine;Pwd=mine;")
                Using command As New Odbc.OdbcCommand("SELECT * FROM Jobs WHERE id = @id", _
                                                  connection)
                    connection.Open()

                    Using reader As Odbc.OdbcDataReader = command.ExecuteReader(CommandBehavior.SingleRow)
                        If reader.HasRows Then
                            'Get data from reader, e.g.
                            Dim name As String = CStr(reader("id"))
                        Else
                            MessageBox.Show("No Such Job.")
                        End If
                    End Using
                End Using
            End Using
        End If
    End Sub

Ok, so now as you can see I'm searching through Job ID's now instead of employee ID's. The code debugs fine, however, it does not have the desired effect.

When I enter a job number, say I have it starting on job number 1 and put job number 2 into the box then hit enter it just beeps.

Any Ideas my friend?

Thx
Newander

You've got quite a few problems with that code. First up, you create a command with a parameter in the SQL code but you never add a parameter to the command itself, so how is the ID supposed to get into the query?

Secondly, you test the reader's HasRows property but, if it's True, you don't call Read, so you never actually advance to the data to read it.

Finally, if you did get as far as getting the data that was read you'd be getting the ID into a variable and doing nothing with it, so nothing useful would ever happen.

 

[cjard]

Actually, they're not. Try this:

MessageBox.Show(CInt(Keys.Enter).ToString(), Keys.Enter.ToString())
MessageBox.Show(CInt(Keys.Return).ToString(), Keys.Return.ToString()

I feel robbed!

And OP; Postgres? Ugh.. Nothing like using an wizard-unsupported database when youre learning ADO.NET; youre missing 90% of the enhancements and good functionality (from a well-formed OO point of view) and it's forcing you to make rather poor coding decisions (like putting DB code in event handlers.. )