passing SQL result to variable

sql server United States
  • 19 years ago

    Hmm.. I've the first entry..


    Got one question here. How do I pass the result of a SQL statement to a ASP script variable? My code is as follow:


    maxrcno = "select max('trreceiptno') from trreceipt_ag"
    conn.execute(maxrcno)

  • 19 years ago

    I'd would use a stand alone command object, you need to write a stored procedure to retrive the data, it needs to contain output parameters to pass values back the command object.


    Sub Main()
      'Dimension the command object
       Dim Cm


       'Setup the command passing the name of your sp to the GetCm function (see below)
       Set Cm = GetCm("<sp_Name>")
       'Set the parameters of the command (see function below)
       SetCommandParameter Cm, "<Param Name>", <Param Value>
       'Execute the command
       Cm.Execute


        'Read the output parameters into your ASP variable (see function below)
         <Asp Variable> = ReadCommandParameter(Cm, "<Param Name>")
    End Sub


    Function GetCm(spName)
       Dim Cm
       Dim Cn
       
       Set Cn = GetConnection()
       
       If Not IsEmpty(Cn) Then
               Set Cm = Server.CreateObject("ADODB.Command")
               With Cm
                   .CommandType = 4
                   .CommandText = spName
                   .ActiveConnection = Cn
               End With
       End If
       Set GetCm = Cm
    End Function



    Sub SetCommandParameter(Cm, ParameterName, ParameterValue)
       On Error Resume Next
       Cm.Parameters("@" & ParameterName).Value = ParameterValue


         If Err.Number <> 0 Then
           Cm.Parameters(ParameterName).Value = ParameterValue
       End If
    End Sub


    Function ReadCommandParameter(Cm, ParameterName)


       Err.Clear
       On error resume next
       ReadCommandParameter = Cm.Parameters("@" & ParameterName).Value
       If Err.Count = 0 Then
           Exit Function
       End If
       Err = 0
       
       ReadCommandParameter = Cm.Parameters(ParameterName).Value
       
    End Function


    'The get connection function picks up the connection string from the Application collection, the connection string is set in the Global.asa ApplicationOnstart event.
    'Example of Application OnStart() event
    Sub Application
    OnStart()
       Application("ConnectString") = "Provider=SQLOLEDB;DRIVER=SQLs erver;SERVER=<ServerName>;uid=<SQLLogin>;pwd=<Password>;DATABASE=<DBName>"    
    End Sub
       
    Function GetConnection()
       Dim Cn


        Set Cn = Server.CreateObject("ADODB.Connection")
        Cn.ConnectionString = Application("ConnectString")


        Cn.Open


        Set GetConnection = Cn
     
    End Function

  • 19 years ago

    Thats an awful lot of code. I would just do the following.


    dim rec, maxrec
    set rec = server.createobject("adodb.recordset")
    maxrcno = "select max('trreceiptno') from trreceipt_ag"
    set rec = conn.execute(maxrcno)
    maxrec = rec.fields(0).value


    It's also worth remembering that alot of web authors use remote hosts and might not have access to global.asa etc.

  • 19 years ago

    Musician,


    If you don't have access to the Global.asa you would have to hard code the connection string in the GetConnection() routine.


    You can place all the generic functions into a seperate .vbs file and reference it from your asp page. This means you can reuse it from any of your asp pages. Also using command objects is a lot faster and more efficient than using a recordset. Using stored procedures is also a lot faster and more efficient because Sql Server pre-compiles them.


    Regards


    P

  • 19 years ago

    I agree Stored Procedures are much more efficient. The benefits increase with larger sql procedures. As a webmaster using asp and sql server provided by a remote host I use alot of stored procedures. However I see little visible improvement on small procedures like getting the max value from a table and assigning it to a variable. Obviously if a table contains alot of rows then the sp would show much more efficiency but it pays to know all methods of doing something including the easy less efficient way.
    I tend to assume people posting here are relatively new to asp and as a result demonstrate the simplest way of acheiving it. Especially since I don't know what database they might be using. This does not mean you should assume I don't know all possible methods available. If your method is more efficient then I say use it but I was giving an easier alternative in case the poster was new to this game.
    To be specific to your code I would advise opening a connection and pooling it to every part of your code on the asp page that needs it. Initialise the connection at the beginning of the page and use it anywhere it is needed.


    i.e.
    'start of asp page
    Dim conn
    set conn = server.createobject("adodb.connection")
    .
    .
    .
    'later on in your asp page
    rec.open spname, conn
    .
    .
    .
    'even later in asp page
    rec2.open mysql, conn
    .
    .
    .
    .
    'end of page
    rec.close
    set rec = nothing
    rec2.close
    set rec2 = nothing
    if conn.state = adstateopen then
       conn.close
    end if
    set conn = nothing



    I typically have the initialising of connection/command/recordset objects in a file included at the beginning of the asp page and then the destorying of objects in a file included at the end. Connection pooling is very efficient.

  • 19 years ago

    Musician,


    In a production environment I always place DB related code like this in a Com+ component, mostly to protect the code from prying eyes but also for speed / efficiency.


    If you you  are using the OLEDB provider for SQL server and have MDAC 2.+ installed on the server then as long as you instance your connection objects in the same process the OLEDB provider will handle all the connection pooling for you. You must also make sure your connection credentials don't change or OLEDB will create a separate pool.


    Because I put all my DB related code into the same Com+ configured component I don't have to worry about pooling OLEDB handles it for me


    I assume that connection pooling will work the same way for the Inetinfo.exe process when it is processing connection objects instanced in asp's but I am not totally sure. If it does then it is more efficient to dimesion the Connection object inside each function so that it is released back into the pool and free to be used again. Ted Pattitson has written a really good book called Programming Distributed Applications with Com+, I found it very useful.


    It is nice to see other knowledgable people like your good self on the boards


    Regards


    P


Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“In order to understand recursion, one must first understand recursion.”