Using Parameters in VB.NET

Posted on 2004-11-11
Last Modified: 2008-02-01
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
Question by:hendrix500
    LVL 1

    Expert Comment

    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

    LVL 1

    Author Comment

    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
    LVL 9

    Accepted Solution

    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.
    LVL 9

    Expert Comment

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

    Expert Comment

    by: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>)
                      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

    LVL 24

    Expert Comment

    by: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.

    LVL 1

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    This article is filled with multiple code samples and explanations for mathematical calculations. They are as follows: 1. General tips 2. Quadratic formula 3. Object collision 4. Projectile path General Tips       Here are some of my tips f…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now