Re: Can you see the problem?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

jamesfreddyc wrote:
VB6.0 / ADO recordsets / SQLServer 2005

Well, I am attempting to execute a StoredProcedure that is an UPDATE
on one single table in the db. There are no errors thrown and
previous testing on the SProc itself indicates that it works just
fine. But something is not right --- the UPDATE doesn't happen.

Your first step in such a situation should be to fire up SQL Profiler
and run a trace to make sure VB is doing what you expect it to be doing.
However, read on.
<snip>
Public Function rs_UPDATE_Storms(OID As Long, CSCHarge As Double,
UOWCharge As Double, MSCharge As Double) As ADODB.Recordset

On Error GoTo Proc_Err

Set rs_UPDATE_Storms = New ADODB.Recordset

Huh? Why are you using a recordset?
<snip>

ParamCSCHARGE.Type = adDouble
adDouble is not the correct datatype for numeric(38,8) parameters. You
need to use adNumeric, and then set the parameter object's Precision and
NumericScale properties to 38 and 8 before assigning its value.
With ParamCSCHARGE
.Type = adNumeric
.Precision=38
.NumericScale=8
End With
<snip>
pCommand.Parameters.Append ParamOID
The first parameter you should be appending is the Return parameter,
even if you are not using a return value:

With pCommand
..Parameters.Append .CreateParameter("Return", _
adInteger, adParamReturnValue)
end with

Then append the rest of the parameter objects ... IN THE CORRECT ORDER.
They need to be appended in the same order in which they are declared in
the stored procedure!
cscharge, followed by uowcharge, followed by mscharge, followed by
stormsoid
Named-parameters does not work in VB.

<snip>
Set rs_UPDATE_Storms.ActiveConnection = pConn
No no no - you should be setting the Command object's active connection:
Set pCommand.ActiveConnection = pConn
You should be doing this even if your procedure returns a resultset and
you are planning to open a recordset on that returned resultset.
Oh wait, you already did that before starting the parameter creation
steps up in the part I snipped. The only Activeconnection you need to
set is the Command's.

<snip>
ALTER PROCEDURE [mmsadmin].[Storms_Property_Q2]
@CSCHARGE numeric(38,8),
@UOWCHARGE numeric(38,8),
@MSCHARGE numeric(38,8),
@STORMSOID int

<snip>
This procedure does not return a resultset. You should not use a
recordset object to execute it.
This procedure could easily be executed by this code:

pConn.Storms_Property_Q2 CSCHarge, UOWCharge, _
MSCharge, OID

However, most VB programmers prefer to be more explicit, so go ahead and
create the Command object - just use its Execute method without setting
a recordset object to its return value:
pCommand.Execute





--
HTH,
Bob Barrows


.

Relevant Pages

  • Re: Can you see the problem?
    ... I am attempting to execute a StoredProcedure that is an UPDATE ... recordset object to execute it. ...
    (microsoft.public.data.ado)
  • Re: executable file compiled in VFAT filesystem cant be executed
    ... > Move the files off of the VFAT filesystem, and onto one of the Unix ... which you could execute your program (because it is a Unixish fs, ... Lew Pitcher, IT Specialist, Enterprise Data Systems ...
    (alt.os.linux.suse)
  • Re: [Full-Disclosure] Removing ShKit Root Kit
    ... > isn't a cleverly concealed backdoor? ... Well, if the execute bit isn't set, then I'd assume it can be ... Brian Eckman ...
    (Full-Disclosure)
  • Re: Execute method vs Recordset object
    ... You are ALWAYS using a recordset object, even if you don't create one ... When Execute is used, a recordset object with the default ... property settings is created implicitly. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • Re: posix threads
    ... > it produces no errors or warnings. ... but if i execute the binary, ... using pthread_joinon each of the threads after you spawn the last one ...
    (comp.unix.programmer)