Professional Web Applications Themes

Something rare! - Microsoft SQL / MS SQL Server

Hi, I am having a rare situation when executing this store procedure: create procedure test AS declare pk int begin tran insert tbltest (...) values(...) if ERROR = 0 BEGIN COMMIT TRAN SELECT * FROM vwWorkOrder WHERE PK=IDENTITY END else ROLLBACK TRAN GO this store procedure have no error when running with Query yzer but I hit EOF/BOF when executing it with ASP/COM HELP!!! Thanks, Kenny...

  1. #1

    Default Something rare!

    Hi,

    I am having a rare situation when executing this store procedure:

    create procedure test
    AS
    declare pk int
    begin tran
    insert tbltest (...) values(...)
    if ERROR = 0
    BEGIN
    COMMIT TRAN
    SELECT * FROM vwWorkOrder WHERE PK=IDENTITY
    END
    else
    ROLLBACK TRAN
    GO

    this store procedure have no error when running with Query yzer
    but I hit EOF/BOF when executing it with ASP/COM

    HELP!!!

    Thanks,
    Kenny


    Kenny Guest

  2. #2

    Default Re: Something rare!

    I think it could be a problem with the cursor type that you are using in
    ADO. Maybe you can post your ASP code and we could give an answer.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Kenny" <com> wrote in message
    news:phx.gbl... 


    SriSamp Guest

  3. #3

    Default Re: Something rare!

    Anything wrong with the cursor?

    rs.CursorLocation = adUseClient
    rs.Open Cmd, , adOpenUnspecified, adLockBatchOptimistic, adCmdUnspecified

    "SriSamp" <co.in> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Kenny Guest

  4. #4

    Default Re: Something rare!

    Nope. Just had a thought there, since we had a similar problem in the past
    because of the cursor type. Upon further investigation, if you add a SET
    NOCOUNT ON at the begining of the procedure (which is what Tibor suggested),
    everything works fine. Here is the same VB program that I tried for a
    similar procedure:
    =====
    Private Sub Form_Load()
    Dim oCommand As ADODB.Command
    Dim oRs As ADODB.Recordset

    Set oCommand = New ADODB.Command
    oCommand.CommandType = adCmdText
    oCommand.CommandText = "EXEC test"
    oCommand.ActiveConnection = "Provider=sqloledb;Data
    Source=localhost;Initial Catalog=pubs;User Id=sa;Password=password;"

    Set oRs = New ADODB.Recordset
    oRs.CursorLocation = adUseClient
    Call oRs.Open(oCommand)

    End Sub
    =====
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Kenny" <com> wrote in message
    news:upBd$phx.gbl... 
    > >
    > >[/ref]
    >
    >[/ref]


    SriSamp Guest

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