error in function Must declare the variable '@subjidParam' -- parameter variable for sql server sql query

I keep getting the following error in my asp.net app:
------------------
Must declare the variable '@subjidParam'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@subjidParam'.

Source Error:

Line 347:        'Populate the DataTable from the Sql Server table:
Line 348:        'If Not (objDt Is Nothing) Then
Line 349:        da.Fill(objDt)
Line 350:        'End If
Line 351:
----------------


I just can't seem to find why this error is occuring.  I have a subjid string (ex: 'UC003') that is being passed into this function.  In Vis Studio 2003 debugging, I can see that the string successfully gets passed into the function.  I don't see why this error is occurring.  Please help!!

here is the code:
-----------
    Sub BindData(ByVal filterValue As String)

        '1. Create a connection
        Dim strConnString As String = _
            ConfigurationSettings.AppSettings("ConnectionString")
        Dim objConn As New SqlConnection(strConnString)

        'Create an appropriate SQL command string
        Dim strSQL As String
        If filterValue = String.Empty Then
            'Return empty dataset:
            strSQL = "SELECT * from viwMedications_withTextNames WHERE 1=2"
        Else
            'SQL needs WHERE clause
            strSQL = "SELECT * from viwMedications_withTextNames WHERE (visitnum=1) AND (subjid=@subjidParam)"
        End If

        '2. Create a command object for the query
        Dim objCmd As New SqlCommand(strSQL, objConn)

        'Add parameter for WHERE clause if needed
        Dim subjidParam As New SqlParameter("@subjidParam", SqlDbType.NVarChar, 5)
        If strSQL <> String.Empty Then
            subjidParam.Value = filterValue
            objCmd.Parameters.Add(subjidParam)
        End If

        objConn.Open() 'Open the connection

        'Get DataTable from Session:
        Dim objDt As New DataTable
        objDt = Session("tbl")

        'Create the DataAdapter (to connect to SQL Server table - to populate DataTable
        Dim da As SqlDataAdapter
        da = New SqlDataAdapter(strSQL, objConn)

        'Populate the DataTable from the Sql Server table:
        'If Not (objDt Is Nothing) Then
        da.Fill(objDt)
        'End If

        'Display/Hide Grid/label as appropriate:
        If (objDt Is Nothing) Or (objDt.Rows.Count = 0) Then
            dgMeds.Visible = False
            lblNoMeds.Visible = True
        Else
            dgMeds.Visible = True
            lblNoMeds.Visible = False
        End If

        'Finally, specify the DataSource and call DataBind()
        dgMeds.DataSource = objDt
        dgMeds.DataBind()

        objConn.Close() 'Close the connection

        'Save DataTable back to Session:
        Session("tbl") = objDt

    End Sub
-----------
LVL 9
sah18Asked:
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
           strSQL = "SELECT * from viwMedications_withTextNames WHERE (visitnum=1) AND (subjid=?)"
0

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
sah18Author Commented:
I tried the syntax above, but it's not happy with that either:
==============
Server Error in '/db/chae01' Application.
--------------------------------------------------------------------------------

Line 1: Incorrect syntax near '?'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '?'.

Source Error:

Line 349:        'Populate the DataTable from the Sql Server table:
Line 350:        'If Not (objDt Is Nothing) Then
Line 351:        da.Fill(objDt)
Line 352:        'End If
Line 353:
==============

I've successfully used the @blahblahblahParam syntax in other parts of my code (on other forms), without problem.  I'm just not seeing why it can't find the subjidParam parameter.  Any other ideas on what might be wrong?

 
0
sah18Author Commented:
It just dawned on me that I'm doing this the hard way (it always seems to be the case, doesn't it?!).  In my other code where I am using parameters, they are insert or update statements, where I really do need to use parameters.  Here, this is just a select statement.  I don't if SQL even allows parameters within select statements.  Anyway, instead of using parameters at all for this, I just changes my sql string to read:

strSQL = "SELECT * from viwMedications_withTextNames WHERE (visitnum=1) AND (subjid='" & filterValue & "')"

And it now works!!

I do appreciate your time, though.  Sometimes just knowing that someone else is out there willing to help takes the pressure off enough to find the solution!

0
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
ASP.NET

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.