Home > ASP Development > Microsoft JET Database Engine error '80040e07' Help please?

Microsoft JET Database Engine error '80040e07' Help please?



Hi



I got this error :



Microsoft JET Database Engine error '80040e07'



Data type mismatch in criteria expression.

AddRequestForm.asp, line 67



from the code below - i can't figure out what it could be.



this is my code :


Code:




<html>



<head>

<meta http-equiv="Content-Type" content="text/html; charset=windows-1256">

<title>Mail Reference NO</title>

</head>



<body>



<p><img border="0" src="pic/Reference.jpg" width="580" height="100"></p>



<% 

	

		Dim cnnSearch

		Dim rs1

		Dim strSQL

		Dim ID

		Dim serial

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

	  cnnSearch.Mode = 3

Dept=request("Dept")





x=request("B1")	



cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

       "Data Source=E:\Environment Intranet\LettersDB\Letters2005.mdb;" & _

        "Jet OLEDB:Database Password=no999em"

 



if(x="Submit")then

		Min=request("Ministry")

		Initial=request("Initials")

		Date1=request("Date1")

		To1=request("To1")

		SendTo=request("SendTo")

		Subject=request("Subject")

		Ref=request("Ref")

		MailedBy=request("MailedBy")

		Coments=request("Coments")

		arabic=request("arabic")

		

		if Min="0" then

				%>Select the ministry <%

		elseif Initial ="" then

				%>Please Type your Initials <%

		else

    

		var_date=date() 'get the current date 

		var_date=FormatDateTime(var_date,2) 

		

		today_y = DatePart("yyyy", Now())

    year1 = Right(today_y, 2)

    

		dim dd

		

		if Dept=0 then 

			 dd="DGA"

		elseif Dept=1 then 

			 dd="DGE"

		elseif Dept=2 then 

				dd="DEAP"

		elseif Dept=3 then 

				dd="DEC"

		elseif Dept=4 then 

				dd="AAF"

		elseif Dept=5 then 

				dd="PREI"

		end if

    

    

    Set rs1 = cnnSearch.Execute("select * from deptserial where dept='" & dd & "'")

    

		serial =rs1.Fields("Serial").Value

    serial = serial + 1

  

   

    if arabic="a" then

			 If Dept=3 Then

       		department = "رب"

    	 ElseIf Dept=2 Then

       				department = "ت ب"

			 ElseIf Dept=5 Then

       				department = "ع ع"

    	 Else

       		 department = "ش ب"

    	 End If

    	 'refNo = department

			 'refNo =refNo & "/" & min & "/" & year1 & "/" & serial & "/" 

			 'refNo =refNo & "" & Initial

			 

			 'refNo = department

			 'refNo =refNo & "/" & min 

			 'refNo =refNo & "/" & year1 

			 'refNo =refNo & "/" & serial & "/" 

			 'refNo =refNo & "" & Initial

			 

       refNo = department

			 refNo =refNo & "/" & serial & "/" & year1 & "/" & min & "/" 

			 refNo =refNo & "" & Initial

		elseif arabic="e" then

		

			 If Dept=3 Then

       		 department = "EC"

    	 ElseIf Dept=2 Then

       		 department = "EAP"

			 ElseIf Dept=5 Then

       		 department = "PR"

    	 Else

       		 department = "EA"

    	 End If

			 refNo = department

			 refNo =refNo & "/" & min & "/" & year1 & "/" & serial & "/" 

			 refNo =refNo & "" & Initial

    end if

     

                

    

      

 

		

		'Min=request("Ministry")

		'Initial=request("Initials")

		'Date1=request("Date1")

		'To1=request("To1")

		'SendTo=request("Send To")

		'Subject=request("Subject")

		'Ref=request("Ref")

		'MailedBy=request("MailedBy")

		'Coments=request("Coments")

		



       strSQL = "INSERT INTO [Letter Info](Dept, Ministry, Initials, [Date], [To], SendTo, Subject, ReferenceNo, Comments, Reference) Values('" & dd & "' , '" & min & "' , '" & Initial & "' , '" & var_date & "' , '" & To1 & "' , '" & SendTo & "' , '" & Subject & "' , '" & refNo & "' , '" & Coments & "' , '" & Ref & "')"

		cnnSearch.execute(strSQL)

		

		cnnSearch.execute("UPDATE deptserial SET deptserial.Serial = " & serial & " where dept='" & dd & "'")

    %> <p align="center"> <br> <%

		'response.write "  Reference Number is: " & department & refNo

		

    'if arabic="a" then 

       	' response.write "  Reference Number is: " & department & "/" & serial & "/" & year1 & "/" & min & "/"& "" & Initial

		'elseif arabic="e" then 

			  'response.write "  Reference Number is: " & department & "/" & min & "/" & year1 & "/" & serial & "/"& "" & Initial

		'end if

		

 response.write "  Reference Number is: " & department & "/" & min & "/" & year1 & "/" & serial & "/"& "" & Initial

    

		%>

		

    </p>

		<%

		end if



else



    dim var_date

		var_date=date() 'get the current date 

		var_date=FormatDateTime(var_date,2) 

		

	





		 %>

<form name="form1" method="get" action="Reference.asp" webbot-action="--WEBBOT-SELF--">

  <table border="0" width="100%" height="249">



    <tr>

      <td width="14%" height="23"><font size="3" color="#000080"><b>Department&nbsp;</b></font></td>

      <td width="86%" height="23">

			<select size="1" name="Dept" onchange="document.form1.submit()">

          <option value="0" <%  if (Dept=0)then%> selected <% end if %>  >DGA </option>

          <option value="1" <%  if (Dept=1)then%> selected <% end if %>  >DGE</option>

          <option value="2" <%  if (Dept=2)then%> selected <% end if %>  >DEAP</option>

          <option value="3" <%  if (Dept=3)then%> selected <% end if %>  >DEC</option>

          <option value="4" <%  if (Dept=4)then%> selected <% end if %>  >AAF</option>

          <option value="5" <%  if (Dept=5)then%> selected <% end if %>  >PREI</option>

         </select></td>

    </tr>

    <tr>

      <td width="14%" height="23"><font size="3" color="#000080"><b>File No&nbsp;</b></font></td>

      <td width="86%" height="23">

			<select size="1" name="Ministry">

       

		  <option value="0" selected>---Select---</option>

			<%  

			if(Dept=4) then

			   strSQL = "select * from [AAF Files]"

			elseif(Dept=5) then

			   strSQL = "select * from [PREIfiles]"

			else

				 strSQL = "select * from ministry"

			end if

          Set rstSearch = cnnSearch.Execute(strSQL) 

					while not rstSearch.eof

					%>

			      <option value=<%= rstSearch.Fields("FileNo").Value %>><%= rstSearch.Fields("ministry").Value & "  " & rstSearch.Fields("MinistryE").Value & "  " & rstSearch.Fields("FileNo").Value %></option>

		

			<% 

			rstSearch.movenext

			wend %>

				</select>

				</td>

    </tr>

    <tr>

      <td width="14%" height="23"><font size="3" color="#000080"><b>Initials</b></font></td>

      <td width="86%" height="23"><input type="text" name="Initials" size="60"> 

			<font size="1">Type initials in Arabic for Arabic Reference, in English for English Reference</font></td>

    </tr>

    <tr>

      <td width="14%" height="23"><font size="3" color="#000080"><b>Date</b></font></td>

      <td width="86%" height="23"><input type="text" name="Date1" size="40" value=<%=var_date%>></td>

    </tr>

    <tr>

      <td width="14%" height="23"><font size="3" color="#000080"><b>To</b></font></td>

      <td width="86%" height="23"><input type="text" name="To1" size="60">

        <font size="1">(Place)</font></td>

    </tr>

    <tr>

      <td width="14%" height="23"><font size="3" color="#000080"><b>Send To</b></font></td>

      <td width="86%" height="23"><input type="text" name="SendTo" size="60">

        <font size="1">(Person)</font></td>

    </tr>

   



    <tr>

      <td width="14%" height="19"><font size="3" color="#000080"><b>Subject</b></font></td>

      <td width="86%" height="19"><input type="text" name="Subject" size="60"></td>

    </tr>

    <tr>

      <td width="14%" height="19"><font size="3" color="#000080"><b>Reference&nbsp;</b></font></td>

      <td width="86%" height="19"><input type="text" name="Ref" size="60">

        <font size="1">(Letters refer to this Letter)(Optional)</font></td>

    </tr>

    <tr>

      <td width="14%" height="19"><font size="3" color="#000080"><b>Mailed By</b></font></td>

      <td width="86%" height="19"><input type="text" name="MailedBy" size="60">

        <font size="1">(Optional)</font></td>

    </tr>

    <tr>

      <td width="14%" height="19"><font size="3" color="#000080"><b>Comments</b></font></td>

      <td width="86%" height="19"><textarea rows="2" name="Coments" cols="60"></textarea>

        <font size="2"> </font><font size="1">(Optional)</font></td>

    </tr>

    <tr>

      <td width="14%" height="19"></td>

      <td width="86%" height="19"><b><font size="3" color="#000080">Arabic</font></b>

        <input type="radio" name="arabic" checked value="a">&nbsp;&nbsp;&nbsp;&nbsp; <b><font size="3" color="#000080">English</font></b> 

        <input type="radio" value="e"  name="arabic"></td>

    </tr>

  </table>

  <p align="center">

  <input type="submit" value="Submit" name="B1"></p>

</form>

<% end if %>

<p align="center">

	  <a href=Home.htm >Home</a>

    </p>

</body>



</html>






the line in bold is where i get the error any help please?



the data types are:

dept--------- Text

Ministry-----text

Initials----------text

Date---------Date/Time

To--------Memo

SendTo------Memo

Subject------Memo

ReferenceNo-----Text

Comments-------Memo

Reference-------Text

    
Guest
Best Answer


try this.
strSQL = "INSERT INTO [Letter Info](Dept, Ministry, Initials, [Date], [To], SendTo, Subject, ReferenceNo, Comments, Reference) Values('" & dd & "' , '" & min & "' , '" & Initial & "' , #" & var_date & "# , '" & To1 & "' , '" & SendTo & "' , '" & Subject & "' , '" & refNo & "' , '" & Coments & "' , '" & Ref & "')"

Was this answer helpful ? Yes No   
Guest


add two bold line in ur code..and post what ur getting

Code:



<% 
	
		Dim cnnSearch
		Dim rs1
		Dim strSQL
		Dim ID
		Dim serial
		Set cnnSearch = Server.CreateObject("ADODB.Connection")
	  cnnSearch.Mode = 3
Dept=request("Dept")


x=request("B1")	

cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	   "Data Source=E:\Environment Intranet\LettersDB\Letters2005.mdb;" & _
		"Jet OLEDB:Database Password=no999em"
 

if(x="Submit")then
		Min=request("Ministry")
		Initial=request("Initials")
		Date1=request("Date1")
		To1=request("To1")
		SendTo=request("SendTo")
		Subject=request("Subject")
		Ref=request("Ref")
		MailedBy=request("MailedBy")
		Coments=request("Coments")
		arabic=request("arabic")
		
		if Min="0" then
				%>Select the ministry <%
		elseif Initial ="" then
				%>Please Type your Initials <%
		else
	
		var_date=date() 'get the current date 
		var_date=FormatDateTime(var_date,2) 
		
		today_y = DatePart("yyyy", Now())
	year1 = Right(today_y, 2)
	
		dim dd
		
		if Dept=0 then 
			 dd="DGA"
		elseif Dept=1 then 
			 dd="DGE"
		elseif Dept=2 then 
				dd="DEAP"
		elseif Dept=3 then 
				dd="DEC"
		elseif Dept=4 then 
				dd="AAF"
		elseif Dept=5 then 
				dd="PREI"
		end if
	
	
	Set rs1 = cnnSearch.Execute("select * from deptserial where dept='" & dd & "'")
	
		serial =rs1.Fields("Serial").Value
	serial = serial + 1
  
   
	if arabic="a" then
			 If Dept=3 Then
	   		department = "رب"
		 ElseIf Dept=2 Then
	   				department = "ت ب"
			 ElseIf Dept=5 Then
	   				department = "ع ع"
		 Else
	   		 department = "ش ب"
		 End If
		 'refNo = department
			 'refNo =refNo & "/" & min & "/" & year1 & "/" & serial & "/" 
			 'refNo =refNo & "" & Initial
			 
			 'refNo = department
			 'refNo =refNo & "/" & min 
			 'refNo =refNo & "/" & year1 
			 'refNo =refNo & "/" & serial & "/" 
			 'refNo =refNo & "" & Initial
			 
	   refNo = department
			 refNo =refNo & "/" & serial & "/" & year1 & "/" & min & "/" 
			 refNo =refNo & "" & Initial
		elseif arabic="e" then
		
			 If Dept=3 Then
	   		 department = "EC"
		 ElseIf Dept=2 Then
	   		 department = "EAP"
			 ElseIf Dept=5 Then
	   		 department = "PR"
		 Else
	   		 department = "EA"
		 End If
			 refNo = department
			 refNo =refNo & "/" & min & "/" & year1 & "/" & serial & "/" 
			 refNo =refNo & "" & Initial
	end if
	 
				
	
	  
 
		
		'Min=request("Ministry")
		'Initial=request("Initials")
		'Date1=request("Date1")
		'To1=request("To1")
		'SendTo=request("Send To")
		'Subject=request("Subject")
		'Ref=request("Ref")
		'MailedBy=request("MailedBy")
		'Coments=request("Coments")
		

	   strSQL = "INSERT INTO [Letter Info](Dept, Ministry, Initials, [Date], [To], SendTo, Subject, ReferenceNo, Comments, Reference) Values('" & dd & "' , '" & min & "' , '" & Initial & "' , '" & var_date & "' , '" & To1 & "' , '" & SendTo & "' , '" & Subject & "' , '" & refNo & "' , '" & Coments & "' , '" & Ref & "')"

Response.write(strsql)
Response.end
 	cnnSearch.execute(strSQL)
		
		cnnSearch.execute("UPDATE deptserial SET deptserial.Serial = " & serial & " where dept='" & dd & "'")
	%> <p align="center"> <br> <%
		'response.write "  Reference Number is: " & department & refNo
		
	'if arabic="a" then 
	   	' response.write "  Reference Number is: " & department & "/" & serial & "/" & year1 & "/" & min & "/"& "" & Initial
		'elseif arabic="e" then 
			  'response.write "  Reference Number is: " & department & "/" & min & "/" & year1 & "/" & serial & "/"& "" & Initial
		'end if
		
 response.write "  Reference Number is: " & department & "/" & min & "/" & year1 & "/" & serial & "/"& "" & Initial
	
		%>
		
	</p>
		<%
		end if

else

	dim var_date
		var_date=date() 'get the current date 
		var_date=FormatDateTime(var_date,2) 
		
	


		 %>
<form name="form1" method="get" action="Reference.asp" webbot-action="--WEBBOT-SELF--">
  <table border="0" width="100%" height="249">

	<tr>
	  <td width="14%" height="23"><font size="3" color="#000080"><b>Department&nbsp;</b></font></td>
	  <td width="86%" height="23">
			<select size="1" name="Dept" onchange="document.form1.submit()">
		  <option value="0" <%  if (Dept=0)then%> selected <% end if %>  >DGA </option>
		  <option value="1" <%  if (Dept=1)then%> selected <% end if %>  >DGE</option>
		  <option value="2" <%  if (Dept=2)then%> selected <% end if %>  >DEAP</option>
		  <option value="3" <%  if (Dept=3)then%> selected <% end if %>  >DEC</option>
		  <option value="4" <%  if (Dept=4)then%> selected <% end if %>  >AAF</option>
		  <option value="5" <%  if (Dept=5)then%> selected <% end if %>  >PREI</option>
		 </select></td>
	</tr>
	<tr>
	  <td width="14%" height="23"><font size="3" color="#000080"><b>File No&nbsp;</b></font></td>
	  <td width="86%" height="23">
			<select size="1" name="Ministry">
	   
		  <option value="0" selected>---Select---</option>
			<%  
			if(Dept=4) then
			   strSQL = "select * from [AAF Files]"
			elseif(Dept=5) then
			   strSQL = "select * from [PREIfiles]"
			else
				 strSQL = "select * from ministry"
			end if
		  Set rstSearch = cnnSearch.Execute(strSQL) 
					while not rstSearch.eof
					%>
				  <option value=<%= rstSearch.Fields("FileNo").Value %>><%= rstSearch.Fields("ministry").Value & "  " & rstSearch.Fields("MinistryE").Value & "  " & rstSearch.Fields("FileNo").Value %></option>
		
			<% 
			rstSearch.movenext
			wend %>
				</select>
				</td>
	</tr>
	<tr>
	  <td width="14%" height="23"><font size="3" color="#000080"><b>Initials</b></font></td>
	  <td width="86%" height="23"><input type="text" name="Initials" size="60"> 
			<font size="1">Type initials in Arabic for Arabic Reference, in English for English Reference</font></td>
	</tr>
	<tr>
	  <td width="14%" height="23"><font size="3" color="#000080"><b>Date</b></font></td>
	  <td width="86%" height="23"><input type="text" name="Date1" size="40" value=<%=var_date%>></td>
	</tr>
	<tr>
	  <td width="14%" height="23"><font size="3" color="#000080"><b>To</b></font></td>
	  <td width="86%" height="23"><input type="text" name="To1" size="60">
		<font size="1">(Place)</font></td>
	</tr>
	<tr>
	  <td width="14%" height="23"><font size="3" color="#000080"><b>Send To</b></font></td>
	  <td width="86%" height="23"><input type="text" name="SendTo" size="60">
		<font size="1">(Person)</font></td>
	</tr>
   

	<tr>
	  <td width="14%" height="19"><font size="3" color="#000080"><b>Subject</b></font></td>
	  <td width="86%" height="19"><input type="text" name="Subject" size="60"></td>
	</tr>
	<tr>
	  <td width="14%" height="19"><font size="3" color="#000080"><b>Reference&nbsp;</b></font></td>
	  <td width="86%" height="19"><input type="text" name="Ref" size="60">
		<font size="1">(Letters refer to this Letter)(Optional)</font></td>
	</tr>
	<tr>
	  <td width="14%" height="19"><font size="3" color="#000080"><b>Mailed By</b></font></td>
	  <td width="86%" height="19"><input type="text" name="MailedBy" size="60">
		<font size="1">(Optional)</font></td>
	</tr>
	<tr>
	  <td width="14%" height="19"><font size="3" color="#000080"><b>Comments</b></font></td>
	  <td width="86%" height="19"><textarea rows="2" name="Coments" cols="60"></textarea>
		<font size="2"> </font><font size="1">(Optional)</font></td>
	</tr>
	<tr>
	  <td width="14%" height="19"></td>
	  <td width="86%" height="19"><b><font size="3" color="#000080">Arabic</font></b>
		<input type="radio" name="arabic" checked value="a">&nbsp;&nbsp;&nbsp;&nbsp; <b><font size="3" color="#000080">English</font></b> 
		<input type="radio" value="e"  name="arabic"></td>
	</tr>
  </table>
  <p align="center">
  <input type="submit" value="Submit" name="B1"></p>
</form>
<% end if %>
<p align="center">
	  <a href=Home.htm >Home</a>
	</p>
</body>

</html>



Was this answer helpful ? Yes No   
Guest


i get this when i click on the button sumbit after i insetred what you told me to do :

INSERT INTO [Letter Info](Dept, Ministry, Initials, [Date], [To], SendTo, Subject, ReferenceNo, Comments, Reference) Values('PREI' , '01' , 'k l','‏10/‏10/‏2005' , '' , '' , '' , 'ع ع/452/05/01/k l' , '' , '')

when i click on the button submit i should be getting only this :
'ع ع/452/05/01
which is the reference number
?

Was this answer helpful ? Yes No   
Guest


check to,subject,sendto are getting values or not

Was this answer helpful ? Yes No   
Guest


Quote:
Originally Posted by nawaray
i get this when i click on the button sumbit after i insetred what you told me to do :



INSERT INTO [Letter Info](Dept, Ministry, Initials, [Date], [To], SendTo, Subject, ReferenceNo, Comments, Reference) Values('PREI' , '01' , 'k l','‏10/‏10/‏2005' , '' , '' , '' , 'ع ع/452/05/01/k l' , '' , '')



when i click on the button submit i should be getting only this :

'ع ع/452/05/01

which is the reference number

?






the response.end halts the program so what you see is the executed code up until the response.end...anything afterwards will NOT be executed...includiing the reference number....first thing is first....the mismatch error....make sure the datatype in the database MATCH the syntax in query. for example IF you set the DATE(by the way...stray away from "key" words...it's great that yoiu put brackets around; however, not a good practice...try using something more meaningful so when yoiu debug the code it's easier to pick out...like ReferenceDate) as date in the ACCESS database then needs to be surrounded by # NOT '....try that first if the case

Was this answer helpful ? Yes No   
Guest


as you can see in my above code that i have declared the date as var_date

var_date=date() 'get the current date

var_date=FormatDateTime(var_date,2)


and im sure the the datatype matche sthe syntax in my query ...

so what do you recommand me to change in my code ?
sorry for asking alot

Was this answer helpful ? Yes No   
Guest


Quote:
Originally Posted by nawaray
as you can see in my above code that i have declared the date as var_date



var_date=date() 'get the current date



var_date=FormatDateTime(var_date,2)





and im sure the the datatype matche sthe syntax in my query ...



so what do you recommand me to change in my code ?

sorry for asking alot






that has nada, zip, zilch, nothing to do with the sql string

btw...not asking a lot: see Guddu's example



also, i was referring to the database Date field not the var...good luck

Was this answer helpful ? Yes No   
Guest


i tried this before and i get

Microsoft JET Database Engine error '80040e07'



Syntax error in date in query expression '#?0/?0/?005#'.

Was this answer helpful ? Yes No   
Guest


i'm still getting this error :

Microsoft JET Database Engine error '80040e07'



Syntax error in date in query expression '#?0/?0/?005#'.

Was this answer helpful ? Yes No   
Guest
1    [2]    [3]    Next    Last
Answer it
Ask Question About ASP Development
 
 
Home - About Infoqu - Contact - Privacy Statement - Link to Infoqu - Bookmark Infoqu

Copyright 2007-2008 by Infoqu. All rights reserved