[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
>