inserting/updating database

[s4dadmin]

I am using ASP with MySQL. This is code for a calendar application that saves info to the database based on date. It is not giving an error when processed. Nothing is being inserted into the database and nothing is being updated either. Can anyone help?

Dim vDate
Dim chkTxt
Dim DiaryID
vDate = request("view_date")

Call OpenDB()
' Check to see if it's a new record to be added or an old one to update
StrSql= "Select * from diary where dte = '$vDate'"
set rs = DbConn.Execute (StrSql)
chkTxt = chkString(request("txt"))

if rs.BOF or rs.EOF then ' No records found. i.e. New record
StrSql ="INSERT INTO diary (dte, text_field) values ('" & vDate & "','" & chkTxt & "')"
else ' Record found. i.e. update record.
DiaryID = rs("id")
StrSql = "UPDATE diary SET diary.dte = '$vDate', text_field = '$chkTxt' WHERE id = '$DiaryID'"
End If

DbConn.Execute (strSql)

Also, I have verified that VDate shows a correct date but it only is entered as 0000-00-00 in the database. Should I use timestamp if time is not necessary?

[raf]

Hmm. I see this
if rs.BOF or rs.EOF then

and think that is the problem. The pointer will probably be BOF. So try changing it to

if rs.EOF=True then

I personally alway use

sql="SELECT Count(*) as numrec FROM diary where dte = '$vDate'"
...
if rs.Fields("numrec") > 0 then
...
else
your insert
end if


The recordset will be a lot smaller (never use * unless you really need all fields from the record).
I have verified that VDate shows a correct date but it only is entered as 0000-00-00 in the database.
I don't get that. If it is a column of type date, then it will only record the data. If it is a column of type datetime, it will record both date and time.
Should I use timestamp if time is not necessary?
if you look at the syntax, you'll see it is timestamp(x). Depending on the x, you'll get only the year, or year and month up till timestamp(14) --> date and time.
more info here:
http://www.mysql.com/doc/en/DATETIME.html