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

        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

        'Data Reader
         Dim reader As OleDb.OleDbDataReader

        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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:

dbParam_Prodcode.ParameterName = "@Prodcode"
dbParam_Prodcode.ParameterName = "Prodcode"

And it should work

hendrix500Author Commented:
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
Named parameters are not supported when using CommandType.Text.

Redefine your SQL string using ? for parameters instead:
 'SQL Statement
        strsql = "SELECT p.proddesc " & _
                 "FROM products p " & _
                 "WHERE p.prodcode = ? "

In this case the order in which parameters are added to the parameters collection correspond to the ordering of the question marks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I should add this applies only to OleDbCommand, as per your example.
Jeff CertainCommented:
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>)
                  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
            End Function

Hope this helps

Jeff CertainCommented:
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.

hendrix500Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.