updating sql table and returning value to .adp form

[LTusing]

I have created a sql sp with a cmd prompt to run sp with parameters to update form and receive error "parameter is improperly defined. Inconsistent or incomplete information was provided." I have posted code below and would appreciate input. Thank you!

Private Sub Update_Record_Click()
'Declare new connection and recordset
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim icd_id as Parameter
Dim pt_id as Parameter
Dim vdt_con as Parameter
Dim cons_dt as Parameter
Dim vdt_hip1 as Parameter
Dim hipa_dt as Parameter

Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "procConsentUpdate"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter( _
"RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter( _
"icd_id", adInteger, adParamnput, , Me.icd_id)
.Parameters.Append .CreateParameter( _
"pt_id", adInteger, adParamInput, , Me.pt_id)
.Parameters.Append .CreateParameter( _
"vdt_con", adsmalldatetime, adParamInput, , Me.vdt_con)
.Parameters.Append .CreateParameter( _
"cons_dt", adsmalldatetime, adParamInput, , Me.cons_dt)
.Parameters.Append .CreateParameter( _
"vdt_hip1", adsmalldatetime, adParamInput, , Me.vdt_hip1)
.Parameters.Append .CreateParameter( _
"hipa_dt", adsmalldatetime, adParamInput, , Me.hipa_dt)

.Execute

End With

End Sub

 

[PWise]

what is the code of the SP

 

[LTusing]

Thank you, the code is:

CREATE PROCEDURE procConsentUpdate(
@icd_id int = NULL,
@pt_id int = NULL,
@RetCode int = NULL OUTPUT,
@RetMsg varchar(150) = NULL OUTPUT)
AS
DECLARE @vdt_con smalldatetime
DECLARE @cons_dt smalldatetime
DECLARE @vdt_hip1 smalldatetime
DECLARE @hipa_dt smalldatetime
DECLARE @Err int
DECLARE @Exists bit
DECLARE @strSQL nvarchar(200)
DECLARE @ConsentCount int
SET NOCOUNT ON

/* Check if icd exists. */

IF @icd_id IS NULL
BEGIN
SELECT @RetCode = 0,
@RetMsg = 'icd_id required.'
RETURN
END

--Bail if record does not exist
IF
(SELECT COUNT(*) FROM Consent
WHERE icd_id = @icd_id) = 0
BEGIN
SELECT @RetCode = 0,
@RetMsg = 'icd_id does not exist.'
RETURN
END

SELECT @strSQL = N'UPDATE CONSENTSET '

--Bail if nothing to update
IF @Exists = 0
BEGIN
SELECT @RetCode = 0,
@RetMsg = 'Nothing to do.'
RETURN
END

--Strip the trailing comma
SELECT @strSQL=LEFT ( @strSQL, LEN (@strSQL) -1)

--Add the WHERE clause
SELECT @strSQL = @strSQL +
' WHERE icd_id = ' + CAST (@icd_id AS varchar(7))

--Generate a plan
EXECUTE sp_executesql @strSQL

--If error, branch to error handler
SELECT @Err = @@ERROR
IF (@Err <> 0) GOTO HandleErr

--If we got this far, then no error
SELECT @RetCode = 1,
@RetMsg = 'Record Edited'
RETURN

HandleErr:
SELECT @icd_id = 0,
@RetCode = 0,
@RetMsg = 'Runtime Error: ' + CAST(@Err AS varchar(10))
RETURN
GO

 

[danvlas]

Well, for starters, the names of the parameters start with "@", which is missing in your VB code...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[LTusing]

Thank you, HTH. However, it is still not working. I think it has something to do with my procedure, although the sp passed the syntax check via Enterprise Manager.

 

[danvlas]

It's not the stored proc. It's the VB.
Try adDate instead of adsmalldatetime (I don't think ADO knows all flavours of SQL data types)

HTH actually means 'hope that helps'. My name is Dan -


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[cmmrfrds]

Here is a link to data type mapping for ADO.

http://www.carlprothman.net/Default.aspx?tabid=97

 

[cmmrfrds]

Is this a typo? adParamInput?

.Parameters.Append .CreateParameter( _
"icd_id", adInteger, adParamnput, , Me.icd_id)

I usually set the name/value outside of the create parameter, like so.

Set parm1 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append parm1
parm1.Value = Me.icd_id

 

[LTusing]

Thank you, very much.

I have made all revisions and continue to be at a dead-lock. I am currently receiving message "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another". I have revised information from the ADO mapping site, as recommended. I appreciate any other thoughts. Thank you!

 

[danvlas]

After a closer look, it seems that you have 4 parameters defined in the stored proc and 6 parameters defined in VB.

For instance, vdt_con is NOT a parameter, but a variable declared inside the stored proc.

You can't return SQL variable values to VB.

Another thing: you test (in the sp) the value for variable @Exists, but it is never instantiated.

Having so many inconsistencies, I believe you will have to reconsider everything and re-write both the stored proc and the VB code...


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[LTusing]

Thank you much! lt