Dup entries when creating a new DB entry (ASP Classic with an MDB file)

Phillip Windell

unread,

Dec 12, 2009, 4:39:56 PM12/12/09

to

Hi guys! (Hi Bob)
Been a couple years since I've been here. I used to try to help people here
with their questions (within my limitations). But now I am here with
something I am stuck on myself.

I am having problem with my ASP page creating a new database record *twice*
when I submit the page. This creates a duplicate records (except for the
autonumber ID field). They are ASP Classic pages written in VBS. The
database is an MS Access MDB file (2000 format).

What is not happening,...there is no happless user running this and
refreshing a page a extra time or anything like that to cause an accidental
resubmittal. I may even later set a couple fields to "no duplicates" to
prevent that. It is just me running for now,..no one else. It is not in
production yet. I am using the actual MS Access itself as a temporary "UI"
for the production copy until i get the Web ASP "UI" working right.

Below you will find first a "Response.write" output of an SQL Statement.
Then below that is the code of the page. The page is fairly short. You
should be able to tell from the SQL statement that the previous pages that
created the SQL statement leading up to this page are working corrrectly.

As you can see I am using a concatenated SQL statement which I hate,...I'd
love to use an Access Query but can't figure out how. I can do it fine for
retrieving data but can't get them to work for editing records or creating
new records.

This site is very small and there are no "company secrets" in it. If you
want a copy of the whole thing I can email it to you in a Zip file.

I'm lising the code for the Page itself and the database "include
file",...the other include file should not be relevant,..but I'll post it if
you like, or maybe the part of it that is actually used.

Thanks
--
Phillip Windell
The views expressed, are my own and not those of my employer, or Microsoft,
or anyone else associated with me, including my cats.
-----------------------------------------------------

---------SQL Statement---Ignore line-wrap----------------
INSERT INTO tblEvents (fldTitle, fldStartDate, fldEndDate, fldDetails,
fldApproved) VALUES ('This is a Title', #1/12/2012#, #1/13/2012#, 'This is a
Description', on)

------------Page code for "database include file"-----------------
<%
Response.Expires = 0
Response.Expiresabsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.CacheControl = "no-cache"

Dim strFilePath
Dim objConn
Dim objRs
Dim strServerName

strServerName = Request.ServerVariables("SERVER_NAME")

Select Case strServerName
Case "d6vg91d1"
strFilePath = "C:\inetpub\database\CommCalendar.mdb"
Case "wandfile1"
strFilePath = "H:\database\CommCalendar.mdb"
Case "support.wandtv.com"
strFilePath = "H:\database\CommCalendar.mdb"
Case Else
strFilePath = "C:\inetpub\database\CommCalendar.mdb"
End Select

'ADO connection to database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & strFilePath
Set objRs = Server.CreateObject("ADODB.Recordset")
%>

-----------------------Actual Page code--------------------------
<% @LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> </TITLE>
<META content="text/html; charset=unicode" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18852"></HEAD>
<BODY>

<%
Dim intID
Dim strTitle
Dim strStartDate
Dim strEndDate
Dim strDetails
Dim strApproved
Dim strSQL

strTitle = CleanIllegalChar(Request.Form.Item("txtTitle"))
strStartDate = cDate(Request.Form.Item("txtStartDate"))
strEndDate = cDate(Request.Form.Item("txtEndDate"))
strDetails = CleanIllegalChar(Request.Form.Item("areaDetails"))
strApproved = Request.Form.Item("chkApproved")
If strApproved = "" then strApproved ="False"

'Create SQL string for Update
strSQL = "INSERT INTO tblEvents (fldTitle, fldStartDate, fldEndDate,
fldDetails, fldApproved) "
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & strTitle & "', "
strSQL = strSQL & "#" & strStartDate & "#, "
strSQL = strSQL & "#" & strEndDate & "#, "
strSQL = strSQL & "'" & strDetails & "', "
strSQL = strSQL & "" & strApproved & ""
strSQL = strSQL & ")"

'Write the changes
objConn.Execute strSQL
set objConn = Nothing
set objRS = Nothing
%>
<Input Type="Button" Name="btnDone"
Value="Done"onClick=window.location.href="manage.asp">
</BODY>
</HTML>

Bob Barrows

unread,

Dec 12, 2009, 5:27:47 PM12/12/09

to

Phillip Windell wrote:
> Hi guys! (Hi Bob)
> Been a couple years since I've been here. I used to try to help
> people here with their questions (within my limitations). But now I
> am here with something I am stuck on myself.
>
> I am having problem with my ASP page creating a new database record
> *twice* when I submit the page. This creates a duplicate records
> (except for the autonumber ID field). They are ASP Classic pages
> written in VBS. The database is an MS Access MDB file (2000 format).
>
> What is not happening,...there is no happless user running this and
> refreshing a page a extra time or anything like that to cause an
> accidental resubmittal. I may even later set a couple fields to "no
> duplicates" to prevent that.

"may"??

Why isn't that the first thing you did? Identify the table's naturaly unique
key and create a unique index using the fields that comprise that key.

Is the problem that you do not know how to create a multi-field index in
Access? It' pretty simple. With your table open in Design mode, locate the
Indexes toolbar button and click it. A dialog will open. It will contain two
columns. In the first column, type in a name for your unique index. In the
second column, in the same row, select the first field. then go to the next
row and select the next field, continuing until you have selected all the
fields to be included in that index.

> It is just me running for now,..no one
> else. It is not in production yet. I am using the actual MS Access
> itself as a temporary "UI" for the production copy until i get the
> Web ASP "UI" working right.
> Below you will find first a "Response.write" output of an SQL
> Statement. Then below that is the code of the page. The page is
> fairly short. You should be able to tell from the SQL statement that
> the previous pages that created the SQL statement leading up to this
> page are working corrrectly.
> As you can see I am using a concatenated SQL statement which I
> hate,...I'd love to use an Access Query but can't figure out how. I
> can do it fine for retrieving data but can't get them to work for
> editing records or creating new records.

See:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

>
> This site is very small and there are no "company secrets" in it. If
> you want a copy of the whole thing I can email it to you in a Zip
> file.
> I'm lising the code for the Page itself and the database "include
> file",...the other include file should not be relevant,..but I'll
> post it if you like, or maybe the part of it that is actually used.
>

Don't bother. Create the unique index ... first. It's part of good database
design. Then worry about your UI running the code twice. The second attempt
to insert the same data should now raise an error, making it easier to
figure out why the code is running twice.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Phillip Windell

unread,

Dec 14, 2009, 10:27:06 AM12/14/09

to

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:uk2Obp3e...@TK2MSFTNGP05.phx.gbl...

> "may"??
>
> Why isn't that the first thing you did? Identify the table's naturaly
> unique key and create a unique index using the fields that comprise that
> key.

It already has a Key,...an autonumber field. It is the only field
"different" between the Dupes. But I did compensate for that as I believe
you are thinking. See futher below. You may not remember,.. but you, I,
and Curt Christenson sat around at breakfest at the 2005 (2006?) MVP Summit
talking about some of these things. I've gotten a little rusty,..but
havent' fogotten it all yet :-)

We were also complaining about ASP.Net that was just commming out at the
time,....I still can't do anything useful with that,...so I stay with ASP
Classic. :-)

> Is the problem that you do not know how to create a multi-field index in
> Access?

Yes, I know how to do that,...and have done it since this was originally
posted,...see below.

>> As you can see I am using a concatenated SQL statement which I
>> hate,...I'd love to use an Access Query but can't figure out how. I
>> can do it fine for retrieving data but can't get them to work for
>> editing records or creating new records.
>
> See:
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

Ok,...I'll look those over.

> Don't bother. Create the unique index ... first. It's part of good
> database design. Then worry about your UI running the code twice. The
> second attempt to insert the same data should now raise an error, making
> it easier to figure out why the code is running twice.

Yes,..I did that yesterday. I picked the first field after the Index,..it
contains a Title,...which should not generally be duplicated (but not
impossible),....I set it to Indexed=Yes, No duplicates". I does generate
the expected error saying that my action would have caused a dupilcate. I
haven't been able to debug it past that yet. I was working on it over the
weekend at home,...just got to work (Monday morning). I am copying the
"fresh" Virtual Machine created over the weekend from my home external drive
to the one at work so I can work on it here at work, that'll take an hour or
so. So by the time you see this or reply it may be ready to go.

Bob Barrows

unread,

Dec 14, 2009, 10:53:13 AM12/14/09

to

Phillip Windell wrote:
> "Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
> news:uk2Obp3e...@TK2MSFTNGP05.phx.gbl...
>> "may"??
>>
>> Why isn't that the first thing you did? Identify the table's naturaly
>> unique key and create a unique index using the fields that comprise
>> that key.
>
> It already has a Key,...an autonumber field.

No, that's a surrogate key, and it obviously does not help prevent
dupes. Only a unique index on the table's natural key can prevent the
dupes.

> It is the only field
> "different" between the Dupes. But I did compensate for that as I
> believe you are thinking. See futher below. You may not remember,..
> but you, I, and Curt Christenson sat around at breakfest at the 2005
> (2006?) MVP Summit talking about some of these things. I've gotten a
> little rusty,..but havent' fogotten it all yet :-)

Yes, I do vaguely remember that. :-)

>
>> Don't bother. Create the unique index ... first. It's part of good
>> database design. Then worry about your UI running the code twice. The
>> second attempt to insert the same data should now raise an error,
>> making it easier to figure out why the code is running twice.
>
> Yes,..I did that yesterday. I picked the first field after the
> Index,..it contains a Title,...which should not generally be
> duplicated (but not impossible),....I set it to Indexed=Yes, No
> duplicates". I does generate the expected error saying that my
> action would have caused a dupilcate. I haven't been able to debug
> it past that yet. I was working on it over the weekend at
> home,...just got to work (Monday morning). I am copying the "fresh"
> Virtual Machine created over the weekend from my home external drive
> to the one at work so I can work on it here at work, that'll take an
> hour or so. So by the time you see this or reply it may be ready to
> go.
>

If a title is allowed to be duplicated, is there another field (not the
autonumber field) that is used to distinguish the two records with the
same title? Perhaps an applies-to date? Something to justify the entry
of two records with the same title?

--
HTH,
Bob Barrows

Neil Gould

unread,

Dec 14, 2009, 12:15:12 PM12/14/09

to

Hi Phillip,

Phillip Windell wrote:
> Hi guys! (Hi Bob)
> Been a couple years since I've been here. I used to try to help
> people here with their questions (within my limitations). But now I
> am here with something I am stuck on myself.
>
> I am having problem with my ASP page creating a new database record
> *twice* when I submit the page. This creates a duplicate records
> (except for the autonumber ID field). They are ASP Classic pages
> written in VBS. The database is an MS Access MDB file (2000 format).
>

So... if I understand this correctly, you are generating entries from an ASP
page, and the result is two indentical entries into your data table? I
didn't see any obvious reason for this in your code, but was curious about
what these pages are doing:

unread,

Dec 14, 2009, 5:39:19 PM12/14/09

to

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:e5KIZmPf...@TK2MSFTNGP05.phx.gbl...

> I'm sorry, but I believe you are missing my poiint: As I said earlier,
> that fldID field is a surrogate key. You have added it to allow the
> database to easily identify a record in the table, and possibly to make
> it easier to join this table to other tables.

You want me to set the Key to be the combination of all three firelds:
fldID, fldTitle, and fldStartDate? No problem, I can do that. All I am
saying is that doing it will prevent Dupes from happening,...but the lack
of doing that I don't believe is the root cause of the Dupes. Right now I
just want to kill the root cause. Yes, I will set them as you are
asking,...unless I misunderstand what you are telling me to do again.

> All I can say is that I concur with Neil: there is nothing in the
> server-side code you showed us that would result in two inserts. I would
> be more suspicious that your page is submitting its form data twice. You
> can confirm or disprove this by checking the IIS logs.

I sure don't know what that would be,...but I can check the logs. Probably
tomorrow,...it is getting late in the day here.

Here's the link to download the Zip file of the whole thing if anyone wants
to look at it. It should take the murkyness out of some of this. The
database included here has the Table's Key set to the combination of fildID,
fldTitle, and fldStartDate as I believe you were asking me to do. This
package does not contain the new Insert or Update queries you mention in the
other post in either the Database or in the code because I have not had time
to do much with that yet, so it still uses the SQL String.

http://support.wandtv.com/calendarsite.zip

After adjusting for the location where you placed the Database (edit the
"database.asp") you would start by running "manage.asp",...click the link at
the bottom to create a new record,...it takes you to "newevent.asp" where
you fill out the form,...it then submits to "neweventapply.asp" and ends
with just a button on the page. Clicking on that button returns you back to
"manage.asp" where you can see the new entry(s).

See ya's tomorrow guys,..I'm outta here for today.....

Neil Gould

unread,

Dec 14, 2009, 6:04:08 PM12/14/09

to

Hi Phillip,

Phillip Windell wrote:
>> 
>> 
>>
>> Could one of these be generating an entry into your table in
>> addition to the
>> code you've shown (sorry, but it was truncated from my reply because
>> it was
>> below your signature)?
>
> Yea, it did the same thing with Bob. I didn't know having it under
> the Sig would do that. No, those two Includes should be fine. The
> "functions" one only contains Subs and Functions that won't do
> anything if they aren't "called" and the DB one is really short and
> simple. I'll repost the code here and won't even include the Sig.
> I'm not including the "functions" page because is really really long,
> I'll just ask that you trust me for now that there isn't anything
> there to mess things up,...but we can look at it later if it comes to
> that.
>
> Shouldn't be hard to read it,...I'm kind of a "neat freak" with my
> code formating.
>

I was able to read your last post, but couldn't reply to it with the code
included. News reader that follow usenet protocol exclude everything after
the double-dash and space used to begin the signature lines, and they are
supposed to be limited to a few lines (4, IIRC).

Once again, as Bob has said, I don't see anything in the code that would
necessarily submit two records (except as noted below), so the cause is
likely to be found outside of this code block, and I now wonder what
"Manage.asp" is doing, since that is what your form calls when you click
BtnDone.

<snip>

> -----------------------Actual Page code--------------------------
> <% @LANGUAGE="VBSCRIPT" %>
> <% Option Explicit %>
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML>
> <HEAD>
> <TITLE> </TITLE>
> <META content="text/html; charset=unicode" http-equiv=Content-Type>
> <META name=GENERATOR content="MSHTML 8.00.6001.18852"></HEAD>
> <BODY>

I'd put this above the HEAD section
----------------------------------vvvvvvvvvvv

----------------------------------^^^^^^^^^^^^^^

I'd put this in a FUNCTION called by btnDone, because if this page _is_
"manage.asp", it will write a record once when the page is first drawn and
again when btnDone is clicked.
-----------------------------------vvvvvvvvvvv
<%
FUNCTION DoItNow

END FUNCTION
> %>
---------------------------------------

> <Input Type="Button" Name="btnDone"
> Value="Done"onClick=window.location.href="manage.asp">
> </BODY>
> </HTML>
>

--
Best,

Neil

Bob Barrows

unread,

Dec 14, 2009, 6:48:07 PM12/14/09

to

Phillip Windell wrote:
> "Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
> news:e5KIZmPf...@TK2MSFTNGP05.phx.gbl...
>> I'm sorry, but I believe you are missing my poiint: As I said
>> earlier, that fldID field is a surrogate key. You have added it to
>> allow the database to easily identify a record in the table, and
>> possibly to make it easier to join this table to other tables.
>
> You want me to set the Key to be the combination of all three firelds:
> fldID, fldTitle, and fldStartDate?

No ... where did I say that? See, I knew you were missing my point. :-)

My suggestion is to have two keys: the first key will be the primary key for
your surrogate autonumber field, and the second will be a unique index for
the real "natural" key. Two indexes.

Don't fall into the trap of thinking the second key is a "waste" (I've heard
that suggested in the past). Not only will it function to keep inappropriate
data from being inserted into the table, it will also almost certainly be
used to aid query performance when you select data from the table by start
date or by title.

Bob Barrows

unread,

Dec 14, 2009, 8:28:05 PM12/14/09

to

The problem is here (on newentry.asp):
<script language="VBScript">
Private Sub btnNewEntrySubmit_OnClick
'Build error message'
If Document.frmNewEntry.txtTitle.Value = "" Then strErrorMsg = strErrorMsg
_
<snip>
If strErrorMsg = "" Then
Document.frmNewEntry.Submit <=====
Else
Dim x
x = MsgBox(strErrorMsg,0,"Errors Encountered!")
End if
End Sub

The indicated line is causing a second submission. There is no need to
explicitly submit the form in this event ... the submit button is already
causing a form submission. What you really want to do is cancel the event if
the data is not valid. I would prefer to do this in the form's onSubmit
event, like this:

Private Sub frmNewEntry_OnSubmit
'Build error message'
If Document.frmNewEntry.txtTitle.Value = "" Then strErrorMsg = strErrorMsg
_
<snip>
If strErrorMsg <> "" Then
MsgBox strErrorMsg,0,"Errors Encountered!"
window.event.returnValue=false
End if
End Sub

--

Phillip Windell

unread,

Dec 15, 2009, 9:28:47 AM12/15/09

to

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:u0vjMYSf...@TK2MSFTNGP04.phx.gbl...

Hi Bob,

Just got in. The IIS log agrees that it was summited twice, as you thought
it would. So give me a bit to wake up and get going this morning and get
that fixed. I'll figure out the "two keys" thing after that, I'm still a
bit fuzzy on that, but I'm sure I'll get it straight eventually.

Thanks!

Bob Barrows

unread,

Dec 15, 2009, 9:48:16 AM12/15/09

to

Phillip Windell wrote:
> Hi Bob,
>
> Just got in. The IIS log agrees that it was summited twice, as you
> thought it would. So give me a bit to wake up and get going this
> morning and get that fixed. I'll figure out the "two keys" thing
> after that, I'm still a bit fuzzy on that, but I'm sure I'll get it
> straight eventually.
>

You don't have to do this in Design view: you can do it via SQL DDL if
you like:

CREATE UNIQUE INDEX IX_Events_Unique
ON tblEvents (fldStartDate ASC, fldTitle ASC)

--
HTH,
Bob Barrows

Phillip Windell

unread,

Dec 15, 2009, 10:30:18 AM12/15/09

to

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:u0vjMYSf...@TK2MSFTNGP04.phx.gbl...

> the data is not valid. I would prefer to do this in the form's onSubmit
> event, like this:
>
> Private Sub frmNewEntry_OnSubmit
> 'Build error message'
> If Document.frmNewEntry.txtTitle.Value = "" Then strErrorMsg = strErrorMsg
> _
> <snip>
> If strErrorMsg <> "" Then
> MsgBox strErrorMsg,0,"Errors Encountered!"
> window.event.returnValue=false
> End if
> End Sub

Very good. That fixed the dupes issue. However I tested the error message
part of it by intensionally leaving a field blank,...no error message like
there should have been,....and it posted to the database with the blank
entry instead of canceling the submit. I'm running out of patients with the
stupid thing so I am just going to pull that block of code out and forget
it,...I went into the database with Access and set those Fields to
Required=yes and Allow Zero Length=no and call it good enough. There is only
one person inputting data and one backup when he is on vacation,...If they
have problems I am right down the hall.

I couldn't get the Parameterized querys to work to replace the SQL
string,..so the SQL String is still there. I'm out of patients there too I
guess. Maybe I took the code sample in those articles too literal and put
things in that were only meant to be place holders and not actually be in
the query's SQL View.

I looked around in Access to figure out what you meant about the Keys. I've
come to the conclusion that I am clueless,...I have no idea what you mean.

On the good side,...it seems like it is at lease "usuable" even if not
perfect.

Bob Barrows

unread,

Dec 15, 2009, 10:51:44 AM12/15/09

to

Phillip Windell wrote:
> "Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
> news:u0vjMYSf...@TK2MSFTNGP04.phx.gbl...
>> the data is not valid. I would prefer to do this in the form's
>> onSubmit event, like this:
>>
>> Private Sub frmNewEntry_OnSubmit
>> 'Build error message'
>> If Document.frmNewEntry.txtTitle.Value = "" Then strErrorMsg =
>> strErrorMsg _
>> <snip>
>> If strErrorMsg <> "" Then
>> MsgBox strErrorMsg,0,"Errors Encountered!"
>> window.event.returnValue=false
>> End if
>> End Sub
>
> Very good. That fixed the dupes issue. However I tested the error
> message part of it by intensionally leaving a field blank,...no error
> message like there should have been,....and it posted to the database
> with the blank entry instead of canceling the submit. I'm running out
> of patients with the stupid thing so I am just going to pull that
> block of code out and forget it,...I went into the database with
> Access and set those Fields to Required=yes and Allow Zero Length=no
> and call it good enough.

Well, that is a good thing to do, but ... I tested the code on my
machine and it definitely generated an error message when I left the
fields blank ... oh, wait! I left out the line I had to add to
initialize the strErrorMsg variable:

strErrorMsg = ""
If If Document.frmNewEntry.txtTitle.Value = ...

>
> I couldn't get the Parameterized querys to work to replace the SQL
> string,..so the SQL String is still there. I'm out of patients there
> too I guess. Maybe I took the code sample in those articles too
> literal and put things in that were only meant to be place holders
> and not actually be in the query's SQL View.
>
> I looked around in Access to figure out what you meant about the
> Keys. I've come to the conclusion that I am clueless,...I have no
> idea what you mean.

Again, you don't have to do it in Access Design View (although it is
certainly possible using the Indexes toolbar button, or going to
View|Indexes in the menu.). Just create a new query in Design view,
close the Choose Tables dialog without choosing a table, switch to SQL
View and paste this sql in:

CREATE UNIQUE INDEX IX_Events_Unique
ON tblEvents (fldStartDate ASC, fldTitle ASC)

Run it. Then open your table in Design View, open the Indexes dialog and
see what it did.

As for the parameterized sql statement, I took the statement that was
the result of your Response.Write:

INSERT INTO tblEvents (fldTitle, fldStartDate, fldEndDate, fldDetails,
fldApproved) VALUES ('This is a Title', #1/12/2012#, #1/13/2012#, 'This
is a
Description', on)

and pasted it into the SQL View of a query in Access. I then replaced
each of the values with a parameter placeholder like this:

INSERT INTO tblEvents (fldTitle, fldStartDate, fldEndDate, fldDetails,

fldApproved) VALUES ([P1], [P2],[P3],[P4],[P5])

and saved the query as qInsertEvent. Then I replaced your vbscript code
with this:

Dim intID
Dim strTitle
Dim strStartDate
Dim strEndDate
Dim strDetails
Dim strApproved
Dim strSQL

strTitle = CleanIllegalChar(Request.Form.Item("txtTitle"))
strStartDate = cDate(Request.Form.Item("txtStartDate"))
strEndDate = cDate(Request.Form.Item("txtEndDate"))
strDetails = CleanIllegalChar(Request.Form.Item("areaDetails"))
strApproved = Request.Form.Item("chkApproved")
If strApproved = "" then strApproved ="False"

'Incidently, you should validate this data here - especially the date
values ...

objConn.qInsertEvent strTitle, strStartDate, strEndDate, strApproved
objConn.close

It works fine for me. What problem did you run into?

--
HTH,
Bob Barrows

Phillip Windell

unread,

Dec 15, 2009, 1:56:40 PM12/15/09

to

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:eBKGC6Zf...@TK2MSFTNGP02.phx.gbl...

> Well, that is a good thing to do, but ... I tested the code on my
> machine and it definitely generated an error message when I left the
> fields blank ... oh, wait! I left out the line I had to add to
> initialize the strErrorMsg variable:
>
> strErrorMsg = ""
> If If Document.frmNewEntry.txtTitle.Value = ...

Ok :-)

> Again, you don't have to do it in Access Design View (although it is
> certainly possible using the Indexes toolbar button, or going to
> View|Indexes in the menu.). Just create a new query in Design view,
> close the Choose Tables dialog without choosing a table, switch to SQL
> View and paste this sql in:
> CREATE UNIQUE INDEX IX_Events_Unique
> ON tblEvents (fldStartDate ASC, fldTitle ASC)
>
> Run it. Then open your table in Design View, open the Indexes dialog and
> see what it did.

OK...

> As for the parameterized sql statement, I took the statement that was
> the result of your Response.Write:
> INSERT INTO tblEvents (fldTitle, fldStartDate, fldEndDate, fldDetails,
> fldApproved) VALUES ('This is a Title', #1/12/2012#, #1/13/2012#, 'This
> is a Description', on)
>
> and pasted it into the SQL View of a query in Access. I then replaced
> each of the values with a parameter placeholder like this:
>
> INSERT INTO tblEvents (fldTitle, fldStartDate, fldEndDate, fldDetails,
> fldApproved) VALUES ([P1], [P2],[P3],[P4],[P5])
> and saved the query as qInsertEvent.

OK...

> strStartDate = cDate(Request.Form.Item("txtStartDate"))
> strEndDate = cDate(Request.Form.Item("txtEndDate"))

> 'Incidently, you should validate this data here - especially the date
> values ...

That's what I was kinda attempting with cDate() but I know it needs to be
better than that. I'll work on that later on.

> objConn.qInsertEvent strTitle, strStartDate, strEndDate, strApproved
> objConn.close
> It works fine for me. What problem did you run into?

I wish I would have kept a better record of what it did but I didn't. I
just remember both the Insert and the Update queries had problems,...and one
of them complained about a Type Mismatch in one of my attempts.

I'll work on these things later today or tomorrow, it has gotten a little
busy around here,...on top of that I am supposed to start "moonlighting" for
a Consultant Company who's been swamped and needs some help. I guess I'm a
glutton for punishment and getting shorter and shorter on time :-)

unread,

Dec 15, 2009, 4:26:44 PM12/15/09

to

Which can effectively be shortened to:

strApproved = NOT strApproved = ""

or for the average minimalist to:

strApproved = strApproved <> ""

or for the extreme minimalist to:

strApproved = strApproved > ""

=============

Some ultraminimalists would even disaprove
of the length of:

strApproved

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Neil Gould

unread,

Dec 15, 2009, 5:06:08 PM12/15/09

to

Phillip Windell wrote:
> Bob found the bad spot in the code and that is fixed,..I've just got
> some other smaller (yet still annoying) issues to iron out. But the
> pressure is off with the dupe thing fixed.
>
> Thanks, Neil
>

Good to hear that your problem is getting resolved.

--
Best,

Neil

Phillip Windell

unread,

Dec 16, 2009, 12:21:19 PM12/16/09

to

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:euwK7ubf...@TK2MSFTNGP02.phx.gbl...

I have it "live" now and so far it seems to do ok. I use NTFS permissions
to control who can use it rather than try to create a built-in
authentication/useraccounts "thing" with the ASP. I've done that before with
other things in the past,...but I'm gettng lazy.

They'll let me know if they have problems. I'll work on perfecting it with
some of the other things we talked about later when I have less things in
different directions going on.

Thanks a bunch Bob!

Later.....

Phillip Windell

unread,

Dec 16, 2009, 1:32:16 PM12/16/09

to

> =============
>
> Some ultraminimalists would even disaprove
> of the length of:
>
> strApproved

I would say I might be an ultraminimalist,...but it would take too long to
say it :-)

P.W.