• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

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
-----------
0
sah18
Asked:
sah18
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
           strSQL = "SELECT * from viwMedications_withTextNames WHERE (visitnum=1) AND (subjid=?)"
0
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now