How to handle, when SQL command returns a single value

V

vasanth kumar

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>
 
R

Ray at

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,902
Latest member
Elena68X5

Latest Threads

Top