[AccessD] Re: [dba-SQLServer]Copying a record VB6 & SQL Server 7.0

Francisco H Tapia my.lists at verizon.net
Mon Jul 21 10:21:27 CDT 2003


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
:
: __________________________________________________________________________
: 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


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the dba-SQLServer mailing list