"Invalid Data" Error When Editing Records

[sysadmin07]

Hi,

I have a CF page that allows me to edit records in a MS Access database (the primary key in the database is autonumber-based). If I try to edit one row of the database from my CF page, everything works fine. However, if more than one row needs edited and is displayed on my CF page, upon clicking the "Submit" button (which invokes a cfupdate page) I get the following error (where, in this case, 1 and 2 are the primary keys of two, separate rows in the database):

Invalid data 1,2 for CFSQLTYPE CF_SQL_INTEGER

Any ideas why this may be happening?

Thank you.

 

[GUJUm0deL]

How are you updating multiple records at once? Are you doing a cfloop? Post some code so we can look.

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[sysadmin07]

Here is an example of how I'm displaying the records:

<CFQUERY NAME="displayData" DATASOURCE="test">
SELECT * FROM tbltest WHERE recordID='#session.recordID#'
</CFQUERY>

Upon submission, the page utilizes an "action" page to use the code below to perform the update:

<cfupdate datasource="test" tablename="tbltest">

Here's an example of my table layout in Access:

Primary Key Row1 Row2
1 r1 r2
2 r1 r4

I'm using a session variable to, for example, display all rows with a value of "r1." In this case, two rows would be displayed on my page, which could be edited. However, when I perform the edit, I get the error listed in my initial post.

Thank you

 

[TruthInSatire]

why are you using cfupdate as opposed to cfquery and an update query?

 

[sysadmin07]

To be honest, I've never used an update query before. Also, all of my other pages use a cfupdate command. I'd be willing to research the function and implement it, if it's the cause for my problem. If not, please let me know if you have any other ideas.

Thanks.

 

[sysadmin07]

Anyone have any more ideas?

Thanks a lot!

 

[TruthInSatire]

what exactly are you trying to update?

http://www.w3schools.com/sql/sql_update.asp

 

[GUJUm0deL]

First of all, I'd stay away from doing select * coding practice. Type in all fieldnames instead.

Secondly, you're displaying two rows both of which the user has the ability to update, right? You're going to need a <cfloop> to loop over the field and update the record accordingly.

Something like this:

<!--- LOOP OVER SESSION VAR WHICH DISPLAYS ALL ROWS WITH VALUE "r1" --->
<cfloop index="Gm" list="#SESSION.varname#">
	<cfquery name="updateREC" datasource="#default_ds#">
		UPDATE	myTable
		SET		Row1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Gm#">
		WHERE	PrimaryKey = #FORM.PK#
	</cfquery>
</cfloop>

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[sysadmin07]

Thanks, GUJUm0deL. The number of rows displayed is dependent upon the amount of records equal to, in this example, "r1" so there could be more than two. Should I place your code on the action page, in place of the cfupdate? Also, what does the cfsqltype perform? I'm not using sql in this application.

 

[GUJUm0deL]

The code above would be on the action page, replaced by the <cfupdate> tag. Avoid using <cfupdate> all together, use <cfquery> tag instead.

Using <cfqueryparam> helps make your code cleaner, faster and protects you from SQL injections (this is a hacking attempt whereby a user tricks your code into deleting your tables and records).

I'm not using sql in this application.

You are using SQL. If you're using any database (MS SQL Server, Access, Oracle, etc) your using SQL. Anytime you use SELECT statement, you're using SQL code.

As always, perform tests in a dev environment and avoid using live data.

cfqueryparam


_____________________________
Just Imagine.

http://www.imajinarts.com

 

[GUJUm0deL]

BTW, how is your form set up? How are you identifying one row from another? Are you using a unique identifier like a primary key? Is it a hidden field or part of the display? Can you post the form?

I ask because chances are list="#SESSION.varname#" might need to be replaced with that unique identifier instead.

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[sysadmin07]

The unique identifier is a non-primary key field. If multiple rows have the same unique identifier, then they will be displayed on the form. Unfortunately, I can't post the form, as it contains proprietary information.

Thank you for your help. I will report back, based upon my progress.

 

[GUJUm0deL]

Good luck.

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[sysadmin07]

I've come across another issue now. On my form page, if I display all rows that contain the value "r1", and then submit to my action page, the variable "PrimaryKey" contains "1,2", "Column 1" contains "r1,r1" and "Column2" contains "r2,r4". When I'm looping through the values on the action page to update each row, how do I "tell" ColdFusion to traverse and update row 1's values and then proceed to row 2?

PrimaryKey Column1 Column2
1 r1 r2
2 r1 r4
3 r2 r5

 

[sysadmin07]

Does anyone please have any ideas from my question in the above post? I've been researching the issue and I can't determine a way to traverse row by row, when the values are tied to a single variable and separated by commas.

Thanks

 

[philhege]

The trick is to name the fields uniquely in your form construction. Append the primary key to the field name, then parse it in your action page.

<form ...>
<cfoutput query = "myquery">
<tr><td><input type="hidden" name ="primarykey_#primarykey#" value="#primarykey#"></td>
<td><input type="text" name="column1_#primarykey#"
value="#column1#">

etc.



Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[GUJUm0deL]

That's pretty much what I was trying to ask you the first time around.

BTW, how is your form set up? How are you identifying one row from another? Are you using a unique identifier like a primary key? Is it a hidden field or part of the display?

Sorry, I've been battling the flu for the last several days...almost have it killed, lol

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[sysadmin07]

Thanks, that sounds like it might work. However, when adding the revisions, as outlined in Phil's post, I receive the error "The given fieldname "primarykey_1" could not be found in the table "tbltest"." I believe this is because the name of the field in the database (which I cannot change due to it's other uses within our organization) is "primarykey", not "primarykey_1". Is it possible to do so with my pre-existing field names?

PS I hope you're feeling better, GUJUm0deL.

 

[sysadmin07]

Got it working! Was related to the code on my action page.

Thanks very much for your help

 

[GUJUm0deL]

We're glad.



_____________________________
Just Imagine.

http://www.imajinarts.com