Programming Help

Web Development



What's Here?

  • Members: 136,848
  • Replies: 480,556
  • Topics: 74,911
  • Snippets: 2,567
  • Tutorials: 677
  • Total Online: 1,636
  • Members: 83
  • Guests: 1,553

Welcome to Dream.In.Code
Getting Help is Easy!

Join 136,848 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,636 people online right now. Registration is fast and FREE... Join Now!




SQL inserting blanks or default value into database

 
 Closed TopicStart new topic

SQL inserting blanks or default value into database
bonneylake
22 Jul, 2008 - 06:58 AM
Post #1

New D.I.C Head
*

Joined: 22 Jul, 2008
Posts: 37
Hey Everyone,

Well i am not sure i have this in the right section. If i don't i am sorry. Just not sure which section to put this question in.

What i am doing is i am using ajax to add multiple fields to my form.Like say the field is serialnum. They can add multiple serialnum to the same form. An the form works great, however, when i submit the form an the information goes into my database is when i run into problems.

In my form a lot of the fields aren't required so people can leave them blank.
So they can add serialnum multiple times an leave one blank, another not blank and such on.
However, for some reason the database will not accept the blanks or show blanks.

So lets say i have entered in
serial num 1: Blank serial num 2: dog

when i go to look at my results it displays it as
serial num 1: dog serial num 2: blank

i have also run into the problem where i am using a cfloop to loop through my results so that instead of putting
every serial i add into one record in my database it will put the information
into multiple records.However, this is what i run into

so lets say i have entered in
serial num 1: Blank serial num 2: dog

when i go to look at my results it displays it as
serial num 1: dog serial num 2: dog

I am basically wondering how can i get my database to except blanks or
maybe even just a default value?

Thank you,
Rachel

CODE
<cfloop index="x" list="#Form.serialnum#" delimiters=",">
<CFQUERY Name="serial" DataSource="CustomerSupport">
   exec usp_CS_Insertserial
      '#x#','#Form.ID#','#Form.modelno#','#Form.producttype#','#Form.softhardware#',
  '#Form.resolution#','#Form.resdate#','#Form.resvertified#','#Form.vertifidate#','#Form.deptvendor#','#Form.hardwarefailure#','
   #Form.rma#'
   </CFQUERY>
</CFLOOP>
User is offlineProfile CardPM
+Quote Post

PsychoCoder
RE: SQL Inserting Blanks Or Default Value Into Database
22 Jul, 2008 - 07:03 AM
Post #2

using DIC.Core;
Group Icon

Joined: 26 Jul, 2007
Posts: 8,997



Thanked: 126 times
Dream Kudos: 8625
Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net

My Contributions
Moved to ColdFusion smile.gif
User is online!Profile CardPM
+Quote Post

garyrgilbert
RE: SQL Inserting Blanks Or Default Value Into Database
22 Jul, 2008 - 10:14 AM
Post #3

New D.I.C Head
*

Joined: 22 Jul, 2008
Posts: 4
Hi Rachel

I think the problem may stem from the way you are submitting your multiple serialnum.

If you are dynamically adding the serialnumbers and the name of the input field doesn't change (same input name for each field) the values get submitted to the server as a list so your form field would look something like this

serialnum:43123,,829382,128976

The 'problem' is that Coldfusion ignores empty list elements so your list actually contain 3 items instead of 4.

The way around this is to use the split() function. It's undocumented but you can use that to turn your list into an array.

example:
CODE

<cfset lList = "1,2,,4,5,6,,">
<cfdump var="#lList.split(',')#"/>




Cheers!

Gary Gilbert
http://www.garyrgilbert.com/tutorials

This post has been edited by garyrgilbert: 22 Jul, 2008 - 10:15 AM
User is offlineProfile CardPM
+Quote Post

bonneylake
RE: SQL Inserting Blanks Or Default Value Into Database
22 Jul, 2008 - 12:02 PM
Post #4

New D.I.C Head
*

Joined: 22 Jul, 2008
Posts: 37
Mr. Gilbert,

Thank you for responding to my post. I have been trying for a week to find someone who understands my problem. I was only able to find one thing online that helped me understand my problem, which was at JustinTubbs.com.
However, with his idea i was not able to apply it to my particular project.

But anyway i tried your idea an well i can not seem to get it to work right. I tried to do it a bit different then your example. Trying to leave it in a cfloop so that each entry i add into the database instead of it all going in one record it goes in as multiple records.

If you could please tell me what i am doing wrong i would appreciate it.

Thank you,
Rachel


The error i got was ColdFusion was unable to determine the value of the parameter

CODE
<cfloop index="x" list="#Form.serialnum#" delimiters=",">
<CFQUERY Name="serial" DataSource="CustomerSupport">
   exec usp_CS_Insertserial
      '#x#','#Form.ID#','#Form.modelno#','#Form.producttype#','#Form.softhardware#',
  '#Form.resolution#','#Form.resdate#','#Form.resvertified#','#Form.vertifidate#','#Form.deptvendor#','#Form.hardwarefailure#','
   #Form.rma#'
   </CFQUERY>
</CFLOOP>
<cfdump var="#x.split(',')#"/>





QUOTE(garyrgilbert @ 22 Jul, 2008 - 11:14 AM) *

Hi Rachel

I think the problem may stem from the way you are submitting your multiple serialnum.

If you are dynamically adding the serialnumbers and the name of the input field doesn't change (same input name for each field) the values get submitted to the server as a list so your form field would look something like this

serialnum:43123,,829382,128976

The 'problem' is that Coldfusion ignores empty list elements so your list actually contain 3 items instead of 4.

The way around this is to use the split() function. It's undocumented but you can use that to turn your list into an array.

example:
CODE

<cfset lList = "1,2,,4,5,6,,">
<cfdump var="#lList.split(',')#"/>




Cheers!

Gary Gilbert
http://www.garyrgilbert.com/tutorials
User is offlineProfile CardPM
+Quote Post

sansclue
RE: SQL Inserting Blanks Or Default Value Into Database
27 Jul, 2008 - 06:37 PM
Post #5

D.I.C Head
**

Joined: 21 Nov, 2007
Posts: 113



Thanked: 7 times
My Contributions
Rachel,

I think Gary is suggesting that you turn your list into an array and then loop through the array instead of the list.

CODE

<!--- split the values into an array --->
<cfset serialNumArray = Form.serialnum.split(",")>

<!--- loop through the array and insert each value --->
<cfloop from="1" to="#arrayLen(serialNumArray)#" index="x">
    <cfset currentSerialNumber = serialNumArray[x]>    
    
       <CFQUERY Name="serial" DataSource="CustomerSupport">
           exec usp_CS_Insertserial
                  '#currentSerialNumber #',
                  '#Form.ID#',

                  .... etc ....
       </CFQUERY>

</cfloop>


This post has been edited by sansclue: 27 Jul, 2008 - 06:38 PM
User is offlineProfile CardPM
+Quote Post

bonneylake
RE: SQL Inserting Blanks Or Default Value Into Database
31 Jul, 2008 - 05:33 AM
Post #6

New D.I.C Head
*

Joined: 22 Jul, 2008
Posts: 37
Sansclue,

Sorry i did not respond to your post till now. I ended up finding someone who helped me with my problem.

In my javascript code i had a part called count in there an i only had used count to display what serial number they was adding like serial number 1, serial number 2.heres the javascript code i used if you want to check it out
http://www.dustindiaz.com/add-and-remove-h...ith-javascript/. But anyway
I didn't realize that i had to have count in every single section that i added a multiple field to like for example

CODE
<select name='modelno_" + count + "' >
.

He then added a hidden field to the end of it all
CODE
"<input type='hidden' name='serialcount' value='" + count + "'>";


an then in my action page we did this

CODE

<CFIF REQUEST_METHOD EQ "POST">
<CFSET machineListLen = listLen(Form.serialcount)>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
<CFSET serialnum       = "Form.serialnum_" & machineCount>
<CFSET modelno         = "Form.modelno_" & machineCount>
<CFSET producttype     = "Form.producttype_" & machineCount>
<CFSET softhardware    = "Form.softhardware_" & machineCount>
<CFSET resolution      = "Form.resolution_" & machineCount>
<CFSET resdate         = "Form.resdate_" & machineCount>
<CFSET resvertified    = "Form.resvertified_" & machineCount>
<CFSET vertifidate     = "Form.vertifidate_" & machineCount>
<CFSET deptvendor      = "Form.deptvendor_" & machinecount>
<CFSET hardwarefailure = "Form.hardwarefailure_" & machineCount>
<CFSET rma             = "Form.rma_" & machineCount>
<CFSET thedescription  = "Form.thedescription_" & machineCount>
  <CFSET text  = "Form.text_" & machineCount>
   <CFSET attachment1  = "Form.attachment1_" & machineCount>

<CFQUERY Name="serial" DataSource="CustomerSupport">
   exec usp_CS_Insertserial
      '#evaluate(serialnum)#','#Form.ID#','#evaluate(modelno)#','#evaluate(producttype)#','#evaluate(softhardware)#',
      '#evaluate(resolution)#','#evaluate(resdate)#','#evaluate(resvertified)#','#evaluate(vertifidate)#','#evaluate(deptvendor)#',
  '#evaluate(hardwarefailure)#','#evaluate(rma)#'
   </CFQUERY>
</CFLOOP>
</CFIF>


so basically the same idea you had. But i wanted to post this in case someone else runs into a similar problem as me an also so you an gary know i found a solution. But thank you both so much for all the help

Rachel
User is offlineProfile CardPM
+Quote Post

sansclue
RE: SQL Inserting Blanks Or Default Value Into Database
31 Jul, 2008 - 03:49 PM
Post #7

D.I.C Head
**

Joined: 21 Nov, 2007
Posts: 113



Thanked: 7 times
My Contributions
Rachel,

Thanks for posting the outcome.

QUOTE(bonneylake @ 31 Jul, 2008 - 06:33 AM) *

He then added a hidden field to the end of it all
CODE
"<input type='hidden' name='serialcount' value='" + count + "'>";


an then in my action page we did this
CODE

...
<CFSET machineListLen = listLen(Form.serialcount)>
<CFLOOP from="1" to="#machineListLen#" index="machineCount">
<CFSET serialnum       = "Form.serialnum_" & machineCount>
<CFSET modelno      = "Form.modelno_" & machineCount>
...



I think they have the right idea, but the code could be simplified by using array notation instead of evaluate. Generally, you should avoid evaluate() when not not needed ... and it not needed in this case. Supposedly evaluate() does a bit of extra processing over other methods and may be a little slower. That is the general recommendation in the documentation anyway ...

Also, you should seriously consider using cfqueryparam. One of its benefits is the usage of bind variables. Bind variables can help increase the performance of queries that are executed multiple times, or inside a loop, as you are doing here.


CODE

<CFLOOP from="1" to="#machineListLen#" index="machineCount">
    <CFQUERY Name="serial" DataSource="CustomerSupport">
         exec usp_CS_Insertserial
         '#form["serialnum"&_ machineCount)#',
             '#Form.ID#',
             '#form["modelno_"& machineCount#',
             .... more fields ...
   </CFQUERY>
</CFLOOP>
</CFIF>


QUOTE

CODE

<input type='hidden' name='serialcount' value='" + count + "'>";
....
<CFSET machineListLen = listLen(Form.serialcount)>



I do not see the value of creating multiple fields to store the "total count" of machines. Just use a single field. Each time the javascript function adds a field, it should update the total machine count. Then on your action page use the total value. Your form would work either way. It just seems strange to store 20 values when all you need is 1 ;-)

CODE

<CFLOOP from="1" to="#form.totalMachineCount#" index="machineCount">
....
</CFLOOP>


HTH
User is offlineProfile CardPM
+Quote Post

Closed TopicStart new topic
Time is now: 12/3/08 06:15PM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month