VB.Net using access as database(OLEDB) - No Value given for one or more required parameters

See more:

VB

MS-Access

error-handling

Good Day everyone. I've been developing a Simple Guidance System for our school. This was our project in modeling and simulation subject. I've been stuck in coding the search of specific records for almost 3 days. i couldn't figure out the solution for this. I have a feeling the bug is taking place in Sub Routine Search() I wish someone can help me with this since we got a deadline this week. Thanks in advance and God Bless!

Problem encountered:

Upon Clicking search button. Try Catch Reacted:
(No Value given for one or more required parameters)

I'm Using MS Access 2013

Filename : records.accdb
table name: tbl_Students

Fields Data Type
count AutoNumber
id_number Long_Text PRIMARY KEY
Last_Name Long_Text
First_Name Long_Text
Middle_Name Long_Text
.
.
.



Code:

Imports System.Data.OleDb

Public Class clsSearchRecord
Public conn As New OleDbConnection("Provider=Microsoft.Ace.Oledb.12.0;Data source=" & Application.StartupPath & "\records.accdb")
Public cmd As New OleDbCommand
Public rd As OleDbDataReader

Public Sub data_refresh()
Dim da As New OleDbDataAdapter("select * from tbl_students", conn)
Dim dt As New DataTable
da.Fill(dt)
For Each DataRow In dt.Rows
frmSearchRecord.DataGridView1.DataSource = dt.DefaultView

Next
End Sub

Public Sub get_list()
REM this needs revision to be used later
frmSearchRecord.ListBox1.Items.Clear()

conn.Open()
cmd.CommandText = "select * from tbl_Students where Last_Name like '%" & frmSearchRecord.txtSearch.Text & "%'"
rd = cmd.ExecuteReader

If rd.HasRows Then
While (rd.Read())

frmSearchRecord.ListBox1.Items.Add(rd("Last_Name" & ", " & "First_Name" & " " & "Middle_Name")) REM this needs revision

'frmSearchRecord.TextBox1.Text = (rd("id"))
'frmNewPersonalDataSheet.TextBox2.Text = (rd("name"))
'frmNewPersonalDataSheet.TextBox3.Text = (rd("address"))
'Form1.TextBox4.Text = (rd("age"))
'Form1.TextBox5.Text = (rd("email"))

'Form1.ListView1.Items.Add(rd("name") & " " & rd("address"))


End While
End If


conn.Close()
End Sub

Public Sub search()
Dim strSQL As String = "select Last_Name, First_Name, Middle_Name, Course, id_number from tbl_students where id_number=" & frmSearchRecord.txtSearch.Text & " "
Dim y_da As New OleDbDataAdapter(strSQL, conn)
Dim y_dt As New DataTable 'DataTable
y_da.Fill(y_dt)

For Each DataRow In y_dt.Rows

With frmSearchRecord
.txtLastName.Text = DataRow("Last_Name")
.txtFirstName.Text = DataRow("First_Name")
.txtFirstName.Text = DataRow("Middle_Name")
.txtCourse.Text = DataRow("Course")
'.txtStudentID.Text = DataRow("id_number")

End With


Next
End Sub

End Class


Imports System.Data.OleDb

Public Class frmSearchRecord

Dim conn39 As New clsSearchRecord

Private Sub frmSearchRecord_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.FormBorderStyle = Windows.Forms.FormBorderStyle.FixedSingle

'Populate DataGridView with Records
conn39.data_refresh()

' this will populate cboCriteria.Text

cboCriteria.Items.Add("last_name")
'cboCriteria.Items.Add("")
'cboCriteria.Items.Add("")

txtSearch.TextAlign = HorizontalAlignment.Center





'The following block of statements that were color grayed are ignored upon runtime.

#If comment Then REM this is another way of commenting a code
conn39.cmd.Connection = conn39.conn
Dim da As New OleDbDataAdapter("select * from tbl_students", conn39.conn)
Dim dt As New DataTable
da.Fill(dt)

For Each DataRow In dt.Rows
DataGridView1.DataSource = dt.DefaultView
Next
#End If

End Sub

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
'conn39.cmd.Connection = conn39.conn
'conn39.conn.Open()
'conn39.get_list()



Try

If txtSearch.Text = "" Then
MessageBox.Show("Search Box Empty!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
txtSearch.Focus()
Else
conn39.search()
conn39.conn.Close()
End If


Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Information)
conn39.conn.Close()
End Try

End Sub

Private Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
#If comment Then
Try
conn39.get_list()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Information)
conn39.conn.Close()
End Try
#End If
End Sub



Public Sub zzzSearchStudent()
Try

Dim thisField As String

thisField = cboCriteria.SelectedItem

Dim sqlquery1 As String

' course, year, sem, syear

'sqlquery1 = "SELECT last_name, first_name, middle_name FROM tbl_Students WHERE " & thisField & " " & "like '%" & txtSearch.Text & "%' "

sqlquery1 = "SELECT * tbl_Students WHERE=" & thisField & " " & "like '%"
sqlquery1 = sqlquery1 + txtSearch.Text

Dim sql As OleDbCommand = New OleDbCommand(sqlquery1, conn39.conn)

Dim thisFullname As String

Dim da As New OleDbDataAdapter(sqlquery1 + "", conn39.conn)
Dim dt As New DataTable

da.Fill(dt)

For Each DataRow In dt.Rows

thisFullname = DataRow("last_name") & " " & DataRow("first_name") & " " & DataRow("middle_name")

ListBox1.Items.Clear()
ListBox1.Items.Add(thisFullname)

#If comment Then
With Me
.txtsid.Text = DataRow("studentID")
.txtFullName.Text = thisFullname
.txtCourse.Text = DataRow("course")
.txtYear.Text = DataRow("year")
.txtSem.Text = DataRow("sem")
.txtsyear.Text = DataRow("syear")
'.lstResult.Items.Add(thisFullname)
End With
#End If


Next

conn39.conn.Close()

Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Information)
conn39.conn.Close()
End Try

End Sub

End Class