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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Phillip Windell wrote:
"Bob Barrows" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:u0vjMYSfKHA.4636@xxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: problem with apostrophe in search criteria
    ... SQL this way, because it could lead to SQL injection attack from the ... Dim Param1 As ADODB.Parameter ... I want to open a database through the Open command> and create a REcordset based on specific records linked to names. ... When I enter this name in the WHERE clause of> the filter I keep getting an error message. ...
    (microsoft.public.vb.database.ado)
  • Re: Error: operation not allowed when object is closed
    ... msgbox CN.state ... > I'm getting an error message saying "operation not allowed when object> is closed." ... > DIM Param1 ... > msgbox SQL ...
    (microsoft.public.scripting.vbscript)
  • RE: Run a Stored Procedure from Excel
    ... The SQL seperator may be a colon instead of a semicolon. ... get a single line multiple command working from ... I changed all the Charto Charand found that I got an error message ... Dim cnnDW As ADODB.Connection ...
    (microsoft.public.excel.programming)
  • Re: Return ID value of inserted record, and populate text box
    ... Dim rsSTUDENT_ID As ADODB.Recordset ... a SQL command to insert a new record into table "STUDENTS", ... Mabye because this is .MDB? ... The error message says "Compile error: ...
    (microsoft.public.access.formscoding)
  • Re: Which identity?
    ... > Some postings I read suggested using MAXto retrieve the inserted record ... > each of these 3 SQL features? ... Another way to do it if using the .AddNew method on a Jet based ADO ... Dim rsTest As ADODB.Recordset ...
    (microsoft.public.access.queries)