?
Solved

Using Parameters in VB.NET

Posted on 2004-11-11
7
Medium Priority
?
798 Views
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

       '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
0
Comment
Question by:hendrix500
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 1

Expert Comment

by:dapcom
ID: 12553793
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
0
 
LVL 1

Author Comment

by:hendrix500
ID: 12554271
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
0
 
LVL 9

Accepted Solution

by:
_ys_ earned 225 total points
ID: 12554417
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 9

Expert Comment

by:_ys_
ID: 12554425
I should add this applies only to OleDbCommand, as per your example.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12557480
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
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12557491
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
0
 
LVL 1

Author Comment

by:hendrix500
ID: 12563331
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
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…

839 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