I've come across an error that I cannot figure out why it is being generated.
I am using ADO in an MS Access database to push a record to a SQL server using a stored proceedure.
What is confusing me is that I am using cmd.parameters.refresh to populate the ADO command object so therefore, in my eyes, there shouldn't be any way for a type mismatch error to crop up after the stored procedure is executed successfully.
So the answer I am looking for is how can the procedure execute successfully, store the data exactly how it is supposed to from an ADO command object populated with parameters from the proceedure and still get a type mismatch error.
I am still fairly new to SQL programming & using ADO so if I am missing something simple, feel free to point it out.
ADO Procedures
Public Function prepCmdObject_InsertStateForm(ByRef cn As ADODB.Connection, ByRef cmd As ADODB.Command) As Boolean
On Error Resume Next
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Db_Exec.InsertState_Form"
cmd.Parameters.Refresh
If Err.Number = 0 Then prepCmdObject_InsertStateForm = True
End Function
SQL Stored Proc
CREATE PROCEDURE [Db_Exec].[InsertState_Form]
@Form_ID int,
@State_ID int,
@Form_Date nvarchar(50),
@Filing_Type_ID int,
@Revision tinyint,
@State_Specific tinyint,
@Filing_Date nvarchar(50) = null,
@Filing_Status_ID int = null,
@Approval_Date nvarchar(50) = null
AS
BEGIN
SET NOCOUNT ON;
IF (@Form_ID = null OR
@State_ID = null OR
@Form_Date = null OR
@Filing_Type_ID = null OR
@Revision = null OR
@State_Specific = null)
RETURN -1;
IF (ISDATE(@Form_Date) <> 1 OR
((ISDATE(@Filing_Date) <> 1) and (@Filing_Date IS NOT NULL)) OR
((ISDATE(@Approval_Date) <> 1) and (@Approval_Date is not null)))
RETURN -2;
INSERT destTable
VALUES
(
@Form_ID,
@State_ID,
cast(@Form_Date as DATE),
@Filing_Type_ID,
@Revision,
@State_Specific,
cast(@Filing_Date AS DATE) ,
@Filing_Status_ID,
cast(@Approval_Date as DATE)
);
SELECT @@ERROR
END
This is the parent function. As I stated in the original question, the data is stored just as it should and the stored proc returns 0. However, Access is still throwing a error when in reality, I cannot see a reason for it to.
Public Function addNewForm_FromStateList_ByStateForm_ADO(stateList() As Long, sForm As stateForm) As Boolean
On Error Resume Next
Dim r As Recordset
Dim cn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim i As Integer, sz As Long
addNewForm_FromStateList_ByStateForm_ADO = False
If isLongArrayNull_rSz(stateList, sz) = True Then Exit Function
If Not checkStateForm(sForm) Then Exit Function
Set cn = dbConnect
If connOpen(cn) = False Then Exit Function
If prepCmdObject_InsertStateForm2(cn, cmd) = False Then Exit Function
If setCmdObjectValues_FromStateFromType(cmd, sForm) = False Then Exit Function
For i = 0 To sz
cmd.Parameters("@State_ID").Value = stateList(i)
Set r = cmd.Execute
Next
If Err.Number = 0 Then
addNewForm_FromStateList_ByStateForm_ADO = True
Else
Debug.Print cmd.Parameters("@RETURN_VALUE").Value
End If
Set cmd = Nothing
Call CloseConn(cn)
End Function