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 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
As you can see I have declared all my parameters after I have instantiated my DataAdaptor.