Re: SP to ADO interaction

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 13:49:06 -0500

smk23 wrote:
> 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

You should also modify the command execute to add the following:
Call Command.Execute(, , adCmdStoredProc Or adExecuteNoRecords)

With the adExecuteNoResults there should be no reason to loop through
any result sets. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • RE: SP to ADO interaction
    ... > I am a newbie to ADO and mystified by this: ... > that updates 4 different tables sequentially. ... > Dim oCmd As ADODB.Command ... > Set cnn = New ADODB.Connection ...
    (microsoft.public.sqlserver.programming)
  • 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)
  • RE: updateable snapshot recordset in sub form.
    ... I forgot to mention that in my experiment without VBA I did use a stored ... as my recordsource and it is updatebable. ... > automatically updates my remote SQL database when ever I change values in the ... > Set cnn = New ADODB.Connection ...
    (microsoft.public.access.formscoding)
  • SP to ADO 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.sqlserver.programming)