Link to home
Start Free TrialLog in
Avatar of sah18
sah18Flag for United States of America

asked on

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
-----------
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sah18

ASKER

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?

 
Avatar of sah18

ASKER

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!