How to pass parameters to dataadapter?

Question

I have code below which i use to select data from sql database table using dataadapter but i throws an error:

"Must declare the scalar variable"@UserName"

Where is the mistake in code????

I have tried the code below which throws an error "Must declare the scalar variable"@UserName"

 Dim Query as string ="SELECT *FROM UserLogins WHERE [Login Name]= 
 @Username  AND Password=@passcode"

 // add parameters to dataadapter select command
 SQL.da.SelectCommand.Parameters.AddWithValue("@Username", "%" + 
 txtuserName.Text + "%")
 SQL.da.SelectCommand.Parameters.AddWithValue("@passcode", "%" + 
 txtpasslogin.Text + "%")

  //execute query and fill dataset
        da = New SqlDataAdapter(Query,Con)           
        cb = New SqlCommandBuilder(da)
        ds = New DataSet
        da.Fill(ds)
 Datagridview1.Datasource=ds.tables(0)

You are adding parameters to the da.SelectCommand object, then creating a new da object, so no, there aren't any parameters anymore. — LarsTech, Apr 08 '19 at 19:23
thanks for the comment may you please use code to clarify your answer. — , Apr 08 '19 at 19:40
Try adding the parameters *after* you create the new adapter. — LarsTech, Apr 08 '19 at 19:41

score 1 · Answer 1 · answered Apr 08 '19 at 20:52

Whew, ok, there's a lot of weird things going on with this code.

First. Two Slashes (//) is not how comments are done in VB.NET. That's C# syntax. Use the single quote instead (')

Next, the error you received is an error coming from SQL Server.

"Must declare the scalar variable @UserName"

LarsTech specifically mentioned in his comment that the code you provided is adding the variables first and then creating a new instance of the SQLDataAdapter right after. If you modify your code to create the data adapter first, and then populate the variables, you should get a better result.

I'm not going to assume that the variables da, cb, and ds are already declared, but that the variable Con is declared somewhere.

Dim Query as string ="SELECT * FROM UserLogins WHERE [Login Name]=     
@Username  AND Password=@passcode"

'Note that I moved these two lines up here and made sure they were properly declared
Dim da as New SqlDataAdapter(Query,Con)           
Dim cb as New SqlCommandBuilder(da)

' add parameters to dataadapter select command
da.SelectCommand.Parameters.AddWithValue("@Username", "%" + 
    txtuserName.Text + "%")
da.SelectCommand.Parameters.AddWithValue("@passcode", "%" + 
    txtpasslogin.Text + "%")

' execute query and fill dataset

ds = New DataSet
da.Fill(ds)
Datagridview1.Datasource=ds.tables(0)

Finally, your sql statement should also have a space between the * and the FROM, and as mentioned by Mary in comments, the % signs are not necessary here because you aren't doing a like.

How to pass parameters to dataadapter?

1 Answers1