Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recordset works first time then fails

Status
Not open for further replies.

patrussell

Technical User
May 14, 2001
71
US
I have a recordset that retrieves data using a parameterized DE command. The parameter is set in a textbox where the user enters a date then clicks a button to retrieve the data.

I can get this page to work the first time I load it but after that I get an ADODB error and have to reload (not refresh) the page to get it to work. I would like to have everything updated when a new date is entered and the button pressed again. I'm very new at this and am learning as I go so I'm sure this is something obvious I am missing.

I have posted the error I get and my code below. Thanks for any help you can give.

Here is the error message:
Technical Information (for support personnel)

Error Type:
ADODB.Parameter (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
/_ScriptLibrary/Recordset.ASP, line 456


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; Avant Browser [avantbrowser.com])

Page:
POST 483 bytes to /ba_muffledata.asp

POST Data:
Textbox2=6%2F30%2F03&_method=%2Fba_muffledata.asp%3F_method%3D_EM__onclientevent%26pcount%3D2%26p0%3DButton1%26p1%3Donclick&_Textbox2_state=_bVisible%3Dtrue%26_nColumnCount%3D20%26_nMaxLength%3D20&_Bu . . .


Here is my asp page code:
Code:
<%@ Language=VBScript %>
<SCRIPT id=DebugDirectives runat=server language=javascript>
// Set these to true to enable debugging or tracing
@set @debug=false
@set @trace=false
</SCRIPT>
<% ' VI 6.0 Scripting Object Model Enabled %>
<!--#include file=&quot;_ScriptLibrary/pm.asp&quot;-->
<% if StartPageProcessing() Then Response.End() %>
<FORM name=thisForm METHOD=post>
<HTML>
<HEAD>
<META NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio 6.0&quot;>
<SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript RUNAT=Server>

Sub Button1_onclick()
	startdate = cdate(Textbox2.value)
	'Textbox2.value = &quot;'&quot; & startdate & &quot;'&quot;
	Recordset1.close()
	Recordset1.setParameter 0, startdate
	Recordset1.open()
	
End Sub

</SCRIPT>
</HEAD>
<BODY>
<br>

Start Date: 
<!--METADATA TYPE=&quot;DesignerControl&quot; startspan
<OBJECT id=Textbox2 style=&quot;LEFT: 0px; TOP: 0px&quot; classid=&quot;clsid:B5F0E469-DC5F-11D0-9846-0000F8027CA0&quot;>
	<PARAM NAME=&quot;_ExtentX&quot; VALUE=&quot;3175&quot;>
	<PARAM NAME=&quot;_ExtentY&quot; VALUE=&quot;503&quot;>
	<PARAM NAME=&quot;id&quot; VALUE=&quot;Textbox2&quot;>
	<PARAM NAME=&quot;ControlType&quot; VALUE=&quot;0&quot;>
	<PARAM NAME=&quot;Lines&quot; VALUE=&quot;3&quot;>
	<PARAM NAME=&quot;DataSource&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;DataField&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;Enabled&quot; VALUE=&quot;-1&quot;>
	<PARAM NAME=&quot;Visible&quot; VALUE=&quot;-1&quot;>
	<PARAM NAME=&quot;MaxChars&quot; VALUE=&quot;20&quot;>
	<PARAM NAME=&quot;DisplayWidth&quot; VALUE=&quot;20&quot;>
	<PARAM NAME=&quot;Platform&quot; VALUE=&quot;256&quot;>
	<PARAM NAME=&quot;LocalPath&quot; VALUE=&quot;&quot;></OBJECT>
-->
<!--#INCLUDE FILE=&quot;_ScriptLibrary/TextBox.ASP&quot;-->
<SCRIPT LANGUAGE=JavaScript RUNAT=Server>
function _initTextbox2()
{
	Textbox2.setStyle(TXT_TEXTBOX);
	Textbox2.setMaxLength(20);
	Textbox2.setColumnCount(20);
}
function _Textbox2_ctor()
{
	CreateTextbox('Textbox2', _initTextbox2, null);
}
</script>
<% Textbox2.display %>

<!--METADATA TYPE=&quot;DesignerControl&quot; endspan-->
<!--METADATA TYPE=&quot;DesignerControl&quot; startspan
<OBJECT id=Button1 style=&quot;LEFT: 10px; WIDTH: 112px; TOP: 53px; HEIGHT: 27px&quot; height=27 
	width=112 classid=&quot;clsid:B6FC3A14-F837-11D0-9CC8-006008058731&quot;>
	<PARAM NAME=&quot;_ExtentX&quot; VALUE=&quot;2963&quot;>
	<PARAM NAME=&quot;_ExtentY&quot; VALUE=&quot;714&quot;>
	<PARAM NAME=&quot;id&quot; VALUE=&quot;Button1&quot;>
	<PARAM NAME=&quot;Caption&quot; VALUE=&quot;Retrieve Data&quot;>
	<PARAM NAME=&quot;Image&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;AltText&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;Visible&quot; VALUE=&quot;-1&quot;>
	<PARAM NAME=&quot;Platform&quot; VALUE=&quot;256&quot;>
	<PARAM NAME=&quot;LocalPath&quot; VALUE=&quot;&quot;></OBJECT>
-->
<!--#INCLUDE FILE=&quot;_ScriptLibrary/Button.ASP&quot;-->
<SCRIPT LANGUAGE=JavaScript RUNAT=Server>
function _initButton1()
{
	Button1.value = 'Retrieve Data';
	Button1.setStyle(0);
}
function _Button1_ctor()
{
	CreateButton('Button1', _initButton1, null);
}
</script>
<% Button1.display %>

<!--METADATA TYPE=&quot;DesignerControl&quot; endspan-->
<br>


<br>

<P> </P>
<!--METADATA TYPE=&quot;DesignerControl&quot; startspan
<OBJECT id=Recordset1 style=&quot;LEFT: 0px; TOP: 0px&quot; classid=&quot;clsid:9CF5D7C2-EC10-11D0-9862-0000F8027CA0&quot;>
	<PARAM NAME=&quot;ExtentX&quot; VALUE=&quot;12197&quot;>
	<PARAM NAME=&quot;ExtentY&quot; VALUE=&quot;2090&quot;>
	<PARAM NAME=&quot;State&quot; VALUE=&quot;(TCConn=\qBright\q,TCDBObject=\qDE\sCommands\q,TCDBObjectName=\qBA_MuffleData\q,TCControlID_Unmatched=\qRecordset1\q,TCPPConn=\qBright\q,RCDBObject=\qRCDBObject\q,TCPPDBObject=\qDE\sCommands\q,TCPPDBObjectName=\qBA_MuffleData\q,TCCursorType=\q3\s-\sStatic\q,TCCursorLocation=\q3\s-\sUse\sclient-side\scursors\q,TCLockType=\q3\s-\sOptimistic\q,TCCacheSize_Unmatched=\q100\q,TCCommTimeout_Unmatched=\q30\q,CCPrepared=0,CCAllRecords=1,TCNRecords_Unmatched=\q10\q,TCODBCSyntax_Unmatched=\q\q,TCHTargetPlatform=\q\q,TCHTargetBrowser_Unmatched=\qServer\s(ASP)\q,TCTargetPlatform=\qInherit\sfrom\spage\q,RCCache=\qRCBookPage\q,CCOpen=0,GCParameters=(Rows=1,Row1=(CType_Unmatched=\qIn\q,CParName_Unmatched=\qParam1\q,CDataType_Unmatched=\qDBTmieStamp\q,CSize_Unmatched=\q16\q,CReq=1,CValue_Unmatched=\q\q)))&quot;></OBJECT>
-->
<!--#INCLUDE FILE=&quot;_ScriptLibrary/Recordset.ASP&quot;-->
<SCRIPT LANGUAGE=&quot;JavaScript&quot; RUNAT=&quot;server&quot;>
function _setParametersRecordset1()
{
}
function _initRecordset1()
{
	Recordset1.advise(RS_ONBEFOREOPEN, _setParametersRecordset1);
	thisPage.createDE();
	var rsTmp = DE.Recordsets('BA_MuffleData');
	Recordset1.setRecordSource(rsTmp);
	if (thisPage.getState('pb_Recordset1') != null)
		Recordset1.setBookmark(thisPage.getState('pb_Recordset1'));
}
function _Recordset1_ctor()
{
	CreateRecordset('Recordset1', _initRecordset1, null);
}
function _Recordset1_dtor()
{
	Recordset1._preserveState();
	thisPage.setState('pb_Recordset1', Recordset1.getBookmark());
}
</SCRIPT>

<!--METADATA TYPE=&quot;DesignerControl&quot; endspan-->

<!--METADATA TYPE=&quot;DesignerControl&quot; startspan
<OBJECT id=Grid1 style=&quot;LEFT: 0px; TOP: 0px&quot; classid=&quot;clsid:277FC3F2-E90F-11D0-B767-0000F81E081D&quot;>
	<PARAM NAME=&quot;_ExtentX&quot; VALUE=&quot;8043&quot;>
	<PARAM NAME=&quot;_ExtentY&quot; VALUE=&quot;3889&quot;>
	<PARAM NAME=&quot;DataConnection&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;SourceType&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;Recordset&quot; VALUE=&quot;Recordset1&quot;>
	<PARAM NAME=&quot;CtrlName&quot; VALUE=&quot;Grid1&quot;>
	<PARAM NAME=&quot;UseAdvancedOnly&quot; VALUE=&quot;0&quot;>
	<PARAM NAME=&quot;AdvAddToStyles&quot; VALUE=&quot;-1&quot;>
	<PARAM NAME=&quot;AdvTableTag&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;AdvHeaderRowTag&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;AdvHeaderCellTag&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;AdvDetailRowTag&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;AdvDetailCellTag&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;ScriptLanguage&quot; VALUE=&quot;1&quot;>
	<PARAM NAME=&quot;ScriptingPlatform&quot; VALUE=&quot;0&quot;>
	<PARAM NAME=&quot;EnableRowNav&quot; VALUE=&quot;0&quot;>
	<PARAM NAME=&quot;HiliteColor&quot; VALUE=&quot;&quot;>
	<PARAM NAME=&quot;RecNavBarHasNextButton&quot; VALUE=&quot;-1&quot;>
	<PARAM NAME=&quot;RecNavBarHasPrevButton&quot; VALUE=&quot;-1&quot;>
	<PARAM NAME=&quot;RecNavBarNextText&quot; VALUE=&quot;   >   &quot;>
	<PARAM NAME=&quot;RecNavBarPrevText&quot; VALUE=&quot;   <   &quot;>
	<PARAM NAME=&quot;ColumnsNames&quot; VALUE='&quot;DateTime&quot;,&quot;OP1 Position&quot;,&quot;OP2 Position&quot;,&quot;DR1 Position&quot;,&quot;DR2 Position&quot;,&quot;Line Speed&quot;,&quot;Gauge&quot;,&quot;Width&quot;,&quot;Zone 1 Temp&quot;'>
	<PARAM NAME=&quot;columnIndex&quot; VALUE=&quot;0,1,2,3,4,5,6,7,8&quot;>
	<PARAM NAME=&quot;displayWidth&quot; VALUE=&quot;68,68,68,68,68,68,68,68,68&quot;>
	<PARAM NAME=&quot;Coltype&quot; VALUE=&quot;1,1,1,1,1,1,1,1,1&quot;>
	<PARAM NAME=&quot;formated&quot; VALUE=&quot;0,0,0,0,0,0,0,0,0&quot;>
	<PARAM NAME=&quot;DisplayName&quot; VALUE='&quot;DateTime&quot;,&quot;OP1 Position&quot;,&quot;OP2 Position&quot;,&quot;DR1 Position&quot;,&quot;DR2 Position&quot;,&quot;Line Speed&quot;,&quot;Gauge&quot;,&quot;Width&quot;,&quot;Zone 1 Temp&quot;'>
	<PARAM NAME=&quot;DetailAlignment&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;HeaderAlignment&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;DetailBackColor&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;HeaderBackColor&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;HeaderFont&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;HeaderFontColor&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;HeaderFontSize&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;HeaderFontStyle&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;DetailFont&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;DetailFontColor&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;DetailFontSize&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;DetailFontStyle&quot; VALUE=&quot;,,,,,,,,&quot;>
	<PARAM NAME=&quot;ColumnCount&quot; VALUE=&quot;9&quot;>
	<PARAM NAME=&quot;CurStyle&quot; VALUE=&quot;Basic Maroon&quot;>
	<PARAM NAME=&quot;TitleFont&quot; VALUE=&quot;Arial Narrow&quot;>
	<PARAM NAME=&quot;titleFontSize&quot; VALUE=&quot;3&quot;>
	<PARAM NAME=&quot;TitleFontColor&quot; VALUE=&quot;16777215&quot;>
	<PARAM NAME=&quot;TitleBackColor&quot; VALUE=&quot;8388608&quot;>
	<PARAM NAME=&quot;TitleFontStyle&quot; VALUE=&quot;1&quot;>
	<PARAM NAME=&quot;TitleAlignment&quot; VALUE=&quot;0&quot;>
	<PARAM NAME=&quot;RowFont&quot; VALUE=&quot;Arial Narrow&quot;>
	<PARAM NAME=&quot;RowFontColor&quot; VALUE=&quot;0&quot;>
	<PARAM NAME=&quot;RowFontStyle&quot; VALUE=&quot;0&quot;>
	<PARAM NAME=&quot;RowFontSize&quot; VALUE=&quot;2&quot;>
	<PARAM NAME=&quot;RowBackColor&quot; VALUE=&quot;16777215&quot;>
	<PARAM NAME=&quot;RowAlignment&quot; VALUE=&quot;0&quot;>
	<PARAM NAME=&quot;HighlightColor3D&quot; VALUE=&quot;268435455&quot;>
	<PARAM NAME=&quot;ShadowColor3D&quot; VALUE=&quot;268435455&quot;>
	<PARAM NAME=&quot;PageSize&quot; VALUE=&quot;20&quot;>
	<PARAM NAME=&quot;MoveFirstCaption&quot; VALUE=&quot;    |<    &quot;>
	<PARAM NAME=&quot;MoveLastCaption&quot; VALUE=&quot;    >|    &quot;>
	<PARAM NAME=&quot;MovePrevCaption&quot; VALUE=&quot;    <<    &quot;>
	<PARAM NAME=&quot;MoveNextCaption&quot; VALUE=&quot;    >>    &quot;>
	<PARAM NAME=&quot;BorderSize&quot; VALUE=&quot;1&quot;>
	<PARAM NAME=&quot;BorderColor&quot; VALUE=&quot;16777215&quot;>
	<PARAM NAME=&quot;GridBackColor&quot; VALUE=&quot;8388608&quot;>
	<PARAM NAME=&quot;AltRowBckgnd&quot; VALUE=&quot;12632256&quot;>
	<PARAM NAME=&quot;CellSpacing&quot; VALUE=&quot;1&quot;>
	<PARAM NAME=&quot;WidthSelectionMode&quot; VALUE=&quot;1&quot;>
	<PARAM NAME=&quot;GridWidth&quot; VALUE=&quot;304&quot;>
	<PARAM NAME=&quot;EnablePaging&quot; VALUE=&quot;0&quot;>
	<PARAM NAME=&quot;ShowStatus&quot; VALUE=&quot;-1&quot;>
	<PARAM NAME=&quot;StyleValue&quot; VALUE=&quot;437229&quot;>
	<PARAM NAME=&quot;LocalPath&quot; VALUE=&quot;&quot;></OBJECT>
-->
<!--#INCLUDE FILE=&quot;_ScriptLibrary/RSNavBar.ASP&quot;-->
<!--#INCLUDE FILE=&quot;_ScriptLibrary/DataGrid.ASP&quot;-->
<SCRIPT LANGUAGE=JavaScript RUNAT=Server>
function _initGrid1()
{
Grid1.pageSize = 0;
Grid1.setDataSource(Recordset1);
Grid1.tableAttributes = ' cellpadding=2  cellspacing=1 bordercolor=White bgcolor=Maroon border=1 cols=9 rules=ALL WIDTH=304 nowrap';
Grid1.headerAttributes = '   bgcolor=Maroon align=Left nowrap';
Grid1.headerWidth[0] = ' WIDTH=68';
Grid1.headerWidth[1] = ' WIDTH=68';
Grid1.headerWidth[2] = ' WIDTH=68';
Grid1.headerWidth[3] = ' WIDTH=68';
Grid1.headerWidth[4] = ' WIDTH=68';
Grid1.headerWidth[5] = ' WIDTH=68';
Grid1.headerWidth[6] = ' WIDTH=68';
Grid1.headerWidth[7] = ' WIDTH=68';
Grid1.headerWidth[8] = ' WIDTH=68';
Grid1.headerFormat = '<Font face=&quot;Arial Narrow&quot; size=3 color=White> <b>';
Grid1.colHeader[0] = '\'DateTime\'';
Grid1.colHeader[1] = '\'OP1 Position\'';
Grid1.colHeader[2] = '\'OP2 Position\'';
Grid1.colHeader[3] = '\'DR1 Position\'';
Grid1.colHeader[4] = '\'DR2 Position\'';
Grid1.colHeader[5] = '\'Line Speed\'';
Grid1.colHeader[6] = '\'Gauge\'';
Grid1.colHeader[7] = '\'Width\'';
Grid1.colHeader[8] = '\'Zone 1 Temp\'';
Grid1.rowAttributes[0] = '  bgcolor = White align=Left nowrap  bordercolor=White';
Grid1.rowAttributes[1] = '  bgcolor = Silver align=Left nowrap  bordercolor=White';
Grid1.rowFormat[0] = ' <Font face=&quot;Arial Narrow&quot; size=2 color=Black >';
Grid1.colAttributes[0] = '  WIDTH=68';
Grid1.colFormat[0] = '<Font Size=2 Face=&quot;Arial Narrow&quot; Color=Black >';
Grid1.colData[0] = 'Recordset1.fields.getValue(\'DateTime\')';
Grid1.colAttributes[1] = '  WIDTH=68';
Grid1.colFormat[1] = '<Font Size=2 Face=&quot;Arial Narrow&quot; Color=Black >';
Grid1.colData[1] = 'Recordset1.fields.getValue(\'OP1 Position\')';
Grid1.colAttributes[2] = '  WIDTH=68';
Grid1.colFormat[2] = '<Font Size=2 Face=&quot;Arial Narrow&quot; Color=Black >';
Grid1.colData[2] = 'Recordset1.fields.getValue(\'OP2 Position\')';
Grid1.colAttributes[3] = '  WIDTH=68';
Grid1.colFormat[3] = '<Font Size=2 Face=&quot;Arial Narrow&quot; Color=Black >';
Grid1.colData[3] = 'Recordset1.fields.getValue(\'DR1 Position\')';
Grid1.colAttributes[4] = '  WIDTH=68';
Grid1.colFormat[4] = '<Font Size=2 Face=&quot;Arial Narrow&quot; Color=Black >';
Grid1.colData[4] = 'Recordset1.fields.getValue(\'DR2 Position\')';
Grid1.colAttributes[5] = '  WIDTH=68';
Grid1.colFormat[5] = '<Font Size=2 Face=&quot;Arial Narrow&quot; Color=Black >';
Grid1.colData[5] = 'Recordset1.fields.getValue(\'Line Speed\')';
Grid1.colAttributes[6] = '  WIDTH=68';
Grid1.colFormat[6] = '<Font Size=2 Face=&quot;Arial Narrow&quot; Color=Black >';
Grid1.colData[6] = 'Recordset1.fields.getValue(\'Gauge\')';
Grid1.colAttributes[7] = '  WIDTH=68';
Grid1.colFormat[7] = '<Font Size=2 Face=&quot;Arial Narrow&quot; Color=Black >';
Grid1.colData[7] = 'Recordset1.fields.getValue(\'Width\')';
Grid1.colAttributes[8] = '  WIDTH=68';
Grid1.colFormat[8] = '<Font Size=2 Face=&quot;Arial Narrow&quot; Color=Black >';
Grid1.colData[8] = 'Recordset1.fields.getValue(\'Zone 1 Temp\')';
}
function _Grid1_ctor()
{
	CreateDataGrid('Grid1',_initGrid1);
}
</SCRIPT>

<%	Grid1.display %>


<!--METADATA TYPE=&quot;DesignerControl&quot; endspan-->


</BODY>
<% ' VI 6.0 Scripting Object Model Enabled %>
<% EndPageProcessing() %>
</FORM>
</HTML>
 
ur code is too huge....

can u just give me the line that is wreaking havoc?

Known is handfull, Unknown is worldfull
 
This code looks OK, but what shows on the first page load? Just the date text box and a button?

If the complete grid shows, then the query does not expect a parameter at all. For parameters to work, you must place a ? for each one in the WHERE clause. The first page load does not supply a parameter - so the grid should be invisible.

Some care needs to be taken over Dates, as it may require a correctly formatted String, not a date variable. Try formatting your date as dd-mon-yyyy (01-Jan-2004) and see if this makes any difference.

ps. Ignore vbkris, he doesn't know about DTC's.

 
ok MerlinB:
ur right i dont know what a DTC is...

can u tell me what it is?
any links...

Known is handfull, Unknown is worldfull
 
I'm pretty sure the problem is with the formatting of the textbox content. As you can see I've tried to remedy that by formatting the text for a date when the button is clicked but this only worked the first time the page is loaded.

I have since resolved the problem by having the results open in Excel. This both allows the user to work with the results in a familiar app and forces the page to reload if they want to do another query.

Of course I still don't know why this would only work once, but will have to figure that out later. Thanks for all the help!

Pat Russell
 
Whew! that's a lot of code.

Did you make sure that you closed your recordset when you were done with it?

Rich
 
Ooops, I forgot about this thread!

The problem seems to be that I hadn't updated the server with my latest page. The code I posted above is what is running now and works fine.

Sorry for not closing this out sooner.

Pat Russell
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top