vb.net sql parameterization

Question

I really need help this time. I search everywhere, tried numerous solutions. but i can't seem to solve my problem. Now i'm going to ask, please help. I have been having this problem for a week now.

ExecuteSQL("select * from account_database where idnum= @idnum and password= @pass")
'Dim idnum As New SqlParameter("@idnum", SqlDbType.VarChar)
'Dim pass As New SqlParameter("@pass", SqlDbType.VarChar, -1)
'idnum.Value = idnumtxt.Text
'pass.Value = output
'cmd.Parameters.Add(idnum)
'cmd.Parameters.Add(pass)
cmd.Parameters.Add("@idnum", SqlDbType.VarChar).Value = idnumtxt.Text
cmd.Parameters.Add("@pass", SqlDbType.VarChar, -1, "password").Value = output

those commented out lines are the codes which i have tried, also there are codes which i implemented that also failed.

The error message concludes as "Must declare scalar variable @idnum"

i really need help please. Please shine some light.

This is the code what the function executeSQL contains :

 Public Shared Sub ExecuteSQL(ByVal strSQL As String)
    Try
        If connection.State = 1 Then ' check connection if open
            connection.Close()
        End If
        ' connection
        connection.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Jr\documents\visual studio 2010\Projects\VotingSystem\VotingSystem\Resources\Database.mdf;Integrated Security=True;User Instance=True"
        connection.Open()
        Dim rowAffected As Integer = 0
        'cmd = New SqlCommand(strSQL, connection) 'buiding the sql command with the use of strSQL (sql statement) and connection (database connection)
        cmd = New SqlCommand(strSQL, connection)
        DARec = New SqlDataAdapter(strSQL, connection) 'buiding the adapter
        cb = New SqlCommandBuilder(DARec)
        rowAffected = cmd.ExecuteNonQuery() 'executing of sql statement
        successID = 1
        connection.Close()
    Catch ex As Exception
        successID = 0
        MsgBox(ex.Message)
    End Try
End Sub

Thanks and please help.

Lucretius · Accepted Answer · 2014-08-19 16:14:49Z

Problem is simply you're doing this in the wrong order. You're attempting to execute your SQL statement before defining the parameters. You don't need ExecuteSQL() until you've defined your parameters. It likely breaks on the following line in ExecuteSQL()

' See how many rows the query will impact
' Since @idnum and @pass are not defined until the 
' ExecuteSQL() sub is finished, this line breaks.
rowAffected = cmd.ExecuteNonQuery()

You need to build your SqlCommand() to first include the select statement, and then use AddWithValue() on the parameters you've defined in the string. Defining the datatypes is also unnecessary because your database already knows, and form validation should handle input.

' Define your connection
connection.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Jr\documents\visual studio 2010\Projects\VotingSystem\VotingSystem\Resources\Database.mdf;Integrated Security=True;User Instance=True"

' Setup your SQL Command.
cmd = New SqlCommand("select * from account_database where idnum = @idnum and password = @pass", connection)

' Define the parameters you've created
cmd.Parameters.AddWithValue("@idnum", idnumtxt.Text)
cmd.Parameters.AddWithValue("@pass", output)

' Now execute your statement
connection.open()
cmd.ExecuteNonQuery()
connection.close()

And here is a better version of the above code, since you understand the order of events now. This ensures that in the event of exception the connection is closed.

strConn = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Jr\documents\visual studio 2010\Projects\VotingSystem\VotingSystem\Resources\Database.mdf;Integrated Security=True;User Instance=True"
strSQL = "select * from account_database where idnum = @idnum and password = @pass"
Using connection As New SqlConnection(strConn), cmd As SqlCommand(strSQL, connection)
  cmd.Parameters.Add("@idnum", SqlDbType.VarChar).Value = idnumtxt.Text
  cmd.Parameters.Add("@pass", SqlDbType.VarChar, -1, "password").Value = output
  connection.Open()
  cmd.ExecuteNonQuery()
End Using

Victor Zakharov · Accepted Answer · 2014-08-19 15:06:14Z

0

Try this:

cmd.Parameters.AddWithValue("idnum", idnumtxt.Text)

Reference:

SqlParameterCollection.AddWithValue @ MSDN.

answered Aug 19, 2014 at 15:06

Victor Zakharov

26.6k18 gold badges95 silver badges158 bronze badges

2 Comments

Jr Uy Over a year ago

The problem still persists. I also tried it without dropping the @ in the idnum variable.

Victor Zakharov Over a year ago

@JrUy: In your code above you have ExecuteSQL before you add SQL parameters to the command. Should be the other way around.

Victor Zakharov · Accepted Answer · 2014-09-04 15:51:47Z

0

It should just be a case of the following to add an input param

cmd.Parameters.Add("@idnum", idnumtxt.Text)

Except you'll need cmd.parameters.add() before the executesql as you're currently defining your params after executesql has ran.

edited Sep 4, 2014 at 15:51

Victor Zakharov

26.6k18 gold badges95 silver badges158 bronze badges

answered Aug 19, 2014 at 15:09

Liam Wheldon

7751 gold badge5 silver badges20 bronze badges

Collectives™ on Stack Overflow

vb.net sql parameterization

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related