Professional Web Applications Themes

Help with Stored Procedure returning recordset - Microsoft SQL / MS SQL Server

Hi, Try to add set nocount on in the beginning of procedure. "Scott McDaniel" <junkjunk.com> wrote in message news:lf6cnfj1SI2zZZ-iU-KYvAcomcast.com... > I have a stored procedure on SQL Server 2000: > > CREATE PROCEDURE [dbo].[AddNewEmployee] > > Prefix varchar (10), > FName varchar (50), > LName varchar (50), > MiddleInit varchar (10), > Street varchar (100), > Street2 varchar (100), > City varchar (50), > State varchar (2), > Zip varchar (10), > Phone varchar (15), > EmerName varchar (50), > EmerPhone varchar (10), > SSN varchar (15), > EmpType int, > EmpStatus int, > DateHired datetime, > DateFired datetime > ...

  1. #1

    Default Re: Help with Stored Procedure returning recordset


    Hi,

    Try to add set nocount on in the beginning of procedure.

    "Scott McDaniel" <junkjunk.com> wrote in message
    news:lf6cnfj1SI2zZZ-iU-KYvAcomcast.com...
    > I have a stored procedure on SQL Server 2000:
    >
    > CREATE PROCEDURE [dbo].[AddNewEmployee]
    >
    > Prefix varchar (10),
    > FName varchar (50),
    > LName varchar (50),
    > MiddleInit varchar (10),
    > Street varchar (100),
    > Street2 varchar (100),
    > City varchar (50),
    > State varchar (2),
    > Zip varchar (10),
    > Phone varchar (15),
    > EmerName varchar (50),
    > EmerPhone varchar (10),
    > SSN varchar (15),
    > EmpType int,
    > EmpStatus int,
    > DateHired datetime,
    > DateFired datetime
    >
    > AS
    >
    > BEGIN
    >
    > INSERT INTO tblEmployees(strPrefix, strFName, strLName, strMiddleInit,
    > strStreet, strStreet2, strCity, strState, strZip, strPhone, strEmerName,
    > strEmerContact,
    > strSSN, intEmpType, intStatus, dteDateHired, dteDateTerminated)
    > VALUES(Prefix, FName, LName, MiddleInit, Street, Street2, City,
    > State, Zip, Phone, EmerName, EmerPhone, SSN, EmpType,
    > EmpStatus, DateHired, DateFired)
    >
    > SELECT IDENTITY AS NewID
    >
    > END
    > GO
    >
    >
    > In my VB program, I use the following code to add a new Employee record:
    >
    >
    > Dim strEmp As String
    > Dim rst As ADODB.Recordset
    >
    > With mEmp
    > strEmp = "EXECUTE AddNewEmployee '" & .Prefix & "','" & .FName & "','" &
    > .LName & "','" & .MiddleInit & "','" _
    > & .Street & "','" & .Street2 & "','" & .City & "','" & .State & "','"
    &
    > .Zip & "','" & .Phone & "','" _
    > & .EmerName & "','" & .EmerContact & "','" & .SSN & "'," & .EmpTypeID
    &
    > "," & .EmpStatusID & ",'" _
    > & .DateHired & "','" & .DateFired & "'"
    > End With
    >
    > Set rst = gCON.Execute(strEmp)
    >
    > If Not IsNull(rst("NewID")) Then
    > MsgBox rst("NewID")
    > Else
    > MsgBox "no id"
    > End If
    >
    > Set rst = Nothing
    >
    > This is in a class module, and mEmp is a UDT that is declared as Private
    at
    > the Module level. The record inserts fine, but the SP won't return any
    > values. The error I get is :
    >
    > Runtime Error 3265
    > Item cannot be found in the collection
    >
    > Am I calling this wrong, or have I written the SP incorrectly? I've looked
    > through many examples but can't seem to find my error.
    >
    > Thanks in advance.
    >
    >
    >

    Ivar Guest

  2. #2

    Default Help with Stored Procedure returning recordset

    Scott:

    Another caveat: If you have a trigger on the tblEmployees
    table, the IDENTITY function may return undesired
    values. You may want to use SCOPE_IDENTITY instead. See
    BOL for more info.

    Vern
    >-----Original Message-----
    >I have a stored procedure on SQL Server 2000:
    >
    >CREATE PROCEDURE [dbo].[AddNewEmployee]
    >
    >Prefix varchar (10),
    >FName varchar (50),
    >LName varchar (50),
    >MiddleInit varchar (10),
    >Street varchar (100),
    >Street2 varchar (100),
    >City varchar (50),
    >State varchar (2),
    >Zip varchar (10),
    >Phone varchar (15),
    >EmerName varchar (50),
    >EmerPhone varchar (10),
    >SSN varchar (15),
    >EmpType int,
    >EmpStatus int,
    >DateHired datetime,
    >DateFired datetime
    >
    >AS
    >
    >BEGIN
    >
    >INSERT INTO tblEmployees(strPrefix, strFName, strLName,
    strMiddleInit,
    >strStreet, strStreet2, strCity, strState, strZip,
    strPhone, strEmerName,
    >strEmerContact,
    >strSSN, intEmpType, intStatus, dteDateHired,
    dteDateTerminated)
    >VALUES(Prefix, FName, LName, MiddleInit, Street,
    Street2, City,
    >State, Zip, Phone, EmerName, EmerPhone, SSN,
    EmpType,
    >EmpStatus, DateHired, DateFired)
    >
    >SELECT IDENTITY AS NewID
    >
    >END
    >GO
    >
    >
    >In my VB program, I use the following code to add a new
    Employee record:
    >
    >
    >Dim strEmp As String
    >Dim rst As ADODB.Recordset
    >
    >With mEmp
    > strEmp = "EXECUTE AddNewEmployee '" & .Prefix & "','"
    & .FName & "','" &
    >..LName & "','" & .MiddleInit & "','" _
    > & .Street & "','" & .Street2 & "','" & .City & "','"
    & .State & "','" &
    >..Zip & "','" & .Phone & "','" _
    > & .EmerName & "','" & .EmerContact & "','" & .SSN
    & "'," & .EmpTypeID &
    >"," & .EmpStatusID & ",'" _
    > & .DateHired & "','" & .DateFired & "'"
    >End With
    >
    >Set rst = gCON.Execute(strEmp)
    >
    >If Not IsNull(rst("NewID")) Then
    > MsgBox rst("NewID")
    >Else
    > MsgBox "no id"
    >End If
    >
    >Set rst = Nothing
    >
    >This is in a class module, and mEmp is a UDT that is
    declared as Private at
    >the Module level. The record inserts fine, but the SP
    won't return any
    >values. The error I get is :
    >
    >Runtime Error 3265
    >Item cannot be found in the collection
    >
    >Am I calling this wrong, or have I written the SP
    incorrectly? I've looked
    >through many examples but can't seem to find my error.
    >
    >Thanks in advance.
    >
    >
    >
    >.
    >
    Vern Rabe Guest

Similar Threads

  1. Help - stored procedure not returning a recordset
    By Bill S. in forum ASP Database
    Replies: 5
    Last Post: February 13th, 10:06 AM
  2. Suppressing second recordset from stored procedure
    By A_X_L_V in forum ASP Database
    Replies: 5
    Last Post: November 6th, 09:54 PM
  3. Stored Procedure not returning values.
    By Bob Barrows in forum ASP
    Replies: 2
    Last Post: August 14th, 12:31 PM
  4. Stored Procedure vs. SQL Through Recordset.
    By craig v in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 10th, 03:15 PM
  5. Stored Procedure Not Returning
    By Amanda Osment in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 02:07 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139