没有给出一个或多个必需参数的值（VB net ms-access） [英] No value given for one or more required parameters (VB net ms-access)

问题描述

我有2个数据表与这些字段

* dataordner

1.存档日期/时间（datetimepicker，它已经是数据）

2.存储短文本（组合框）

3. BulanOrdner ShortText（组合框）

4. TahunOrdner日期/时间（datetimepicker）



* datalemari

1.归档日期/时间（datetimepicker，它已经是数据）

2.存储短文本（组合框）

3. Lemari短文（组合框）

4. BulanOrdner ShortText（组合框）

5. TahunOrdner日期/时间（datetimepicker）



我现在很困惑为什么我错了



我尝试过的事情：



我已经完成了我的代码，但总是错误的错误没有给予一个值......

i call 2 table with如果在组合框中存储（存储）

这是我的代码，我认为组合框中的所选项目都是如此

注意：

storage =组合框

i have a 2 datatable with these field
*dataordner
1. Archive Date/Time (datetimepicker, it's already data)
2. Storage Short Text (combobox)
3. BulanOrdner ShortText (combobox)
4. TahunOrdner Date/Time (datetimepicker)

*datalemari
1. Archive Date/Time (datetimepicker, it's already data)
2. Storage Short Text (combobox)
3. Lemari Short Text (combobox)
4. BulanOrdner ShortText (combobox)
5. TahunOrdner Date/Time (datetimepicker)

and i now very confused why i'm wrong

What I have tried:

i've done with my codes but always error with that error "no value given for one more...."
i call 2 table with if else in combobox(storage)
this is my codes, i think that's all true with selected item in combobox
note:
storage = combobox

Private Sub storage_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles storage.SelectedIndexChanged
    If storage.SelectedIndex = 1 Then
        groupordner.Visible = True
        grouplemari.Visible = False
    Else
        If storage.SelectedIndex = 2 Then
            groupordner.Visible = False
            grouplemari.Visible = True
        End If
    End If
End Sub

Private Sub save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click
    If storage.SelectedItem = "Ordner" Then
        str = "Update dataordner set Storage = " & storage.Text & ", BulanOrdner = " & bulanordner.Text & ", TahunOrdner = '" & tahunordner.Value & "' Where Archive = '" & tanggalarchive.Value & "'"
        proses.ExecuteNonQuery(str)
        MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
        Call bersih()
        Call data_penjualan()
    Else
        If storage.SelectedItem = "Lemari" Then
            str = "Update datalemari set Storage = " & storage.Text & ", Lemari = " & lemari.Text & ", BulanLemari = " & bulanlemari.Text & ", TahunLemari = '" & tahunlemari.Value & "' Where Archive = '" & tanggalarchive.Value & "'"
            proses.ExecuteNonQuery(str)
            MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
            Call bersih()
            Call data_penjualan()
        End If
    End If
End Sub

推荐答案

不要这样做！

永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

Don't do it like that!
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

哪个SQL看作三个单独的命令：

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

完全有效的SELECT

A perfectly valid SELECT

DROP TABLE MyTable;

完全有效的删除表格通讯和

A perfectly valid "delete the table" command

--'

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你经常定期备份，不是吗？



在你的整个应用程序中修复它 - 因为如果你错过了其中一个人会找到它 - 你会发现您的现有问题同时消失。

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Fix that throughout your whole app - because if you miss just one somebody else will find it - and you will find that your existing problem vanishes at the same time.

除了OriginalGriff的解决方案，我建议将您分成 DAL [ ^ ]和 BLL [ ^ ]。

您之所以拥有在参考文章中描述了这样做。



要找出导致错误的原因，你必须调试你的程序：学习使用Visual Studio调试器进行调试 - Visual Studio | Microsoft Docs [ ^ ]



我猜你有 oledbexception（0x80040e10），这意味着你没有提供参数，这是强制性的。请参阅： OLEDB错误 [ ^ ]



关于你的代码的几点注释：

1.你的代码没有返回 ExecutNonQuery 方法的结果，所以有不知道这个方法是否成功。

2.你的代码没有错误处理程序......通过代码捕获错误是一种很好的编程习惯。看看下面的代码。它并不完美，但它对初学者来说很好。

In addition to OriginalGriff's solution, i'd recommend to separate your into DAL[^] and BLL[^].
The reason why you have to do that is described in referenced articles.

To find out why you get such of error, you have to debug your programme: Learn to debug using the Visual Studio debugger - Visual Studio | Microsoft Docs[^]

I guess you've got oledbexception (0x80040e10), which means you did not provide parameter, which is obligatory. See: OLEDB Errors[^]

Few notes about your code:
1. Your code does not return the result of ExecutNonQuery method, so have no idea if this method has been succeeded.
2. Your code does not have an error handler... It's good programming practice to catch errors by code. Take a look at below code. It's not perfect, but it's good for starters.

Function GetEmployee(Optional ByVal empIdToFind As Integer = 111) Ad DataTable
    Dim dt As DataTable = New DataTable()
	Try
		Dim sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FullFileNameOfMsAccessDatabase];Persist Security Info =False;"
		Dim oRdr As OleDbDataReader
			
		Using oConn As OleDbConnection = New OleDbConnection(sConn)
			oConn.Open()
			Using oComm AS OleDbCommand = New OleDbCommand()
				oComm.Connection = oConn
				oComm.CommandType = CommandType.Text
				'using named paramters
				oComm.CommandText =  "SELECT * FROM MyUsers WHERE EmpId=@empid;"
				oComm.Parameters.AddWithValue("@empid", empIdToFind)
				oComm.ExecuteNonQuery()
				oRdr = oComm.ExecuteReader()
				dt.Load(oRdr)

			End Using
			oConn.Close()
		End Using
	
	Catch ex As Exception
		Console.WriteLine(ex.Message)
	
	End Try
    Return dt
End Function

以上功能需要 empIdToFind 输入参数能够返回DataTable（完整记录）。

注意：命名/非命名参数的顺序很重要！

3.此代码：

Above function requires empIdToFind input parameter to be able to return DataTable (complete record).
Note: the order of named/non-named parameters does matter!
3. This code:

If storage.SelectedIndex = 1 Then
    groupordner.Visible = True
    grouplemari.Visible = False
Else
    If storage.SelectedIndex = 2 Then
        groupordner.Visible = False
        grouplemari.Visible = True
    End If
End If

可简化为形式：

can be simplified into form:

groupordner.Visible = If(storage.SelectedIndex=1, True, False)
grouplemari.Visible = Not groupordner.Visible

请参阅：如果是运算符（Visual Basic）| Microsoft Docs [ ^ ]



祝你好运！

See: If Operator (Visual Basic) | Microsoft Docs[^]

Good luck!

这篇关于没有给出一个或多个必需参数的值（VB net ms-access）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！