OleDbDataAdapter Help!!!! (RESOLVED)

Hey all need some help!!

At the moment i have a simple form that retrieves data from a database using a dataAdapter using SQL fills a DataSet then binds the data to textboxes.

What i want to do is create a textbox called 'txtSearch.text then when 'btnSearch' button is clicked it will retrieve that record and display in the textboxes

i think i will have to use a WHERE clause in the SQL in the OleDbDataAdapter to specify a specific Student_ID and direct it to the textbox txtsearch (How do i do tha?)

not sure how to add parameters to my SQL in the dataAdpater to do this can anyone edit my code so that can be done.... thanks

VB Code:

'Declare Objects...
    Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter( _
        "SELECT Student_ID, Student_Fname, Student_Lname, Date_of_Birth, Address, Post_Code, Telephone_No " & _
        "FROM Student", OleDbConnection1)
 
    Dim myDS As DataSet
    Dim myDV As DataView
 
    Private Sub FillDataSetAndView()
        'Initialize a new instance of the DataSet object...
        myDS = New DataSet
 
        'Set Connection and Fill DataSet object...
        myAdapter.SelectCommand.Connection = OleDbConnection1
        myAdapter.Fill(myDS, "Student")
 
        'Set the DataView object to the DataSet
        myDV = New DataView(myDS.Tables("Student"))
    End Sub
 
    Private Sub BindFields()
 
        'Clear any previous bindings...
        txtStudentID.DataBindings.Clear()
        txtFname.DataBindings.Clear()
        txtSname.DataBindings.Clear()
        dtpDOB.DataBindings.Clear()
        txtAddress.DataBindings.Clear()
        txtPostCode.DataBindings.Clear()
        txtTelephone.DataBindings.Clear()
 
        'Add new bindings to the DataView object...
        txtStudentID.DataBindings.Add("Text", myDV, "Student_ID")
        txtFname.DataBindings.Add("Text", myDV, "Student_Fname")
        txtSname.DataBindings.Add("Text", myDV, "Student_Lname")
        dtpDOB.DataBindings.Add("Text", myDV, "Date_of_Birth")
        txtAddress.DataBindings.Add("Text", myDV, "Address")
        txtPostCode.DataBindings.Add("Text", myDV, "Post_Code")
        txtTelephone.DataBindings.Add("Text", myDV, "Telephone_No")
 
    End Sub
 
    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        'Fill the dataset and bind the field...
        FillDataSetAndView()
        BindFields()
    End Sub

thanks for the reply

at the university i attend we are all given a card with our Student ID e.g
02016168 is a student number and we all must know the number for exams and stuff

Hey done what you said but had an error!!!

An unhandled exception of type 'System.NullReferenceException' occurred in WindowsApplication1.exe

Additional information: Object reference not set to an instance of an object.

All the the below is in yellow with the above error

VB Code:

Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter( _
        "SELECT Student_ID, Student_Fname, Student_Lname, Date_of_Birth, Address, Post_Code, Telephone_No " & _
        "FROM Student WHERE Student_Fname='" & txtSearchCriteria.text & "%'", OleDbConnection1)

can anyone help????

Hey kulrom

got it to work by re arranging the code

I added the 'Dim myAdapter as OleDbDataAdapter constructor and placed it in the FillDataSetAndView() walllahhh work

cheers again


From this

VB Code:

'Declare Objects...
        Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter( _
            "SELECT Student_ID, Student_Fname, Student_Lname, Date_of_Birth, Address, Post_Code, Telephone_No " & _
            "FROM Student WHERE Student_ID ='" & txtSearchCriteria.Text & "'", OleDbConnection1)
 
    Dim myDS As DataSet
    Dim myDV As DataView
 
    Private Sub FillDataSetAndView()
        'Initialize a new instance of the DataSet object...
        myDS = New DataSet
 
        'Set Connection and Fill DataSet object...
        myAdapter.SelectCommand.Connection = OleDbConnection1
        myAdapter.Fill(myDS, "Student")
 
        'Set the DataView object to the DataSet
        myDV = New DataView(myDS.Tables("Student"))
    End Sub

to this

VB Code:

Private Sub FillDataSetAndView()
        'Initialize a new instance of the DataSet object...
        myDS = New DataSet
 
        'Declare Objects...
        Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter( _
            "SELECT Student_ID, Student_Fname, Student_Lname, Date_of_Birth, Address, Post_Code, Telephone_No " & _
            "FROM Student WHERE Student_ID ='" & txtSearchCriteria.Text & "'", OleDbConnection1)
 
        'Set Connection and Fill DataSet object...
        myAdapter.SelectCommand.Connection = OleDbConnection1
        myAdapter.Fill(myDS, "Student")
 
        'Set the DataView object to the DataSet
        myDV = New DataView(myDS.Tables("Student"))

i have solved that problem by adding a dropdown box with all the fields of the table in it.. then if the Firstname is selected from the drop down box it will search the firstname field and the same for all the fields