471,831 Members | 873 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,831 software developers and data experts.

How to handle, when SQL command returns a single value

Hi,

I have a problem in dealing with the return value of the SQL command. I know how to do, when SQL command returns Recordsets. But in my case it returns a single value. The following code fails when "objRst.open strQ" is executed. Can somebody suggest me how to overcome this problem.

Any Help is appreciated.
Thanks in advance,
Vasanth
-----------------------
Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFilePath & ";"
Set objRst = Server.CreateObject("ADODB.Recordset")
strQ = "Select MAX(index) from library where index like '*B-*';"
objRst.Open strQ
cmd.execute
while Not objRst.EOF
index=objRst("index")
objrst.movenext
Wend
Set objRst = nothing
objConnect.Close
Set objConnect = nothing
%>
<html>
<head>
<title>
</title>
</head>
<body>
<form action="newupdate.asp" method=post>
index:<%=index%>
</form>
</body>
</html>
----------------------------------------
I also tried the following alternative. Here it fails when I try to print "objRst" on the page.
Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFilePath & ";"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = objConnect
strQ = "Select MAX(index) from library where index like '*B-*';"
cmd.commandtext=strQ
set objRst = cmd.execute
objConnect.Close
Set objConnect = nothing
%>
<html>
<head>
<title>
</title>
</head>
<body>
<form action="newupdate.asp" method=post>
index:<%=objRst%>
</form>
</body>
</html>
Jul 19 '05 #1
2 2217
The problem is that you do not have a column named INDEX in your recordset.
You have an unnamed column that represents MAX(index). You can either name
it in your query or use the index value in the recordset to get it. (Not
the same "index" as in your column name.)

"Select MAX(index) as TheMax from library..."
index = objRst.Fields.Item("TheMax").Value

Or, leave your query as is and do:

index = objRst.Fields.Item(0).Value

Ray at work

"vasanth kumar" <va***********@eds.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
Hi,

I have a problem in dealing with the return value of the SQL command. I
know how to do, when SQL command returns Recordsets. But in my case it
returns a single value. The following code fails when "objRst.open strQ" is
executed. Can somebody suggest me how to overcome this problem.

Any Help is appreciated.
Thanks in advance,
Vasanth
-----------------------
Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
mdbFilePath & ";"
Set objRst = Server.CreateObject("ADODB.Recordset")
strQ = "Select MAX(index) from library where index like '*B-*';"
objRst.Open strQ
cmd.execute
while Not objRst.EOF
index=objRst("index")
objrst.movenext
Wend
Jul 19 '05 #2
Thanks for the help. It worked.

-Vasanth
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OH*************@TK2MSFTNGP12.phx.gbl...
The problem is that you do not have a column named INDEX in your recordset. You have an unnamed column that represents MAX(index). You can either name it in your query or use the index value in the recordset to get it. (Not
the same "index" as in your column name.)

"Select MAX(index) as TheMax from library..."
index = objRst.Fields.Item("TheMax").Value

Or, leave your query as is and do:

index = objRst.Fields.Item(0).Value

Ray at work

"vasanth kumar" <va***********@eds.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
Hi,

I have a problem in dealing with the return value of the SQL command. I know how to do, when SQL command returns Recordsets. But in my case it
returns a single value. The following code fails when "objRst.open strQ" is executed. Can somebody suggest me how to overcome this problem.

Any Help is appreciated.
Thanks in advance,
Vasanth
-----------------------
Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
mdbFilePath & ";"
Set objRst = Server.CreateObject("ADODB.Recordset")
strQ = "Select MAX(index) from library where index like '*B-*';"
objRst.Open strQ
cmd.execute
while Not objRst.EOF
index=objRst("index")
objrst.movenext
Wend

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.