Re: ado to stored procedure interaction
From: Val Mazur (group51a_at_hotmail.com)
Date: 11/24/04
- Next message: Bob Butler: "Re: Creating a Login Form in VB6"
- Previous message: DavidM: "Creating a Login Form in VB6"
- In reply to: smk23: "ado to stored procedure interaction"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 24 Nov 2004 18:44:00 -0500
Hi,
Like for me code looks fine and I do not see anything wrong. For the purpose
of testing first check if it fails on any table or just on specific one.
Then try to run SQL Profiler to see what is going on between the client and
SQL Server. Also check if you have proper indexes on a fields involved into
WHERE conditions and JOINs (if you have one). If it is a transaction, then
check if transaction is not opened after timeout occurs. If it is, then
these tables will be locked for other users.
--
Val Mazur
Microsoft MVP
"smk23" <smk23@discussions.microsoft.com> wrote in message
news:2E4B5F00-995F-4E29-BA02-ECB9A01B6640@microsoft.com...
> 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()
> 'On Error GoTo Error_Handler
>
> 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
- Next message: Bob Butler: "Re: Creating a Login Form in VB6"
- Previous message: DavidM: "Creating a Login Form in VB6"
- In reply to: smk23: "ado to stored procedure interaction"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
