ASP
I have an asp form that has 2 fields (name & date) when form is submitted it returns data from an Access database table via a 2nd asp form. However, I think there's a problem with the "data type" for ReportDate. I keep getting "Data type mismatch in criteria expression". Help?
1st asp page:
<html>
<head></head>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("Master.mdb"))
set rs1 = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")
SQL1 = "SELECT DISTINCT Owner from tblScorecard"
SQL2 = "SELECT DISTINCT ReportDate from tblScorecard"
rs1.Open SQL1, conn
rs2.Open SQL2, conn
Set vOwner= rs1.Fields("Owner")
Set vReportDate= rs2.Fields("ReportDate")
%>
<form method="post" action="Scorecard.asp">
<p>
<label for="Name">Name: </label>
<select name="strOwner">
<%Do while not(rs1.eof)%>
<option value="<%=vOwner%>"><%=vOwner%></option>
<% rs1.movenext
loop
rs1.Close()
Set rs1 = Nothing
%>
</select>
<br/>
<label for="ReportDate">Report Date: </label>
<select name="strReportDate">
<%Do while not(rs2.eof)%>
<option value="<%=vReportDate%>"><%=vReportDate%></option>
<% rs2.movenext
loop
rs2.Close()
Set rs2 = Nothing
%>
</select>
<br/>
<input type="submit" value="Submit" /> <input type="reset" value="Reset" />
</p>
</form>
</body>
</html>
*********************************
2nd asp page
*********************************
<%
response.buffer = true
server.scripttimeout = 300
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
strDb = "Data Source=" & Server.MapPath("Master.mdb")
strConn = strProvider & strDb
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn
if Request.Form("strOwner") = "" THEN
Owner = "None"
else
Owner = Request.Form("strOwner")
end if
if Request.Form("strReportDate") = "" THEN
ReportDate = ""
else
'Response.Write("it hot here")
'Response.End
ReportDate = Request.Form("strReportDate")
end if
Response.Write(Owner)
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = '" & strReportDate & "' order by t.ReportDate"
'SQL1 = ("Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = '" & ReportDate & "' order by t.ReportDate")
'SQL1 = ("Select * from tblScorecard t where t.Owner = '" & Owner & "' order by t.ReportDate")
'Response.Write("Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = '" & strReportDate & "' order by t.ReportDate")
Set rs = conn.Execute(SQL1)
%>
<html>
<head>
</head>
<body>
<CENTER><STRONG><FONT SIZE="-2"> </FONT></STRONG></CENTER><BR><P>
<TABLE WIDTH=100% BORDER=1 CELLSPACING=1 CELLPADDING=1>
<tr>
<th bgcolor="#F2F2F2"><font size="-1">Title</font></th>
<th bgcolor="#F2F2F2"><font size="-1">Owner</font></th>
<th bgcolor="#F2F2F2"><font size="-1">ReportDate</font></th>
<th bgcolor="#F2F2F2"><font size="-1">Section</font></th>
<th bgcolor="#F2F2F2"><font size="-1">Desc</font></th>
<th bgcolor="#F2F2F2"><font size="-1">PointValue</font></th>
<th bgcolor="#F2F2F2"><font size="-1">Grp1Name</font></th>
<th bgcolor="#F2F2F2"><font size="-1">Grp1Goal</font></th>
<th bgcolor="#F2F2F2"><font size="-1">Grp1Actual</font></th>
<th bgcolor="#F2F2F2"><font size="-1">Grp1%ofGoal</font></th>
<th bgcolor="#F2F2F2"><font size="-1">Grp1PointsAchieved</font></th>
</tr>
<% Do While Not RS.EOF %>
<TR>
<TD><font size="-1"><%=UCase(RS("Title"))%></font></TD>
<td><font size="-1"><%=UCase(RS("Owner"))%></font></td>
<TD><font size="-1"><%=UCase(RS("ReportDate"))%></font></TD>
<TD><font size="-1"><%=UCase(RS("Section"))%></font></TD>
<TD><font size="-1"><%=UCase(RS("Desc"))%></font></TD>
<TD><font size="-1"><%=UCase(RS("PointValue"))%></font></TD>
<TD><font size="-1"><%=UCase(RS("Grp1Name"))%></font></TD>
<TD><font size="-1"><%=UCase(RS("Grp1Goal"))%></font></TD>
<TD><font size="-1"><%=UCase(RS("Grp1Actual"))%></font></TD>
<TD><font size="-1"><%=UCase(RS("Grp1%ofGoal"))%></font></TD>
<TD><font size="-1"><%=UCase(RS("Grp1PointsAchieved"))%></font></TD>
</TR>
<%
RS.movenext
Loop
%>
</TABLE>
<%
'do until rs.EOF
' for each x in rs.Fields
' Response.Write(x.Owner)
' Response.Write(" = ")
' Response.Write(x.value & "<br />")
' next
' Response.Write("<br />")
' rs.MoveNext
'loop
rs.close
conn.close
%>
</body>
</html>
ASKER
Thanks for the tip...tried it but I got a message "Syntax error in date in query expression 't.Owner = 'Joe Black' and t.ReportDate = ##'. Then I tried this:
'#" & ReportDate & "#' and got the message again of: Data type mismatch in criteria expression
'#" & ReportDate & "#' and got the message again of: Data type mismatch in criteria expression
do not put the hyphen '
Around it.
Around it.
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = #" & strReportDate & "# order by t.ReportDate"
ASKER
Copied your code & got the following error:
Error Type:
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 't.Owner = 'Adrienne Peters' and t.ReportDate = ##'.
/intranet/mis/wfm/NewScore
line 34 is the piece of code that says:
Set rs = conn.Execute(SQL1)
Error Type:
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 't.Owner = 'Adrienne Peters' and t.ReportDate = ##'.
/intranet/mis/wfm/NewScore
line 34 is the piece of code that says:
Set rs = conn.Execute(SQL1)
ASKER
I also tried your SQL code to try & debug using the following:
Response.Write(SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = #" & strReportDate & "# order by t.ReportDate")
and got the following error....Command text was not set for the command object line 34
Response.Write(SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = #" & strReportDate & "# order by t.ReportDate")
and got the following error....Command text was not set for the command object line 34
ReportDate = Request.Form("strReportDat
changed to
strReportDate = Request.Form("strReportDat
And your sql is:
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = #" & strReportDate & "# order by t.ReportDate"
Carrzkiss
changed to
strReportDate = Request.Form("strReportDat
And your sql is:
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = #" & strReportDate & "# order by t.ReportDate"
Carrzkiss
<%
response.buffer = true
server.scripttimeout = 300
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
strDb = "Data Source=" & Server.MapPath("Master.mdb")
strConn = strProvider & strDb
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn
if Request.Form("strOwner") = "" THEN
Owner = "None"
else
Owner = Request.Form("strOwner")
end if
if Request.Form("strReportDate") = "" THEN
ReportDate = ""
else
'Response.Write("it hot here")
'Response.End
strReportDate = Request.Form("strReportDate")
end if
Response.Write(Owner)
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = #" & strReportDate & "# order by t.ReportDate"
Set rs = conn.Execute(SQL1)
%>
scratch the above one
Changed the code in your SQL to be
t.ReportDate = #" & ReportDate & "#
instead of
t.ReportDate = #" & strReportDate & "#
Changed the code in your SQL to be
t.ReportDate = #" & ReportDate & "#
instead of
t.ReportDate = #" & strReportDate & "#
<%
response.buffer = true
server.scripttimeout = 300
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
strDb = "Data Source=" & Server.MapPath("Master.mdb")
strConn = strProvider & strDb
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn
if Request.Form("strOwner") = "" THEN
Owner = "None"
else
Owner = Request.Form("strOwner")
end if
if Request.Form("strReportDate") = "" THEN
ReportDate = ""
else
'Response.Write("it hot here")
'Response.End
ReportDate = Request.Form("strReportDate")
end if
Response.Write(Owner)
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = #" & ReportDate & "# order by t.ReportDate"
Set rs = conn.Execute(SQL1)
%>
ASKER
ok...it's gettng there....at least there's a new error:
Could not use "; file already in use.
line 10
line 10 is the code: conn.Open strConn
Could not use "; file already in use.
line 10
line 10 is the code: conn.Open strConn
Close the database and open it back up.
(You have it opened in Office Access, that is why you are getting the error)
So.
Close the database and then open it back up.
Carrzkiss
(You have it opened in Office Access, that is why you are getting the error)
So.
Close the database and then open it back up.
Carrzkiss
ASKER
Sorry, had a "duh" moment....closed the db & got
Error Type:
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 't.Owner = 'Adrienne Peters' and t.ReportDate = ##'.
/intranet/mis/wfm/NewScore
line 30 is the conn..Execute(SQL1)
Sorry, I know just enough ASP to be dangerous
Error Type:
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 't.Owner = 'Adrienne Peters' and t.ReportDate = ##'.
/intranet/mis/wfm/NewScore
line 30 is the conn..Execute(SQL1)
Sorry, I know just enough ASP to be dangerous
is that field a Date/Time field?
ASKER
yes
FieldName = ReportDate
DataType = Date/Time (mm/dd/yyyy)
FieldName = ReportDate
DataType = Date/Time (mm/dd/yyyy)
try this.
If one exist, then the other will go through.
Untested, but should work or give you another error.
If one exist, then the other will go through.
Untested, but should work or give you another error.
<%
response.buffer = true
server.scripttimeout = 300
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
strDb = "Data Source=" & Server.MapPath("Master.mdb")
strConn = strProvider & strDb
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn
if Request.Form("strOwner") = "" THEN
Owner = "None"
else
Owner = Request.Form("strOwner")
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' order by t.ReportDate"
end if
if if Request.Form("strOwner") = "" THEN
ReportDate = ""
else
'Response.Write("it hot here")
'Response.End
ReportDate = Request.Form("strReportDate")
SQL1 = "Select * from tblScorecard t where t.ReportDate = #" & strReportDate & "# order by t.ReportDate"
end if
if Request.Form("strOwner") = "" and if Request.Form("strOwner") = "" then
Owner = "None"
ReportDate = ""
else
Owner = Request.Form("strOwner")
ReportDate = Request.Form("strReportDate")
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = #" & strReportDate & "# order by t.ReportDate"
end if
Set rs = conn.Execute(SQL1)
%>
opps
Forgot to change the Owner to date
Forgot to change the Owner to date
<%
response.buffer = true
server.scripttimeout = 300
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
strDb = "Data Source=" & Server.MapPath("Master.mdb")
strConn = strProvider & strDb
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn
if Request.Form("strOwner") = "" THEN
Owner = "None"
else
Owner = Request.Form("strOwner")
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' order by t.ReportDate"
end if
if if Request.Form("strReportDate") = "" THEN
ReportDate = ""
else
'Response.Write("it hot here")
'Response.End
ReportDate = Request.Form("strReportDate")
SQL1 = "Select * from tblScorecard t where t.ReportDate = #" & strReportDate & "# order by t.ReportDate"
end if
if Request.Form("strOwner") = "" and if Request.Form("strOwner") = "" then
Owner = "None"
ReportDate = ""
else
Owner = Request.Form("strOwner")
ReportDate = Request.Form("strReportDate")
SQL1 = "Select * from tblScorecard t where t.Owner = '" & Owner & "' and t.ReportDate = #" & strReportDate & "# order by t.ReportDate"
end if
Set rs = conn.Execute(SQL1)
%>
ASKER
Updated the code to what you had listed above and got the following error:
Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/intranet/mis/wfm/NewScore
I think it's because it doesn't like the "AND" function in this piece of code:
if Request.Form("strOwner") = "" and if Request.Form("strOwner") = "" then
Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/intranet/mis/wfm/NewScore
I think it's because it doesn't like the "AND" function in this piece of code:
if Request.Form("strOwner") = "" and if Request.Form("strOwner") = "" then
Change
if Request.Form("strOwner") = "" and if Request.Form("strOwner") = "" then
to
if Request.Form("strOwner") = "" and Request.Form("strReportDat
Sorry for that
Removed the IF
and change to 2nd one to strReportDate
if Request.Form("strOwner") = "" and if Request.Form("strOwner") = "" then
to
if Request.Form("strOwner") = "" and Request.Form("strReportDat
Sorry for that
Removed the IF
and change to 2nd one to strReportDate
ASKER
Made the change but now were back to
Error Type:
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 't.Owner = 'Adrienne Peters' and t.ReportDate = ##'.
/intranet/mis/wfm/NewScore
Error Type:
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 't.Owner = 'Adrienne Peters' and t.ReportDate = ##'.
/intranet/mis/wfm/NewScore
I am leaving out for the rest of the evening. Going to a Music Festival with my son.
Will check back with you on this when I get back in.
(If you are still experiancing issues with this, send me over a sample of your database.
And I will test against it once I get back later on this evening)
Have a good one
Carrzkiss
Will check back with you on this when I get back in.
(If you are still experiancing issues with this, send me over a sample of your database.
And I will test against it once I get back later on this evening)
Have a good one
Carrzkiss
ASKER
Thank you soo much for all your help! You've definitely helped me! Appreciate it!
ASKER
Still can't seem to get the code to recognize the "reportdate" variable. I've attached the access db.....what am I doing wrong?
Master.mdb
Master.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YOU ARE AWESOME! Hope the concert was great & you had a great weekend too. Thanks again for all your expertise.
ASKER
Thank you! You are awesome! I'm learning a great deal about ASP & you've been very helpful!
You are very welcome.
And the concert was great. One of my Son's favorite bands was there.
And they recognized him, and came out and gave him a shirt and some band stickers.
And took a picture with him. All pretty cool.
Contagin = www.myspace.com/contagin
They are a Hardcore band, great group of guys.
---------------
Right now I am learning SQL Server, as I had to cross over from Access database to SQL Server
As the site that I made that was suppose to be released last week, could not be
As the Access Database would not handle all the members.
So, I had to cross over to SQL Server for a more robust backend.
And so far the transition over to SQL Server is going well.
I am "hoping" that I can release the site by this Wednesday.
As long as nothing happens.
I am back in the learners seat.
As I have been asking a lot of questions on how to do stuff in SQL Server.
So. This is strange for me.
------------
Have a good one BC
Hope your site does well for you.
Carrzkiss
And the concert was great. One of my Son's favorite bands was there.
And they recognized him, and came out and gave him a shirt and some band stickers.
And took a picture with him. All pretty cool.
Contagin = www.myspace.com/contagin
They are a Hardcore band, great group of guys.
---------------
Right now I am learning SQL Server, as I had to cross over from Access database to SQL Server
As the site that I made that was suppose to be released last week, could not be
As the Access Database would not handle all the members.
So, I had to cross over to SQL Server for a more robust backend.
And so far the transition over to SQL Server is going well.
I am "hoping" that I can release the site by this Wednesday.
As long as nothing happens.
I am back in the learners seat.
As I have been asking a lot of questions on how to do stuff in SQL Server.
So. This is strange for me.
------------
Have a good one BC
Hope your site does well for you.
Carrzkiss
'" & ReportDate & "'
to
#" & ReportDate & "#
See if that helps you.
(Dates in Access have to have the Pound Sign around them)
Carrzkiss