VB.Net使用访问作为数据库（OLEDB） - 没有给出一个或多个必需参数的值 [英] VB.Net using access as database(OLEDB) - No Value given for one or more required parameters
问题描述
遇到的问题：
点击搜索按钮。尝试Catch Reacted：
（没有给出一个或多个必需参数的值）
我正在使用MS Access 2013
文件名：records.accdb
表名：tbl_Students
字段数据类型
count自动编号
id_number Long_Text PRIMARY KEY
Last_Name Long_Text
First_Name Long_Text
Middle_Name Long_Text
。
。
。
代码：
进口System.Data.OleDb
公开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作为新的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
下一页
End Sub
Public Sub get_list（）
REM这需要修改以便以后使用
frmSearchRecord.ListBox1.Items.Clear（）
conn.Open（）
cmd.CommandText =select * from tbl_Students where Last_Name like'％&frmSearchRecord.txtSearch.Text &％'
rd = cmd.ExecuteReader
如果rd.HasRows那么
While（rd .Read（））
frmSearchRecord.ListBox1.Items.Add（rd（Last_Name&，&First_Name&&Middle_Name） ）REM需要修改
'frmSearchRecord.TextBox 1.Text =（rd（id））
'frmNewPersonalDataSheet.TextBox2.Text =（rd（name））
'frmNewPersonalDataSheet.TextBox3.Text = （rd（地址））
'Form1TextBox4.Text =（rd（age））
'Form1.TextBox5.Text =（rd（电子邮件））
'Form1.ListView1.Items.Add（rd（name）&&rd（address））
结束时
结束如果
conn.Close（）
结束子
公共子搜索（）
Dim strSQL As String =选择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）
对于每个DataRow在y_dt.Rows
使用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）
结束
下一页
结束次级
结束班级
Imports System.Data.OleDb
公共类frmSearchRecord
Dim conn39 As new clsSearchRecord
Private Sub frmSearchRecord_Load（ByVal sender As System.Object，ByVal e As System.EventArgs）处理MyBase.Load
Me.FormBorderStyle = Windows.Forms.FormBorderStyle.FixedSingle
'用记录填充DataGridView
conn39.data_refresh（）
'这将填充cboCriteria.Text
cboCriteria.Items.Add（last_name）
'cboCriteria.Items.Add（）
'cboCriteria。 Items.Add（）
txtSearch.TextAlign = HorizontalAlignment.Center
'运行时会忽略以下颜色为灰色的语句块。
#If comment Then REM这是另一种评论代码的方式
conn39.cmd.Connection = conn39.conn
Dim da As新的OleDbDataAdapter（select * from tbl_students，conn39.conn）
Dim dt As New DataTable
da.Fill（dt）
每个DataRow在dt.Rows
DataGridView1.DataSource = dt.DefaultView
下一页
#End如果
End Sub
Private Sub btnSearch_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理btnSearch.Click
'conn39.cmd.Connection = conn39。 conn
'conn39.conn.Open（）
'conn39.get_list（）
尝试
如果txtSearch.Text =那么
MessageBox.Show（ Search Box Empty！，ERROR，MessageBoxButtons.OK，MessageBoxIcon.Error）
txtSearch.Focus（）
Else
conn39 .search（）
conn39.conn.Close（）
结束如果
Catch ex As OleDb.OleDbException
MessageBox.Show（ex.Message，ERROR，MessageBoxButton s.OK，MessageBoxIcon.Information）
conn39.conn.Close（）
结束尝试
结束Sub $ / $
Private Sub txtSearch_TextChanged（ByVal sender As System.Object，ByVal e As System.EventArgs）处理txtSearch.TextChanged
#If comment然后
尝试
conn39.get_list（）
Catch ex As OleDb.OleDbException
MessageBox.Show（ex .Message，ERROR，MessageBoxButtons.OK，MessageBoxIcon.Information）
conn39.conn.Close（）
结束尝试
#End如果
结束子
Public Sub zzzSearchStudent（）
尝试
将此字段调暗为字符串
thisField = cboCriteria.SelectedItem
Dim sqlquery1 As String
'当然，年， 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作为OleDbCommand =新的OleDbCommand（sqlquery1，conn39.conn）
Dim thisFullname As String
Dim da As New OleDbDataAdapter（sqlquery1 +，conn39.conn）
Dim dt As New DataTable
da.Fill（dt）
每个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如果
下一页
conn39.conn.Close（）
Catch ex As OleDb.OleDbException
MessageBox.Show（ex.Message，ERROR，MessageBoxButtons.OK，MessageBoxIcon.Information）
conn39.conn.Close（）
结束尝试
< br $>
结束子
结束类
在搜索中更改以下代码（ ）功能。原因是SQL查询未正确写入 - id_number 列期望未传递的值。所以你得到错误。
Dim strSQL As 字符串 = 选择Last_Name，First_Name，Middle_Name ，课程，来自tbl_students的id_number，其中id_number ='& frmSearchRecord.txtSearch.Text& '
我还有一件事我建议你在ADO.NET中使用Parameterised查询以避免SQL注入。如果要实现相同的内容，请参阅以下代码：
Dim strSQL 作为 字符串 = 从tbl_students中选择Last_Name，First_Name，Middle_Name，Course，id_number，其中id_number = @ ID_Number
Dim conn As 新 OleDbConnection（ ConnectionString）
Dim cmd As 新 OleDbCommand（）
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = strSQL
cmd.Parameters.AddWithValue（ @ ID_Number，frmSearchRecord.txtSearch.Tex t）
Dim y_da As 新 OleDbDataAdapter（cmd）
Dim y_dt As 新 DataTable（）
' DataTable
y_da.Fill（y_dt）
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
Change below code in search() function. Reason is SQL query is not properly written - id_number column is expect a value which was not passed. So you are getting error.
Dim strSQL As String = "select Last_Name, First_Name, Middle_Name, Course, id_number from tbl_students where id_number='" & frmSearchRecord.txtSearch.Text & "'"
One more thing I want to suggest you to use Parameterised query in ADO.NET in order to avoid SQL Injection. If you want to implement the same, see following code :
Dim strSQL As String = "select Last_Name, First_Name, Middle_Name, Course, id_number from tbl_students where id_number=@ID_Number" Dim conn As New OleDbConnection("ConnectionString") Dim cmd As New OleDbCommand() cmd.Connection = conn cmd.CommandType = CommandType.Text cmd.CommandText = strSQL cmd.Parameters.AddWithValue("@ID_Number", frmSearchRecord.txtSearch.Text) Dim y_da As New OleDbDataAdapter(cmd) Dim y_dt As New DataTable() 'DataTable y_da.Fill(y_dt)
这篇关于VB.Net使用访问作为数据库（OLEDB） - 没有给出一个或多个必需参数的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
