[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.frontpage.programming

Database problem

=?Utf-8?B?cm9kY2hhcg==?=

4/21/2004 1:05:00 PM

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
6 Answers

Net55

4/21/2004 4:46:00 PM

0

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

Net55

net55@frontpagewiz.com

http://www,frontpagewiz.com

>-----Original Message-----
>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
>.
>

=?Utf-8?B?cm9kY2hhcg==?=

4/21/2004 5:34:00 PM

0

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


>-----Original Message-----
>You probaly need to post the code, but try opening
Access?
>db up, goto design view and change allow zero lenght to
>Yes.
>
>Net55
>
>net55@frontpagewiz.com
>
>http://www,frontpagewiz.com
>
>>-----Original Message-----
>>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
>>.
>>
>.
>

news

4/21/2004 5:41:00 PM

0

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.inter...
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)
|/---------------------------------------------------
*----------------------------------------------------


"Bonnie" <anonymous@discussions.microsoft.com> wrote in message news:<22ee01c427a1$4ca02c80$a001280a@phx.gbl>...
> 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

=?Utf-8?B?cm9kY2hhcg==?=

4/21/2004 5:43:00 PM

0

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
>-----Original Message-----
>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,OrgCod
e
>,Title,EmpID,Phone,SSNumber,Location,NetworkAcct,Outlook,T
S
>O,IMS,CADD,RUMS,BAMS,PPMS,Dialup,HTRIS,EMS,PMIS,PONTIS,FMS
I
>I,LUPS,VAX,COMPLETE,DATAWAREHOUSE,OTHER,Comments,Superviso
r
>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
>
>
>>-----Original Message-----
>>You probaly need to post the code, but try opening
>Access?
>>db up, goto design view and change allow zero lenght to
>>Yes.
>>
>>Net55
>>
>>net55@frontpagewiz.com
>>
>>http://www,frontpagewiz.com
>>
>>>-----Original Message-----
>>>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
>>>.
>>>
>>.
>>
>.
>

=?Utf-8?B?cm9kY2hhcg==?=

4/21/2004 6:34:00 PM

0

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

Thanks!
Bonnie

>-----Original Message-----
>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.inter...
>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)
>|/---------------------------------------------------
>*----------------------------------------------------
>
>
>"Bonnie" <anonymous@discussions.microsoft.com> wrote in
message news:<22ee01c427a1$4ca02c80$a001280a@phx.gbl>...
>> 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

4/21/2004 8:10:00 PM

0

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
>>-----Original Message-----
>>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,OrgCo
d
>e
>>,Title,EmpID,Phone,SSNumber,Location,NetworkAcct,Outlook,
T
>S
>>O,IMS,CADD,RUMS,BAMS,PPMS,Dialup,HTRIS,EMS,PMIS,PONTIS,FM
S
>I
>>I,LUPS,VAX,COMPLETE,DATAWAREHOUSE,OTHER,Comments,Supervis
o
>r
>>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
>>
>>
>>>-----Original Message-----
>>>You probaly need to post the code, but try opening
>>Access?
>>>db up, goto design view and change allow zero lenght to
>>>Yes.
>>>
>>>Net55
>>>
>>>net55@frontpagewiz.com
>>>
>>>http://www,frontpagewiz.com
>>>
>>>>-----Original Message-----
>>>>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
>>>>.
>>>>
>>>.
>>>
>>.
>>
>.
>