[dba-SQLServer]Number of rows affected by update, insert etc

Paul I'm reposting my previous message, if you check out the variable
lngAffected it spits out the number of records affected just use that or
pass it to what ever msgbox/prompt or screens you need.
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
Francisco H Tapia
Sent: Monday, July 21, 2003 8:21 AM
To: dba-sqlserver at databaseadvisors.com; AccessD
Subject: Re: [dba-SQLServer]Copying a record VB6 & SQL Server 7.0

Paul,
it has been my experiance that when you have a long series of parameters,
it is often best to pass them back using a command object. something along
the line the following
Dim Cmd As adodb.Command, lngAffected As Long
Set Cmd = New adodb.Command
With Cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "stp_004C_Insert"
.Parameters.Append .CreateParameter("@CaseNumberID", adInteger,
adParamInput, , Nz(Me.txtCaseNumberID, 0))
.Parameters.Append .CreateParameter("@MachineID", adGUID,
adParamInput, , Me.txtMachineID)
.Parameters.Append .CreateParameter("@LoginID", adVarChar,
adParamInput, 50, fOSUserName())
.Parameters.Append .CreateParameter("@ContactAddrID", adGUID,
adParamInput, , Me.txtContactAddrID)
.Parameters.Append .CreateParameter("@ReasonID", adInteger,
adParamInput, , Me.cboReason)
.Parameters.Append .CreateParameter("@Type", adInteger,
adParamInput, , Me.cboType)
.Parameters.Append .CreateParameter("@Unneccessary", adInteger,
adParamInput, , Me.chkUnnecessary)
.Parameters.Append .CreateParameter("@FailCodeID", adInteger,
adParamInput, , Me.cboFailCode)
.Parameters.Append .CreateParameter("@Status", adInteger,
adParamInput, , Me.chkCloseCall)
.Parameters.Append .CreateParameter("@CallNotes", adVarChar,
adParamInput, 1000, Me.txtCallNotes)
.Execute , lngAffected
.ActiveConnection = Nothing
End With
More helpful tips on using ADO w/ Sql can be found here..
http://www.able-consulting.com/ADO_Faq.htm
-Francisco
http://rcm.netfirms.com
On Monday, July 21, 2003 7:59 AM [GMT-8],
paul.hartland at fsmail.net <paul.hartland at fsmail.net> wrote:
: To all,
:
: we have a high turnover of staff, mainly because most are casual
: workers and don't want full-time employment. When a member of staff
: leaves we issue them with a finish date. However if they decide they
: want to come back we need to re-employ them with a new payroll
: number, I have a re-employ button that asks the user for the new
: payroll number...but here is where I need help : I then need to
: create an identical personnel record (66 fields to the one shown on
: screen) but with the new payroll number.
:
: My front-end is Visual Basic 6 and my back-end is SQL Server 7.0. I
: thought the easiest way would be to create an insert stored procedure
: with parameters and then execute it from VB, so I created the SP and
: ran it using Query Analyser (Worked Fine), I also added it to my
: dataenvironment as a command. However when I type
: DEGenSQL.sp_Personnel_ReEmploy I get no option to enter any of the
: parameters and if I try RUN/Start With Full Compile I get the
: following error message :
:
: Compile Error : Subscript Out Of Range
:
: This makes me think there are too many parameters required.
:
: Has anyone else come across this, or does anyone know of a better way
: to go about this.
:
: Paul Hartland


-Francisco
http://rcm.netfirms.com

On Wednesday, July 23, 2003 8:57 AM [GMT-8],
paul.hartland at fsmail.net <paul.hartland at fsmail.net> wrote:

: To all,
:
: I have a VB6 application which uses the dataenvironment to link to a
: Access2002 database, and I use command objects connnected to the
: dataenvironment which update, delete & insert records.....
:
: Is there anyway I can get the count of how many rows have been
: affected
:
: Thanks in advance for you help
:
: Paul Hartland
:
: __________________________________________________________________________
: Join Freeserve http://www.freeserve.com/time/
:
: Winner of the 2003 Internet Service Providers' Association awards for
: Best Unmetered ISP and Best Consumer Application.
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com