kwilliams Posted May 9, 2006 Share Posted May 9, 2006 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now