I have inserted searching through passing parameter likewise. In search form there is only one textbox to pass the value. But there are three field names which are associated with that text box.

For example: I have searched by passing phone number
But there three field name tel1, tel2 and tel3 which which are called when the phone number is passed. my code is as follows. my frontend is vb.net and my backend is ms access.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=K:\Database\db1.mdb"
    Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection(conStr)
    Dim S As String
    Dim datagrid1 As New DataGrid

    Dim tmp As New ADODB.Recordset
    tmp.Open("add1", conStr, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

    If TextBox1.Text = tmp.Fields("tel1").Value Then
        S = "select * from add1 where tel1='" & TextBox1.Text & "'"
    ElseIf TextBox1.Text = tmp.Fields("tel2").Value Then
        S = "select * from add1 where tel2='" & TextBox1.Text & "'"
    ElseIf TextBox1.Text = tmp.Fields("tel3").Value Then
        S = "Select * from add1 where tel3='" & TextBox1.Text & "'"

    ElseIf Not (tmp.EOF = True And tmp.BOF = True) Then : MsgBox("no record found")
    End If
    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(S, con)
    da.Fill(Db1DataSet1, "add1")
    datagrid1.DataSource = Db1DataSet1.DefaultViewManager
End Sub

In this snippet I have faced one problem. It shows one build error at

OleDb.OleDbDataAdapter(S, con)

which says

Variable 'S' is used before it has been assigned a value. A null reference exception could result at runtime.   

And when I run this program it executes until I pass the values present in the field , but when i pass the values not present in the fields then the msg box is shown as mentioned the program...but the problem persists when I click the ok button of msg box or try to stop debugging.
It shows the error at

da.Fill(Db1DataSet1, "add1")

which shows

OledbException was unhandled


Command text was not set for the command object.


I m a beginner of VB.net. so please help me what type of error is this and how can I solve this problem. please provide me with appropriate code to resolve the problem.

Anticipating the help.



Edited by diafol: fixed formatting

11 Years
Discussion Span
Last Post by preetham.saroja

Hi Kshiteesh,

few points what i have noted here...:

1. Before Creating the DataAdapter, u are not checking if "S" is blank or not.. the error which u have may be the same..
or else in ur If condition if nothing found, then exit sub..

2. Use a new Dataset. Check this:

Dim ds AsNew DataSet
da.Fill(ds, "add1")
DataGrid1.DataSource = ds.DefaultViewManager



always make sure,u declare the
str,connectionstring,sqlcommand,sqldataadapter in this order..
Even if u try to access adapter b4 sqlcommand-then u get the errors(which u have got)...so,make sure first they are declared& then there objects are accesed....

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.