Calling Stored Procedures from MS SQL 7 using ASP

Results 1 to 2 of 2

Thread: Calling Stored Procedures from MS SQL 7 using ASP

  1. #1
    Join Date
    Dec 1969

    Default Calling Stored Procedures from MS SQL 7 using ASP

    I am working on creating an Intranet site for my work and am using ASP to do it. I am new to writing ASP code and have come a long way but I have been using allot of the code snippets from 4Guys. I saw your article on Writing Stored Procedures and am trying to use it for a particular thing I am working on. Let me explain to you what I am trying to accomplish and hopefully you could give me some pointers or advice as to what I am doing wrong or what I need to do.<BR><BR>My DBA has made numerous Stored Procedures in MS SQL 7 and I am needing to use those in my Intranet site. I know that I need to call the procedure to retrieve a variable. In the stored procedure he is trying to get the Next Available ID from a table and I need to find out what it is and put it into a variable like NextFreeId. That would mean that when calling the Stored Procedure it would search the Table and determine what has been used and take the last one and add 1 to it. That would then give me the next available ID. NextFreeId = ??? I would then use it to insert a new record of ID, LoginName, Password. The ID being the NextFreeID.<BR><BR>This is what I am thinking the code would look like for me:<BR>&#060;%<BR> Dim DataConnection, SQL, RecordSet, NextFreeId<BR><BR> Set Dataconnection = Server.CreateObject ("ADODB.Connection")<BR> DataConnection.ConnectionString = "DRIVER={SQL Server};DSN=ServerDB;SERVER=SQL2;UID=sa;PWD=;DATAB ASE=server104" <BR> DataConnection.Open <BR> Set cmdDC = Server.CreateObject("ADODB.Command")<BR> Set cmdDC.ActiveConnection = DataConnection<BR> SQL = "dbo.Tax_GetNextFreeID" & NextFreeId<BR> Set RecordSet = DataConnection.Execute(SQL) <BR> NextFreeId = SQL<BR>%&#062; <BR><BR>Please give me any feedback on this and tell me if I am doing this right. I would very much appreciate it.

  2. #2
    Join Date
    Dec 1969

    Default RE: Calling Stored Procedures from MS SQL 7 using

    try this way<BR><BR> Dim conn &#039As ADODB.Connection<BR> Dim comm &#039As ADODB.Command<BR> Set conn = CreateObject("ADODB.Connection")<BR> Set comm = CreateObject("ADODB.Command")<BR> dim objRS &#039 as ADO Recordset<BR> set objRS = CreateObject("ADODB.Recordset")<BR><BR> conn.Open "DSN=your dns;UID=userid;PWD=pass"<BR> Set comm.ActiveConnection = conn<BR><BR> comm.CommandType = 1 &#039adCmdText<BR> comm.CommandText = "{call your_stored_procedure(&#039"& input_variable &"&#039,{resultset 20, Your_output_variable})}"<BR><BR> comm.Execute<BR><BR> &#039Set up recordset<BR> objRS.CursorType = 3 &#039adOpenStatic<BR> objRS.LockType = 1 &#039adLockReadOnly<BR> set objRS.Source = comm<BR><BR> &#039Now get the recordset from the stored procedure<BR> objRS.Open<BR> objRS.movefirst<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts