Jim Lawrence (AccessD)
accessd at shaw.ca
Sat May 22 13:34:09 CDT 2004
Martin:
Insanity. If you can resist the temptation to push the computer through the
gyhp-rock you have had a successful day. ...but, being through similar
circumstance before, it is always amazing how fast you can re-build code
running on an adrenalin high.
My two cent worth
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Martin Reid
Sent: Saturday, May 22, 2004 11:14 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] ADO Parameters - Driving me nuts
Oh and dont do this one
Cut out all the parameters from the VBA module, close it and then save the
changes. Its a real kick in the %^$$% when you go back to change a parameter
and not only is it not there but the other 49 are gone as well.
Going for a smoke and cup of coffee and maybe kick the yard door a time or
two!!!!
MArtin
----- Original Message -----
From: "Martin Reid" <mwp.reid at qub.ac.uk>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Saturday, May 22, 2004 7:05 PM
Subject: Re: [AccessD] ADO Parameters - Driving me nuts
> Thanks Jim
>
> Wil try it out now.
>
>
> Martin
>
>
>
>
> ----- Original Message -----
> From: "Jim Lawrence (AccessD)" <accessd at shaw.ca>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Saturday, May 22, 2004 6:23 PM
> Subject: RE: [AccessD] ADO Parameters - Driving me nuts
>
>
> > Hi Martin:
> >
> > Pay particular attention to the receiving SP. The numeric types and
> > percision has to exactly match the receiving variables, names, fields,
> > strings the exact length or longer and the order of the recieiving
> variable
> > has to exactly match. Another problem, that I found and resolved, was to
> > change all dates to strings.
> >
> > Eaxample:
> > <FrontEnd>
> > ...
> > Set objCmd = New ADODB.Command
> > With objCmd
> > .ActiveConnection = globalConnectionString
> > .CommandText = "SQLSave03"
> > .CommandType = adCmdStoredProc
> > .Parameters.Append .CreateParameter("@chvLast", adVarChar,
adParamInput,
> > 40, strLast)
> > .Parameters.Append .CreateParameter("@chvFirst", adVarChar,
adParamInput,
> > 30, strFirst)
> > .Parameters.Append .CreateParameter("@chvMiddle", adVarChar,
> adParamInput,
> > 30, strMiddle)
> > .Parameters.Append .CreateParameter("@chvBirthDate", adVarChar,
> > adParamInput, 11, strBirthDate)
> > .Parameters.Append .CreateParameter("@chvRangeDate", adVarChar,
> > adParamInput, 11, strRangeDate)
> > .Parameters.Append .CreateParameter("@intRecordCount", adInteger,
> > adParamOutput)
> > ...
> > End With
> > ...
> >
> > ' Number of records changed: @intRecordCount
> > If objCmd(1) > 0 then
> > ...
> >
> > </FrontEnd>
> >
> > <BackEnd>
> > CREATE PROC dbo.SQLSave03
> > @chvLast varchar(40),
> > @chvFirst varchar(30),
> > @chvMiddle varchar(30),
> > @chvBirthDate varchar(11),
> > @chvRangeDate varchar(11),
> > @intRecordCount INT OUTPUT
> > AS
> > ...
> > </BackEnd>
> >
> >
> > You can always use an 'INSERT' sql string like:
> >
> > <FrontEnd>
> > ...
> > Dim strSQL As String
> > Dim objConn As ADODB.Connection
> >
> > Set objConn = New ADODB.Connection
> > objConn.Open globalConnectionString
> > objConn.BeginTrans
> >
> > 'Note:
> > '1. That MyRecord is just a 'type' structure that matches a specific
> record
> > from a specific form.
> > '2. Single quotes around all strings including dates.
> >
> > With MyRecord
> > strSQL = "INSERT " & _
> > "INTO AlsoKnowAs " & _
> > (Counter, EMPNUM, NAME, RECORD_DATE) " & _
> > "VALUES" & _
> > (" & lngCounter & ", " & glEmployeeNumber & ", '" & .AKANAME & _
> > "', '" & .AKARECORD_DATE & "')"
> > End With
> >
> > objConn.Execute strSQL
> > objConn.CommitTrans
> > ...
> > </FrontEnd>
> >
> > That is about all for now and I hope it helps.
> > Jim
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Martin Reid
> > Sent: Saturday, May 22, 2004 7:56 AM
> > To: Access Developers discussion and problem solving
> > Subject: [AccessD] ADO Parameters - Driving me nuts
> >
> >
> > ADO Parameters being passed to an Insert Stored Procedure
> >
> > Keeps falling over with a Precision not valid message
> >
> > I reworte putting in the Numberic Scale and the Precision values before
> > appending the param and it still fails.
> >
> > Any advice more than welcome.
> >
> > There are approx 40 parameters being passed. Mosst of the are Advarchar
> with
> > about 5 being Ints. This isidriving me nuts.
> >
> >
> > Martin
> >
> > --
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com