dbStartUp=EditUpdate results in error with querystring supplied SQL variable

[Lenanrt]

I'm passing the ID-field of a record to ASPdb through the URL querystring, and keep getting this error:

"Error # (Get_RS_OPEN - After open TOP SQL) = 80040E0C(-2147217908)
Description = Command text was not set for the command object.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State =
Native Error = 0"

I'm setting the variable(s) correctly to session variables, etc.
And the funny thing is, that when I include the ID-field variable directly in the ASPdb page, it works fine.
Only when I attempt to pass and get the variable through the querystring, it gives me this "command text was not set error".
See below for example code:

Any ideas are much appreciated.
Thanks

EXAMPLE CODE
--------------------------

setID = Cint(Request("ID&#34)

Set X= Server.CreateObject("Asp.DB&#34
X.dbUnit = "3"

if (setID <> &#34;&#34 then
Session(&#34;setID&#34 = setID
X.dbReset(3)
end if

WhereClause = Session(&#34;setID&#34

sqlStatement = &#34;Select * FROM tblServices WHERE (ServiceID = &#34; & WhereClause & &#34&#34;
X.dbSQL = sqlStatement
X.dbStartUp = &#34;EditUpdate&#34;
X.dbNavigationItem = &#34;add, update, delete&#34;

ET CETERA

[John]

Hi,

A couple thoughts...

1) If you&#39;re getting the &#34;TOP SQL&#34; part in there, are you setting dbDBType properly? Make sure you&#39;ve set that to the type of DB you&#39;re using.

2) Try doing a response.write of your sqlStatement variable so that you can see what it is you&#39;re actually executing. That&#39;s always a great way to catch bugs that creep in when dropping variables into your strings.

3) Turn on the debug function (dbDebug) and check out what you&#39;re executing. See the docs for the various options, but you can watch the SQL statement that gets executed along with many more.

Hope this helps,

John


------------
Lenanrt at 3/27/01 10:22:00 AM

I&#39;m passing the ID-field of a record to ASPdb through the URL querystring, and keep getting this error:

&#34;Error # (Get_RS_OPEN - After open TOP SQL) = 80040E0C(-2147217908)
Description = Command text was not set for the command object.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State =
Native Error = 0&#34;

I&#39;m setting the variable(s) correctly to session variables, etc.
And the funny thing is, that when I include the ID-field variable directly in the ASPdb page, it works fine.
Only when I attempt to pass and get the variable through the querystring, it gives me this &#34;command text was not set error&#34;.
See below for example code:

Any ideas are much appreciated.
Thanks

EXAMPLE CODE
--------------------------

setID = Cint(Request(&#34;ID&#34)

Set X= Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit = &#34;3&#34;

if (setID <> &#34;&#34 then
Session(&#34;setID&#34 = setID
X.dbReset(3)
end if

WhereClause = Session(&#34;setID&#34

sqlStatement = &#34;Select * FROM tblServices WHERE (ServiceID = &#34; & WhereClause & &#34&#34;
X.dbSQL = sqlStatement
X.dbStartUp = &#34;EditUpdate&#34;
X.dbNavigationItem = &#34;add, update, delete&#34;

ET CETERA

[Lennart]

Thanks for your thoughts, John.

Although not mentioned in my posting, I&#39;ve tried all your points.

I&#39;m getting the Top because I tried setting the dbRecordCount to 1, to see if that makes a change (but doesn&#39;t).
I&#39;ve checked output of all variables and gone through all dbDebug options.

The thing is that all works OK, until I&#39;m specifying something in the querystring. Then it does not pass my SQL statement to ASPdb (although the ouput of my variables show me it&#39;s there), and generates this error of &#39;Command text not set. . .&#34; ???????

I&#39;m clueless. . . .

------------
John at 3/27/01 11:14:36 PM

Hi,

A couple thoughts...

1) If you&#39;re getting the &#34;TOP SQL&#34; part in there, are you setting dbDBType properly? Make sure you&#39;ve set that to the type of DB you&#39;re using.

2) Try doing a response.write of your sqlStatement variable so that you can see what it is you&#39;re actually executing. That&#39;s always a great way to catch bugs that creep in when dropping variables into your strings.

3) Turn on the debug function (dbDebug) and check out what you&#39;re executing. See the docs for the various options, but you can watch the SQL statement that gets executed along with many more.

Hope this helps,

John


------------
Lenanrt at 3/27/01 10:22:00 AM

I&#39;m passing the ID-field of a record to ASPdb through the URL querystring, and keep getting this error:

&#34;Error # (Get_RS_OPEN - After open TOP SQL) = 80040E0C(-2147217908)
Description = Command text was not set for the command object.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State =
Native Error = 0&#34;

I&#39;m setting the variable(s) correctly to session variables, etc.
And the funny thing is, that when I include the ID-field variable directly in the ASPdb page, it works fine.
Only when I attempt to pass and get the variable through the querystring, it gives me this &#34;command text was not set error&#34;.
See below for example code:

Any ideas are much appreciated.
Thanks

EXAMPLE CODE
--------------------------

setID = Cint(Request(&#34;ID&#34)

Set X= Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit = &#34;3&#34;

if (setID <> &#34;&#34 then
Session(&#34;setID&#34 = setID
X.dbReset(3)
end if

WhereClause = Session(&#34;setID&#34

sqlStatement = &#34;Select * FROM tblServices WHERE (ServiceID = &#34; & WhereClause & &#34&#34;
X.dbSQL = sqlStatement
X.dbStartUp = &#34;EditUpdate&#34;
X.dbNavigationItem = &#34;add, update, delete&#34;

ET CETERA

[John]

Hi Lennart,

Looks like you&#39;re doing things right, but I&#39;m afraid there&#39;s a problem in building up your dbSQL property. I have a feeling that the value passed in your querystring isn&#39;t quite what you expect it to be. Here&#39;s how I&#39;d fix the problem.

After your statement when you build up your sqlStatement variable, add:

Response.write(&#34;sqlStatement [&#34; & sqlStatement & &#34;]<BR>&#34

Then watch that output. I have a feeling that you&#39;ll see something wrong with the sql (missing quote, string vs number, etc.) that&#39;s causing your problem. If it&#39;s still not obvious, strip your program down to the minimum that fails and post it here with the output results (including the sqlStatement output).

Thanks,
John

------------
Lennart at 3/28/01 10:09:02 AM

Thanks for your thoughts, John.

Although not mentioned in my posting, I&#39;ve tried all your points.

I&#39;m getting the Top because I tried setting the dbRecordCount to 1, to see if that makes a change (but doesn&#39;t).
I&#39;ve checked output of all variables and gone through all dbDebug options.

The thing is that all works OK, until I&#39;m specifying something in the querystring. Then it does not pass my SQL statement to ASPdb (although the ouput of my variables show me it&#39;s there), and generates this error of &#39;Command text not set. . .&#34; ???????

I&#39;m clueless. . . .

------------
John at 3/27/01 11:14:36 PM

Hi,

A couple thoughts...

1) If you&#39;re getting the &#34;TOP SQL&#34; part in there, are you setting dbDBType properly? Make sure you&#39;ve set that to the type of DB you&#39;re using.

2) Try doing a response.write of your sqlStatement variable so that you can see what it is you&#39;re actually executing. That&#39;s always a great way to catch bugs that creep in when dropping variables into your strings.

3) Turn on the debug function (dbDebug) and check out what you&#39;re executing. See the docs for the various options, but you can watch the SQL statement that gets executed along with many more.

Hope this helps,

John


------------
Lenanrt at 3/27/01 10:22:00 AM

I&#39;m passing the ID-field of a record to ASPdb through the URL querystring, and keep getting this error:

&#34;Error # (Get_RS_OPEN - After open TOP SQL) = 80040E0C(-2147217908)
Description = Command text was not set for the command object.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State =
Native Error = 0&#34;

I&#39;m setting the variable(s) correctly to session variables, etc.
And the funny thing is, that when I include the ID-field variable directly in the ASPdb page, it works fine.
Only when I attempt to pass and get the variable through the querystring, it gives me this &#34;command text was not set error&#34;.
See below for example code:

Any ideas are much appreciated.
Thanks

EXAMPLE CODE
--------------------------

setID = Cint(Request(&#34;ID&#34)

Set X= Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit = &#34;3&#34;

if (setID <> &#34;&#34 then
Session(&#34;setID&#34 = setID
X.dbReset(3)
end if

WhereClause = Session(&#34;setID&#34

sqlStatement = &#34;Select * FROM tblServices WHERE (ServiceID = &#34; & WhereClause & &#34&#34;
X.dbSQL = sqlStatement
X.dbStartUp = &#34;EditUpdate&#34;
X.dbNavigationItem = &#34;add, update, delete&#34;

ET CETERA

[Lennart]

below is the code.

What happens is:
>>when I &#39;hard code&#39; the sqlStatement with a given recordID, aspDB EditUpdate mode works fine
>>until I supply a value in the querystring (e.g. &#39;page.asp?ID=2&#39; )

In other words, even though the sqlstatement has all the values it requires (coded into the page, not using Request), aspDB ONLY works if nothing is specified in the URL querystring!!!


CODE:
<%
strRecordID = Request.QueryString(&#34;ID&#34

recordID = Cint(strRecordID)

Set X=Server.CreateObject(&#34;ASP.db&#34

X.dbUnit = 111

If recordID <> 0 Then
Session(&#34;recordID&#34 = recordID
X.dbReset(111)
End If

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & recordID

X.dbDSN = &#34;DSN=portal; UID=sa; PWD=&#34;
X.dbDBType = &#34;SQL&#34;
X.dbSQL=sqlStatement
X.dbStartUp = &#34;editupdate&#34;
X.dbEditParams = &#34;(;,)TableName=tblServices,BookMarkFlds=0&# 34;
X.dbEditFlds = &#34;ServiceID, ServiceNm&#34;
X.dbNavigationItem = &#34;Add, update, next&#34;
X.aspdb

Response.Write(&#34;sqlStatement = &#34; & sqlStatement & &#34;<BR>&#34
%>


------------
John at 4/6/01 4:20:13 PM


Hi Lennart,

Looks like you&#39;re doing things right, but I&#39;m afraid there&#39;s a problem in building up your dbSQL property. I have a feeling that the value passed in your querystring isn&#39;t quite what you expect it to be. Here&#39;s how I&#39;d fix the problem.

After your statement when you build up your sqlStatement variable, add:

Response.write(&#34;sqlStatement [&#34; & sqlStatement & &#34;]<BR>&#34

Then watch that output. I have a feeling that you&#39;ll see something wrong with the sql (missing quote, string vs number, etc.) that&#39;s causing your problem. If it&#39;s still not obvious, strip your program down to the minimum that fails and post it here with the output results (including the sqlStatement output).

Thanks,
John

------------
Lennart at 3/28/01 10:09:02 AM

Thanks for your thoughts, John.

Although not mentioned in my posting, I&#39;ve tried all your points.

I&#39;m getting the Top because I tried setting the dbRecordCount to 1, to see if that makes a change (but doesn&#39;t).
I&#39;ve checked output of all variables and gone through all dbDebug options.

The thing is that all works OK, until I&#39;m specifying something in the querystring. Then it does not pass my SQL statement to ASPdb (although the ouput of my variables show me it&#39;s there), and generates this error of &#39;Command text not set. . .&#34; ???????

I&#39;m clueless. . . .

------------
John at 3/27/01 11:14:36 PM

Hi,

A couple thoughts...

1) If you&#39;re getting the &#34;TOP SQL&#34; part in there, are you setting dbDBType properly? Make sure you&#39;ve set that to the type of DB you&#39;re using.

2) Try doing a response.write of your sqlStatement variable so that you can see what it is you&#39;re actually executing. That&#39;s always a great way to catch bugs that creep in when dropping variables into your strings.

3) Turn on the debug function (dbDebug) and check out what you&#39;re executing. See the docs for the various options, but you can watch the SQL statement that gets executed along with many more.

Hope this helps,

John


------------
Lenanrt at 3/27/01 10:22:00 AM

I&#39;m passing the ID-field of a record to ASPdb through the URL querystring, and keep getting this error:

&#34;Error # (Get_RS_OPEN - After open TOP SQL) = 80040E0C(-2147217908)
Description = Command text was not set for the command object.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State =
Native Error = 0&#34;

I&#39;m setting the variable(s) correctly to session variables, etc.
And the funny thing is, that when I include the ID-field variable directly in the ASPdb page, it works fine.
Only when I attempt to pass and get the variable through the querystring, it gives me this &#34;command text was not set error&#34;.
See below for example code:

Any ideas are much appreciated.
Thanks

EXAMPLE CODE
--------------------------

setID = Cint(Request(&#34;ID&#34)

Set X= Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit = &#34;3&#34;

if (setID <> &#34;&#34 then
Session(&#34;setID&#34 = setID
X.dbReset(3)
end if

WhereClause = Session(&#34;setID&#34

sqlStatement = &#34;Select * FROM tblServices WHERE (ServiceID = &#34; & WhereClause & &#34&#34;
X.dbSQL = sqlStatement
X.dbStartUp = &#34;EditUpdate&#34;
X.dbNavigationItem = &#34;add, update, delete&#34;

ET CETERA

[Mark]

Lennart,
I think you just have one small mistake.
Change you SQL statement from:

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & recordID

to:

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & Session(&#34;RecordID&#34

Mark.


------------
Lennart at 4/11/01 5:13:46 PM

below is the code.

What happens is:
>>when I &#39;hard code&#39; the sqlStatement with a given recordID, aspDB EditUpdate mode works fine
>>until I supply a value in the querystring (e.g. &#39;page.asp?ID=2&#39; )

In other words, even though the sqlstatement has all the values it requires (coded into the page, not using Request), aspDB ONLY works if nothing is specified in the URL querystring!!!


CODE:
<%
strRecordID = Request.QueryString(&#34;ID&#34

recordID = Cint(strRecordID)

Set X=Server.CreateObject(&#34;ASP.db&#34

X.dbUnit = 111

If recordID <> 0 Then
Session(&#34;recordID&#34 = recordID
X.dbReset(111)
End If

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & recordID

X.dbDSN = &#34;DSN=portal; UID=sa; PWD=&#34;
X.dbDBType = &#34;SQL&#34;
X.dbSQL=sqlStatement
X.dbStartUp = &#34;editupdate&#34;
X.dbEditParams = &#34;(;,)TableName=tblServices,BookMarkFlds=0&# 34;
X.dbEditFlds = &#34;ServiceID, ServiceNm&#34;
X.dbNavigationItem = &#34;Add, update, next&#34;
X.aspdb

Response.Write(&#34;sqlStatement = &#34; & sqlStatement & &#34;<BR>&#34
%>


------------
John at 4/6/01 4:20:13 PM


Hi Lennart,

Looks like you&#39;re doing things right, but I&#39;m afraid there&#39;s a problem in building up your dbSQL property. I have a feeling that the value passed in your querystring isn&#39;t quite what you expect it to be. Here&#39;s how I&#39;d fix the problem.

After your statement when you build up your sqlStatement variable, add:

Response.write(&#34;sqlStatement [&#34; & sqlStatement & &#34;]<BR>&#34

Then watch that output. I have a feeling that you&#39;ll see something wrong with the sql (missing quote, string vs number, etc.) that&#39;s causing your problem. If it&#39;s still not obvious, strip your program down to the minimum that fails and post it here with the output results (including the sqlStatement output).

Thanks,
John

------------
Lennart at 3/28/01 10:09:02 AM

Thanks for your thoughts, John.

Although not mentioned in my posting, I&#39;ve tried all your points.

I&#39;m getting the Top because I tried setting the dbRecordCount to 1, to see if that makes a change (but doesn&#39;t).
I&#39;ve checked output of all variables and gone through all dbDebug options.

The thing is that all works OK, until I&#39;m specifying something in the querystring. Then it does not pass my SQL statement to ASPdb (although the ouput of my variables show me it&#39;s there), and generates this error of &#39;Command text not set. . .&#34; ???????

I&#39;m clueless. . . .

------------
John at 3/27/01 11:14:36 PM

Hi,

A couple thoughts...

1) If you&#39;re getting the &#34;TOP SQL&#34; part in there, are you setting dbDBType properly? Make sure you&#39;ve set that to the type of DB you&#39;re using.

2) Try doing a response.write of your sqlStatement variable so that you can see what it is you&#39;re actually executing. That&#39;s always a great way to catch bugs that creep in when dropping variables into your strings.

3) Turn on the debug function (dbDebug) and check out what you&#39;re executing. See the docs for the various options, but you can watch the SQL statement that gets executed along with many more.

Hope this helps,

John


------------
Lenanrt at 3/27/01 10:22:00 AM

I&#39;m passing the ID-field of a record to ASPdb through the URL querystring, and keep getting this error:

&#34;Error # (Get_RS_OPEN - After open TOP SQL) = 80040E0C(-2147217908)
Description = Command text was not set for the command object.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State =
Native Error = 0&#34;

I&#39;m setting the variable(s) correctly to session variables, etc.
And the funny thing is, that when I include the ID-field variable directly in the ASPdb page, it works fine.
Only when I attempt to pass and get the variable through the querystring, it gives me this &#34;command text was not set error&#34;.
See below for example code:

Any ideas are much appreciated.
Thanks

EXAMPLE CODE
--------------------------

setID = Cint(Request(&#34;ID&#34)

Set X= Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit = &#34;3&#34;

if (setID <> &#34;&#34 then
Session(&#34;setID&#34 = setID
X.dbReset(3)
end if

WhereClause = Session(&#34;setID&#34

sqlStatement = &#34;Select * FROM tblServices WHERE (ServiceID = &#34; & WhereClause & &#34&#34;
X.dbSQL = sqlStatement
X.dbStartUp = &#34;EditUpdate&#34;
X.dbNavigationItem = &#34;add, update, delete&#34;

ET CETERA

[lennart]

For the record, I had your syntax before, but overlooked it when posting the reply. Sorry

The interesting part still is that when:
>> I supply the &#39;mypage.asp?ID=value&#39; in the querystring is gives the same error message.
>> then when I just request the &#34;mypage.asp&#34; is works, using the Session(&#34;RecordID&#34 that was set in the previous action

In other words, ASPdb stops functioning as soon as you specify a querystring!!

Lennart



------------
Mark at 4/11/01 5:21:23 PM

Lennart,
I think you just have one small mistake.
Change you SQL statement from:

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & recordID

to:

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & Session(&#34;RecordID&#34

Mark.


------------
Lennart at 4/11/01 5:13:46 PM

below is the code.

What happens is:
>>when I &#39;hard code&#39; the sqlStatement with a given recordID, aspDB EditUpdate mode works fine
>>until I supply a value in the querystring (e.g. &#39;page.asp?ID=2&#39; )

In other words, even though the sqlstatement has all the values it requires (coded into the page, not using Request), aspDB ONLY works if nothing is specified in the URL querystring!!!


CODE:
<%
strRecordID = Request.QueryString(&#34;ID&#34

recordID = Cint(strRecordID)

Set X=Server.CreateObject(&#34;ASP.db&#34

X.dbUnit = 111

If recordID <> 0 Then
Session(&#34;recordID&#34 = recordID
X.dbReset(111)
End If

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & recordID

X.dbDSN = &#34;DSN=portal; UID=sa; PWD=&#34;
X.dbDBType = &#34;SQL&#34;
X.dbSQL=sqlStatement
X.dbStartUp = &#34;editupdate&#34;
X.dbEditParams = &#34;(;,)TableName=tblServices,BookMarkFlds=0&# 34;
X.dbEditFlds = &#34;ServiceID, ServiceNm&#34;
X.dbNavigationItem = &#34;Add, update, next&#34;
X.aspdb

Response.Write(&#34;sqlStatement = &#34; & sqlStatement & &#34;<BR>&#34
%>


------------
John at 4/6/01 4:20:13 PM


Hi Lennart,

Looks like you&#39;re doing things right, but I&#39;m afraid there&#39;s a problem in building up your dbSQL property. I have a feeling that the value passed in your querystring isn&#39;t quite what you expect it to be. Here&#39;s how I&#39;d fix the problem.

After your statement when you build up your sqlStatement variable, add:

Response.write(&#34;sqlStatement [&#34; & sqlStatement & &#34;]<BR>&#34

Then watch that output. I have a feeling that you&#39;ll see something wrong with the sql (missing quote, string vs number, etc.) that&#39;s causing your problem. If it&#39;s still not obvious, strip your program down to the minimum that fails and post it here with the output results (including the sqlStatement output).

Thanks,
John

------------
Lennart at 3/28/01 10:09:02 AM

Thanks for your thoughts, John.

Although not mentioned in my posting, I&#39;ve tried all your points.

I&#39;m getting the Top because I tried setting the dbRecordCount to 1, to see if that makes a change (but doesn&#39;t).
I&#39;ve checked output of all variables and gone through all dbDebug options.

The thing is that all works OK, until I&#39;m specifying something in the querystring. Then it does not pass my SQL statement to ASPdb (although the ouput of my variables show me it&#39;s there), and generates this error of &#39;Command text not set. . .&#34; ???????

I&#39;m clueless. . . .

------------
John at 3/27/01 11:14:36 PM

Hi,

A couple thoughts...

1) If you&#39;re getting the &#34;TOP SQL&#34; part in there, are you setting dbDBType properly? Make sure you&#39;ve set that to the type of DB you&#39;re using.

2) Try doing a response.write of your sqlStatement variable so that you can see what it is you&#39;re actually executing. That&#39;s always a great way to catch bugs that creep in when dropping variables into your strings.

3) Turn on the debug function (dbDebug) and check out what you&#39;re executing. See the docs for the various options, but you can watch the SQL statement that gets executed along with many more.

Hope this helps,

John


------------
Lenanrt at 3/27/01 10:22:00 AM

I&#39;m passing the ID-field of a record to ASPdb through the URL querystring, and keep getting this error:

&#34;Error # (Get_RS_OPEN - After open TOP SQL) = 80040E0C(-2147217908)
Description = Command text was not set for the command object.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State =
Native Error = 0&#34;

I&#39;m setting the variable(s) correctly to session variables, etc.
And the funny thing is, that when I include the ID-field variable directly in the ASPdb page, it works fine.
Only when I attempt to pass and get the variable through the querystring, it gives me this &#34;command text was not set error&#34;.
See below for example code:

Any ideas are much appreciated.
Thanks

EXAMPLE CODE
--------------------------

setID = Cint(Request(&#34;ID&#34)

Set X= Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit = &#34;3&#34;

if (setID <> &#34;&#34 then
Session(&#34;setID&#34 = setID
X.dbReset(3)
end if

WhereClause = Session(&#34;setID&#34

sqlStatement = &#34;Select * FROM tblServices WHERE (ServiceID = &#34; & WhereClause & &#34&#34;
X.dbSQL = sqlStatement
X.dbStartUp = &#34;EditUpdate&#34;
X.dbNavigationItem = &#34;add, update, delete&#34;

ET CETERA

[Mark]

Lennart,

Please post your exact code, not an old version that you have since modified. Also, you said it &#34;gives the same error message&#34;. Could you please tell us what that error message was? Also you have a line in your code that says:

Response.Write(&#34;sqlStatement = &#34; & sqlStatement & &#34;<BR>&#34

It would be helpful to know the result of that response.write.

Thanks,

Mark.





------------
lennart at 4/11/01 5:34:24 PM

For the record, I had your syntax before, but overlooked it when posting the reply. Sorry

The interesting part still is that when:
>> I supply the &#39;mypage.asp?ID=value&#39; in the querystring is gives the same error message.
>> then when I just request the &#34;mypage.asp&#34; is works, using the Session(&#34;RecordID&#34 that was set in the previous action

In other words, ASPdb stops functioning as soon as you specify a querystring!!

Lennart



------------
Mark at 4/11/01 5:21:23 PM

Lennart,
I think you just have one small mistake.
Change you SQL statement from:

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & recordID

to:

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & Session(&#34;RecordID&#34

Mark.


------------
Lennart at 4/11/01 5:13:46 PM

below is the code.

What happens is:
>>when I &#39;hard code&#39; the sqlStatement with a given recordID, aspDB EditUpdate mode works fine
>>until I supply a value in the querystring (e.g. &#39;page.asp?ID=2&#39; )

In other words, even though the sqlstatement has all the values it requires (coded into the page, not using Request), aspDB ONLY works if nothing is specified in the URL querystring!!!


CODE:
<%
strRecordID = Request.QueryString(&#34;ID&#34

recordID = Cint(strRecordID)

Set X=Server.CreateObject(&#34;ASP.db&#34

X.dbUnit = 111

If recordID <> 0 Then
Session(&#34;recordID&#34 = recordID
X.dbReset(111)
End If

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & recordID

X.dbDSN = &#34;DSN=portal; UID=sa; PWD=&#34;
X.dbDBType = &#34;SQL&#34;
X.dbSQL=sqlStatement
X.dbStartUp = &#34;editupdate&#34;
X.dbEditParams = &#34;(;,)TableName=tblServices,BookMarkFlds=0&# 34;
X.dbEditFlds = &#34;ServiceID, ServiceNm&#34;
X.dbNavigationItem = &#34;Add, update, next&#34;
X.aspdb

Response.Write(&#34;sqlStatement = &#34; & sqlStatement & &#34;<BR>&#34
%>


------------
John at 4/6/01 4:20:13 PM


Hi Lennart,

Looks like you&#39;re doing things right, but I&#39;m afraid there&#39;s a problem in building up your dbSQL property. I have a feeling that the value passed in your querystring isn&#39;t quite what you expect it to be. Here&#39;s how I&#39;d fix the problem.

After your statement when you build up your sqlStatement variable, add:

Response.write(&#34;sqlStatement [&#34; & sqlStatement & &#34;]<BR>&#34

Then watch that output. I have a feeling that you&#39;ll see something wrong with the sql (missing quote, string vs number, etc.) that&#39;s causing your problem. If it&#39;s still not obvious, strip your program down to the minimum that fails and post it here with the output results (including the sqlStatement output).

Thanks,
John

------------
Lennart at 3/28/01 10:09:02 AM

Thanks for your thoughts, John.

Although not mentioned in my posting, I&#39;ve tried all your points.

I&#39;m getting the Top because I tried setting the dbRecordCount to 1, to see if that makes a change (but doesn&#39;t).
I&#39;ve checked output of all variables and gone through all dbDebug options.

The thing is that all works OK, until I&#39;m specifying something in the querystring. Then it does not pass my SQL statement to ASPdb (although the ouput of my variables show me it&#39;s there), and generates this error of &#39;Command text not set. . .&#34; ???????

I&#39;m clueless. . . .

------------
John at 3/27/01 11:14:36 PM

Hi,

A couple thoughts...

1) If you&#39;re getting the &#34;TOP SQL&#34; part in there, are you setting dbDBType properly? Make sure you&#39;ve set that to the type of DB you&#39;re using.

2) Try doing a response.write of your sqlStatement variable so that you can see what it is you&#39;re actually executing. That&#39;s always a great way to catch bugs that creep in when dropping variables into your strings.

3) Turn on the debug function (dbDebug) and check out what you&#39;re executing. See the docs for the various options, but you can watch the SQL statement that gets executed along with many more.

Hope this helps,

John


------------
Lenanrt at 3/27/01 10:22:00 AM

I&#39;m passing the ID-field of a record to ASPdb through the URL querystring, and keep getting this error:

&#34;Error # (Get_RS_OPEN - After open TOP SQL) = 80040E0C(-2147217908)
Description = Command text was not set for the command object.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State =
Native Error = 0&#34;

I&#39;m setting the variable(s) correctly to session variables, etc.
And the funny thing is, that when I include the ID-field variable directly in the ASPdb page, it works fine.
Only when I attempt to pass and get the variable through the querystring, it gives me this &#34;command text was not set error&#34;.
See below for example code:

Any ideas are much appreciated.
Thanks

EXAMPLE CODE
--------------------------

setID = Cint(Request(&#34;ID&#34)

Set X= Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit = &#34;3&#34;

if (setID <> &#34;&#34 then
Session(&#34;setID&#34 = setID
X.dbReset(3)
end if

WhereClause = Session(&#34;setID&#34

sqlStatement = &#34;Select * FROM tblServices WHERE (ServiceID = &#34; & WhereClause & &#34&#34;
X.dbSQL = sqlStatement
X.dbStartUp = &#34;EditUpdate&#34;
X.dbNavigationItem = &#34;add, update, delete&#34;

ET CETERA

[Lennart]

Code & error message have already been posted in first message!!

The sqlStatement gives exactly what it should give:

&#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = 1&#34; Or whatever number passed to recordID variable


------------
Mark at 4/12/01 9:14:05 AM

Lennart,

Please post your exact code, not an old version that you have since modified. Also, you said it &#34;gives the same error message&#34;. Could you please tell us what that error message was? Also you have a line in your code that says:

Response.Write(&#34;sqlStatement = &#34; & sqlStatement & &#34;<BR>&#34

It would be helpful to know the result of that response.write.

Thanks,

Mark.





------------
lennart at 4/11/01 5:34:24 PM

For the record, I had your syntax before, but overlooked it when posting the reply. Sorry

The interesting part still is that when:
>> I supply the &#39;mypage.asp?ID=value&#39; in the querystring is gives the same error message.
>> then when I just request the &#34;mypage.asp&#34; is works, using the Session(&#34;RecordID&#34 that was set in the previous action

In other words, ASPdb stops functioning as soon as you specify a querystring!!

Lennart



------------
Mark at 4/11/01 5:21:23 PM

Lennart,
I think you just have one small mistake.
Change you SQL statement from:

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & recordID

to:

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & Session(&#34;RecordID&#34

Mark.


------------
Lennart at 4/11/01 5:13:46 PM

below is the code.

What happens is:
>>when I &#39;hard code&#39; the sqlStatement with a given recordID, aspDB EditUpdate mode works fine
>>until I supply a value in the querystring (e.g. &#39;page.asp?ID=2&#39; )

In other words, even though the sqlstatement has all the values it requires (coded into the page, not using Request), aspDB ONLY works if nothing is specified in the URL querystring!!!


CODE:
<%
strRecordID = Request.QueryString(&#34;ID&#34

recordID = Cint(strRecordID)

Set X=Server.CreateObject(&#34;ASP.db&#34

X.dbUnit = 111

If recordID <> 0 Then
Session(&#34;recordID&#34 = recordID
X.dbReset(111)
End If

sqlStatement = &#34;SELECT ServiceID, ServiceNm, type FROM tblServices WHERE ServiceID = &#34; & recordID

X.dbDSN = &#34;DSN=portal; UID=sa; PWD=&#34;
X.dbDBType = &#34;SQL&#34;
X.dbSQL=sqlStatement
X.dbStartUp = &#34;editupdate&#34;
X.dbEditParams = &#34;(;,)TableName=tblServices,BookMarkFlds=0&# 34;
X.dbEditFlds = &#34;ServiceID, ServiceNm&#34;
X.dbNavigationItem = &#34;Add, update, next&#34;
X.aspdb

Response.Write(&#34;sqlStatement = &#34; & sqlStatement & &#34;<BR>&#34
%>


------------
John at 4/6/01 4:20:13 PM


Hi Lennart,

Looks like you&#39;re doing things right, but I&#39;m afraid there&#39;s a problem in building up your dbSQL property. I have a feeling that the value passed in your querystring isn&#39;t quite what you expect it to be. Here&#39;s how I&#39;d fix the problem.

After your statement when you build up your sqlStatement variable, add:

Response.write(&#34;sqlStatement [&#34; & sqlStatement & &#34;]<BR>&#34

Then watch that output. I have a feeling that you&#39;ll see something wrong with the sql (missing quote, string vs number, etc.) that&#39;s causing your problem. If it&#39;s still not obvious, strip your program down to the minimum that fails and post it here with the output results (including the sqlStatement output).

Thanks,
John

------------
Lennart at 3/28/01 10:09:02 AM

Thanks for your thoughts, John.

Although not mentioned in my posting, I&#39;ve tried all your points.

I&#39;m getting the Top because I tried setting the dbRecordCount to 1, to see if that makes a change (but doesn&#39;t).
I&#39;ve checked output of all variables and gone through all dbDebug options.

The thing is that all works OK, until I&#39;m specifying something in the querystring. Then it does not pass my SQL statement to ASPdb (although the ouput of my variables show me it&#39;s there), and generates this error of &#39;Command text not set. . .&#34; ???????

I&#39;m clueless. . . .

------------
John at 3/27/01 11:14:36 PM

Hi,

A couple thoughts...

1) If you&#39;re getting the &#34;TOP SQL&#34; part in there, are you setting dbDBType properly? Make sure you&#39;ve set that to the type of DB you&#39;re using.

2) Try doing a response.write of your sqlStatement variable so that you can see what it is you&#39;re actually executing. That&#39;s always a great way to catch bugs that creep in when dropping variables into your strings.

3) Turn on the debug function (dbDebug) and check out what you&#39;re executing. See the docs for the various options, but you can watch the SQL statement that gets executed along with many more.

Hope this helps,

John


------------
Lenanrt at 3/27/01 10:22:00 AM

I&#39;m passing the ID-field of a record to ASPdb through the URL querystring, and keep getting this error:

&#34;Error # (Get_RS_OPEN - After open TOP SQL) = 80040E0C(-2147217908)
Description = Command text was not set for the command object.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State =
Native Error = 0&#34;

I&#39;m setting the variable(s) correctly to session variables, etc.
And the funny thing is, that when I include the ID-field variable directly in the ASPdb page, it works fine.
Only when I attempt to pass and get the variable through the querystring, it gives me this &#34;command text was not set error&#34;.
See below for example code:

Any ideas are much appreciated.
Thanks

EXAMPLE CODE
--------------------------

setID = Cint(Request(&#34;ID&#34)

Set X= Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit = &#34;3&#34;

if (setID <> &#34;&#34 then
Session(&#34;setID&#34 = setID
X.dbReset(3)
end if

WhereClause = Session(&#34;setID&#34

sqlStatement = &#34;Select * FROM tblServices WHERE (ServiceID = &#34; & WhereClause & &#34&#34;
X.dbSQL = sqlStatement
X.dbStartUp = &#34;EditUpdate&#34;
X.dbNavigationItem = &#34;add, update, delete&#34;

ET CETERA