none
ASP + SQL 2008 data base connection in STORED PROCEDURE

    Question

  • Hello. I am still working with asp classic. I must to do the connection to the data base using Stored Procedure.
    My ASP script is:
    Set cnn = Server.CreateObject("ADODB.Connection")
    cnn.open "PROVIDER=XXX;DATA SOURCE=XXX;UID=XXX;PWD=XXX;DATABASE=XXX"
    How must I do?
    Thank you very much.
    PD. I cant found anything in Google or I don't search by right way.


    ASP vs ASP.net? puede ser, no entiendo soy nuevo

    Tuesday, May 07, 2013 12:42 PM

All replies

  • Hi,

    Please try this:

       <%@ LANGUAGE="VBSCRIPT" %>
       <!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"-->
       <HTML>
       <HEAD><TITLE>Place Document Title Here</TITLE></HEAD>
       <BODY>
       This first method queries the data source about the parameters
       of the stored procedure. This is the least efficient method of calling
       a stored procedure.<BR>
       <%
       Set cn = Server.CreateObject("ADODB.Connection")
       Set cmd = Server.CreateObject("ADODB.Command")
       cn.Open "data source name", "userid", "password"
       Set cmd.ActiveConnection = cn
       cmd.CommandText = "sp_test"
       cmd.CommandType = adCmdStoredProc
       ' Ask the server about the parameters for the stored proc
       cmd.Parameters.Refresh
       ' Assign a value to the 2nd parameter.
       ' Index of 0 represents first parameter.
       cmd.Parameters(1) = 11
       cmd.Execute
       %>
       Calling via method 1<BR>
       ReturnValue = <% Response.Write cmd.Parameters(0) %><P>
    
       <!-- ************************************************************ -->
    
       Method 2 declares the stored procedure, and then explicitly declares
       the parameters.<BR>
       <%
       Set cn = Server.CreateObject("ADODB.Connection")
       cn.Open "data source name", "userid", "password"
       Set cmd = Server.CreateObject("ADODB.Command")
       Set cmd.ActiveConnection = cn
       cmd.CommandText = "sp_test"
       cmd.CommandType = adCmdStoredProc
       cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _
          adParamReturnValue)
       cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
          adParamInput)
       ' Set value of Param1 of the default collection to 22
       cmd("Param1") = 22
       cmd.Execute
       %>
       Calling via method 2<BR>
       ReturnValue = <% Response.Write cmd(0) %><P>
    
       <!-- ************************************************************ -->
    
       Method 3 is probably the most formal way of calling a stored procedure.
       It uses the canocial
       <%
       Set cn = Server.CreateObject("ADODB.Connection")
       cn.Open "data source name", "userid", "password"
       Set cmd = Server.CreateObject("ADODB.Command")
       Set cmd.ActiveConnection = cn
       ' Define the stored procedure's inputs and outputs
       ' Question marks act as placeholders for each parameter for the
       ' stored procedure
       cmd.CommandText = "{?=call sp_test(?)}"
       ' specify parameter info 1 by 1 in the order of the question marks
       ' specified when we defined the stored procedure
       cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _
       adParamReturnValue)
       cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
         adParamInput)
       cmd.Parameters("Param1") = 33
       cmd.Execute
       %>
       Calling via method 3<BR>
       ReturnValue = <% Response.Write cmd("RetVal") %><P>
       </BODY>
       </HTML>
    	

    Source: http://support.microsoft.com/kb/164485

    Hope this helps,



    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    Tuesday, May 07, 2013 1:01 PM
  • Hello Pedro, I see but dont understand how use SP (what I must to write on it) to do the connection to de DB. Another thing, if I write the server_name, userid and pass on the ASP file, for what I need the SP. Thank you very much.

    ASP vs ASP.net? puede ser, no entiendo soy nuevo

    Wednesday, May 08, 2013 2:40 PM
  • Another thing Pedro, you are not tell me how to do a connection to the sql database using SP, you are show me how to work between ASP and SP SQL. Do you know how do a connection with SP? Thanks again for your help.

    ASP vs ASP.net? puede ser, no entiendo soy nuevo

    Monday, May 13, 2013 2:07 PM
  • Hi boraemi, You do not need to use SP(stored procedure) to connect to a database.


    Regards, RSingh

    Monday, May 13, 2013 2:41 PM
  • Hi boraemi,

    Stored procedure are some Transact-SQL statements which can execute within SQL Server, after connecting to SQL Server with our ASP page, we can execute the stored procedure. Please refer to the following document about how to access SQL Server in Active Server Pages.

    How to access SQL Server in Active Server Pages
    http://support.microsoft.com/kb/169377/en-us


    Allen Li
    TechNet Community Support

    Wednesday, May 15, 2013 8:28 AM
    Moderator