Database problem

[Bonnie]

As previously posted, I had a form that sent its results
to a database. This worked fine but I also needed to send
the results by email. THANKS to Jim Buyens for helping
with my previous post, I have written some ASP code that
does this for me. The problem I have now is with the
database portion of the script. The form contains a large
number of option groups with three option buttons per
group. The user will pick and choose only a few options
out of the groups (most will be null). In my script, I am
using an INSERT statement that inserts data from the form
into the table. The problem occurs (I think) because I am
not sending values for all the fields (since most are null
coming out of the form). Is there any way to get this to
work without building in some hellacious nested IF
statements to check to see if there's a value in all the
fields before including the field in the INSERT?

THANKS for your help!
Bonnie

 

[Net55]

You probaly need to post the code, but try opening Access?
db up, goto design view and change allow zero lenght to
Yes.

Net55

(e-mail address removed)

http://www,frontpagewiz.com

 

[Bonnie]

You have a point with the Allow Zero Length property
because the Northwind database form/database example
works, even when all fields are not filled in, and I guess
that's why! But here's my code just in case you see
something that I missed:

<%
Dim myConnString
Dim myConnection
Dim mySQL
myConnString = Application
("SecurityAccess_ConnectionString")
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open myConnString
mySQL= "INSERT INTO Results "
mySQL= mySQL
& "(EmpType,FirstName,MiddleInitial,LastName,FMS2ID,OrgCode
,Title,EmpID,Phone,SSNumber,Location,NetworkAcct,Outlook,TS
O,IMS,CADD,RUMS,BAMS,PPMS,Dialup,HTRIS,EMS,PMIS,PONTIS,FMSI
I,LUPS,VAX,COMPLETE,DATAWAREHOUSE,OTHER,Comments,Supervisor
Name,SupervisorEmail) "
mySQL= mySQL & "VALUES ('" & Request.Form("EmpType")
& "','"
mySQL= mySQL & Request.Form("FirstName") & "'"
mySQL= mySQL & ",'" & Request.Form("MiddleInitial") & "'"
mySQL= mySQL & ",'" & Request.Form("LastName") & "'"
mySQL= mySQL & ",'" & Request.Form("FMS2ID") & "'"
mySQL= mySQL & ",'" & Request.Form("OrgCode") & "'"
mySQL= mySQL & ",'" & Request.Form("Title") & "'"
mySQL= mySQL & ",'" & Request.Form("EmpID") & "'"
mySQL= mySQL & ",'" & Request.Form("Phone") & "'"
mySQL= mySQL & ",'" & Request.Form("SSNumber") & "'"
mySQL= mySQL & ",'" & Request.Form("Location") & "'"
mySQL= mySQL & ",'" & Request.Form("NetworkAcct") & "'"
mySQL= mySQL & ",'" & Request.Form("Outlook") & "'"
mySQL= mySQL & ",'" & Request.Form("TSO") & "'"
mySQL= mySQL & ",'" & Request.Form("IMS") & "'"
mySQL= mySQL & ",'" & Request.Form("CADD") & "'"
mySQL= mySQL & ",'" & Request.Form("RUMS") & "'"
mySQL= mySQL & ",'" & Request.Form("BAMS") & "'"
mySQL= mySQL & ",'" & Request.Form("PPMS") & "'"
mySQL= mySQL & ",'" & Request.Form("Dialup") & "'"
mySQL= mySQL & ",'" & Request.Form("HTRIS") & "'"
mySQL= mySQL & ",'" & Request.Form("EMS") & "'"
mySQL= mySQL & ",'" & Request.Form("PMIS") & "'"
mySQL= mySQL & ",'" & Request.Form("PONTIS") & "'"
mySQL= mySQL & ",'" & Request.Form("FMSII") & "'"
mySQL= mySQL & ",'" & Request.Form("LUPS") & "'"
mySQL= mySQL & ",'" & Request.Form("VAX") & "'"
mySQL= mySQL & ",'" & Request.Form("COMPLETE") & "'"
mySQL= mySQL & ",'" & Request.Form("DATAWAREHOUSE") & "'"
mySQL= mySQL & ",'" & Request.Form("OTHER") & "'"
mySQL= mySQL & ",'" & Request.Form("Comments") & "','"
mySQL= mySQL & Request.Form("SupervisorName") & "','"
mySQL= mySQL & Request.Form("SupervisorEmail") & "')"

myConnection.Execute mySQL
myConnection.Close
Set myConnection = Nothing

%>

THANKS!!
Bonnie

 

[Jim Buyens]

When you build your INSERT statement, you'll need to code one
field name and one field value for each option group. Here's
an example, assuming the database fields are character:

SQL = "INSERT MYTABLE (PLANES, TRAINS, AUTOS" & _
") VALUES (" & _
"'" & request("optPlanes") & "'," & _
"'" & request("optTrains") & "'," & _
"'" & request("optAutos") & "') "

If the value of optPlanes can be high, medium, or low, the
PLANES database field will then contain an empty string,
high, medium, or low.

If the values are numeric, code:

SQL = "INSERT MYTABLE (PLANES, TRAINS, AUTOS" & _
") VALUES (" & _
"0" & request("optPlanes") & "," & _
"0" & request("optTrains") & "," & _
"0" & request("optAutos") & ") "

Then, if the value of optPlanes can be 1, 2, or 3, the
PLANES database field will then contain 0, 1, 2, or 3.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Guest]

I changed the Allow Zero Property on all the fields that
weren't required and it worked great. THANKS for your
help!! It's always the little things that get ya!!

BTW, does anyone know a good tool to debug script?
Frontpage just aint gettin' it!!

Thanks
Bonnie

 

[Guest]

Turns out it was the Allow Zero Length property in the db
that was causing it to bomb.

Thanks!
Bonnie

 

[Net55]

MS Script Editor, it's free

-----Original Message-----
I changed the Allow Zero Property on all the fields that
weren't required and it worked great. THANKS for your
help!! It's always the little things that get ya!!

BTW, does anyone know a good tool to debug script?
Frontpage just aint gettin' it!!

Thanks
Bonnie d
T
S
o
.