我为什么要“在运行时必须声明标量变量错误” [英] Why Am I Getting "Must Declare The Scalar Variable Errors At Run Time"
问题描述
我有一个使用OLEDBDataAdaptor从SQL Server数据库读取和更新表的表单，我有可以在表上插入行，更新行和删除行的代码，并且我使用了命名参数。初始化工作，我用我的查询中检索的用户名填充我的列表框，当我在列表框中选择一个用户时，我在右侧的文本框中显示名字，姓氏，用户名和密码，我在表单上添加了按钮用户到表，编辑用户并删除表。这三个函数都成功更新了数据集中的数据。我的最后一个按钮是OK，调用oledbdataadaptor.update（）方法将数据包保存到数据库。无论我做了哪些数据操作（删除，插入，更新），我的代码错误都带有错误必须声明标量变量@Parameter取决于我所做的操作，将取决于错误中列出的参数。 br />
I have a form that reads and updates a table from a SQL Server database using OLEDBDataAdaptor, I have code that can insert rows, update rows and delete rows on the table and I have used named parameters. the initialization works, and I populate my listbox with the Username retrieved from my query, when I select a user in the listbox I display the firstname, lastname, username and password in text boxes to the right, I have buttons on the form to add a user to the table, edit a user and delete the table. all three functions updates the data in the dataset successfully. my final button is OK which calls the oledbdataadaptor.update() method to save the databack to the database. No matter which data operation I have done(Delete, Insert, Update), my code errors with the error "Must Declare the Scalar Variable @Parameter" Depending on which operation I done, will depend on which parameter is listed in the error.
Public Class Form1
'Dim sqlconnection As SqlConnection
Dim oledbconnection As OleDbConnection
Dim dsusers As DataSet
Dim oleda As OleDbDataAdapter
Const oleconstring As String = "Initial Catalog=db1;Data Source=vmsvr01;Integrated Security=SSPI;provider=sqloledb"
Const sqlselect As String = "select * from users"
Const sqldelete As String = "delete from users where [userid] = @userid"
Const sqlinsert As String = "insert into users ([firstname],[lastname],[username],[password]) values (@firstname,@lastname,@username,@password)"
Const sqlupdate As String = "update users set [firstname] = @firstname, [lastname] = @lastname, [username] = @username, [password] = @password where [userid] = @userid"
Dim userid, si As Integer
Dim dr() As DataRow
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
initializeOLEDB()
End Sub
Private Sub initializeOLEDB()
dsusers = New DataSet
oledbconnection = New OleDbConnection(oleconstring)
Dim cmddelete = New OleDbCommand(sqldelete, oledbconnection)
Dim cmdinsert = New OleDbCommand(sqlinsert, oledbconnection)
Dim cmdupdate = New OleDbCommand(sqlupdate, oledbconnection)
oleda = New OleDbDataAdapter(sqlselect, oledbconnection)
oleda.DeleteCommand = cmddelete
oleda.InsertCommand = cmdinsert
oleda.UpdateCommand = cmdupdate
cmddelete.Parameters.Add("@userid", OleDbType.Integer, Nothing, "userid")
cmdinsert.Parameters.Add("@firstname", OleDbType.VarChar, 50, "firstname")
cmdinsert.Parameters.Add("@lastname", OleDbType.VarChar, 50, "lastname")
cmdinsert.Parameters.Add("@username", OleDbType.VarChar, 50, "username")
cmdinsert.Parameters.Add("@password", OleDbType.VarChar, 255, "Password")
cmdupdate.Parameters.Add("@firstname", OleDbType.VarChar, 50, "firstname")
cmdupdate.Parameters.Add("@lastname", OleDbType.VarChar, 50, "lastname")
cmdupdate.Parameters.Add("@username", OleDbType.VarChar, 50, "username")
cmdupdate.Parameters.Add("@password", OleDbType.VarChar, 255, "Password")
cmdupdate.Parameters.Add("@userid", OleDbType.Integer, Nothing, "UserID")
oleda.Fill(dsusers, "users")
dsusers.Tables!users.Columns!userid.AutoIncrement = True
dsusers.Tables!users.Columns!userid.AutoIncrementSeed = -1
dsusers.Tables!users.Columns!userid.AutoIncrementStep = -1
lb.ValueMember = "userid"
lb.DisplayMember = "Username"
lb.DataSource = dsusers.Tables!users
End Sub
Private Sub lb_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lb.SelectedIndexChanged
If lb.SelectedIndex > -1 Then
si = lb.SelectedIndex
userid = CType(lb.SelectedValue.ToString, Integer)
dr = dsusers.Tables!users.Select("userid = " & userid)
txtuserid.Text = CType(dr(0)!userid, String)
txtfirstname.Text = CType(dr(0)!Firstname, String)
txtlastname.Text = CType(dr(0)!lastname, String)
txtusername.Text = CType(dr(0)!username, String)
txtpassword.Text = CType(dr(0)!password, String)
End If
End Sub
Private Sub LinkLabel2_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel2.LinkClicked
dr(0).Delete()
End Sub
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
If dsusers.HasChanges Then
oleda.Update(dsusers, "Users")
End If
End Sub
Private Sub lladd_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles lladd.LinkClicked
Dim newdr As DataRow = dsusers.Tables!users.NewRow
newdr!firstname = txtfirstname.Text
newdr!lastname = txtlastname.Text
newdr!username = txtusername.Text
newdr!password = txtpassword.Text
dsusers.Tables!users.Rows.Add(newdr)
End Sub
Private Sub llsave_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles llsave.LinkClicked
lb.SelectedIndex = -1
dr(0)!firstname = txtfirstname.Text
dr(0)!lastname = txtlastname.Text
dr(0)!username = txtusername.Text
dr(0)!password = txtpassword.Text
lb.SelectedIndex = si
End Sub
end class
如你所见，我已宣布全部我实例化DataAdaptor后的参数。
As you can see I have declared all my parameters after I have instantiated my DataAdaptor.
推荐答案
来自 OleDbParameter [ ^ ]文档：
OLE DB.NET框架数据提供程序使用标有问号（？）而不是命名参数的位置参数。
所以你的命令需要看起来像这样：
From OleDbParameter[^] documentation:
The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters.
So your commands need to look like this:
delete from users where [userid] = ?
insert into users ([firstname],[lastname],[username],[password]) values (?,?,?,?)
update users set [firstname] = ?, [lastname] = ?, [username] = ?, [password] = ? where [userid] = ?
您应该在命令'<$ c $中保持相同的顺序c>参数集合否则它们会混淆。
编辑 - SQL服务器
现在我意识到你正在连接到SQL Server。所以你应该改为 SqlDataAdapter [ ^ ]和 SqlCommand [ ^ ]。
And you should keep the same order in the commands' Parameters collections otherwise they will be mixed up.
Edit - SQL server
Now I realized you are connecting to SQL Server. So you should change to SqlDataAdapter[^] and SqlCommand[^].
是的，使用SQLClient会使更多的是，但这是一个非常小的应用程序，我需要它连接到SQL数据库或访问数据库。我发布的代码是它的更加整合的版本。 我实际上已经将数据库层封装在一个类中，在初始化时将连接字符串传递给类，并将数据集返回到我操作数据集的表单。 I然后将数据集传递回我的类以执行任何更新。我想使用OLEDB Provider，因为它适用于SQL Server和Access，只需要很少的更改。我不想要一个SQL类和一个单独的Access类。在这样做的时候，我必须用非语言填写我的表单if if语句来确定用于不同操作的类。
此代码在连接到Access数据库，而不是SQL数据库。
Yes using SQLClient would make more sence, but this is a very small application and I need it run connecting to either a SQL Database or an access database. The code I posted is a more consolidated version of it. I have actually encapsulated the database layer in a class, pass the connection string to the class when I initialize it and return the dataset to my form where I manipulate the dataset. I then pass the dataset back to my class to perform any updates. I want to use the OLEDB Provider because it works with both SQL Server and Access, with minimal changes. I don’t want a class for SQL and a separate class for Access. In doing this, I have to fill my form with non-sensical if then statements to determine which class to use for the different operations.
This code works when connecting to the Access database, just not the SQL database.
我会说：全球化思考，在本地工作！
我不同意声明使用OleDb是您的最佳方法。您可以编写自定义DAL，它接受不同类型的数据库驱动程序。请参阅：编写可移植数据访问层 [ ^ ]。
试试！
I'd say: Think globally, work locally!
I disagree with statement that using OleDb is the best approach in your case. You can write custom DAL, which accept different types of database drivers. Please, see: Writing a Portable Data Access Layer[^].
Try!
这篇关于我为什么要“在运行时必须声明标量变量错误”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
