asked on

Using Parameters in VB.NET

Hi everyone,

I'm using VB/NET and I'm trying to get in to the habbit of using parameters when executing sql statements rather than stringing in variables (bad idea!). The code below is an example of what im trying to do:

'Connection Variables
Dim conn_tasc As OleDb.OleDbConnection
conn_tasc = New OleDb.OleDbConnection(sqlconstr_TASC)

'SQL Statement
strsql = "SELECT p.proddesc " & _
"FROM products p " & _
"WHERE p.prodcode = @Prodcode "

'Command Object
Dim mycommand As New OleDb.OleDbCommand

'Paramter
Dim dbParam_Prodcode As System.Data.OleDb.OleDbParameter = New System.Data.OleDb.OleDbParameter
dbParam_Prodcode.ParameterName = "@Prodcode"
dbParam_Prodcode.Value = '0701111'
dbParam_Prodcode.DbType = System.Data.DbType.String

mycommand.Connection = conn_tasc
mycommand.CommandText = strsql
mycommand.CommandType = CommandType.Text
mycommand.Parameters.Add(dbParam_Prodcode)

'Data Reader
Dim reader As OleDb.OleDbDataReader

'Execute
mycommand.Connection.Open()
mycommand.ExecuteScalar()
reader = mycommand.ExecuteReader()

My problem is that i get an error saying "Must declare the Parameter @Prodcode". I think it must be something simple but im stumped!

Any help would be great

dapcom

Hello hendrix500,

It's because yopu should the parameter name is really Prodcode in your example. The @ in the sql code indicates to the server that the word that follows iios a parameter name. So it has to be there in the query, but not in the parameterName property:

change
dbParam_Prodcode.ParameterName = "@Prodcode"
to
dbParam_Prodcode.ParameterName = "Prodcode"

And it should work

Dapcom

hendrix500

ASKER

Tried that but got the same error. It seems the Parameter is not being added to the command object successfully. If I execute the SQL in Query Analyser I get the same error

ASKER CERTIFIED SOLUTION

_ys_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_ys_

I should add this applies only to OleDbCommand, as per your example.

Jeff Certain

First, you should be using the SQLClient objects if you are connecting to an SQL database.

Second, here is code that works to return an integer from the stored procedure.

            Public Shared Function NewTreatment(ByVal componentId As Integer, ByVal treatmentTypeId As Integer) As Integer
                  Dim conn As SqlConnection = New SqlConnection(<connectionString>)
                  conn.Open()
                  Dim cmd As SqlCommand = New SqlCommand(<stored procedure name>, conn)
                  cmd.CommandType = CommandType.StoredProcedure
                  ' Set parameters
                  Dim componentParam As SqlParameter = cmd.Parameters.Add("@componentId", SqlDbType.Int)
                  componentParam.Value = componentId
                  Dim treatmentTypeParam As SqlParameter = cmd.Parameters.Add("@treatmentTypeId", SqlDbType.Int)
                  treatmentTypeParam.Value = treatmentTypeId
                  ' Get the new treatmentId
                  NewTreatment = cmd.ExecuteScalar
                  ' Cleanup
                  cmd.Dispose()
                  conn.Close()
            End Function

Hope this helps

Jeff

Jeff Certain

Sorry. I noticed that you're not using stored procedures on the SQL server either. You should be ;) This will help improve your application's efficiency, and simplify getting the data you need from the server.

Jeff

hendrix500

ASKER

Yeah thanks for that Jeff. I'm aware that I should use Stored Procedures but this particular query returns just 1 row, will be executed very rarely, and by a single user so the performance benefits of using a SP will be negligable.