SQL statement problem

[meldrape]

Greetings,

I have two fields I am querying: CertNum and GradID. CertNum is varchar and GradID is int. The user looks up info by typing in either their CertNum or GradID. Here's the query:

strCert = Request.QueryString("cert&quot 'from the form where they enter their number

Set oRs = objConn.Execute("SELECT * from vw_ProfileNew WHERE (GradID = " & strCert & &quot or (CertNum = '" & strCert & "')&quot

Here's the problem:

Each part of the where clause works great independently. Using 8050B for certnum and 111142 for CertGradID. If I use both clauses together though and type in 8050B, it returns all records which are in the thousands. If I use 111142 though, it works properly. If I take out GradID where clause and use 8050B, it works great. I am at a loss. any thoughts would be greatly appreciated.


Thank you.
Mel

 

[Niv3k]

Which version of SQL Server are you running?

 

[meldrape]

Oh, sorry, 7.0

Any thoughts? Is it just impossible to query on a both a numeric or text field in the same statement?

I have to figure out some way to do this... Thanks.

 

[fluteplr]

No it is not impossible I do it all the time.

When I am having a problem like this I usually turn on sql trace and see exactly what sql statement the server is recieving, more often than not I realize there was some dumb syntax error in the statement.

 

[meldrape]

Hi Fluteplr,

I am using Visual Interdev. The statement is in an ASP. What is the best way to run a trace, especially with the variables? Thanks.

 

[balves]

You could take the SQL statement out and paste it into SQL Query Analyzer.

I don't see how this won't generate an error if GradId is defined as an int:

WHERE GradId = 8050B

Does the ASP page have error trapping which then selects a bunch more records?

 

[meldrape]

Well, certnum is actually the one with the character in it (CertNum = '" & strCert & "'). I'll check the error trapping and run the statement through query analyzer. Thanks again.

 

[Niv3k]

What I was thinking was a stored procedure...
Try adding a stored procedure to the database, like so:

CREATE PROCEDURE dbo.sp_ProfileNew (@CertNum varchar (5), @GradID int) AS

If @GradID Is Null AND @CertNum Is Null
    SELECT * FROM vw_ProfileNew 
ELSE
If @GradID Is Null 
BEGIN
    SELECT * FROM vw_ProfileNew 
    WHERE CertNum = @CertNum 
END
ELSE
If @CertNum Is Null
BEGIN
    SELECT * FROM vw_ProfileNew 
    WHERE GradID = @GradID 
END
ELSE
BEGIN
    SELECT * FROM vw_ProfileNew 
    WHERE GradID = @GradID or CertNum = @CertNum 
END

GO

Of course, you would change the 5 in the Create Procedure line to the length of CertNum.

Then from the ASP code, you would say:

sCert = Request("Cert") 
If sCert = "" Then sCert = "Null"
sGrad = Request("Grad") 
If sGrad = "" Then sGrad = "Null"
Set oRs = objConn.Execute("exec sp_ProfileNew " & _
          sCert & ", " & sGrad)

This should work fine.