Incorrect number of columns in row?
World Synergy
I am trying to get my <cfhttp> to work as a query, and after quite a while of trying to get it to work I am now running into this error:
Incorrect number of columns in row
Please check the number of columns specified in the columns attribute and in the target file
I have 7 columns both on my page and on the data page. I did find reply to this in another forum but they did not actually address the issue they just stated that:
" Anyway, did you know that CFHTTP can automatically turn a comma-delimited file into a query object for you?.....
Note: this only works if your columns do not contain a carriage return!
You get a "Incorrect number of columns in row"
For example: if you have a webapplication with database that has a memo-field (html textfield) and you have one or more carriage returns in it, these are (ofcourse) also stored in the database.
Now if you cfhttp to a webpage that returns these database-results in a html-page, you get a page with extra carriage returns at the position of the carriage return from the memo-field. Every line in the http-response must be one row!.....
Do you know of anyway around this bug?
.... When you're programming, all the variables you juggle around are doing similar tricks as they present one face to you and a totally different one to the machine. .....
Each Stack Frame represents a function. The bottom frame is always the main function, and the frames above it are the other functions that main calls. At any given time, the stack can show you the path your code has taken to get to where it is. The top frame represents the function the code is currently executing, and the frame below it is the function that called the current function, and the frame below that represents the function that called the function that called the current function, and so on all the way down to main, which is the starting point of any C program."
What doe that mean? Can I create a query out of this? Here is my code:
<cfparam name="PageNum_tabledata" default="1">
<cfhttp url="http://www.etanksystems.net/get/getPage.asp?UserID=culcadmin&Password=genesis&days=&page=15"
method="get"
columns="MonitorId,ReadingDate,ReadingTime,Capacity,Units,AmtFull,PercentFull"
name="tabledata1"
firstrowasheaders="yes"
resolveurl="yes"
delimiter=","
textqualifier="""">
</cfhttp>
<cfset tabledata.RecordCount=0>
<cfset MaxRows_tabledata=10>
<cfset StartRow_tabledata=Min((PageNum_tabledata-1)*MaxRows_tabledata+1,Max(tabledata.RecordCount,1))>
<cfset EndRow_tabledata=Min(StartRow_tabledata+MaxRows_tabledata-1,tabledata.RecordCount)>
<cfset TotalPages_tabledata=Ceiling(tabledata.RecordCount/MaxRows_tabledata)>
<cfquery name="tabledata" dbtype="query">
SELECT MonitorId, ReadingDate, ReadingTime, Capacity, Units, AmtFull, PercentFull
FROM tabledata1
</cfquery>
<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<table border="0" cellpadding="0">
<tr>
<td>MonitorId</td>
<td>ReadingDate</td>
<td>ReadingTime</td>
<td>Capacity</td>
<td>Units</td>
<td>AmtFull</td>
<td>PercentFull</td>
</tr>
<cfoutput query="tabledata" startRow="#StartRow_tabledata#" maxRows="#MaxRows_tabledata#">
<tr>
<td>#tabledata.MonitorId#</td>
<td>#tabledata.ReadingDate#</td>
<td>#tabledata.ReadingTime#</td>
<td>#tabledata.Capacity#</td>
<td>#tabledata.Units#</td>
<td>#tabledata.AmtFull#</td>
<td>#tabledata.PercentFull#</td>
</tr>
</cfoutput>
</table>
</body>
</html>
World Synergy
Query Of Queries runtime error.
Table named "tabledata1" was not found in Memory. It is misspelled, or the table is not defined.
The error occurred in E:\tanks.culc.com\ROOT\tanks\etanksURL.cfm: line 16
14 : <cfset TotalPages_tabledata=Ceiling(tabledata.RecordCount/MaxRows_tabledata)>
15 :
16 : <cfquery name="tabledata" dbtype="query">
17 : SELECT MonitorId, ReadingDate, ReadingTime, Capacity, Units, AmtFull, PercentFull
18 : FROM tabledata1
singleantler
So, instead of using CFHTTP and reading it as a query, below I've written out how you can do the same thing parsing the data yourself by using a couple of loops and treating everything as a list. The key to being able to do this is the 'Replace' part which takes out the spaces that shouldn't be there and replaces them with line breaks (the #chr(10)##chr(13)# codes)
This works OK for me, so hopefully you should be able to use it.
<!--- Grab the page --->
<cfhttp url="http://www.etanksystems.net/get/getPage.asp?UserID=culcadmin&Password=genesis&days=&page=15"
method="get"
resolveurl="yes">
<!--- Replace the space break with a line break to sort out formatting --->
<CFSET pagedata = Replace(CFHTTP.filecontent, " ", "#chr(10)##chr(13)#")>
<!--- Put in the HTML for your table --->
<table border="0" cellpadding="0">
<tr>
<td>MonitorId</td>
<td>ReadingDate</td>
<td>ReadingTime</td>
<td>Capacity</td>
<td>Units</td>
<td>AmtFull</td>
<td>PercentFull</td>
</tr>
<CFOUTPUT>
<!--- Loop through the data as a big list, taking the line break as the delimiter of the list --->
<CFLOOP LIST="#pagedata#" INDEX="OverLoop" DELIMITERS="#chr(10)##chr(13)#">
<tr>
<!--- Take the line and treat it as another list, this time with "," as the delimiter --->
<CFLOOP LIST="#OverLoop#" INDEX="LineLoop" DELIMITERS='","'>
<td>#LineLoop#</td>
</CFLOOP>
</tr>
</CFLOOP>
</table>
</CFOUTPUT>
As you want to display the data in the same order as the grabbed file, you can use a loop to go through the line of data - the CFLOOP LIST="#OverLoop#" INDEX="LineLoop" line. If you want it in a different order you don't have to use this inner loop, you could just use #ListGetAt(LineLoop, 4, '","')# where 4 is the place in the list.
So you could have:
<CFLOOP LIST="#pagedata#" INDEX="OverLoop" DELIMITERS="#chr(10)##chr(13)#">
<tr>
<td>#ListGetAt(LineLoop, 4, '","')#</td>
<td>#ListGetAt(LineLoop, 2, '","')#</td>
<td>#ListGetAt(LineLoop, 7, '","')#</td>
</tr>
</CFLOOP>
Hope this helps
Paul
Paul Silver - Freelance Web Developer
ColdFusion articles and code: http://www.paulsilver.co.uk/code/
CFDEBUG
<cfoutput query="tabledata1">
#readingdate#
</cfoutput>
Also a suggestion. When you post your code, make sure you remove any references to any user id or password. You have a user id and password in your code which I suggest you remove it just for security purposes. You never know who checks these forums.
CFDEBUG
World Synergy
Thank you for the heads up on the password, I usually remember to x that out. Oops.
I tried what you suggested which is what I thought I should be able to do in the first place and I am getting this error:
"Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently "tabledata1", is invalid. "
Here is my code:
<html>
<head>
<title>Use Get Method</title>
</head>
<body>
<cfhttp
method="Get"
url="http://www.etanksystems.net/get/getPage.asp?UserID=xxxx&Password=xxxx&days=1&page=15"
resolveurl="Yes"
delimiter=","
textqualifier=""""
columns="Monitorid,Capacity,readingdate,Amountfull,MonDate,MonTime,Temperature">
</cfhttp>
<cfoutput query="tabledata1">
#Monitorid#
</cfoutput>
</body>
</html>
World Synergy
Thanks for the input. The first part of the code worked great! I haven't quite figred out the second part of the code, and that part may be what I need. However my problem is I need to be able to manipulate this data, I need to be able to sort the columns and to be able to select parameters from a dropdown list (see why I think I need a consultant).
In the second part of your reply you said:
As you want to display the data in the same order as the grabbed file, you can use a loop to go through the line of data - the CFLOOP LIST="#OverLoop#" INDEX="LineLoop" line. If you want it in a different order you don't have to use this inner loop, you could just use #ListGetAt(LineLoop, 4, '","')# where 4 is the place in the list.
So you could have:
<CFLOOP LIST="#pagedata#" INDEX="OverLoop" DELIMITERS="#chr(10)##chr(13)#">
<tr>
<td>#ListGetAt(LineLoop, 4, '","')#</td>
<td>#ListGetAt(LineLoop, 2, '","')#</td>
<td>#ListGetAt(LineLoop, 7, '","')#</td>
</tr>
</CFLOOP>
I feel like a total moron but what do you mean by "4 is the place in the list"? and does this live by itself or does this go inside the other loop?
I thought maybe I could create a page with this data (minus the returns) and then create another page that <cfhttp>s off of it but that's not working either.
Thanks for your help,
Natalie
World Synergy
"Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently "tabledata1", is invalid "
Here is my new and improved code:
<html>
<head>
<title>Use Get Method</title>
</head>
<body>
<cfhttp
method="Get"
url="http://www.etanksystems.net/get/getPage.asp?UserID=xxx&Password=xx&days=1&page=15"
resolveurl="Yes"
delimiter=","
name="tabledata1"
textqualifier=""""
columns="Monitorid,Capacity,readingdate,Amountfull,MonDate,MonTime,Temperature">
</cfhttp>
<cfoutput query="tablequery">
#Monitorid#<br>
</cfoutput>
</body>
</html>
CFDEBUG
You could create a query using the values from the list using querynew() and its associated functions. Also you could put this filecontent into a txt file and then use cfhttp to get it again and in that case, name attribute outputs it to a query. So you can sort it or do whatever you want.
singleantler
The first loop takes your lump of data and then treats it as a list, basically it just gives you one line of your data in the variable 'Overloop' each time it loops over itself.
So the first time it loops 'Overloop' is the first line of the file:
"7124","U003947","20040125","1852","0","+066","262"
The second time it loops, 'Overloop' is set to the second line of the file:
"7123","U003944","20040125","2120","0","+064","262"
All well and good.
Now, while we're inside the loop, we can take 'Overloop' and treat it as another list, this time split up by "," between each bit.
So, for the first time the loop runs, we can split up 'Overloop' using the command ListGetAt
ListGetAt(OverLoop, 1, '","') is 7124, as in it's the first part of that line before a ","
ListGetAt(OverLoop, 2, '","') is U003947
ListGetAt(OverLoop, 3, '","') is 20040125
and so on up to the 7 bits of data in the list, with ","s in-between.
The second time the loop runs, Overloop has become the second line of the file, so...
ListGetAt(OverLoop, 1, '","') is 7123, as in it's the first part of the second line before a ","
ListGetAt(OverLoop, 2, '","') is U003944
ListGetAt(OverLoop, 3, '","') is 20040125
etc.
I hope that's a little clearer. It's the sort of thing you get used to when you faff about with lists a lot.
Cheers
World Synergy
I have to thank both of you for walking me through this, I would be totally lost without your help!
I used <cfhttp> to put asp page into a txt file. I then used <cfhttp> to get the text file and use it as my query, and it works! Except that there is a little square at the end of the txt document that is messing up the <cfhttp>. If I go in manually and delete that little the page runs perfect! Do you know how I can get rid of this?
>>>>>>Here is my code that calls the page from the other site:
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<!--- Grab the page --->
<cfhttp url="http://www.etanksystems.net/get/getPage.asp?UserID=xxx&Password=xxx&days=&page=15"
method="get"
resolveurl="yes"
path="E:\tanks.culc.com\ROOT\tanks"
file="urldata2.txt" >
<p><font color="#000099" face="Arial, Helvetica, sans-serif">One moment while we get your data...</font></p>
<!--- Refresh sends user to data page --->
<meta http-equiv="refresh" content="3;URL=datapage.cfm" />
</body>
</html>
>>>>> Here is some of the data I am getting:
"3750","U002434","20040127","0409","3","+073","262"
"3752","U002433","20040127","0428","3","+069","262"
"7123","U003944","20040127","0544","4","+073","262"
"7123","U003944","20040127","0928","4","+073","262"
"4203","U002536","20040127","1123","4","+062","263"
(this box is the problem)
>>>>>>>Here is my code that reads the new txt file:
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<cfhttp
method="get"
name="table"
columns="qw,we,er,rt,ty,yu,ui"
url="http://tanks.culc.com/tanks/urldata2.txt" >
</cfhttp>
<p>data page
</p>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>qw</td>
<td>we</td>
<td>er</td>
<td>rt</td>
<td>ty</td>
</tr>
<tr><cfoutput query="table">
<td>#table.qw#</td>
<td>#table.we#</td>
<td>#table.er#</td>
<td>#table.rt#</td>
<td>#table.ty#</td>
</tr></cfoutput>
</table>
<p> </p>
</body>
</html>
>>>>> If I delete that box I get a table that looks like this (which is great!)
qw we er rt ty
7123 U003944 20040126 1851 0
7423 U004121 20040126 1914 0
7422 U003955 20040126 1935 0
7124 U003947 20040126 1936 0
6603 U003816 20040126 2148 3
There has to be a simple solution to this. Thank you so much for your help :-)
singleantler
The only way I've thought of to get rid of it is to put the following code after the first <CFHTTP command that grabs the original data, and before the "one moment" bit. It reads the urldata2.txt file in, removes the final character off the end by working out how long it is, then doing a 'Left' for that length minus one. Finally it writes the changed data over the top of the file it read.
<CFFILE ACTION="READ"
FILE="E:\tanks.culc.com\ROOT\tanks\urldata2.txt"
VARIABLE="gotdata">
<CFSET gotdata = Left(gotdata, Len(gotdata) -1)>
<CFFILE ACTION="WRITE"
FILE="E:\tanks.culc.com\ROOT\tanks\urldata2.txt"
OUTPUT="#gotdata#">
You can do this slightly shorter by not saving the data to a file to start with and just doing it at the end:
<cfhttp url="http://www.etanksystems.net/get/getPage.asp?UserID=xxx&Password=xxx&days=&page=15"
method="get"
resolveurl="yes">
<CFSET gotdata = Left(cfhttp.filecontent, Len(cfhttp.filecontent) -1)>
<CFFILE ACTION="WRITE"
FILE="E:\tanks.culc.com\ROOT\tanks\urldata2.txt"
OUTPUT="#gotdata#">
So this grabs the data to the variable 'cfhttp.filecontent', which we then remove the last character from, then save it to the hard drive.
HTH
World Synergy
Way cool it works! I have been working on that for hours, even my boss was working on it with me for an hour!
Thank you so much!
singleantler
I'm just going to go off and blush to myself now ;-)