ASP.NET & SQL Server 2K Stored Procedure

kwilliams · May 9th, 2006, 12:11 PM

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

Code:

<%
'Declare variables
Dim 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 apostrophes
strFName_txt = Replace(strFName_txt, "'", "''")
strLName_txt = Replace(strLName_txt, "'", "''")

'Assign combo variable
If strFName_txt = "" Then
    'Assign first name only
    strUserCombo = strLName_txt
Else
    'Assign first and last name
    strUserCombo = strLName_txt + " " + strFName_txt
End If
%>
<%
'Display query form
Response.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("#32;") 'spacer
Response.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 Name
1 DOE JOHN
2 DOE JANE
3 SCHMO JOHN

SQL SERVER BACK-END - STORED PROCEDURE

CREATE PROCEDURE [dbo].[spUser]
@User char (45),
@Year char (4)
AS
SET NOCOUNT ON
SELECT * FROM tblUser
WHERE User LIKE '%' + @User + '%' AND ([Year] = @Year)
ORDER BY ID ASC
SET NOCOUNT OFF
GO

When 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.

KWilliams

Imar · May 9th, 2006, 01:05 PM

Hi KWilliams,

Are you sure this is an ASP.NET 2 application? I see a lot of stuff that looks like classic ASP (Response.Write, Recordsets etc). But, I also see something like this:

Dim strSubmitForm As String = Server.HTMLEncode(Left(Trim(Request.Form("hfSubmit Form")), 5))

which seems to suggest it's indeed an ASP.NET page.

Is there any reason why you're using this old skool style programming? Why are you not using SqlDataReaders, DataSets and databound controls??

That said, I think the error is caused by the type of your parameters. You declare it as an char, which means the value will be padded with spaces. So, you're probably not searching for %John Doe% but for %John Doe % which doesn't exist in the database. Use a varchar instead.

But, with the code you have right now, you probably have other things to worry about. Maybe you have a good reason, but if I were you, I'd dig a little deeper in what ASP.NET 2.0 can do for you. With code like the stuff you posted, you're missing out most (if not all) of the features that .NET 2 has to offer....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

kwilliams · May 9th, 2006, 01:23 PM

Hi Imar,

I did try using the built-in ASP.NET features for a time, but I found it to be extremely time-consuming, limiting, and a major resource hog. After posting my frustrations to another forum, I was given the advice by several developers to just code the stuff directly instead of using the built-in features, as most programmers dont use it anyway. I of course know that he nor I can take what he said as fact, but I have had a lot less trouble developing in this method than I did with the built-in ASP.NET behaviors. I will make sure to study up on ways that I can inprove my code, but I would really like help with this problem first.

That said, my variables are all trimmed, so that "Jane Doe " becomes "Jane Doe". So I do not really understand why the char datatype would have anything to do with that. If you can enlighten me more on that, that would be great. Thanks Imar.

KWilliams

Imar · May 9th, 2006, 01:34 PM

Hi KWilliams,

I am not sure what other developers you met and what they said, but I haven't met one that use Response.Write to write out controls in a page. I'd be happy to see a link to one of those forums though...

If I were you, I'd start with a book like Beginning ASP.NET by Wrox, or even the free "Build a Web Site now" from Microsoft.

I really can't believe you find it harder to this:

Dim userName As String = txtUserName.Text.Trim()

then this

Dim userName As String = Trim(Request.Form("txtUserName")

In the first example, you get type checking (e.g. if txtUserName does not exist, you get a compile time error), Intelli Sense (e.g. VWD tells you there is a Trim() method and much more.

The same applies to your other code. ADODB.Recordsets are for the classic COM world, like ASP and VB. You're much better off using smart controls (like the GridView, the ObjectDataSource or even the SqlDataSSource controls), code behind, SqlConnection objects, app configuration settings, etc etc. Man, I don't even know where to start with this to convince you.... ;)

Really, you're missing out many of the great features. In fact, with the current code you have, there is *absolutely* no point to run this page like an APSX page. You're much better off saving it with an .asp extension.

With regards to the param: you trim what you pass in. However, when the value enters the sproc, it's assigned to a char datatype that, by default, pads itself with spaces. Try using a varchar instead.

Once again: I highly recommend looking into how to use ASP.NET the proper way. Nothing personal, but with the current code you have, I hope I never have to work with you on a project together... ;)

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

kwilliams · May 9th, 2006, 02:29 PM

I do appreciate your thorough advice, and I will look into what you've mentioned concerning ASP.NET. But I must say that so far, ASP appears to be an easier way to code for a beginner developer than ASP.NET. And I'm not the only person saying that. I've also been disapointed with its performance, but I guess that there's not much that can be done about that.

I must say that I think that constructive criticism is great. I always appreciate it, as I can get a completely new outlook on something from a user I don't normally work with. But I must admit that some of your wording doesn't exactly encourage me or other users to request real help from this forum. It just scares a person off, so that they won't appear as a fool. If that's your intention, then you've succeeded. But if it wasn't your intention, which I don't believe that it was, then you might want to rethink your method. Just a little tip that you can take or leave. Thanks again for your advice.

Quote:

quote:Nothing personal, but with the current code you have, I hope I never have to work with you on a project together... ;)

That's fine...the feeling is mutual:)

KWilliams

Imar · May 9th, 2006, 04:31 PM

I'll be more than happy to take the tip, but I'll try to explain my reasoning behind the "advice" I gave you.

From what I have seen so far, you're using the wrong tool for the job. It's a bit like constructing a car. Let's say you want to create a new Volvo S60 car with a high performant, turbo diesel motor that can easily drive 150 miles per hour. To make such a car, you need a thorough understanding of engines, fuel injection, gear boxes and the concepts of Turbo.

In your situation, you ditched the idea of the turbo and the fuel injection, because you haven't been able to familiarize yourself with these techniques yet. There is nothing wrong with that; after all, these are broad and difficult concepts that take some time to master.

However, with the code you posted, and the concepts you're using, you seem to be giving up trying to understand these concepts, and instead use tree-trunks for wheels, and a hole in the floor to stick your feet through for acceleration.... While it will build you a car that moves, it may not be the one you have envisioned. In the long run, you'll find yourself in a loosing position, if you have to compete with other drivers / programmers. Let's be honest, in a race, would you like to be the navigator in the fast car, or in the wooden version? ;)

Don't get me wrong though. I understand how hard it can be to learn .NET. I understand what a steep learning curve you have to go through to understand all these new concepts and how overwhelming it all can be. I also appreciate that classic ASP code may be a whole lot easier to understand and code. What I was trying to say if all this applies to you, you're probably better off coding classic ASP pages for a while instead. Since you're not using any of the ASP.NET features, there isn't much point in using the platform. This may also be the reason for the "bad performance" you have encountered. This may not be the fault of the platform, but of the way you use it. If you put tree-trunks under your Volvo, it won't drive very fast...

Sorry if I offended you or came across as rude. That was never my intention. I was referring to the code you have; not to the efforts, time or enthusiasm you have put in it, and especially not to you personally. I tried to tone down my remarks by saying "nothing personal", and adding a smiley to it.

BTW: did you fix the parameter problem?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

kwilliams · May 9th, 2006, 04:41 PM

I appreciate your reply, and the further insight that you have given. I will definitely take a step back from the way that I'm programming to do it in the proper way, even though it can become quite overwhelming to me as a newbie to ASP.NET and VB.NET.

I know that a lot of what frustrated me personally was the fact that almost every book and tutorial is geared towards the user that uses Visual Studio.NET. I felt a lot more comfortable with ASP Web Matrix for my needs, but found hardly any real support for this application. So after a lot of searching for that type of support, and a lot of trial and error, I just gave up. But I will try it again once I get a firm grasp on the entire "machine".

Your tip did fix the issue. I simply changed the parameter's datatype from char to varchar, and it worked properly. Thanks for your help. I really do appreciate it...I promise. Have a good afternoon;)

KWilliams

Imar · May 10th, 2006, 12:55 AM

If you're building ASP.NET 2.0 applications, then you'll need indeed Visual Studio / Visual Web Developer.

Each version of .NET is tied to a specific version of the development tools. With Visual Studio .NET 2002 you target .NET 1. With VS.NET 2003 you target .NET 1.1 and you need Visual Studio 2005 / Visual Web Developer 2005 to build sites for .NET 2.0

The Web Matrix was targeted at .NET 1.x so you can't use it anymore for development of ASP.NET 2.0 sites. If you try anyway, you'll find that most of its capabilities are gone, and you can mostly use it as "Notepad on steroids".

The new (and free!!) Visual Web Developer 2005 Express Edition is the replacement of Web Matrix. Although VWD is different in some respects, it's also similar to Web Matrix in many other areas. If you are going to build ASP.NET 2.0 sites, you can't do without VWD / Visual Studio 2005 (well, you could as you could write your pages in a plain text editor, but that's not a pleasant experience)....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004