Stored Procedure To Create New Record

Results 1 to 3 of 3

Thread: Stored Procedure To Create New Record

  1. #1
    Matt Riley Guest

    Default Stored Procedure To Create New Record

    I am using a database to store record details in SQL Server in a table.<BR>I have created a stored procedure called spCreateMortgageUser to create a<BR>new record and return the GUID of the record called MAID.<BR><BR>------Stored Procedure-------<BR>CREATE PROCEDURE spCreateMortgageUser @MAID uniqueidentifier OUTPUT<BR>AS<BR>INSERT INTO tblMortgageApplications(Purpose)<BR>VALUES(1) <BR><BR>SELECT @MAID = MAID FROM tblMortgageApplications<BR><BR>[Purpose] is just a default value which will be changed later.<BR><BR>I am trying to return the MAID value to set in the clients cookie for<BR>future use and have written the following test code to display the MAID<BR>value.<BR><BR>------ASP Code---------<BR>&#060;%@ LANGUAGE="VBScript"%&#062;<BR>&#060;% <BR>Option Explicit <BR>Const adCmdStoredProc = 4<BR>Const adGUID = 72<BR>Const adParamOutput = &H0002<BR>Dim conMortgages<BR>Dim strMortgagesCon<BR>Dim cmdspCreateMortgageUser<BR>Dim prmspCreateMortgageUser<BR>Dim rstMAID<BR><BR>Set conMortgages = Server.CreateObject("ADODB.Connection")<BR>strMort gagesCon = "Data Source=MoneySave;User ID=****;Password=*****"<BR>conMortgages.Open strMortgagesCon<BR><BR>Set cmdspCreateMortgageUser = Server.CreateObject("ADODB.Command")<BR>Set cmdspCreateMortgageUser.ActiveConnection = conMortgages<BR>cmdspCreateMortgageUser.CommandTex t = "spCreateMortgageUser"<BR>cmdspCreateMortgageUser. CommandType = adCmdStoredProc<BR><BR>Set prmspCreateMortgageUser =<BR>cmdspCreateMortgageUser.CreateParameter("@MAI D",adGUID,adParamOutput)<BR>cmdspCreateMortgageUse r.Parameters.Append prmspCreateMortgageUser<BR><BR>Set rstMAID = Server.CreateObject("ADODB.Recordset")<BR>Set rstMAID = cmdspCreateMortgageUser.Execute<BR><BR>Response.Wr ite "MAID :" & rstMAID(0)<BR><BR>rstMAID.Close<BR>conMortgages.Cl ose<BR>Set conMortgages = Nothing<BR><BR>%&#062;<BR><BR>It returns the following message:<BR>-------HTML-----------<BR><BR>ADODB.Fields error &#039 800a0cc1&#039 <BR>ADO could not find the object in the collection corresponding to the name<BR>or ordinal reference requested by the application. <BR>/moneysave/Andy/DB Access Tutorials/StoredProcedure.asp, line 28 <BR><BR><BR>Does anyone have any ideas?<BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    778

    Default RE: Stored Procedure To Create New Record

    Matt,<BR><BR>Why don&#039t you give this a try.<BR><BR>Set prmspCreateMortgageUser =<BR>cmdspCreateMortgageUser.CreateParameter("@MAI D",adGUID,adParamOutput)<BR>cmdspCreateMortgageUse r.Parameters.Append prmspCreateMortgageUser<BR><BR>cmdspCreateMortgage User.Execute<BR>response.write cmdspCreateMortgageUser("@MAID")<BR><BR>my ASP has examples that always seem to go in this direction. You should only create a recordset when you are returning large chunks of data, not when you are just returning a single value.<BR><BR>hope this works,<BR><BR>bob<BR>

  3. #3
    Matt Riley Guest

    Default RE: Stored Procedure To Create New Record

    Many Thanks Bob, that works fine.

Posting Permissions

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