Solved

INSERT, VALUES are variables

Posted on 2011-03-18
29
365 Views
Last Modified: 2012-05-11
hello again all,

please take a look at my code. the insert statement is built from form variables. I have the INSERT INTO part working fine its the VALUES which im finding difficult.

<cfif isDefined ("form.submit")>
<cfset HeaderNames = (GetProdFields.columnList)>	

<cfset "form" = {} />
<cfset thevalues = "" />

<cfquery name="CreateNewProd" datasource="#request.dsn#" username="#request.dsnuser#" password="#request.dsnpassword#">
INSERT INTO Products (#HeaderNames#)
VALUES (
<cfloop collection="#form#" item="key">
<cfset thevalues &= "form"[key] & "," />
</cfloop>
)

<cfset theValues = left(theValues,len(thevalues)-1) />

</cfquery>
</cfif>

Open in new window


At the moment when i run this i'm getting:

No matching property [fieldnames] found in [string]

I know i probably need to scrap my code completly but i thought i'd post it to show you guys what it is i'm trying to - although it may be so wrong that it may confuse matters further.
0
Comment
Question by:bede123
  • 12
  • 11
  • 6
29 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 35167163
You are actully not putting any values in your VALUES (  )  part of your insert, you set value, but never output them to the cfquery.

You don't have to build the "values"  so I removed the variable values altogether and just looped through the form fields to output their values

I don't know what this is for..
 <cfset "form" = {} />
I have never seen that syntax before, I just removed it.  You know you have a form variable existing because the <CFIF tests to see if form.submit exists

Also note that you may have a primary key field come over in the form field, you probably won't be able to insert that if you have auto increment on, at that point it would be an update, but I suspect that comes later.

This may be more challenging if you have different data types, date and integer.  You also have to be careful about sql injection attacks, you can add <cfqueryparam..> to this to make it safter, but then you will need to know the data type of the column

<cfif isDefined ("form.submit")>
  <cfset HeaderNames = (GetProdFields.columnList)>	
  <cfset isTheFirst= true>
  <cfquery name="CreateNewProd" datasource="#request.dsn#" username="#request.dsnuser#" password="#request.dsnpassword#">
  INSERT INTO Products (#HeaderNames#)
  VALUES (
  <cfloop collection="#form#" item="key">
   <cfif isTheFirst><cfset isTheFirst=false><cfelse>,</cfif>
    '#form[key]#'
  </cfloop>
  )
  </cfquery>
</cfif>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35167172
oh, here's another problem.

For your column list, you are looping every field of your TABLE, for your value list, you are looping every field in the FORM.   What if they don't match exactly?  The form is going to have more items in it than the table.  

Also the loop will be in a different order, you can not guaranteed your form fields will be in your columm list order...
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35167215

<cfif isDefined ("form.submit")>
  <cfset HeaderNames = (GetProdFields.columnList)>	
  <cfset isTheFirst= true>
  <cfquery name="CreateNewProd" datasource="#request.dsn#" username="#request.dsnuser#" password="#request.dsnpassword#">
    INSERT INTO Products (#HeaderNames#)
    VALUES (
  <cfloop index="aCol" list="#HeaderNames#">
   <cfif isTheFirst><cfset isTheFirst=false><cfelse>,</cfif>
   <cfif structKeyExists(form,aCol)>
      <cfif len(trim(form[aCol])) eq 0>
         NULL
      <cfelse>
        '#form[aCol]#'
      </cfif>
   </cfif>
  </cfloop>
    )
  </cfquery>
</cfif>

Open in new window

0
 
LVL 1

Author Comment

by:bede123
ID: 35167423
ok. before i ask you a couple of questions about what you've posted let me show your this.

i ran the code in your last post and i get this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '


,


,


,


)' at line 7

so i kind of looks like the values are still blank?
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 35167559
I see, that means that your form doesn't have any of the columns in it

This version will handle that...


<cfif isDefined ("form.submit")>
  <cfset HeaderNames = (GetProdFields.columnList)>	
  <cfset isTheFirst= true>
  <cfquery name="CreateNewProd" datasource="#request.dsn#" username="#request.dsnuser#" password="#request.dsnpassword#">
    INSERT INTO Products (#HeaderNames#)
    VALUES (
  <cfloop index="aCol" list="#HeaderNames#">
    <cfif isTheFirst><cfset isTheFirst=false><cfelse>,</cfif>
    <cfif structKeyExists(form,aCol) and len(trim(form[aCol]))>
     '#form[aCol]#'
    <cfelse>
      NULL
    </cfif>
  </cfloop>
    )
  </cfquery>
</cfif>

Open in new window

0
 
LVL 1

Author Comment

by:bede123
ID: 35167594
:-)

getting there:

Message Column 'ProdID_1' cannot be null
SQL
INSERT INTO Products (AUTOID,PRODNAME_1,PRODID_1,PRODPRICE_1,PRODDESC_2)
VALUES (



NULL


,

NULL


,

NULL


,

NULL


,

NULL


)
 
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35167623
Did you remove this line as gdemaria mentioned

       <cfset "form" = {} />

? Also, dump the FORM scope at the very top of the page. So you can see what values *are* being passed

       <cfdump var="#FORM#">
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 500 total points
ID: 35167629

It's working..  

Your error is because a column is required.  You didn't supply the value for it, so it is going to give you an error.

This is similar to what I was saying about having integers and date fields, you need to account for data specific variations

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35167641
agx, I don't think there is a form posting to this page yet, just a guess though

bede, try posting your form to the page after filling out the form
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35167649
Ooops. Ignore my last comment.  Didn't see the "len(trim(form[aCol]))" part ;-)

0
 
LVL 1

Author Comment

by:bede123
ID: 35167820
yes i'm posting a form to this page. here is the dump:

 dump
so as you can see, data is coming from the form but you're right it was concerning data types.

i had the column prodID_1 set as 'int'. i have now changed it to varchar.

so how will i account for data specific variations? is this as basic as form validation or something different?



0
 
LVL 1

Author Comment

by:bede123
ID: 35167832
sorry i should ad also that it is now working since i changed the column type to varchar
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35167959
> i had the column prodID_1 set as 'int'. i have now changed it to varchar.

If prodID should be numeric, don't change it to varchar, that doesn't make sense.

When you fill out the form, make sure you enter a number for that field, from the dump it looks like you typed in a string.

To manage it, can have field validation before the post make sure the correct types were entered, or you can do something like this to test..


<cfelseif form[aCol] eq val(form[aCol])>  <--- then it's a number

<cfelse>  <---- its a string (or date)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35168021
Just out of curiosity, why does the form processing code need to be this dynamic?  
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:bede123
ID: 35168344
sorry for slow reply. driving home again.


prodid can/should be a mix, either numeric of text - either or / both

>Just out of curiosity, why does the form processing code need to be this dynamic?

over the past couple of days ive created two pages for a new app.

page one allows me (a user) to create product fields

page two allows me (a user) to input data into those fields and save them to the db.

page three will be the display page where all of this data is displayed.

this is all part of a much larger project that.... has only just begun! :-)


from what i have just explained and from what code you have seen over the past couple of days, do you think i'm going about this the right way?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35168402
There is another way to do this, it may be better, but you can decide based on your application..

Create a table with generic fields, such as String1, String2, String3, Number1, Number2, Number3, etc..

You can create 2, 3 or perhaps 4 different field types:  Short String, Long String (big text field), Number and Date.

Allow the user to see a list of available fields, the user can then "activate" a field by entering the Label, a description (if desired)

Other users can elect to use different fields, it doesn't matter, the fields that are active are on a user-by-user basis.

The approach can make a lot of the parts of the puzzle easier, you just have to manage showing labels because you will understand the type of field, the general size of the field and how to validate it..

I know some professional apps (like Oracle Apps) do it this way

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35168498
If it does need to be completely dynamic, then you'll need more robust validation routines for your forms/queries. Obviously you can't reliably determine the data type of the target columns from examining the form values. You'll probably need to use the db metadata for that.  Something to keep in mind ...
0
 
LVL 1

Author Comment

by:bede123
ID: 35168510
hmm ok, thats somthing to consider. i guess there's not really any limit as to how many generic fields i could have ready.

the user by user detail wont really apply for this app, so that makes things even easier.

having said that... apart from creating fields for images all the hard work with my current 'plan' has been done. the code thats been wriiten over the last few days can be reused and reused, tweeked and reused again.

my brain is ticking over... like a small two stroke engine....;-)
0
 
LVL 1

Author Comment

by:bede123
ID: 35168543
@agx

so i'd query for INFORMATION_SCHEMA and use the returned data to match up against?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35168566

If you're going to stick with your current approach, you could enhance it by storing the field's attributes in a table.

That is, you will still create the field dynamically in a table, but in another table, you will store...

 ColumnName
 Column Data Type
 Max Length
 is Field Required
 Column Label


You can create the column in the table dynamically, then save this information about the field, that will help you know how to dynamically validate the data, ensure required fields are populated and display the propper column lable at the top of reports and such...

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35168620
You could pull some of that information from the database table definition, but that would make it harder to change.  It would require you to alter the table.  If the user wanted to make a field required, or extend it from 30 to 50 characters, just saving that into the  attributes table would manage it.

One field I forgot you could add is the Form-Field Type.

  Do you want the form field to show a textarea box, select box (for a list of values), a short text field, checkboxes, etc...

  And, of course, then you expand it to have a child table of value list of values you can select from SELECT tag or checkboxes, or radio tags.   This is an expandable approach as the client starts asking for more and more... (they always do)

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35168625
It depends on what you're talking about.  Form validation is one thing and db validation another. They're obviously related but have different needs.  My recommendation would be go slowly, and thoroughly think out any design first. Because having done the dynamic form/db table route before, it gets complicated fast - and it's a lot of work to do it well.
0
 
LVL 1

Author Comment

by:bede123
ID: 35168921
yes i'm already using;

ColumnName
 Column Data Type
 Max Length
 is Field Required
 Column Label

except column label.

so when i'm creating a new field i can choose the type, length, required etc etc

>One field I forgot you could add is the Form-Field Type

the way i do this now is by adding the _1 or _2 to the end of the newly created field/column name.

1 = text input
2 - textarea

hence:

<cfloop list="#ColList#" index="theField">
<cfif listLast(theField, "_") eq "1">
         <cfoutput>
            <label for="#theField#">#theField#</label></br>
            <cfinput type="text" name="#theField#"></br>
            </cfoutput>
    <cfelseif listLast(theField, "_") eq "2">
         <cfoutput>
            <label for="#theField#">#theField#</label></br>
            <textarea name="#theField#" rows="20" cols="70"></textarea></br>
            </cfoutput>
    <!---  <cfelse>
          not 1 or 2. do something else</br> --->
     </cfif>


i did think about having a seperate column in the db for field-types but i wasnt sure of how to relate them to the newly created fields/columns

0
 
LVL 1

Author Comment

by:bede123
ID: 35168935
oh you mean actually having an 'attributes' table?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35168990

adding a code to the end of the column name could work, but would limit your flexibility.

 What if the user wanted to change the field type from Radio Box to SELECT tag or from a short text field to a textarea field.  You would have to change the name of the database column which would be a lot of work

>  oh you mean actually having an 'attributes' table?

Yes, in this seperate table you store all the information about the columns, that makes it very flexible when you want to change things..
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35169060
I can see the complexity has kicked in ;-)

i did think about having a seperate column in the db for field-types but i wasnt sure of how to relate them to the newly created fields/columns


What is your actual table schema now?
0
 
LVL 1

Author Comment

by:bede123
ID: 35169134
pheeewy, my life line was down ...... (EE maintenance)

>What is your actual table schema now?

the db isnt really worth talking about at the moment. it isnt anything... one table, a few columns with a few rows of data.

i'm still at the stage where i i could stop and start going down a different track....

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35169996

My feeling is to maintain a table of attributes about the columns created.  These are like pointers to the table that actually contains the data.   So you have (as an example)  TableAttributes  and TableData.     TableData is the one that comtains the user's data.  You can create dynamic fields there if you like, but I don't see a big advantage to creating custom column names.  MIght as well predetermine the column names; whether the table is prepopulated is 20-30 or more columns or if you create the columns one by one as the user needs them, I guess that's not much of a difference.   But having a predetermined column name and a table of attributes to help you manage it, I think would make the process smoother and less complex.

But as agx said, the complexity is coming, this is not trivial..

0
 
LVL 1

Author Comment

by:bede123
ID: 35179787
just wanted to say thanks for all your help. i spent the weekend trying to visually draw this out and i think i've made some decisions

zac
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now