SQL Data Types - Please Help

ASP Free Forums Sponsor:

December 6th, 2003, 08:58 AM

jsaunders

Registered User

Join Date: Nov 2003

Location: North Carolina

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

SQL Data Types - Please Help

I have a search page that has several options to search a SQL Server 2000 database. The text box is the string, radio buttons to decide which field to search by..

Code:

'Request querystring search field first
If srcBy = "" Then

'Determine what field to search by
If Request.Form("rSel") = "S1" Then
strSearchBy = "SSNUM"
End If
If Request.Form("rSel") = "S2" Then
strSearchBy = "PATACNTNUM"
End If
If Request.Form("rSel") = "S3" Then
strSearchBy = "NSRPLCYNUM"
End If
If Request.Form("rSel") = "S4" Then
strSearchBy = "PATIENTNAM"
End If
Else
strSearchBy = Request.QueryString("srcBy")
End If

And then the query to the database:

Code:

If strID = "" Then
'Initialize the strSQL variable with a SQL statement to query the database.
strSQL =  "SELECT * FROM tblVitalData WHERE " & strSearchBy & " = " & strSearch
Else
strSQL = "SELECT * FROM tblVitalData WHERE " & strSearchBy & " = " & strSearch & " AND VITALID=" & strID & ""
End If
'Query the database.
rsInfo.Open strSQL, strCon, 1, 3    'This is line 158

If SSNUM or PATACNTNUM are selected (which are type float(8)), I have no problem searching the database. If NSRPLCYNUM (a policy number type nvarchar(255)) is selected, and I search for 1234A I get this error:

Quote:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'A'.
/vitaldata/search.asp, line 158

The same thing happens if I select PATIENTNAM (also nvarchar(255)) and search for John Smith. It would say error near Smith. Can someone please help here??

December 8th, 2003, 09:26 PM

sbaxter

Moderator: Access, SQL

Join Date: Oct 2003

Posts: 5,126

Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 13

The reason you sal works for the field that are a Number is because you code is set up to only work for Numbers. You have to allow the ability to specify that certain fields are strings and not numbers by surroundng them with a single quote '

Try the following:

If srcBy = "" Then

'Determine what field to search by
If Request.Form("rSel") = "S1" Then
strSearchBy = "SSNUM"
StringValue = ""
End If
If Request.Form("rSel") = "S2" Then
strSearchBy = "PATACNTNUM"
StringValue = ""
End If
If Request.Form("rSel") = "S3" Then
strSearchBy = "NSRPLCYNUM"
StringValue = "'"
End If
If Request.Form("rSel") = "S4" Then
strSearchBy = "PATIENTNAM"
StringValue = "'"
End If
Else
strSearchBy = Request.QueryString("srcBy")
StringValue = ""
End If

If strID = "" Then
'Initialize the strSQL variable with a SQL statement to query the database.
strSQL = "SELECT * FROM tblVitalData WHERE " & strSearchBy & " = " & StringValue & strSearch & StringValue
Else
strSQL = "SELECT * FROM tblVitalData WHERE " & strSearchBy & " = " & StringValue & strSearch & StringValue & " AND VITALID=" & strID & "
End If

December 9th, 2003, 08:18 AM

jsaunders

Registered User

Join Date: Nov 2003

Location: North Carolina

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

Thank you, I did eventually figure that out.. Here's a question for you though... I had my program working perfectly, exactly how it was requested to work. It work fast working with 15,000 records. Now that I have loaded a month's worth of data (188,000 records), it cannot work fast enough. It times out doing a query that big. It seems that there will be around 2.3 million records per year being pushed into this database! Is there any possible way to search all of this over the intranet?

December 9th, 2003, 11:15 AM

sbaxter

Moderator: Access, SQL

Join Date: Oct 2003

Posts: 5,126

Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 13

Just about anything is possible, it is just a matter of finding what works

You could create a stored procedure and pass the parameters to the procedure. Stored procedure run faster then when you push a query to SQL Server. (which I believe runs on the SQL and send you the SQL result only). Some procedures pull the whole dataset to the local machine and then process it.

You could also add some indexes to you table, which will speed the search up.

I am not farmilar with ASP, but I woudl imagine that ASP has different ways to do the same thing and some a problably faster then others.

There a lots of things to do with that and there are people smarter then me who would be able to give you more specific answer, hopefully they will read this thread too.

S-

December 9th, 2003, 12:02 PM

Memnoch

Unholy Moderator

Join Date: Oct 2003

Location: In hell, where did you think?

Posts: 11,781

Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470

I agree with sbaxter...create a stored procedure, index the necessary field(s). Then call the procedure through your asp code.

create 2 stored procedures...1 for the first sql statement and 1 for the else part of the code

Code:

CREATE PROCEDURE stp_SearchOne
   @strSearch varchar(30)
AS
SELECT *
FROM tblVitalData
WHERE @strSearch = @strSearch

GO

'Second stored procedure

Code:

CREATE PROCEDURE stp_SearchTwo
   @strSearch varchar(30)
   @strID int
AS
SELECT *
FROM tblVitalData
WHERE @strSearch = @strSearch AND
VITALID = @strID

GO

'In your ASP page

Code:

If strID = "" Then
   rsInfo = Conn.Execute("EXEC dbo.SearchOne @strSearch = strSearch")
Else
   rsInfo = Conn.Execute("EXEC dbo.SearchTwo @strSearch = strSearch, @strID = strID")
End If

Response.write(rsInfo("field1"))
etc...

This may need some adjustment to work properly, because I am going of the top of my head.

Last edited by Memnoch : December 9th, 2003 at 03:54 PM.

December 9th, 2003, 12:48 PM

jsaunders

Registered User

Join Date: Nov 2003

Location: North Carolina

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

That's sounds like a great idea. However, I get the error:

Quote:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@strSearch'.

When trying the first stored procedure through SQL Query Analyzer

December 9th, 2003, 03:53 PM

Memnoch

Unholy Moderator

Join Date: Oct 2003

Location: In hell, where did you think?

Posts: 11,781

Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470

my mistake...I added an AS in the wrong place...I updated the code from my earlier posting.

Last edited by Memnoch : December 9th, 2003 at 03:56 PM.

December 9th, 2003, 04:35 PM

jsaunders

Registered User

Join Date: Nov 2003

Location: North Carolina

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

Ah ok, makes much more sense now. I've created several stored procedures for each search arrangement. From SQL Query Analyzer, it takes 0 seconds to return the results. When I run that over the web, it times out, telling me that the ASP script timeout setting needs to be changed. It is at 120 seconds already... Any suggestions? This is running on my local pc's IIS currently, by the way. I have to get it working before I move it to our web server here.

December 9th, 2003, 04:47 PM

Memnoch

Unholy Moderator

Join Date: Oct 2003

Location: In hell, where did you think?

Posts: 11,781

Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470

Add this code to your ASP Page.

Code:

Server.ScriptTimeout = 1800

The default timeout is 90 seconds...this will allow the script to run for 1800 seconds (30 minutes) before it returns a script timeout error.

#10

December 9th, 2003, 09:23 PM

jsaunders

Registered User

Join Date: Nov 2003

Location: North Carolina

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

Yes, I had changed that before in IIS. It took 1 minute 35 sec before in SQL Query Analyzer to run the SQL query. It now takes 0 seconds to run the stored procedure. Shouldn't it be the same basically through the asp? Especially since it's currently a local SQL server and local web? It seemed to take 0 seconds when I ran the SQL query through ASP with 15,000 records. With the stored procedure, plus I added 2 indexes, it would seem that it would have to run faster.. By the way, Memnoch & sbaxter, I should really thank you for all of your suggestions and time helping me on this. I couldn't have gotten this far without all the help.

#11

December 9th, 2003, 09:50 PM

jsaunders

Registered User

Join Date: Nov 2003

Location: North Carolina

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

I am connecting the correct way right?

PHP Code:


		
			
'Create a connection odject.

Set adoCon = Server.CreateObject("ADODB.Connection")



'Database connection info and driver.

strCon = "driver={SQL Server};Server=(local);Database=VitalData;Uid=;Pwd  ="



'Set an active connection to the Connection object.

On Error Resume Next

adoCon.Open strCon

If Err Then

Response.Redirect("Index.asp?alert=System Cuurently Unavailable\nPlease contact Your System Administrator&Searched=System Currently Unavailable")

End If



'Create a recordset object.

Set rsInfo = Server.CreateObject("ADODB.Recordset")



rsInfo = strCon.Execute("EXEC dbo.SearchOne @strSearch = " & strSearch & "")

Last edited by jsaunders : December 9th, 2003 at 09:53 PM.

#12

December 9th, 2003, 09:54 PM

Memnoch

Unholy Moderator

Join Date: Oct 2003

Location: In hell, where did you think?

Posts: 11,781

Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470

Quote:

It took 1 minute 35 sec before in SQL Query Analyzer to run the SQL query. It now takes 0 seconds to run the stored procedure. Shouldn't it be the same basically through the asp?

Not sure what you mean above?
Do you mean when you ran the actual sql statement ("SELECT * etc...) in the query analyzer it took 95 secs? And when you just executed the store procedure it took 0 seconds?

Shouldn't what be the same through asp? Sending a sql statement, as opposed to executing a stored procedure???

#13

December 10th, 2003, 08:37 AM

jsaunders

Registered User

Join Date: Nov 2003

Location: North Carolina

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

I'm sorry, my post was confusing.. I have played around with it much more and now it is running almost perfectly. From the asp page it takes no time to bring back all of the information. I have a problem bringing back all of the records though..

Quote:

On Error Resume Next
rsInfo = adoCon.Execute("EXEC dbo.sp_demo_ssn @strSearch = " & strSearch & "")
If Err Then
Response.Write("There was a problem executing the stored procedure.")
End If

Response.Write(rsInfo("PATIENTNAM"))

That will instantly bring back the first record. Before I used something like:

While Not rsInfo.EOF

Response.Write(rsInfo("PATIENTNAM"))
rsInfo.MoveNext

wend

to return all the records.. Now that doesn't work.. If I can just get that part, it will be perfect.

#14

December 10th, 2003, 09:42 AM

Memnoch

Unholy Moderator

Join Date: Oct 2003

Location: In hell, where did you think?

Posts: 11,781

Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470

have you tried doing this?