Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 22nd, 2008, 06:32 PM
Authorized User
 
Join Date: Apr 2008
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default help adding dates to ms Access db

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?

Reply With Quote
  #2 (permalink)  
Old April 22nd, 2008, 06:44 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
Reply With Quote
  #3 (permalink)  
Old April 22nd, 2008, 06:54 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
Reply With Quote
  #4 (permalink)  
Old April 22nd, 2008, 07:04 PM
Authorized User
 
Join Date: Apr 2008
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #5 (permalink)  
Old April 22nd, 2008, 07:09 PM
Authorized User
 
Join Date: Apr 2008
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #6 (permalink)  
Old April 22nd, 2008, 07:26 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
Reply With Quote
  #7 (permalink)  
Old April 22nd, 2008, 07:33 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

;;;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
Reply With Quote
  #8 (permalink)  
Old April 22nd, 2008, 07:44 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
Reply With Quote
  #9 (permalink)  
Old April 22nd, 2008, 07:54 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
Reply With Quote
  #10 (permalink)  
Old April 22nd, 2008, 08:53 PM
Authorized User
 
Join Date: Apr 2008
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS ACCESS 2003 FRONTEND AND MS SQL SERVER 2005 DB mohankumar0709 SQL Server 2005 3 March 23rd, 2007 12:48 AM
Ms Access DB in Server anukagni Access 2 February 24th, 2006 01:15 AM
MS Access db on a website lawrenceh VB Databases Basics 0 September 25th, 2004 06:32 PM
JDBCRealm using MS access DB ZouK01 Java Databases 0 May 26th, 2004 02:01 PM
JDBCRealm using MS access DB ZouK01 Apache Tomcat 0 May 26th, 2004 01:59 PM



All times are GMT -4. The time now is 03:54 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.