How to handle, when SQL command returns a single value

Discussion in 'ASP General' started by vasanth kumar, Jul 14, 2004.

  1. 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>
     
    vasanth kumar, Jul 14, 2004
    #1
    1. Advertising

  2. vasanth kumar

    Ray at Guest

    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" <> wrote in message
    news:%...
    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
     
    Ray at, Jul 14, 2004
    #2
    1. Advertising

  3. Thanks for the help. It worked.

    -Vasanth
    "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
    message news:...
    > 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" <> wrote in message
    > news:%...
    > 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
    >
    >
     
    vasanth kumar, Jul 18, 2004
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. SStory
    Replies:
    5
    Views:
    5,786
    SStory
    Jun 7, 2004
  2. Leon
    Replies:
    2
    Views:
    564
  3. MIUSS
    Replies:
    6
    Views:
    866
    John Harrison
    Feb 28, 2007
  4. bogdan
    Replies:
    3
    Views:
    592
    bruce barker
    Apr 8, 2008
  5. Jack
    Replies:
    4
    Views:
    187
Loading...

Share This Page