Jump to content

ASP.NET & SQL Server 2K Stored Procedure


Recommended Posts

I'm having a strange problem with what should be a simple query. First off, I'm using a front-end web application to query a SQL Server 2k table with a stored procedure. All of the other SP's are working fine, but this one uses the LIKE operator, which is giving me strange results.With the code included below, I get no results on the display page. With testing I know that the @User variable is being passed properly, and I know that the SP is being called properly, but no results are showing up. I think that it might have to do with my use of the LIKE operator with the web page's form variable, but I'm not sure. So that's why I'm here. Here's the code:ASP.NET FRONT-END

<%'Declare variablesDim strSubmitForm As String = Server.HTMLEncode(Left(Trim(Request.Form("hfSubmitForm")), 5))Dim strFName_txt As String = Server.HTMLEncode(Ucase(Left(Trim(Request.Form("txtFName")), 45)))Dim strLName_txt As String = Server.HTMLEncode(Ucase(Left(Trim(Request.Form("txtLName")), 45)))Dim strSearchYear As String = "2006"Dim intRecordCount As Integer = 0'Remove apostrophesstrFName_txt = Replace(strFName_txt, "'", "''")strLName_txt = Replace(strLName_txt, "'", "''")'Assign combo variableIf strFName_txt = "" Then	'Assign first name only	strUserCombo = strLName_txtElse	'Assign first and last name	strUserCombo = strLName_txt + " " + strFName_txtEnd If%><%'Display query formResponse.Write("<form id='searchFilter' name='searchFilter' method='post'>")Response.Write("<table>")Response.Write("<tr>")Response.Write("<td bgcolor='#ccccff' valign='top'>")Response.Write("<strong>User:</strong>")Response.Write("</td>")Response.Write("<td>")Response.Write("First Name: <input type='text' id='txtFName' name='txtFName' value='' size='30' maxlength='45' />")Response.Write(" ") 'spacerResponse.Write("Last Name: <input type='text' id='txtLName' name='txtLName' value='' size='30' maxlength='45' />")Response.Write("<input type='hidden' id='hfSubmitForm' name='hfSubmitForm' value='True' />")Response.Write("</td>")Response.Write("</tr>")Response.Write("</table>")Response.Write("</form>")%><%If strSubmitForm = "True" Then	'Display results table	Response.Write("<table>")	Response.Write("<td>First Name</td>")	Response.Write("<td>Last Name</td>")	'------------------Query Begins-----------------------	Dim sqlQuery As Object, rsQuery As Object	objConn = Server.CreateObject("ADODB.Connection")	objConn.Open (strConnLandUse)	sqlQuery = "spUser @User = '" & strUserCombo & "', @Year = '" & strSearchYear & "'"	rsQuery = objConn.Execute(sqlQuery)	'If RS is not empty	Do While NOT rsQuery.EOF  'Record count  intRecordCount = intRecordCount + 1  If intRecordCount >= 500 Then Exit Do 'Exit from infinite loop  If intRecordCount <= 200 Then 	 'Display results (under 200 records) 	 Response.Write("<tr>") 	 Response.Write("<td>" & rsQuery("FName").Value & "</td>") 	 Response.Write("<td>" & rsQuery("LName").Value & "</td>") 	 Response.Write("</tr>")  End If  rsQuery.MoveNext	Loop	sqlQuery = nothing	rsQuery = nothing	objConn.Close	objConn = nothing	'------------------Query Ends-------------------------	Response.Write("</table>")End If%>

SQL SERVER BACK-END - TABLE (tblUser)ID Name1 DOE JOHN2 DOE JANE3 SCHMO JOHNSQL SERVER BACK-END - STORED PROCEDURECREATE PROCEDURE [dbo].[spUser]@User char (45),@Year char (4)ASSET NOCOUNT ONSELECT * FROM tblUserWHERE User LIKE '%' + @User + '%' AND ([Year] = @Year)ORDER BY ID ASCSET NOCOUNT OFFGOWhen I change the SP from:WHERE User LIKE '%' + @User + '%' AND ([Year] = @Year)to:WHERE User LIKE '%DOE JANE%' AND ([Year] = @Year)or:WHERE User LIKE '%' + 'DOE JANE' + '%' AND ([Year] = @Year)...I do get the proper results. Also, if I move the wildcards to the webpage, like this:sqlQuery = "spUser @User = '%" & strUserCombo & "%', @Year = '" & strSearchYear & "'"...and I then remove the wildcards from the SP, like this:WHERE User LIKE @User AND ([Year] = @Year)...I get the first record from the table. Of course, I can't use the LIKE operator when declaring the SP's variables, so I can't use this as a solution.So if anyone sees what I'm doing wrong, and can steer me towards the light, I'd be greatly appreciated. Thanks for any help.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...