issues with a variable being passed via querystring 2

[specialist]

Greetings All-

Happy holidays! I'm running into a bit of an issue with a variable being passed via querystring to return results from a SQL db. I continue to get a :

Microsoft VBScript runtime error '800a000d'

Type mismatch: '[string: "SELECT COUNT (*) CAS"]'

error when opening this page. Perhaps I am confused by the syntax, but for some reason it is not working properly.

The purpose of this page is to display the count and record details from a DB based on a query string value passed from a form on another page...the options are:

entries less than 30 days old
" " 60 days old
" " 90 days old

please help me if you know a possible solution. thank you in advance and happy holidays!

michael

code:

<%
'*** The following items redesignate the incoming variables for use on this page
dateCriteria = Request.Querystring("dC")
readBack = (Date - dateCriteria)

Dim qryCount
Dim qryCount_numRows

Set qryCount = Server.CreateObject("ADODB.Recordset")
qryCount.ActiveConnection = MM_STRING
qryCount.Source = "SELECT COUNT (*) Entry FROM dbo.tblInfo WHERE Initial_DTG < '" + readBack + "' AND Status = 1"
qryCount.CursorType = 0
qryCount.CursorLocation = 2
qryCount.LockType = 1
qryCount.Open()

qryCount_numRows = 0
%>
<%
Dim mainQuery
Dim mainQuery_numRows

Set mainQuery = Server.CreateObject("ADODB.Recordset")
mainQuery.ActiveConnection = MM_STRING
mainQuery.Source = "SELECT * FROM tblInfo WHERE Initial_DTG < '" + readBack + "' AND Status = 1 ORDER BY Initial_DTG ASC "
mainQuery.CursorType = 0
mainQuery.CursorLocation = 2
mainQuery.LockType = 1
mainQuery.Open()

mainQuery_numRows = 0
%>
<%
Function showBreaks(str)
showBreaks = replace(str, chr(10), " <br>")
End Function

%>

 

[Chopstik]

When you are calculating your readBack variable, I am not sure I understand what you are attempting to do.

readBack = (Date - dateCriteria)

If Date is supposed to be an actual date, you should probably use the DateDiff() function to get the differential new date. Else, I'm not sure what you're hoping to accomplish.

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[specialist]

Chopstik-

Thanks for your reply. Date is supposed to be todays date which is defined by the GetDate function (below)

This project was supposed to be easy! moving an app from Access to SQL but is taking much longer, and much more difficult than imagined.

On this page, I am trying to count and display records less than 30, 60, and 90 days.

Perhaps a DateDiff would be good, but it seemed to work fine this way when looking at the Access DB.

Would that cause the error in the string?

-Michael

date script currently being used:

<script language="JavaScript">

// -- made by A1javascripts.com, please keep these credits when using this script
days = new Array(7)
days[1] = "Sunday";
days[2] = "Monday";
days[3] = "Tuesday";
days[4] = "Wednesday";
days[5] = "Thursday";
days[6] = "Friday";
days[7] = "Saturday";
months = new Array(12)
months[1] = "January";
months[2] = "February";
months[3] = "March";
months[4] = "April";
months[5] = "May";
months[6] = "June";
months[7] = "July";
months[8] = "August";
months[9] = "September";
months[10] = "October";
months[11] = "November";
months[12] = "December";
today = new Date(); day = days[today.getDay() + 1]
month = months[today.getMonth() + 1]
date = today.getDate()
year=today.getYear();
if (year < 2000)
year = year + 1900;
document.write ("<font size=2 face='Arial, Helvetica, sans-serif' color=000000> "+ day +
", " + month + " " + date + ", " + year + "</font>")
// -- end hiding
</script>

 

[Genimuse]

If you don't want to use DateDiff, you should at least ensure that the querystring variable is a date (by default it's a string) using CDate(yourVarHere).

It didn't actually work in Access, right, because there you weren't using a QueryString variable. ;-)

 

[specialist]

I do not mind using the dateDiff function, I'm just not sure as to the correct syntax...I keep getting the error:

Microsoft VBScript runtime error '800a000d'

Type mismatch: '[string: "SELECT COUNT (*) ENT"]'

test.asp, line 15

line 15 = qryCount.Source = "SELECT COUNT (*) Entry FROM dbo.tblInfo WHERE Initial_DTG < '" + readBack + "' AND Status = 1"

I think it's that '" + readBack + "' thing.

Besides, I would spend all days trying to figure out how to define DateDiff for 30, 60, and 90 day options on this thing (can u tell im new at this?)

 

[Chopstik]

What is the actual querystring value that you are returning? Also, there should not be a need to determine the date, all you need in ASP is to use the Now() function which gives you the current date and time. Then, you can use the DateDiff() function in terms of days. For help with the DateDiff() function, you can check here to understand the syntax. It is not difficult and should more than work to accomplish your needs.

It would also be a good idea to follow Genimuse's suggestion about converting your querystring variable to a date before using the DateDiff(). Also, your syntax here should probably be ampersand instead of plus. (in red):

qryCount.Source = "SELECT COUNT (*) Entry FROM dbo.tblInfo  WHERE Initial_DTG < '" [COLOR=red]&[/color] readBack [COLOR=red]&[/color] "' AND Status = 1"

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[specialist]

well, first things first:

1. thank you both (Genimuse and Chopstik) very much for your advice. Here are the results so far:

1. The ampersands removed the error. Now I am getting the page w/o any results...which is an improvement!!

2. I really think it is the readBack function now. It does not seem to be read properly for one reason or another. Perhaps it is the confusion where readBack is defined.

Baby steps, I'll get there!

"What is the actual querystring value that you are returning? " asked by Chopstik

The value is based on the end user, there are three options to select from:

<30 days = test.asp?dC=30
<60 days = test.asp?dC=60
<90 days = test.asp?dC=90\

the value is supposed to be "Initial_DTG" which is the start date is less than 30 60 or 90 days.

Perhaps I need to re-define the readBack and dateCriteria function with a Dim???

<%

Dim dateCriteria
Dim readBack

dateCriteria = Request.Querystring("dC")
readBack = (Date - dateCriteria)
%>

would that help in your opinion?

any advice would be great, it's getting somewhat frustrating and I"m under the gun which making it even worse!!

Again, thank you for your support and any direction on this!

michael

 

[Genimuse]

The dim won't make a diffrence, but I still suggest the CInt function (not CDate, like I mistakenly said above), a la:

readBack = (Date - [COLOR=blue]CInt([/color]dateCriteria[COLOR=blue])[/color])

Or use DateAdd, like this:

readBack = DateAdd("d", Date, dateCriteria * -1)

 

[specialist]

Genimuse-

Thanks for the advice, for some reason the count and data are not showing up

here is what I have so far....if you can see a reason why the readBack would not be displayed please let me know if possible! FYI, the include is the connection.

gracias


<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../Connections/con1.asp" -->

<%
'*** The following items redesignate the incoming variables for use on this page
colorFlag = "TRUE"
dateCriteria = Request.Querystring("dC")
readBack = DateAdd("d", Date, dateCriteria * -1)
%>
<%
Dim qryCount
Dim qryCount_numRows

Set qryCount = Server.CreateObject("ADODB.Recordset")
qryCount.ActiveConnection = MM_STRING
qryCount.Source = "SELECT COUNT (*) Entry FROM dbo.tblPInfo WHERE Initial_DTG < '" & readBack & "' AND Status = 1"
qryCount.CursorType = 0
qryCount.CursorLocation = 2
qryCount.LockType = 1
qryCount.Open()

qryCount_numRows = 0

%>
<%
Dim mainQuery
Dim mainQuery_numRows

Set mainQuery = Server.CreateObject("ADODB.Recordset")
mainQuery.ActiveConnection = MM_STRING
mainQuery.Source = "SELECT * FROM tblInfo WHERE Initial_DTG < '" & readBack & "' AND Status = 1 ORDER BY Initial_DTG ASC "
mainQuery.CursorType = 0
mainQuery.CursorLocation = 2
mainQuery.LockType = 1
mainQuery.Open()

mainQuery_numRows = 0
%>
<%
Function showBreaks(str)
showBreaks = replace(str, chr(10), " <br>")
End Function

%>

<html>

<head>
<title>Search results</title>

</head>

<body>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="79%"> </td>
</tr>
<tr>
<td width="79%" height="19"><p align="center">
<script language="JavaScript">

// -- made by A1javascripts.com, please keep these credits when using this script
days = new Array(7)
days[1] = "Sunday";
days[2] = "Monday";
days[3] = "Tuesday";
days[4] = "Wednesday";
days[5] = "Thursday";
days[6] = "Friday";
days[7] = "Saturday";
months = new Array(12)
months[1] = "January";
months[2] = "February";
months[3] = "March";
months[4] = "April";
months[5] = "May";
months[6] = "June";
months[7] = "July";
months[8] = "August";
months[9] = "September";
months[10] = "October";
months[11] = "November";
months[12] = "December";
today = new Date(); day = days[today.getDay() + 1]
month = months[today.getMonth() + 1]
date = today.getDate()
year=today.getYear();
if (year < 2000)
year = year + 1900;
document.write ("<font size=2 face='Arial, Helvetica, sans-serif' color=000000> "+ day +
", " + month + " " + date + ", " + year + "</font>")
// -- end hiding
</script>
<small><font face="Arial">&nbsp; </font></small></td>
</tr>
</table>
<div align="center"><center>

<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="100%"><hr size="1" color="#000080">
</td>
</tr>
</table>
</center></div><div align="center"><center>

<table border="0" cellpadding="0" cellspacing="0" width="97%">
<tr>
<td width="100%">&nbsp;&nbsp; </td>
</tr>
<tr>
<td width="100%"><strong><font face="Arial">Search Results</font></strong></td>
</tr>
</table>
</center></div><div align="center"><center>

<table border="0" cellpadding="0" cellspacing="0" width="97%">
<tr>
<td width="100%">&nbsp;&nbsp;</td>
</tr>
<tr>
<td width="100%"><table border="1" cellpadding="2" cellspacing="0" width="717">
<tr>
<td width="717" bgcolor="#DDFFDD"><%response.write "<font face='arial'><strong><small>Pending jobs greater than " & dateCriteria & " days</small></strong></font><br>"%>
</td>
</tr>
</table>
</td>
</tr>
</table>
</center></div><div align="center"><center>

<table border="0" cellpadding="0" cellspacing="0" width="97%">
<tr>
<td width="100%"></td>
</tr>
<tr>
<td width="100%"><small><font face="Arial">Number of records returned:&nbsp;
<%=(qryCount.Fields.Item("jobs").Value)%> </font></small></td>
</tr>
<tr>
<td width="100%"><font face="Arial"><small>&nbsp; </small></font></td>
</tr>
</table>
</center></div>
<%
Response.write "<div align='center'>"
Response.write "<table border='0' cellpadding='0' cellspacing='0' width='97%'>"
Response.write "<tr>"
Response.write "<td width='5%'><font face='Arial' size='2'><strong>Nr.</strong></font></td>"
Response.write "<td width='6%'><font face='Arial' size='2'><strong>Type</strong></font></td>"
Response.write "<td width='3%'><font face='Arial' size='2'><strong>Cat</strong></font></td>"
Response.write "<td width='14%'><font face='Arial' size='2'><strong>Name</strong></font></td>"
Response.write "<td width='32%'><font face='Arial' size='2'><strong>System</strong></font></td>"
Response.write "<td width='35%'><font face='Arial' size='2'><strong>Comments</strong></font></td>"
Response.write "</tr>"
%>
<%
Response.write "<tr>"
Response.write "<td width='5%'><font face='Arial' size='2'><strong>&nbsp;</strong></font></td>"
Response.write "<td width='6%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='3%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='14%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='32%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='35%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='5%'><font face='Arial' size='2'></font></td>"
Response.write "</tr>"
%>
<%
Do while not mainQuery.eof
%>

<% If colorFlag = "TRUE" then
Response.write "<tr>"
Response.write "<td width='5%'><font face='Arial' size='2'><a href='record_detail.asp?IDField=" & (mainQuery.Fields.Item("job_number").Value) & "'>" & (mainQuery.Fields.Item("section").Value) & "</a></font></td>"
Response.write "<td width='6%'><font face='Arial' size='2'>" & (mainQuery.Fields.Item("Type").Value) & "</font></td>"
Response.write "<td width='3%'><font face='Arial' size='2'>" & (mainQuery.Fields.Item("Category").Value) & "</font></td>"
Response.write "<td width='14%'><font face='Arial' size='2'>" & (mainQuery.Fields.Item("Name").Value) & "</font></td>"
Response.write "<td width='35%'><font face='Arial' size='1'>" & showBreaks(mainQuery.Fields("Equipment_remarks")) & "</font></td>"
'Response.write "<td width='5%'><font face='Arial' size='2'><strong>" & CalcDiff & "</strong></font></td>"
Response.write "</tr>"

Response.write "<tr>"
Response.write "<td width='5%'></td>"
Response.write "<td width='6%'></td>"
Response.write "<td width='3%'></td>"
Response.write "<td width='14%'></td>"
Response.write "<td width='32%'><font face='Arial' size='1'>Impact: " & (mainQuery.Fields.Item("Impact").Value) & "</font></td>"
Response.write "<td width='35%'></td>"
Response.write "<td width='5%'></td>"
Response.write "</tr>"
colorFlag = "FALSE"

Else if colorFlag = "FALSE" then
Response.write "<tr>"
Response.write "<td width='5%' bgcolor='#D7EDDB'><font face='Arial' size='2'><a href='record_detail.asp?IDField=" & (mainQuery.Fields.Item("job_number").Value) & "'>" & (mainQuery.Fields.Item("section").Value) & "</a></font></td>"
Response.write "<td width='6%' bgcolor='#D7EDDB'><font face='Arial' size='2'>" & (mainQuery.Fields.Item("Type").Value) & "</font></td>"
Response.write "<td width='3%' bgcolor='#D7EDDB'><font face='Arial' size='2'>" & (mainQuery.Fields.Item("Category").Value) & "</font></td>"
Response.write "<td width='14%' bgcolor='#D7EDDB'><font face='Arial' size='2'>" & (mainQuery.Fields.Item("Name").Value) & "</font></td>"
Response.write "<td width='35%' bgcolor='#D7EDDB'><font face='Arial' size='1'>" & showBreaks(mainQuery.Fields("Equipment_remarks")) & "</font></td>"
Response.write "</tr>"
Response.write "<tr>"
Response.write "<td width='5%' bgcolor='#D7EDDB'></td>"
Response.write "<td width='6%' bgcolor='#D7EDDB'></td>"
Response.write "<td width='3%' bgcolor='#D7EDDB'></td>"
Response.write "<td width='14%' bgcolor='#D7EDDB'></td>"
Response.write "<td width='32%' bgcolor='#D7EDDB'><font face='Arial' size='1'>Impact: " & (mainQuery.Fields.Item("Impact").Value) & "</font></td>"
Response.write "<td width='35%' bgcolor='#D7EDDB'></td>"
Response.write "<td width='5%' bgcolor='#D7EDDB'></td>"
Response.write "</tr>"
colorFlag = "TRUE"
end if
end if
%>

<%
mainQuery.MoveNext
Loop
%>
<%
Response.write "<tr>"
Response.write "<td width='5%'><font face='Arial' size='2'><strong>&nbsp;</strong></font></td>"
Response.write "<td width='6%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='3%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='14%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='32%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='35%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='5%'><font face='Arial' size='2'></font></td>"
Response.write "</tr>"
%>
<%
Response.write "<tr>"
Response.write "<td width='5%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='6%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='3%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='14%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='32%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='35%'><font face='Arial' size='2'></font></td>"
Response.write "<td width='5%'><font face='Arial' size='2'></font></td>"
Response.write "</tr>"
%>
<%
Response.write "</table>"
Response.write "</center>"
Response.write "</div>"
%>
<div align="center"><center>

<table border="0" cellpadding="0" cellspacing="0" width="97%">
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td>

</td>
</tr>
<tr>
<td width="100%"><strong><small><font face="Arial">End of records.</font></small></strong></td>
</tr>
</table>
</center></div>

<p>&nbsp;</p>
</body>
</html>

<%
qryCount.Close()
Set qryCount = Nothing
%>
<%
mainQuery.Close()
Set mainQuery = Nothing
%>
<%
'*** This function calculates the difference between date submitted and current date
Function CalcDiff
varDate = mainQuery("Initial_DTG")
tmpDiff = DateDiff("d", varDate, Date)

'*** Quick conditionals determine color code
If tmpDiff <= 30 THEN
CalcDiff = "<font color='GREEN'>" & tmpDiff & "</font>"
End If

If tmpDiff >= 31 AND tmpDiff <= 59 THEN
CalcDiff = "<font color='BLUE'>" & tmpDiff & "</font>"
End If

If tmpDiff >= 60 AND tmpDiff <= 89 THEN
CalcDiff = "<font color='ORANGE'>" & tmpDiff & "</font>"
End If

If tmpDiff >= 90 THEN
CalcDiff = "<font color='RED'>" & tmpDiff & "</font>"
End If

End Function
%>

 

[Genimuse]

Yeah, don't use DateDiff, that was a mistake, too. That will return a number of days, but what you want is a date. Use the DateAdd the way I showed it above.

 

[Chopstik]

Is readBack supposed to be an actual date or the number 30, 60 or 90? Along that same line, is the Initial_DTG supposed to be a number or a date? This can make a difference when you're comparing it in your SELECT query. Try this so you can see what you are actually using when you do your SELECT statement and then copy and paste the results into Access for testing.

dateCriteria = Request.Querystring("dC")
readBack = DateAdd("d", Date, [COLOR=red]cInt([/color]dateCriteria[COLOR=red])[/color] * -1)
%>
<%
Dim qryCount
Dim qryCount_numRows

Set qryCount = Server.CreateObject("ADODB.Recordset")
qryCount.ActiveConnection = MM_STRING
qryCount.Source = "SELECT COUNT (*) Entry FROM dbo.tblPInfo  WHERE Initial_DTG < '" & readBack & "' AND Status = 1"
[COLOR=red]response.write "SELECT COUNT (*) Entry FROM dbo.tblPInfo  WHERE Initial_DTG < '" & readBack & "' AND Status = 1"
response.end[/color] [COLOR=green]'This will stop all processing and you can just see what your actual SELECT statement actually looks like.[/color]
qryCount.CursorType = 0

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[specialist]

Chopstik-

Initial_DTG is a date. It is a column from one of the tables in the SQL db.

readBack is supposed to be a number which calculates a function based on the querystring, for example, the original was:

readBack = (Date - dateCriteria)
dateCriteria = Request.Querystring("dC")

If dateCriteria were 30 then the function would subtract 30 from the Date.
Same situation for 60 and 90. The purpose is to give the results based on the last 30, 60, and 90 days related to the certain jobs.

does that make sense, or do i need more coffee ?
I'm literally at the end of my rope on this one!!

-michael

 

[Genimuse]

I swear to God, the DateAdd thing I posted is what you need to use.

Is there something wrong with it or something?

Also, you're going to have to separate your color thing from your date calculation, since there's no way the database is going to want all that HTML. Two functions, if you want, one that calculates the date for the database, like this:

Function CalcDate(intOffset)
    CalcDate = DateAdd("d", Date, intOffset * -1)
End Function

which you would then call in your code using

CalcDate(Request.QueryString("dC"))

inserting it into your SQL.

 

[specialist]

<pulling out whats left of my hair>

I tried the DateAdd thing and nothing come out of it. I still have it on my page.

I am able to get the dC to pass, as the "Pending jobs greater than 30 days" line came up which is located here:

<td width="717" bgcolor="#DDFFDD"><%response.write "<font face='arial'><strong><small>Pending jobs greater than " & dateCriteria & " days</small></strong></font><br>"%>
</td>

One small step at a time I suppose.

However, the readBack is still returning a whole lotta nothing

Would a stored procedure work in this situation? perhaps it would be easier.

 

[Chopstik]

When you say that nothing came of the DateAdd() function, exactly what results were you (not) getting? How is it not working? Also, did you response.write your SQL string so that you can see exactly what is working? I'm sorry, but I am failing to see where your problem is if you are following the advice that Genimuse and I have suggested.

Your DateCriteria is either 30, 60 or 90. If you use the DateAdd() function (as we outlined above), then it should return the current date minus whatever number your DateCriteria holds. You should then be able to use this (date) in your SELECT statement without a problem. This is your problem as I have understood it to this point. If this is incorrect or you are having additional problems, please clarify so that we may better assist you.

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[specialist]

Chopsitk-

Perhoas I wasnt specific yesterday afternoon, quite a frustrating task but I am thankful for this forum to get help and guidance.

When I tried to add the DateAdd() function, there was no change in the results, meaning there were no results displayed.

When I add the response.write statement, I get the following on a page:
for 30.asp:

SELECT COUNT (*) Entry FROM dbo.tblPInfo WHERE Initial_DTG < '11/30/2004' AND Status = 1

for 60.asp:
SELECT COUNT (*) Entry FROM dbo.tblPInfo WHERE Initial_DTG < '10/31/2004' AND Status = 1

for 90.asp:

SELECT COUNT (*) Entry FROM dbo.tblPInfo WHERE Initial_DTG < '10/1/2004' AND Status = 1

I looked at the SQL DB table and the Data Type for Initial DTG is varchar. would that be the cause of all this?

I tried to convert the data type, but when I test the sql statement after changing the datatype to SmallDate or Date I get the folowing message when testing the sql statement:

conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

so, once again I'm stuck, but I feel like I am making progress. The function you sent DateAdd seems to calculate the exact number of days perfectly. Thank you

 

[Chopstik]

If the data type for your field is a varchar(), then yes, that could easily be the cause of your problem. You will need to change it to a datetime data type and that should hopefully resolve your problem.

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[specialist]

Oh, MY-lanta!! I believe I am more than halfway there now.

What a glorious day

I can get the Qrycount to accurately count the number of records based on the Querystring value.

Now, I have to work on displaying the results, those are not coming up quite yet.

Thank you SOO much, youre a blessing...I'm gonna try and get the display results to work...hopefully it wont be as difficult. One ? is I'm "testing" the recordset in DreamweaverMX and I continue to get the error converting to DateTime...is that bad or is the 'Weave just being the 'weave?

-- michael

 

[Genimuse]

That's definitely a problem. If you can't change the field type in the database, you can change you SQL to look like this:

SELECT COUNT (*) Entry FROM dbo.tblPInfo WHERE [COLOR=blue]CAST([/color]Initial_DTG[COLOR=blue] AS DateTime)[/color] < '11/30/2004' AND Status = 1

 

[Chopstik]

Can you clarify the problem that you are having trying to display the results? What error are you getting converting to DateTime? On what line and what is on that line?

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous