[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

Why am i getting the data type mismatch error?

When I load this asp page I get the error:
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/energysysvc/testing/myincidents_view.asp, line 51

I do the reponse.write of the sql and it looks like:
SELECT * FROM tbl4Key WHERE ID="238";
looks right to me, not sure why th error.
<%@ Language=VBScript %>

<!--#include file="../includes/adovbs.inc"-->


<%
	Session("business") = Request("business")
		Session("Category") = Request("Category")
		Session("Year") = Request("Year")
		


dim Rs
Dim SQL
dim RecID

		
		'Response.Write "servername = " & servername
	
	UID = Request.ServerVariables("LOGON_USER")
	Uname = ucase(right(UID,7))	
	RecID = ID
	response.write Uname 
	response.Write UID
	response.Write ID
%>
	<br>
	<%	

		
		
			vXlsFile = "\virtual\documents\Energysysvc\EHS4KeyList.xls"
		

		vXlsFile = Server.Mappath(vXlsFile)

		ExcelConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
		"Data Source=" & chr(34) & vXlsFile & chr(34) & ";" & _
		"Extended Properties=" & chr(34) & "Excel 8.0;"& chr(34)

		'Open Database Connection
		
		Set Conn = Server.CreateObject("ADODB.Connection") 
                Conn.Open ExcelConnString 
		
'set Rs=server.createobject("ADODB.recordset")
				
sql = "SELECT * FROM tbl4Key WHERE ID=" & Request("ID") &";"
response.Write	SQL
set RS = server.createobject("adodb.recordset")
RS.open SQL,Conn,adOpenForwardOnly,adLockReadOnly
%>
<html>

<head>
<meta name="HTML_Expiration_Date" content="">
<meta name="HTML_Reason_Document_Frozen" content>
<meta name="HTML_Retention_Period_Trigger" content="General Business Record">
<meta name="HTML_Retention_Period_Start_Date" content>
<meta name="HTML_Retention_Period" content>
<meta name="HTML_Proprietary_Classification" content="RESTRICTED - For internal use only">
<meta name="HTML_Content_Steward" content="">
<meta name="Author" content="">
<link REL="stylesheet" HREF="common/dow/includes/default.css" TYPE="text/css">
<link REL="stylesheet" HREF="common/dow/includes/hdr_n_nav.css" TYPE="text/css">

		<title>View Incident Report</title>
		<meta name="Incident Report" content="View HC&amp;E Incident Report">
				<meta name="Keywords" content="Incident Report, Event Report, HC&amp;E Event ">
		</head>

<body onload="SetMenu()">

<table width="100%" cellspacing="0" cellpadding="0">
  <tr>
    <td name="HeaderCell" id="HeaderCell"><a name="Top"></a><!--webbot bot="HTMLMarkup" ALT="&lt;h4&gt;The new Header is in place ...&lt;/h4&gt;" TAG="XBOT" StartSpan -->
<IFRAME scrolling="no" width="100%" height="94" frameborder="0" marginheight="0" src="common/dow/dowheader.htm"></IFRAME><!--webbot BOT="HTMLMarkup" endspan -->
</td>
  </tr>
  <tr>
    <td><div align="left"><table border="0" cellpadding="0" cellspacing="0" width="100%">
      <tr>
        <td width="81%">
        <!--webbot bot="Include" U-include="includes/incident_header.htm" TAG="BODY" startspan --><strong>[testing/includes/incident_header.htm]</strong><!--webbot bot="Include" i-checksum="22018" endspan --></td>
        <td name="RightMarginCell" id="RightMarginCell" rowspan="2"><img height="1" hspace="60" src="common/images/rightspacer.gif" width="78"><p><!--webbot bot="PurpleText" PREVIEW="do not modify." --></td>
      </tr>
      <tr>
        <td><div align="left"><table name="ContentTable" id="ContentTable" border="0" cellpadding="0" cellspacing="0" width="100%">
          <tr>
            <td name="MenuCell" id="MenuCell" valign="top" bgcolor="#dddddd">
            <!--webbot bot="Include" U-include="includes/incident_menu.htm" TAG="BODY" startspan --><strong>[testing/includes/incident_menu.htm]</strong><!--webbot bot="Include" i-checksum="18461" endspan --></td>
            <td class="Content" name="ContentCell" id="ContentCell" valign="top" width="80%"><h1>View Incident Report</h1>						<form method="post" action="edit_default.asp">
            				<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
                				<tr>
									<td width="100%">
									<table border="1" cellpadding="2" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber2" cellspacing="0">
                   						<tr>
											<td width="145">Date of Incident</td>
											<td><% = RS.fields(0) %>&nbsp;</td>
										</tr>
										<tr>
											<td width="145">Business</td>
											<td><% = RS.fields(1) %>&nbsp;</td>
										</tr>
										<tr>
											<td width="145">Site</td>
											<td><% = RS.fields(2) %>&nbsp;</td>
											
										</tr>
										<tr>
											<td width="145">Title</td>
											<td><% = RS.fields(3) %>&nbsp;</td>
										</tr>
										<tr>
											<td width="145">Category</td>
											<td><% = RS.fields(4) %>&nbsp;</td>
										</tr>
										<tr>
											<td width="245">Document Location</td>
											<td><% = RS.fields(5) %>&nbsp;</td>
										</tr>
										<tr>
											<td width="145">Make It Personal</td>
											<td><% = RS.fields(7) %>&nbsp;</td>
										</tr>
										<tr>
											<td width="145">Ask Open ended Questions</td>
											<td><% = RS.fields(8) %>&nbsp;</td>
										</tr>
											<td width="145">Create a Culture</td>
											<td><% = RS.fields(9) %>&nbsp;</td>
											</tr>
											<td width="145">Escalation</td>
											<td><% = RS.fields(10) %>&nbsp;</td>
										</tr>
											
											</table>
									<table border="1" cellpadding="2" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="Options">
									   <tr>
										</td></tr></table>
									     </td>
								       </tr>
								<tr>
									<td width="100%">&nbsp;</td>
								</tr>
							</table>
							<input type="hidden" name="webref" value="<% = request.querystring("webref") %>">
							<input type="hidden" name="apprvr" value="<% =request.queryString("reqstr") %>">
						</form>
						</td>
          </tr>
          <tr>
            <td name="FooterCell" id="FooterCell" valign="top" bgcolor="#dddddd"></td>
            <td name="Footer" id="Footer">
            <!--webbot bot="Include" U-include="includes/footer_rh.htm" TAG="BODY" startspan --><strong>[testing/includes/footer_rh.htm]</strong><!--webbot bot="Include" i-checksum="24410" endspan --></td>
          </tr>
        </table>
        </div></td>
      </tr>
      <tr>
        <td name="SpacerCell" id="SpacerCell"><img height="1" src="common/images/topspacer.gif" width="640"></td>
      </tr>
    </table>
    </div></td>
  </tr>
</table>
</body>
</html>

Open in new window

0
jlcannon
Asked:
jlcannon
1 Solution
 
hieloCommented:
ID is most likely a number data type. Get rid of the quotation marks:
SELECT * FROM tbl4Key WHERE ID=238;
0
 
MorcalavinCommented:
sql = "SELECT * FROM tbl4Key WHERE ID=" & Request("ID") &";"
change to
sql = "SELECT * FROM tbl4Key WHERE ID=" & CInt(Request("ID")) &";"


0
 
Wayne BarronCommented:
To go with what everyone else has written
Try this.

' Assuming you are getting the value from a QueryString, it is always best to let it know where it is coming from instead of making your SQL Statement Quess at it.

getID = int(request.querystring("ID"))
'if it is from a form then
getID = int(request.form("ID"))
' Use one of the above but not both...
sql = "SELECT * FROM tbl4Key WHERE ID=getID"

Good Luck
Carrzkiss
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jlcannonAuthor Commented:
while the others may have worked with some tweeking this one worked staight off. thanks to all who posted and much thanks for the quick solution.
0
 
govindarajan78Commented:
try

sql = "SELECT * FROM tbl4Key WHERE ID='" & Request("ID") &"'"

hope
tbl4Key  is a table name (not sheet) defined in the excel
0
 
jlcannonAuthor Commented:
it is the name of a named region in excel. Thanks for the help but points assigned already.
0
 
Wayne BarronCommented:
>> it is the name of a named region in excel. Thanks for the help but points assigned already.

If that is the case, then why did you assign points for information that is not relavent to the issue?
That is a waste of resources here.
It would have been best for you to come in and explain what the issue was, and then except yourself as Answer, instead of eccepting what is considered to be a mis-leading EE Information.

Carrzkiss
0
 
jlcannonAuthor Commented:
i do not understand your message. Whether excel or access once it looks to tbl4Key it does not matter. the fact is I tried your solution and it gave me an error and then I tried morcalavin's solution and it worked without a hitch so I accepted his solution
of

Morcalavin:sql = "SELECT * FROM tbl4Key WHERE ID=" & Request("ID") &";"
change to
sql = "SELECT * FROM tbl4Key WHERE ID=" & CInt(Request("ID")) &";"


so how are you saying I am wasting anyones time or resources.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now