Code for search button in vb.net

Question

tomexlfc 0 Light Poster

12 Years Ago

Hi all,
I have already written a code to search for staff records from my staff database and it's working fine, but the problem is, if someone enters a wrong a wrong staff id or just any number or alphabets, it still displays the "Found Record" message, instead of "No Record Found".

Please I need to write a check if a valid staff id is entered in the search textbox and if its invalid, "No Record Found" should be displayed.

Here's my code below. Thanks for your help in advance

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        provSrcStaf = "PROVIDER=Microsoft.ACE.Oledb.12.0;"
        souSrcStaf = "DATA SOURCE=D:\RTLSALimited.accdb"

        conSrcStaf.ConnectionString = provSrcStaf & souSrcStaf

        If txtSearch.Text = "" Then
            MessageBox.Show("Enter valid Staff ID", "RTL SA Rubber Limited.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtSearch.Focus()
        Else
            StafSQL = "SELECT * FROM Staff WHERE Staff_ID='" & txtSearch.Text & "'"
            daSrcStaf = New OleDb.OleDbDataAdapter(StafSQL, conSrcStaf)
            daSrcStaf.Fill(dsSrcStaf, "Staff2")
            inc = 0

            MessageBox.Show("Record found!", "RTL SA Rubber Limited.", MessageBoxButtons.OK, MessageBoxIcon.Information)

            txtStaffName.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(1)
            txtDesignation.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(2)
            txtAddress.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(3)
            txtTelNo.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(4)
            txtUsername.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(5)
            txtPassword.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(6)

            btnEdit.Enabled = True
            btnDelete.Enabled = True
            btnSearch.Enabled = False

        End If
    End Sub

microsoft-access seo vb.net

5 Contributors
26 Replies
16K Views
1 Year Discussion Span
Latest Post 11 Years Ago Latest Post by waseem.akhtar.3572

hericles 289 Master Poster

12 Years Ago

Check if Staff2 has any rows. If zero rows then say no records found.

if(!dsSrcStaf.Tables("Staff2").Rows.Count() > 0) {
  // display "no records found" message
} else {
  // the code you already have
}
But get rid of the message box saying search found:) No one wants to click on a message box when things worked out OK. The fact that results populated is enough of a hint that things worked.

M.Waqas Aslam 67 Posting Pro in Training

12 Years Ago

hello!
check this code may be this helps you

Dim con As New OleDbConnection("Connection String")
      Dim cmd As OleDbCommand = New OleDbCommand("select * from table1 where StaffID='"&txtStaffID.text&"'", con)
        con.Open()
        Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        Dim myDataSet As DataSet = New DataSet()
        myDA.Fill(myDataSet, "MyTable")
        dgvTableList.DataSource = myDataSet.Tables("MyTable").DefaultView
        con.Close()
'this code will populate all the records of given id , now the most imp thing is 
'WRITE THIS CODE ON THE TEXTCHANGE EVENT OF THE TEXTBOX NAME TXTSTAFFID.

hope this will helps you , if your prob is solved then please mark this thread solved and vote me up :)

Regards

M.Waqas Aslam 67 Posting Pro in Training

12 Years Ago

ohh ok ok . well do like this :)

Dim con As New OleDbConnection("Connection String")
      Dim cmd As OleDbCommand = New OleDbCommand("select * from table1 where StaffID='"&txtStaffID.text&"'", con)
        con.Open()
        Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        Dim myDataSet As DataSet = New DataSet()
        myDA.Fill(myDataSet, "MyTable")
'take a bindingsource from the tool box then
bindingsource.datasource = myDataSet.Tables("MyTable")
if bindingsource.count > 0 then
msgbox("no record found")
else
  dgvTableList.DataSource = myDataSet.Tables("MyTable").DefaultView
end if 
        con.Close()

hope this time it works fine , :)

Regards

Edited 12 Years Ago by M.Waqas Aslam because: n/a

M.Waqas Aslam 67 Posting Pro in Training

12 Years Ago

:P please first check your msgbox text then change the ">" to "<" :P its my mistake sorry :)

Regards

kingsonprisonic 42 Posting Whiz in Training

12 Years Ago

see the top right corner of each post..

there a upper arrow button. just click that...

tomexlfc commented: kingsonprisonic gave a good assistance on my code problems and he was very detailed and explanatory +2

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kingsonprisonic gave a good assistance on my code problems and he was very detailed and explanatory

tomexlfc 0 Light Poster · Answer 1 · 2012-02-18T10:32:36+00:00

Check if Staff2 has any rows. If zero rows then say no records found.

if(!dsSrcStaf.Tables("Staff2").Rows.Count() > 0) {
  // display "no records found" message
} else {
  // the code you already have
}
But get rid of the message box saying search found:) No one wants to click on a message box when things worked out OK. The fact that results populated is enough of a hint that things worked.

Hi Hericles,
thanks for the reply. The first line of your code is giving an error, where you wrote:
"!dsSrcStaf", the error says "Leading "." or "!" can only appear inside a 'With' statement."
What do you suggest i put there instead?
Thank you

tomexlfc 0 Light Poster · Answer 2 · 2012-02-18T11:02:17+00:00

hello!
check this code may be this helps you

Dim con As New OleDbConnection("Connection String")
      Dim cmd As OleDbCommand = New OleDbCommand("select * from table1 where StaffID='"&txtStaffID.text&"'", con)
        con.Open()
        Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        Dim myDataSet As DataSet = New DataSet()
        myDA.Fill(myDataSet, "MyTable")
        dgvTableList.DataSource = myDataSet.Tables("MyTable").DefaultView
        con.Close()
'this code will populate all the records of given id , now the most imp thing is 
'WRITE THIS CODE ON THE TEXTCHANGE EVENT OF THE TEXTBOX NAME TXTSTAFFID.

hope this will helps you , if your prob is solved then please mark this thread solved and vote me up :)

Regards

Hi waqasaslammmeo,

I already know how to write the code for searching a record and populating the text boxes and that's working fine.
The problem is, if wrong staff id or any number is entered in the search textbox and the search button clicked, i want it to display a message "No Record Found". I don't seem to get the code and that's all i need now.

tomexlfc 0 Light Poster · Answer 3 · 2012-02-18T11:22:19+00:00

ohh ok ok . well do like this :)

Dim con As New OleDbConnection("Connection String")
      Dim cmd As OleDbCommand = New OleDbCommand("select * from table1 where StaffID='"&txtStaffID.text&"'", con)
        con.Open()
        Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        Dim myDataSet As DataSet = New DataSet()
        myDA.Fill(myDataSet, "MyTable")
'take a bindingsource from the tool box then
bindingsource.datasource = myDataSet.Tables("MyTable")
if bindingsource.count > 0 then
msgbox("no record found")
else
  dgvTableList.DataSource = myDataSet.Tables("MyTable").DefaultView
end if 
        con.Close()

hope this time it works fine , :)

Regards

Hi, still giving me error and this time, a different error :(
I enter some jargon inside the text box and click the search button and it tells me "Record Found", instead of "No Record Found".
And then this error comes up "IndexOutOfRangeException was unhandled: There is no row at position 0"

tomexlfc 0 Light Poster · Answer 4 · 2012-02-18T11:33:42+00:00

:P please first check your msgbox text then change the ">" to "<" :P its my mistake sorry :)
Regards

Still the same error. Maybe I'll just keep figuring it out in some way by myself, I seem to always find a way out :(
Thanks for your great help :)

hericles 289 Master Poster Featured Poster · Answer 5 · 2012-02-18T13:01:09+00:00

Hi,
If the ! in the loop was stopping the process, reverse the if statements.
if(dsSrcStaf.Tables("Staff2").Rows.Count() > 0) {
// some rows exist so place you binding code here
} else {
// display "no records found" message
}

tomexlfc 0 Light Poster · Answer 6 · 2012-02-18T13:24:49+00:00

Hi,
If the ! in the loop was stopping the process, reverse the if statements.
if(dsSrcStaf.Tables("Staff2").Rows.Count() > 0) {
// some rows exist so place you binding code here
} else {
// display "no records found" message
}

Hi Hericles,

Thanks for the post, but it just brought up a new error message: "Object reference not set to an instance of an object." on the line:

ElseIf (dsSearch.Tables("Student").Rows.Count() > 0) Then

Please where to go from here?

Thank you

M.Waqas Aslam 67 Posting Pro in Training Featured Poster · Answer 7 · 2012-02-18T17:46:26+00:00

hello !
check this code and also the attached solution :) hope this time it works fine .

Dim mycon As New SqlConnection("Data Source=pakistan\sqlexpress;Initial Catalog=testing;Integrated Security=SSPI;")
        mycon.Open()
        Dim cmd As String
        cmd = "select * from product where pid=" & Val(TextBox1.Text)
        Dim da As New SqlDataAdapter(cmd, mycon)
        Dim dt As New DataTable
        da.Fill(dt)
        If dt.Rows.Count = 0 Then
            MsgBox("no records found.")
        End If
        DataGridView1.DataSource = dt
        mycon.Close()

Regards

tomexlfc 0 Light Poster · Answer 8 · 2012-02-18T18:56:14+00:00

hello !
check this code and also the attached solution :) hope this time it works fine .

Dim mycon As New SqlConnection("Data Source=pakistan\sqlexpress;Initial Catalog=testing;Integrated Security=SSPI;")
        mycon.Open()
        Dim cmd As String
        cmd = "select * from product where pid=" & Val(TextBox1.Text)
        Dim da As New SqlDataAdapter(cmd, mycon)
        Dim dt As New DataTable
        da.Fill(dt)
        If dt.Rows.Count = 0 Then
            MsgBox("no records found.")
        End If
        DataGridView1.DataSource = dt
        mycon.Close()

Regards

alright will check it now and let you know how it goes. thanks :)

tomexlfc 0 Light Poster · Answer 9 · 2012-02-18T19:41:40+00:00

hello !
check this code and also the attached solution :) hope this time it works fine .

Dim mycon As New SqlConnection("Data Source=pakistan\sqlexpress;Initial Catalog=testing;Integrated Security=SSPI;")
        mycon.Open()
        Dim cmd As String
        cmd = "select * from product where pid=" & Val(TextBox1.Text)
        Dim da As New SqlDataAdapter(cmd, mycon)
        Dim dt As New DataTable
        da.Fill(dt)
        If dt.Rows.Count = 0 Then
            MsgBox("no records found.")
        End If
        DataGridView1.DataSource = dt
        mycon.Close()

Regards

your codes dont seem to work

M.Waqas Aslam 67 Posting Pro in Training Featured Poster · Answer 10 · 2012-02-18T20:08:05+00:00

what :o , how ? it gives any error ? , it is 100% working at my end , and i also attach that solution :(

kingsonprisonic 42 Posting Whiz in Training · Answer 11 · 2012-02-18T20:35:56+00:00

Actually your programming LOGIC is not correct...

Your code:

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        provSrcStaf = "PROVIDER=Microsoft.ACE.Oledb.12.0;"
        souSrcStaf = "DATA SOURCE=D:\RTLSALimited.accdb"

        conSrcStaf.ConnectionString = provSrcStaf & souSrcStaf

        If txtSearch.Text = "" Then
            MessageBox.Show("Enter valid Staff ID", "RTL SA Rubber Limited.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtSearch.Focus()
        Else
            StafSQL = "SELECT * FROM Staff WHERE Staff_ID='" & txtSearch.Text & "'"
            daSrcStaf = New OleDb.OleDbDataAdapter(StafSQL, conSrcStaf)
            daSrcStaf.Fill(dsSrcStaf, "Staff2")
            inc = 0

'Modifying Here -----------------------------------------------------

if dsSrcStaf.Tables("Staff2").Rows.Count<1 Then


 MessageBox.Show("Enter valid Staff ID", "RTL SA Rubber Limited.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtSearch.Focus()
Exit Sub

End If



'Modifying Here -----------------------------------------------------





            MessageBox.Show("Record found!", "RTL SA Rubber Limited.", MessageBoxButtons.OK, MessageBoxIcon.Information)

            txtStaffName.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(1)
            txtDesignation.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(2)
            txtAddress.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(3)
            txtTelNo.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(4)
            txtUsername.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(5)
            txtPassword.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(6)

            btnEdit.Enabled = True
            btnDelete.Enabled = True
            btnSearch.Enabled = False

        End If
    End Sub

tomexlfc 0 Light Poster · Answer 12 · 2012-02-18T21:28:51+00:00

Actually your programming LOGIC is not correct...

Your code:

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        provSrcStaf = "PROVIDER=Microsoft.ACE.Oledb.12.0;"
        souSrcStaf = "DATA SOURCE=D:\RTLSALimited.accdb"

        conSrcStaf.ConnectionString = provSrcStaf & souSrcStaf

        If txtSearch.Text = "" Then
            MessageBox.Show("Enter valid Staff ID", "RTL SA Rubber Limited.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtSearch.Focus()
        Else
            StafSQL = "SELECT * FROM Staff WHERE Staff_ID='" & txtSearch.Text & "'"
            daSrcStaf = New OleDb.OleDbDataAdapter(StafSQL, conSrcStaf)
            daSrcStaf.Fill(dsSrcStaf, "Staff2")
            inc = 0

'Modifying Here -----------------------------------------------------

if dsSrcStaf.Tables("Staff2").Rows.Count<1 Then


 MessageBox.Show("Enter valid Staff ID", "RTL SA Rubber Limited.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtSearch.Focus()
Exit Sub

End If



'Modifying Here -----------------------------------------------------





            MessageBox.Show("Record found!", "RTL SA Rubber Limited.", MessageBoxButtons.OK, MessageBoxIcon.Information)

            txtStaffName.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(1)
            txtDesignation.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(2)
            txtAddress.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(3)
            txtTelNo.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(4)
            txtUsername.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(5)
            txtPassword.Text = dsSrcStaf.Tables("Staff2").Rows(inc).Item(6)

            btnEdit.Enabled = True
            btnDelete.Enabled = True
            btnSearch.Enabled = False

        End If
    End Sub

Hi Kings,

It's still showing this error message: "Object reference not set to an instance of an object." on the this line of code:

If dsSearch2.Tables("Student").Rows.Count < 1 Then

What's wrong with that line? Do i need to set a "new" object somewhere? I am so confused, please help :(

kingsonprisonic 42 Posting Whiz in Training · Answer 13 · 2012-02-18T21:38:53+00:00

Where r u declaring dsSearch2??

Please provide full code....
So i can help u.

tomexlfc 0 Light Poster · Answer 14 · 2012-02-18T22:21:26+00:00

Where r u declaring dsSearch2??
Please provide full code....
So i can help u.

Hi Kings,

Sorry for late reply, i was out for a while.
Here's the full code for the search button:

Public Class frmSearch

    Dim daSearch As OleDb.OleDbDataAdapter
    Dim dsSearch As New DataSet
    'Dim daSrch As OleDb.OleDbDataAdapter
    'Dim dsSrch As New DataSet
    Dim SrcInc As Integer

    Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim conSearch As New OleDb.OleDbConnection
        Dim conStr As String = "PROVIDER=Microsoft.ACE.Oledb.12.0;"
        Dim conStr2 As String = "DATA SOURCE=D:\DB2Test.accdb"
        Dim StudSQL As String
        Dim inc As Integer
        Dim dsSearch2 As New DataSet

        If txtStudID.Text = "" Then
            MessageBox.Show("Enter a student id", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtStudID.Focus()
        ElseIf dsSearch2.Tables("Student").Rows.Count < 1 Then
            'ElseIf dsSearch2.Rows.Count = 0 Then
            MessageBox.Show("Database not table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)
            Exit Sub

        Else
            StudSQL = "SELECT * FROM Student WHERE Student_ID='" & txtStudID.Text & "'"
            daSearch = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            'Dim daSearch As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            conSearch.ConnectionString = conStr & conStr2
            daSearch.Fill(dsSearch, "Student")
            inc = 0

            MessageBox.Show("Database table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)

            txtStudName.Text = dsSearch.Tables("Student").Rows(inc).Item(1)
            txtStatus.Text = dsSearch.Tables("Student").Rows(inc).Item(2)
            txtSex.Text = dsSearch.Tables("Student").Rows(inc).Item(3)
            txtFaculty.Text = dsSearch.Tables("Student").Rows(inc).Item(4)
            txtPassword.Text = dsSearch.Tables("Student").Rows(inc).Item(5)

        End If
    End Sub

tomexlfc 0 Light Poster · Answer 15 · 2012-02-18T22:23:34+00:00

Hi Kings,

Sorry for late reply, i was out for a while.
Here's the full code for the search button:

Public Class frmSearch

    Dim daSearch As OleDb.OleDbDataAdapter
    Dim dsSearch As New DataSet
    'Dim daSrch As OleDb.OleDbDataAdapter
    'Dim dsSrch As New DataSet
    Dim SrcInc As Integer

    Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim conSearch As New OleDb.OleDbConnection
        Dim conStr As String = "PROVIDER=Microsoft.ACE.Oledb.12.0;"
        Dim conStr2 As String = "DATA SOURCE=D:\DB2Test.accdb"
        Dim StudSQL As String
        Dim inc As Integer
        Dim dsSearch2 As New DataSet

        If txtStudID.Text = "" Then
            MessageBox.Show("Enter a student id", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtStudID.Focus()
        ElseIf dsSearch2.Tables("Student").Rows.Count < 1 Then
            'ElseIf dsSearch2.Rows.Count = 0 Then
            MessageBox.Show("Database not table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)
            Exit Sub

        Else
            StudSQL = "SELECT * FROM Student WHERE Student_ID='" & txtStudID.Text & "'"
            daSearch = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            'Dim daSearch As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            conSearch.ConnectionString = conStr & conStr2
            daSearch.Fill(dsSearch, "Student")
            inc = 0

            MessageBox.Show("Database table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)

            txtStudName.Text = dsSearch.Tables("Student").Rows(inc).Item(1)
            txtStatus.Text = dsSearch.Tables("Student").Rows(inc).Item(2)
            txtSex.Text = dsSearch.Tables("Student").Rows(inc).Item(3)
            txtFaculty.Text = dsSearch.Tables("Student").Rows(inc).Item(4)
            txtPassword.Text = dsSearch.Tables("Student").Rows(inc).Item(5)

        End If
    End Sub

I declared dsSearch2 only because it was giving the error message about "new instance of an object". I didn't declare it before.
Hope you can understand my codes.
Thanks for your help once again. Cheers!

kingsonprisonic 42 Posting Whiz in Training · Answer 16 · 2012-02-18T23:57:39+00:00

Sorry u have to fill the dataset before use...

Your code:

Public Class frmSearch

    Dim daSearch As OleDb.OleDbDataAdapter
    Dim dsSearch As New DataSet
    'Dim daSrch As OleDb.OleDbDataAdapter
    'Dim dsSrch As New DataSet
    Dim SrcInc As Integer

    Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim conSearch As New OleDb.OleDbConnection
        Dim conStr As String = "PROVIDER=Microsoft.ACE.Oledb.12.0;"
        Dim conStr2 As String = "DATA SOURCE=D:\DB2Test.accdb"
        Dim StudSQL As String
        Dim inc As Integer
        Dim dsSearch2 As New DataSet

        If txtStudID.Text = "" Then
            MessageBox.Show("Enter a student id", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtStudID.Focus()
        ElseIf dsSearch2.Tables("Student").Rows.Count < 1 Then
            'ElseIf dsSearch2.Rows.Count = 0 Then
            MessageBox.Show("Database not table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)
            Exit Sub

        Else
            StudSQL = "SELECT * FROM Student WHERE Student_ID='" & txtStudID.Text & "'"
            daSearch = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            'Dim daSearch As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            conSearch.ConnectionString = conStr & conStr2
            daSearch.Fill(dsSearch, "Student")
            inc = 0

            MessageBox.Show("Database table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)

            txtStudName.Text = dsSearch.Tables("Student").Rows(inc).Item(1)
            txtStatus.Text = dsSearch.Tables("Student").Rows(inc).Item(2)
            txtSex.Text = dsSearch.Tables("Student").Rows(inc).Item(3)
            txtFaculty.Text = dsSearch.Tables("Student").Rows(inc).Item(4)
            txtPassword.Text = dsSearch.Tables("Student").Rows(inc).Item(5)

        End If
    End Sub

Modified Code

Public Class frmSearch

    Dim daSearch As OleDb.OleDbDataAdapter
    Dim dsSearch As New DataSet
    'Dim daSrch As OleDb.OleDbDataAdapter
    'Dim dsSrch As New DataSet
    Dim SrcInc As Integer

    Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim conSearch As New OleDb.OleDbConnection
        Dim conStr As String = "PROVIDER=Microsoft.ACE.Oledb.12.0;"
        Dim conStr2 As String = "DATA SOURCE=D:\DB2Test.accdb"
        Dim StudSQL As String
        Dim inc As Integer

'-------------MODIFICATIO---------------
        Dim dsSearch As New DataSet
'-------------MODIFICATIO---------------

        If txtStudID.Text = "" Then
            MessageBox.Show("Enter a student id", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtStudID.Focus()


'-------------MODIFICATIO---------------
'Else If statement not need here.
'-------------MODIFICATIO--------------- 


        Else
            StudSQL = "SELECT * FROM Student WHERE Student_ID='" & txtStudID.Text & "'"
            daSearch = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            'Dim daSearch As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            conSearch.ConnectionString = conStr & conStr2
            daSearch.Fill(dsSearch, "Student")

If dsSearch.Tables("Student").Rows.Count < 1 Then
            MessageBox.Show("Database not table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)
            Exit Sub
End If

            inc = 0

            MessageBox.Show("Database table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)

            txtStudName.Text = dsSearch.Tables("Student").Rows(inc).Item(1)
            txtStatus.Text = dsSearch.Tables("Student").Rows(inc).Item(2)
            txtSex.Text = dsSearch.Tables("Student").Rows(inc).Item(3)
            txtFaculty.Text = dsSearch.Tables("Student").Rows(inc).Item(4)
            txtPassword.Text = dsSearch.Tables("Student").Rows(inc).Item(5)

        End If
    End Sub

Also you declaer dsSearch2 but never fill it into ur code....

So Just change it to dsSearch

tomexlfc 0 Light Poster · Answer 17 · 2012-02-19T01:15:16+00:00

Sorry u have to fill the dataset before use...

Your code:

Public Class frmSearch

    Dim daSearch As OleDb.OleDbDataAdapter
    Dim dsSearch As New DataSet
    'Dim daSrch As OleDb.OleDbDataAdapter
    'Dim dsSrch As New DataSet
    Dim SrcInc As Integer

    Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim conSearch As New OleDb.OleDbConnection
        Dim conStr As String = "PROVIDER=Microsoft.ACE.Oledb.12.0;"
        Dim conStr2 As String = "DATA SOURCE=D:\DB2Test.accdb"
        Dim StudSQL As String
        Dim inc As Integer
        Dim dsSearch2 As New DataSet

        If txtStudID.Text = "" Then
            MessageBox.Show("Enter a student id", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtStudID.Focus()
        ElseIf dsSearch2.Tables("Student").Rows.Count < 1 Then
            'ElseIf dsSearch2.Rows.Count = 0 Then
            MessageBox.Show("Database not table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)
            Exit Sub

        Else
            StudSQL = "SELECT * FROM Student WHERE Student_ID='" & txtStudID.Text & "'"
            daSearch = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            'Dim daSearch As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            conSearch.ConnectionString = conStr & conStr2
            daSearch.Fill(dsSearch, "Student")
            inc = 0

            MessageBox.Show("Database table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)

            txtStudName.Text = dsSearch.Tables("Student").Rows(inc).Item(1)
            txtStatus.Text = dsSearch.Tables("Student").Rows(inc).Item(2)
            txtSex.Text = dsSearch.Tables("Student").Rows(inc).Item(3)
            txtFaculty.Text = dsSearch.Tables("Student").Rows(inc).Item(4)
            txtPassword.Text = dsSearch.Tables("Student").Rows(inc).Item(5)

        End If
    End Sub

Modified Code

Public Class frmSearch

    Dim daSearch As OleDb.OleDbDataAdapter
    Dim dsSearch As New DataSet
    'Dim daSrch As OleDb.OleDbDataAdapter
    'Dim dsSrch As New DataSet
    Dim SrcInc As Integer

    Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim conSearch As New OleDb.OleDbConnection
        Dim conStr As String = "PROVIDER=Microsoft.ACE.Oledb.12.0;"
        Dim conStr2 As String = "DATA SOURCE=D:\DB2Test.accdb"
        Dim StudSQL As String
        Dim inc As Integer

'-------------MODIFICATIO---------------
        Dim dsSearch As New DataSet
'-------------MODIFICATIO---------------

        If txtStudID.Text = "" Then
            MessageBox.Show("Enter a student id", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtStudID.Focus()


'-------------MODIFICATIO---------------
'Else If statement not need here.
'-------------MODIFICATIO--------------- 


        Else
            StudSQL = "SELECT * FROM Student WHERE Student_ID='" & txtStudID.Text & "'"
            daSearch = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            'Dim daSearch As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(StudSQL, conSearch)
            conSearch.ConnectionString = conStr & conStr2
            daSearch.Fill(dsSearch, "Student")

If dsSearch.Tables("Student").Rows.Count < 1 Then
            MessageBox.Show("Database not table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)
            Exit Sub
End If

            inc = 0

            MessageBox.Show("Database table found!", "South City College", MessageBoxButtons.OK, MessageBoxIcon.Question)

            txtStudName.Text = dsSearch.Tables("Student").Rows(inc).Item(1)
            txtStatus.Text = dsSearch.Tables("Student").Rows(inc).Item(2)
            txtSex.Text = dsSearch.Tables("Student").Rows(inc).Item(3)
            txtFaculty.Text = dsSearch.Tables("Student").Rows(inc).Item(4)
            txtPassword.Text = dsSearch.Tables("Student").Rows(inc).Item(5)

        End If
    End Sub

Also you declaer dsSearch2 but never fill it into ur code....

So Just change it to dsSearch

Hi King,

Thanks for the help, it works fine now at last! :)

I found out that I didn't declare a new object instance for my dataset and the way i arranged the "If...Else" statement was wrong as well lol

You're a life saver, thanks man!

Cheers! :)

kingsonprisonic 42 Posting Whiz in Training · Answer 18 · 2012-02-19T01:18:17+00:00

kingsonprisonic 42 Posting Whiz in Training

12 Years Ago

Please don't forget to give me Reputation...

tomexlfc 0 Light Poster · Answer 19 · 2012-02-19T02:01:46+00:00

Please don't forget to give me Reputation...

I was trying to find where they give reputation, but I can't find where the thing is... please kindly give me some direction.. thanks

tomexlfc 0 Light Poster · Answer 20 · 2012-02-19T12:47:25+00:00

see the top right corner of each post..
there a upper arrow button. just click that...

done! thanks again for the help

cheers! ;)

waseem.akhtar.3572 0 Newbie Poster · Answer 21 · 2013-10-04T14:31:22+00:00

M.Waqas Aslam's posted codes are working fine
thanx M.Waqas Aslam