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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com