Click here to Skip to main content
15,921,113 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
VB
'The connection is established before this function is called
'Function to prepare the command object
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
SQL
CREATE PROCEDURE [Db_Exec].[InsertState_Form] 
	-- Add the parameters for the stored procedure here
	@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 added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Check null values
	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;
	
	-- Check Date String
	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 statements for procedure here
	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.
VB
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

'Initializations
addNewForm_FromStateList_ByStateForm_ADO = False

'Check for an empty state list (zero length array)
If isLongArrayNull_rSz(stateList, sz) = True Then Exit Function

'Verify the state form for required fields
If Not checkStateForm(sForm) Then Exit Function

'Establish and Verify the connection
Set cn = dbConnect
If connOpen(cn) = False Then Exit Function

'Prepare the command object
'If prepCmdObject_InsertStateForm(cn, cmd) = False Then Exit Function
If prepCmdObject_InsertStateForm2(cn, cmd) = False Then Exit Function

'Set parameter data
'If setCmdObjectParams_InsertStateForm(cmd, sForm) = False Then Exit Function
If setCmdObjectValues_FromStateFromType(cmd, sForm) = False Then Exit Function

'Got this far, time to push to server
For i = 0 To sz
    cmd.Parameters("@State_ID").Value = stateList(i)
	Set r = cmd.Execute
Next

'Cleanup and exit
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
Posted
Updated 15-Mar-13 10:54am
v2

For the first look, you didn't add parameters:
@Form_ID int,<br />
	@State_ID int,<br />
	@Form_Date nvarchar(50),<br />
	@Filing_Type_ID int,<br />
	@Revision tinyint,<br />
	@State_Specific tinyint,<br />
	@Filing_Date nvarchar(50) = null,<br />
	@Filing_Status_ID int = null,<br />
	@Approval_Date nvarchar(50) = null


See this: http://msdn.microsoft.com/en-us/library/windows/desktop/ms675869%28v=vs.85%29.aspx[^]
 
Share this answer
 
Comments
Foothill 15-Mar-13 16:36pm    
Since I used cmd.parameters.refresh, ADO grabs the parameters from the proceedure itself. I then populate those parameters in another function.
Maciej Los 15-Mar-13 16:39pm    
Debug code. Show us a line with error.
Foothill 15-Mar-13 16:44pm    
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

'Initializations
addNewForm_FromStateList_ByStateForm_ADO = False

'Check for an empty state list (zero length array)
If isLongArrayNull_rSz(stateList, sz) = True Then Exit Function

'Verify the state form for required fields
If Not checkStateForm(sForm) Then Exit Function

'Establish and Verify the connection
Set cn = dbConnect
If connOpen(cn) = False Then Exit Function

'Prepare the command object
'If prepCmdObject_InsertStateForm(cn, cmd) = False Then Exit Function
If prepCmdObject_InsertStateForm2(cn, cmd) = False Then Exit Function

'Set parameter data
'If setCmdObjectParams_InsertStateForm(cmd, sForm) = False Then Exit Function
If setCmdObjectValues_FromStateFromType(cmd, sForm) = False Then Exit Function

'Got this far, time to push to server
For i = 0 To sz
cmd.Parameters("@State_ID").Value = stateList(i)
Set r = cmd.Execute
Next

'Cleanup and exit
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
Foothill 15-Mar-13 16:46pm    
Sorry, a couple of commented out function calls made it in there.
Foothill 15-Mar-13 16:50pm    
Wait, I found it. I left in the "set r = cmd.execute" when the command is returning an integer. Dumb. I've trying to figure this out for an hour. I guess that putting the code in another form helped me spot it.
I found the problem. When I was executing the command, I had left in a bit of code that was trying to assign the stored proc's return (integer) to a recordset. That is why the procedure executed correctly and Access was still throwing an error.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900