Type Mismatch error

[tagriffith]

I am trying to create a script that would be able to track each time a file is downloaded. I have setup an Access database to keep track of how many times a day it is downloaded.

I am having a problem with Mysql code. If I delete all the records in my database the code will run perfectly. It will show the name of the file downloaded, date, and how many times it was downloaded.

The problem that I am having is... if I try to download the same file again (without deleting the recordset from Access). It does not update the "times downloaded" column and I get the following error message:

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

HERE IS MY SQL CODE:

<!--#INCLUDE VIRTUAL="/airquality/includes/dbconnection.asp" -->

<%
DIM mySQL, strTitle, strFile, strDate, objRS, intCount
strTitle = Request("Title")
strFile = Request("File")
strDate = Date()
mySQL = "SELECT * FROM tblDownloads WHERE fTitle = ' " & strTitle & " ' AND fDateDownloaded = ' " & strDate & " ' ;"
Set objRS = Server.CreateObject("ADODB.Recordset")


objRs.LockType = 3

objRS.Open mySQL, objConn, adOpenKeyset

IF objRS.EOF THEN
objRS.AddNew
objRS("fTitle") = Request("Title")
objRS("fFile") = Request("File")
objRS("fDownloads") = 1
objRS("fDateDownloaded") = Date()
objRS.Update
ELSE
objRS.MoveFirst
intCount = objRS("fDownloads")
objRS("fDownloads") = intCount + 1
objRS.Update
END IF

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

Response.Redirect("/airquality/downloads/" & Request("File"))
%>

HERE IS THE DOWNLOAD PAGE CODE: (very simple)

[HTML]
<p><a href="/airquality/includes/downloadtracker.asp?Title=MyDocument&File=TheBreeze-Spring2006.pdf">Download</a></p>
HTML]

I hope someone can help me figure this out! Thanks in advance.

[lmf232s]

what line is it giving the error on.
It might be this line
objRS("fDownloads") = intCount + 1

You are trying to add a string and a integer.
If this is the line in question you might want to convert intCount to an integer or a double.

cint(intCount) + 1

Again thats assuming thats the line in question

[tagriffith]

That is the line giving me the problem.

If this is the line in question you might want to convert intCount to an integer or a double.

How would I go about writing this into the code to convert it?

Thanks.

[lmf232s]

objRS("fDownloads") = cint(intCount) + 1

[tagriffith]

Yeah I tried that. It still came up with the same error code. Anymore ideas?

Here is what I did with the new code:

IF objRS.EOF THEN
objRS.AddNew
objRS("fTitle") = Request("Title")
objRS("fFile") = Request("File")
objRS("fDownloads") = 1
objRS("fDateDownloaded") = Date()
objRS.Update
ELSE
objRS.MoveFirst
intCount = objRS("fDownloads")
objRS("fDownloads") = CInt(intCount) + 1
objRS.Update
END IF

[lmf232s]

i assum that intcount = Null then and your getting an error trying to convert Null to an integer. Just for SNG's do something simple like this and see if it works. If it does then i would create a function where you can pass the value of objRS("fDownloads") to it or pass intcount but give this a shot

ELSE
objRS.MoveFirst
intCount = objRS("fDownloads")
If intCount <> "" then
objRS("fDownloads") = CInt(intCount) + 1
Else
objRS("fDownloads") = 1
End If
objRS.Update
END IF

[tagriffith]

Maybe it is not with that section of code. I added another download file and it will not add it to the database if I already had information in there from a totally different download file. If I deleted the information out of the database it would then work until I would try to download another file. Could it be with the objRs.MoveFirst line of code?

Thanks

[lmf232s]

you dont need the objRS.MoveFirst line of code. Reason being is that you already have the file that you want to work w/. Give it a shot.

[tagriffith]

I am still getting the same code. Here is the new code with your suggestions. Let me know if you see anything that could be causing it. I am going to try and delete a line at a time until I get a different code. I am hoping that the line I delete will lead me to a solution. Thanks for your quick reply on this situation.

<%
DIM mySQL, strTitle, strFile, strDate, objRS, intCount
strTitle = Request("Title")
strFile = Request("File")
strDate = Date()
mySQL = "SELECT * FROM tblDownloads WHERE fTitle = ' " & strTitle & " ' AND fDateDownloaded = ' " & strDate & " ' ;"
Set objRS = Server.CreateObject("ADODB.Recordset")


objRs.LockType = 3

objRS.Open mySQL, objConn, adOpenKeyset

IF objRS.EOF THEN
objRS.AddNew
objRS("fTitle") = Request("Title")
objRS("fFile") = Request("File")
objRS("fDownloads") = 1
objRS("fDateDownloaded") = Date()
objRS.Update
ELSE
intCount = objRS("fDownloads")
If intCount <> "" then
objRS("fDownloads") = CInt(intCount) + 1
Else
objRS("fDownloads") = 1
End If
objRS.Update
END IF

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

Response.Redirect("/airquality/downloads/" & Request("File"))
%>

[tagriffith]

It is telling me that the type mismatch data is in line 14 of the code. that line is:

objRS.Open mySQL, objConn, adOpenKeyset

Does that help with anything?

[tagriffith]

I have been playing around with this and took it to it simplest form. I just wanted to keep adding records to the database (not updating the record if it is already there). I still get the code error when I try to add a second record. I don't understand what is going on with this. I will keep working with it.

[tagriffith]

Okay I have figured it out. The line I was having problems with was:

mySQL = "SELECT * FROM tblDownloads WHERE fTitle = ' " & strTitle & " ' AND fDateDownloaded = ' " & strDate & " ' ;"

I worked when I rewrote the code to:

mySQL = "SELECT * FROM tblDownloads"

That created a new problem for me. How do I get the code to check the database and see if the file has already been downloaded for that day. If so, update the record. If not create a new record?

What it is doing now is updating the same record and just changing the download file name and then adding + 1 to the downloads. Even if it is two seperate files it still updates just one recorded.

Thanx in advance

[lmf232s]

tag,

Your first post of code should work just fine. Remove the movefirst line in the else statement. You said that you were having problems w/ the sql statement. It might be that you were trying to compare a date field to another field that it did not like.

You code looks fine and it should work. You should walk through it line by line and set some debugging statements to see what is going on.

Example before you execute your sql statment do something like this

Response.write mySql & "<BR>

This will write the sql statement to the sceen and will show you what is about to execute. Take that statement into query analyser and run it and see what results you get.

Then set debuggin statements in the If Else like

If ? Then
Response.write "In the If Statement<BR>"
Else
Response.write "In the Else Statement <BR>"
Response.write intCount & "<BR>"
End if

This will help you to see what is going on in the back ground.
Try it out and see what happens.