Parameterised Query Visual Basic

Question

I've been having trouble getting parametrised queries to work. The code is working fine without parameterised SQL.

The error I'm getting is Must declare the scalar variable @RegNo.

Dim conn As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\dbsBank.mdf;Integrated Security=True")

Dim queryString As String = "SELECT PAC FROM Customers WHERE Registration_No = @RegNo"

Dim cmd As New SqlCommand(queryString, conn)
Dim param As SqlParameter = New SqlParameter("@RegNo", SqlDbType.Int)
param.Value = mtbPassword.Text
cmd.Parameters.Add(param)

Dim adap As New SqlDataAdapter(queryString, conn)

dTable = New DataTable

Try
    adap.Fill(dTable)
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

I forgot to remove it after trying something, it's not the cause of the problem though. — Philip Woulfe
– Philip Woulfe, Commented Sep 6, 2015 at 14:52
You are declaring and making a Command object but never using it. That is the reason for the error. — Pradeep Kumar
– Pradeep Kumar, Commented Sep 6, 2015 at 14:55

Pradeep Kumar · Accepted Answer · 2015-09-06 14:58:05Z

4

You build your Command object but never use it. So your DataAdapter never has the parameter definition.

Replace

Dim adap As New SqlDataAdapter(queryString, conn)

with

Dim adap As New SqlDataAdapter(cmd)

This should most likely solve your problem

answered Sep 6, 2015 at 14:58

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Philip Woulfe Over a year ago

Ah, Thank you. That makes a lot of sense.

Collectives™ on Stack Overflow

Parameterised Query Visual Basic

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related