SP to ADO interaction

From: smk23 (smk23_at_discussions.microsoft.com)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 09:29:02 -0800

Greetings and thanks for the help!
I am a newbie to ADO and mystified by this:
In MS Access 2003, I am updating some SQL 2k tables via a stored procedure
that updates 4 different tables sequentially.

When I test the SP with Query Analyzer, it works fine and updates all four
tables. When I try to update from the Access form, it stalls and doesn't
update. I don't get an error message, just a timeout and "EndNow" selection.
I've checked to see that the input parameters match the input variables in
the SP and they do. Once the SP has failed after the attempt from Access, the
SP also fails in query analyzer. I'm wondering if there is some index
somewhere that needs reset... In any case, I'd be grateful if you could
suggest how to troubleshoot this. I don't know what else to try.
Let me know if the SP text would help.

Here is my ADO code:
Private Sub cmdUpdate_Click()

    Dim cnn As ADODB.Connection
    Dim oCmd As ADODB.Command
    
    'open a connection
    Set cnn = New ADODB.Connection
    cnn.CursorLocation = adUseClient
' cnn.Open fstrCnn()
    cnn.Open "Provider=sqloledb;Data Source=MyServer;" & _
            "Initial catalog=MyData;User Id=blank;Password=blank;"
    Set oCmd = New ADODB.Command
    
    With oCmd
        
        Set .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "gp_vwSMKSentinelNode_Update"
        
        .parameters.Append .CreateParameter("@AilmentID", adInteger,
adParamInput)
        .parameters("@AilmentID").Value = Me.AilmentID
        
        .parameters.Append .CreateParameter("@RLB", adSmallInt, adParamInput)
        .parameters("@RLB").Value = Me.txtRLB
... a bunch of parameters

        .Execute
    
    End With
    
    cnn.Close
    Set cnn = Nothing 

-- 
sam

Relevant Pages

  • Re: ado to stored procedure interaction
    ... of testing first check if it fails on any table or just on specific one. ... > that updates 4 different tables sequentially. ... > Dim oCmd As ADODB.Command ... > Set cnn = New ADODB.Connection ...
    (microsoft.public.vb.database.ado)
  • Re: SP to ADO interaction
    ... > I am a newbie to ADO and mystified by this: ... > procedure that updates 4 different tables sequentially. ... > Dim oCmd As ADODB.Command ... > Set cnn = New ADODB.Connection ...
    (microsoft.public.sqlserver.programming)
  • ado to stored procedure interaction
    ... I am a newbie to ADO and mystified by this: ... When I test the SP with Query Analyzer, it works fine and updates all four ... Dim oCmd As ADODB.Command ... Set cnn = New ADODB.Connection ...
    (microsoft.public.vb.database.ado)
  • Re: Am I wrong
    ... > I have used ADO for years, ... closing connections. ... (Connection, Commands and DataReaders) ... Updates until ADO.NET v2.0). ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... via an ADO recordset, then I'd strongly suggest you redesign your solution. ... > to do with the updates being in a transaction but rather on whether you specify ... > spill over and the memory starts paging out to disk that concerns me). ...
    (microsoft.public.data.ado)