[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