Click here to Skip to main content
15,891,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

VB
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


As you can see I have declared all my parameters after I have instantiated my DataAdaptor.
Posted
Comments
Maciej Los 11-Dec-14 8:13am    
Why OleDb? Use SqlClient instead!

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:
SQL
delete from users where [userid] = ?

insert into users ([firstname],[lastname],[username],[password]) values (?,?,?,?)

update users set [firstname] = ?, [lastname] = ?, [username] = ?, [password] = ? where [userid] = ?

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[^].
 
Share this answer
 
v2
Comments
Maciej Los 11-Dec-14 8:11am    
"The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters." - it isn't true at all. Some database providers enable to use named parameters, like MS Access database engine (JET and ACE). I've mentioned it several times on this forum.
Tomas Takac 11-Dec-14 8:20am    
Good to know. Now I realized that OP is connecting to SQL server. So the correct solution would be using SqlCommand and SqlParameter.
Maciej Los 11-Dec-14 8:39am    
That's what i'm pointing out in my comment. ;)
Cheers,
Maciej
Dino the Sink 11-Dec-14 9:23am    
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.
Maciej Los 11-Dec-14 14:39pm    
The size of application has nothing to do with database provider. Please see my answer.
Dino the Sink wrote:
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.


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!
 
Share this answer
 
Comments
Tomas Takac 11-Dec-14 16:25pm    
+5, very true
Maciej Los 11-Dec-14 16:29pm    
Thank you, Tomas ;)
Dino the Sink 11-Dec-14 16:38pm    
For a large application, this may be a good approach, but I am building an application that is simple and small. it connects to existing databases for internal applications. These internal applications have a very specific scheme and that scheme is the same regardless of if they are SQL Server or Access. some of the databases (SQL Server) are large, but all internal applications developed have the same 4 tables with the same columns that handle usernames, versioning, logging and application errors. These tables are very small only 5-6 columns each. my application has 2 forms, a login form, on this form I specify the datasource parameters and from here build the oledb connection string, and then log into the database and retrieve the contents of the other 3 tables and display them for manipulation. the approach you recommend sounds great for a large application, but with the size of my application, I think that would make a lot more code than just figuring out why the named parameters does not work for an OLEDB Connection to a SQL Server.
Maciej Los 11-Dec-14 16:54pm    
As i mentioned, the size of database or application has nothing in common to database provider. It's worth to write a lot of code to create suitable solution for multiple data providers. Of course, the final decision belongs to you ;)
Regards,
Maciej
By The Way: accept all valuable answers (green button).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900