Searching database MS Access with VB.net with 2 textbox only

Question

I do the simple project using VB.net 2008 to link with MS Access 2007. I have two textbox which is data for ID and Fullname.

txtID: ID and txtFullname : Fullname and Button1 : Search

In MS Access My data for ID : 1,2 & 3 and My data for Fullname : John, Peter & Mike

The problem I facing now is only when I key-in data '1' in txtID, output was appear in txtFullname. But when I type '2' in txtID, the output is not appear in txtFullname.

Only ID 1 I can call. BUt another ID can't.

PLease Help me.

This is my code :

Imports System.Data.OleDb

Public Class Form1

Dim con As New OleDbConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\sample.accdb"
    con.Open()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim SQL As String = "SELECT * FROM phonebookTable WHERE ID= ?"

    If con.State = ConnectionState.Closed Then
        con.Open()
    End If

    Using cmd As New OleDbCommand(SQL, con)
        cmd.Parameters.AddWithValue("@p1", Convert.ToInt32(txtID.Text))

        Dim rdr As OleDbDataReader = cmd.ExecuteReader
        If rdr.HasRows Then
            rdr.Read()
            ' use the actual DB column name
            txtFullname.Text = rdr.Item("Fullname").ToString
        Else
            txtFullname.Text = "No records"
        End If
    End Using
End Sub

End Class

your query returns all rows; use a WHERE clause (using parameters) to get matching records from the database — Ňɏssa Pøngjǣrdenlarp, May 23 '14 at 00:33
Could you please be more specific in what you're looking to achieve because from what I have read so far multiple users have provided examples and research while receiving down votes. Which I presume is from other users finding the information irrelevant or not useful. — li x, May 23 '14 at 00:53
I already try solution by crimson and li_x but the problem still not solve. Now I try first Plutonix solution. Then I come back — user3664246, May 23 '14 at 02:44
what is the data type for the ID column in the database? That matters a great deal. — Ňɏssa Pøngjǣrdenlarp, May 23 '14 at 02:46
Li x...I just want to call the data from txtID (ID Item) and then the txtFullname was show the data from (Fullname Item): — user3664246, May 23 '14 at 02:56
It works fine for me. Make sure the name of the control you are typing into is `txtID` and maybe that the table is named `phonebookTable` but that should throw an error if that is wrong; and the ID column has to be Integer or Long. Those are the only reasons I can see it would fail. — Ňɏssa Pøngjǣrdenlarp, May 23 '14 at 04:48
Plutonix thank you very much!!! The problem solve. You code is correct but do not need to put rdr.Read. My Fullname in MS Access was wrong. You are brilliant!! Thank you — user3664246, May 23 '14 at 06:05
Please do not add "SOLVED" to your question title. If you have found an answer, post an answer. Or ask @Plutonix to post one. — Cody Gray - on strike, May 23 '14 at 07:24

score -1 · Answer 1 · answered May 23 '14 at 00:43

-1

 da = New OleDbDataAdapter("SELECT * FROM phonebookTable where ID = '" & txtID.text & "'", con)

answered May 23 '14 at 00:43

crimson

36
1
6

Could have done with a bit of an explanation, not mention avoiding the sql injection vector... – Tony Hopkinson May 23 '14 at 01:29

Searching database MS Access with VB.net with 2 textbox only

1 Answers1