I need to insert dates in my database at times. If a date is entered in the form, there is no issue when submitted for execution. But if no date is entered (the form field is blank) I get a type mismatch error. I understand the error, but I don't know how to fix the code. If the date field is empty, I dont want anything inserted in the database. I need to be able to leave date fields blank. Here is what I currently have:

dtDate=request.form("Kickoff")
if dtDate <> "" then
  rsAddComments.Fields ("Kickoff") = dtDate
else
  dtDate= "# #"
  rsAddComments.Fields ("Kickoff") = dtDate
end if

I either get a syntax error or type mismatch error. Im not sure what the correct syntax is for the "...dtDate= "# #"..." line, but I'd bet what I have is wrong. How can I insert a blank date?

You need to build a conditional SQL statement. Here is one cut out of a page I am working on:

      sql = "UPDATE aboutUs SET heading='" & trim(storeText(request.form("heading"))) & "',info='" & trim(storeText(request.form("info"))) & "'"
      if trim(request.form("Kickoff")) <> "" then
         sql = sql & ",updated=#" & amdate(now()) & "#"
      end if
      sql = sql & " WHERE ID=" & trim(request.form("id")) & ";"

Wind is your friend
Matt
www.elitemarquees.com.au

Sorry I should probably explain my functions:

> The Trim function - you should always use this.

> The 'storetext' function is my string manipulation function to deal with things like single quotes and the like

> The amDate function is my date manipulation function. This ensures all dates are entered in American format for this job into the Access DB

> Also note - I have single quotes inside my amdate function:
  updated=#" & amdate(now()) & "#"
  If you are not wrapping a function around your dates you should add them. EG:
  updated=#'" & amdate(now()) & "'#"



Wind is your friend
Matt
www.elitemarquees.com.au

I just figured it out, before I read your post. Here is how I did it.

dtDate=request.form("Kickoff")
if dtDate <> "" then
rsAddComments.Fields ("Kickoff") = dtDate
else
dtDate= null
rsAddComments.Fields ("Kickoff") = dtDate
end if

I guess null is a value access recognizes as valid for dates. Works perfectly. Does it look alright to you? Thanks for posting your help either way.

I do have another question though, about checkboxes. I can't get my code right for the few checkboxes I have on my form. What I have is below...

if Request.Form("Pass1") = "yes" then
rsAddComments.Fields("Pass1") = 0
else
rsAddComments.Fields("Pass1") = -1
end if

As it is now, a check is inserted in the db regardless of what is on the form. If I swap the 0 and -1 no check is inserted in the db, again, regardless of what is on the form.

Hmm something most be off on your form. I wrote this quick example and it works as expected:

<%
    If Request.Form("chkfoo") = "Yes" Then Response.Write "Foo"
%>
<form action="me.asp" method="POST">
<input type="checkbox" name="chkfoo" value="Yes">
<input type="submit" name="btnsubmit">
</form>

If you tick the checkbox "Foo" is wrote out to the screen, otherwise nothing happens.

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

;;;I guess null is a value access recognizes as valid for dates. Works perfectly. Does it look alright to you? Thanks for posting your help either way.

If its working for you and you are happy with it, very good. IMO its a waste of time and a bad habbit to make you SQL do work it does not have to. Why not let the DB work for you? Use default values and only address fields in your SQL which are required. EG:

I always place a 'inactive' field in tables. This is a 'bit' (SQL Server) or 'Yes No' (ACCESS) type which as a default value. This filed never gets referenced unless i am inactivating or reactivating a record.

Same with dates - lets say we have a field called 'updated' with no default value however NULLS are allowed. I only ever address this field in my SQL if its being updated. An insert into this table updated the 'creationDate' only.

Anyhow IME conditional SQL statements are very very handy. Are you validating the data passed is a valid date? If this is not done you will get an error when you execute your query. Use the isDate function around about here:

if (dtDate <> "") AND isDate(dtDate) then


Wind is your friend
Matt
www.elitemarquees.com.au

Hmmm. This may be a bit of a rehash on Matt's statement but here goes. IMHO (and because I like to be picky) you should never use NULL as a DataType within the context of a database whether it be a database file like Access or an RDBMS like Sql Server.

Outside of the fact that this violates Normal Form rules, I find that it is a pain to deal with NULL data at the application logic level especially if your application is not designed to deal with NULL values coming out of the database. Anyway, as Matt pointed out, giving columns a default value is a good way to go. I will get off my soapbox now. ;]

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

A very good point - I used to have to deal with broken pages as a result of NULLS in my earlier coding days. This lead to me having to place the isNull etc type code in the offending pages, needless to say a real pain.

Now I know where to expect them and cater for them where neccesary when first building the page

Wind is your friend
Matt
www.elitemarquees.com.au

Im doing this for a project at school. So if it works it's good enough for me right now (I've got a TON more to do still). But I would like to know the "correct" way to do this for future reference.

I tried to use a dynamic sql INSERT INTO statement to do this, but it was extremely long and complicated. I just couldn't get it to work.

Is there a site you know of that goes over the trim function and the storeText and amDate functions you created in detail?