sah18
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.SqlE xception: 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.AppS ettings("C onnectionS tring")
Dim objConn As New SqlConnection(strConnStrin g)
'Create an appropriate SQL command string
Dim strSQL As String
If filterValue = String.Empty Then
'Return empty dataset:
strSQL = "SELECT * from viwMedications_withTextNam es WHERE 1=2"
Else
'SQL needs WHERE clause
strSQL = "SELECT * from viwMedications_withTextNam es 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(subj idParam)
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
-----------
------------------
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.SqlE
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.AppS
Dim objConn As New SqlConnection(strConnStrin
'Create an appropriate SQL command string
Dim strSQL As String
If filterValue = String.Empty Then
'Return empty dataset:
strSQL = "SELECT * from viwMedications_withTextNam
Else
'SQL needs WHERE clause
strSQL = "SELECT * from viwMedications_withTextNam
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
If strSQL <> String.Empty Then
subjidParam.Value = filterValue
objCmd.Parameters.Add(subj
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_withTextNam es 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!
strSQL = "SELECT * from viwMedications_withTextNam
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!
ASKER
==============
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.SqlE
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?