Type mismatch error (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

rrnml -> Type mismatch error (5/16/2005 15:52:37)

Not sure what a type mismatch error is. And I'm not sure I've gone about this query the right way. I have a query where you can search by either a keyword or by a user. I'm not sure I have it right. Well obviously it's not right if I'm getting an error! Anyway if anyone can see what I've done wrong please let me know. Here's the code...... Thanks. 

' Create the SQL Query String
strParm1 = Request.Form("fdate")
strParm2 = Request.Form("tdate")
strSearchterm = Request.Form("searchterm")
strUser = Request.Form("scname")
Response.Write strParm1
Response.Write strParm2
Response.Write strUser

If strSearchterm <> "" then
 strSQLWhere = " (DestHost like '%" & strSearchterm & "')  "
Else
If strUser <> "" then
   strSQLWhere = " (ClientUserName like '%" & strUser & "')  "
 Else
   strSQLWhere = " "
 End If
End If
strSQL = " select DISTINCT DestHost " &_
  " from WebProxyLog " & _
  " where (DestHost like '%" & strSearchterm & "%') " & _ 
  " and (logDate between '" & strParm1 & "' and '" & strParm2 & "') " 
   strSQLWhere

strSQL2 = " select ClientUserName " &_
   " from WebProxyLog " & _
   " where (ClientUserName like '%" & strUser & "%') " & _
   " and (logDate between '" & strParm1 & "' and '" & strParm2 & "') " 
      
Response.write strSQL
Response.write strSQL2
server.ScriptTimeOut = 600
rst.Open strSQL, con, adOpenKeyset, adLockOptimistic
rst2.Open strSQL2, con, adOpenKeyset, adLockOptimistic
' Only display records if there are records in the recordset
If NOT rst.EOF then
 'Display the header
%>
<div align="center">
<p align="center"><b>Internet Destinations </b> </p>
<p align="center"><b>from <%=Request.Form("fdate")%></b></p>
<p align="center"><b>thru <%=Request.Form("tdate")%></b> </p>
<div align="center"><center>
<table BORDER="1" height="57" width="501">
 <tr>
   <td align="center" height="17" width="491"><b>Destinations</b></td>
 </tr>
<%
'Loop through the recordset until there are no more records
Do While Not rst.EOF
%>
<tr>
 <td align="center" height="29" width="491"><a href="InternetDestinations4.asp"><%=rst("DestHost")%>
 </a></td>

</tr>    
<% 

'If you forget this next statement, you will be stuck in a loop
rst.MoveNext
Loop
 
Else
%>

<b>
 <tr>
   <td align="center" height="23" width="491">No records in recordset!&nbsp; </b>
<%

%>
</td>
   
 </b>
   
<p></p>
   
<p></p>

   
<%
End If

rst.Close
'rst2.Close
con.Close
%></html>



Spooky -> RE: Type mismatch error (5/16/2005 15:56:29)

Which line?



rrnml -> RE: Type mismatch error (5/16/2005 15:59:32)

It says line 70.



Spooky -> RE: Type mismatch error (5/16/2005 16:00:22)

That doesnt look like the whole script - so which is line 70?



rrnml -> RE: Type mismatch error (5/16/2005 16:07:32)

Looks like it's an empty line. I'll give you the rest of the code that I didn't include. 


<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>
<head>
<title>Internet Destinations</title>

<meta name="Microsoft Border" content="tb">
</head>
<body>
<%
Dim con 
Dim rst
Dim rst2
Dim strSQL
Dim strSQL2
Dim strParm1
Dim strParm2
Dim strSearchterm
Dim strUser
Dim strSQLWhere
Const adOpenKeyset = 0
Const adLockOptimistic = 1
'Create the objects 
Set con = Server.CreateObject("ADODB.Connection") 
Set rst = Server.CreateObject("ADODB.Recordset") 
Set rst2 = Server.CreateObject("ADODB.Recordset") 
' Open the connection to NMLFIRE 
con.Open "ProxyDB", "sqlquery", "querysql"
con.CommandTimeout = 0



Spooky -> RE: Type mismatch error (5/16/2005 16:12:55)

Do you have a URL? and on a different subject, why are you opening rst2?



rrnml -> RE: Type mismatch error (5/16/2005 16:15:55)

Well I'm on an intranet. As for the rst2, that was supposed to be commented out. Sorry about that. I can show you the form as well if you like.



Spooky -> RE: Type mismatch error (5/16/2005 16:17:33)

Can you copy and paste the exact error you see in your browser?



rrnml -> RE: Type mismatch error (5/16/2005 16:18:53)




2005051620050516adriana
Microsoft VBScript runtime error '800a000d'
Type mismatch
/Queries/InternetDestinations3.asp, line 70



Spooky -> RE: Type mismatch error (5/16/2005 16:22:20)

so by my count its this line? There arent any included files?

<p align="center"><b>from <%=Request.Form("fdate")%></b></p>



rrnml -> RE: Type mismatch error (5/16/2005 16:23:07)

No.



Spooky -> RE: Type mismatch error (5/16/2005 16:23:17)

If rst2 isnt going to be used, do you want to remove the associated code and try again?



rrnml -> RE: Type mismatch error (5/16/2005 16:23:49)

Ok. I'll do that.



rrnml -> RE: Type mismatch error (5/16/2005 16:29:00)

Ok I tried that and it said line 67 type mismatch this time. Then I took out the strSQLWhere and it ran. What I was trying to say was if the the search box is blank then run the code to find the keyword else do the other sql statement which is to search by user.  Obviously, the way I wrote it was wrong. Got any ideas?



Spooky -> RE: Type mismatch error (5/16/2005 16:33:49)

Post the full existing code?



rrnml -> RE: Type mismatch error (5/16/2005 16:35:22) 

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>
<head>
<title>Internet Destinations</title>

<meta name="Microsoft Border" content="tb">
</head>
<body>
<%
Dim con 
Dim rst
Dim strSQL
Dim strParm1
Dim strParm2
Dim strSearchterm
Dim strUser
Dim strSQLWhere
Const adOpenKeyset = 0
Const adLockOptimistic = 1
'Create the objects 
Set con = Server.CreateObject("ADODB.Connection") 
Set rst = Server.CreateObject("ADODB.Recordset") 
' Open the connection to NMLFIRE 
con.Open "ProxyDB", "sqlquery", "querysql"
con.CommandTimeout = 0
' Create the SQL Query String
strParm1 = Request.Form("fdate")
strParm2 = Request.Form("tdate")
strSearchterm = Request.Form("searchterm")
strUser = Request.Form("scname")
Response.Write strParm1
Response.Write strParm2
Response.Write strUser

If strSearchterm <> "" then
 strSQLWhere = " (DestHost like '%" & strSearchterm & "')  "
Else
If strUser <> "" then
   strSQLWhere = " (ClientUserName like '%" & strUser & "')  "
 Else
   strSQLWhere = " "
 End If
End If
strSQL = " select DISTINCT DestHost " &_
  " from WebProxyLog " & _
  " where (DestHost like '%" & strSearchterm & "%') " & _ 
  " and (logDate between '" & strParm1 & "' and '" & strParm2 & "') " 

strSQL2 = " select ClientUserName " &_
   " from WebProxyLog " & _
   " where (ClientUserName like '%" & strUser & "%') " & _
   " and (logDate between '" & strParm1 & "' and '" & strParm2 & "') " 
      
Response.write strSQL
Response.write strSQL2
server.ScriptTimeOut = 600
rst.Open strSQL, con, adOpenKeyset, adLockOptimistic
' Only display records if there are records in the recordset
If NOT rst.EOF then
 'Display the header
%>
<div align="center">
<p align="center"><b>Internet Destinations </b> </p>
<p align="center"><b>from <%=Request.Form("fdate")%></b></p>
<p align="center"><b>thru <%=Request.Form("tdate")%></b> </p>
<div align="center"><center>
<table BORDER="1" height="57" width="501">
 <tr>
   <td align="center" height="17" width="491"><b>Destinations</b></td>
 </tr>
<%
'Loop through the recordset until there are no more records
Do While Not rst.EOF
%>
<tr>
 <td align="center" height="29" width="491"><a href="InternetDestinations4.asp"><%=rst("DestHost")%>
 </a></td>

</tr>    
<% 

'If you forget this next statement, you will be stuck in a loop
rst.MoveNext
Loop
 
Else
%>

<b>
 <tr>
   <td align="center" height="23" width="491">No records in recordset!&nbsp; </b>
<%

%>
</td>
   
 </b>
   
<p></p>
   
<p></p>

   
<%
End If

rst.Close
con.Close
%></html>



Spooky -> RE: Type mismatch error (5/16/2005 16:39:06)

"strSQLWhere" isnt actually used by the code though?



rrnml -> RE: Type mismatch error (5/16/2005 16:40:31)

I had it after the first sql statement before I took it out. If I put it back in I will get the type mismatch error.



Spooky -> RE: Type mismatch error (5/16/2005 16:43:23)

In your first example :

strSQL = " select DISTINCT DestHost " &_
" from WebProxyLog " & _
" where (DestHost like '%" & strSearchterm & "%') " & _
" and (logDate between '" & strParm1 & "' and '" & strParm2 & "') "
strSQLWhere

Should be more like :

strSQL = " select DISTINCT DestHost " &_
" from WebProxyLog " & _
" where (DestHost like '%" & strSearchterm & "%') " & _
" and (logDate between '" & strParm1 & "' and '" & strParm2 & "') AND "& strSQLWhere



rrnml -> RE: Type mismatch error (5/16/2005 16:51:34)

Ok that runs with no errors, but I get no records returned and I know there are hundreds of records. This is what gets passed.





2005050220050516nancy select DISTINCT DestHost from WebProxyLog where (DestHost like '%%') and (logDate between '20050502' and '20050516') AND (ClientUserName like '%nancy') select ClientUserName from WebProxyLog where (ClientUserName like '%nancy%') and (logDate between '20050502' and '20050516')

No records in recordset! 




Spooky -> RE: Type mismatch error (5/17/2005 15:20:56)

Youll need to check your SQL statement and isolate the parts that either arent working or not providing you with the info you require.
Your SQL is specific to the records you want to see.
It may be your wild card format (when null) or your date format. It might simply be using an "AND" instead of an "OR"

<edit spelling>



rdouglass -> RE: Type mismatch error (5/17/2005 15:31:18)

quote:

'20050502' and '20050516'


The date format would be my first guess if your host is in the US.  However, I can't tell from your user profile.  When I pass to my SQL Server, I send it in the format:

'mm/dd/yyyy'

so today is

'05/17/2005'

Is this SQL Server?

EDIT:  I have since been informed that your date format should work fine - sorry.  That's why I'm here - to learn more...[8|]



rrnml -> RE: Type mismatch error (5/17/2005 18:24:33)

Thanks guys I'm still working on it. I'll have to check out the SQL statement like you said. Would it be possible to put the two SQL statements together instead of having two?



Spooky -> RE: Type mismatch error (5/17/2005 19:24:39)

You want to use strSQL2 as well?



rrnml -> RE: Type mismatch error (5/17/2005 19:27:54)

Yeah well what I want to do is be able to search by either the keyword which is strSearchterm or by user which is strUser. Can I do that in one SQL statement or do I need two? My brain is just so fried because I've tried many different ways and I get different results but not the way I want it.



rrnml -> RE: Type mismatch error (5/17/2005 19:34:09)

Hey I don't mean to bail but I'll be back in tomorrow. Thanks for your help. I'll be working on it tonight likely. Hopefully, with much progress. Thanks again.



BeTheBall -> RE: Type mismatch error (5/17/2005 19:34:35)

What if you replace: 

If strSearchterm <> "" then
 strSQLWhere = " (DestHost like '%" & strSearchterm & "')  "
Else
If strUser <> "" then
  strSQLWhere = " (ClientUserName like '%" & strUser & "')  "
 Else
  strSQLWhere = " "
 End If
End If
strSQL = " select DISTINCT DestHost " &_
 " from WebProxyLog " & _
 " where (DestHost like '%" & strSearchterm & "%') " & _ 
 " and (logDate between '" & strParm1 & "' and '" & strParm2 & "') " 

strSQL2 = " select ClientUserName " &_
  " from WebProxyLog " & _
  " where (ClientUserName like '%" & strUser & "%') " & _
  " and (logDate between '" & strParm1 & "' and '" & strParm2 & "') " 
     
Response.write strSQL


with: 

If strSearchterm <> "" then
 strSQLWhere = " (DestHost like '%" & strSearchterm & "')  "
Else
If strUser <> "" then
   strSQLWhere = " (ClientUserName like '%" & strUser & "')  "
 Else
   strSQLWhere = " "
 End If
End If
strSQL = " select DISTINCT DestHost " &_
  " from WebProxyLog " & _
  " where "&strSQLWhere&"" & _ 
  " and (logDate between '" & strParm1 & "' and '" & strParm2 & "') "  
      
Response.write strSQL




Spooky -> RE: Type mismatch error (5/17/2005 19:36:54) 

If strSearchterm <> "" then
 strSQLWhere = " AND  (DestHost like '%" & strSearchterm & "')  "
Else
If strUser <> "" then
   strSQLWhere = " AND  (ClientUserName like '%" & strUser & "')  "
 Else
   strSQLWhere = " "
 End If
End If
strSQL = " select DISTINCT DestHost " &_
  " from WebProxyLog " & _
  " where (logDate between '" & strParm1 & "' and '" & strParm2 & "')  "&strSQLWhere  
      
Response.write strSQL


As above incase strSQLWhere is null



BeTheBall -> RE: Type mismatch error (5/17/2005 20:04:24)

Just thought of that myself, of course, Spooky already caught it.



rrnml -> RE: Type mismatch error (5/18/2005 12:02:00)

This seems to make more sense as it will allow you to search by keyword OR by user. And it eliminates the redundancy in the code I had before. However it doesn't return any records at all. Any idea why?



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375