469,352 Members | 1,697 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

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 2159
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 this site, you agree to our Privacy Policy and Terms of Use.