ADO，SQL存储过程和错误＃13（类型不匹配） [英] ADO, SQL Stored Proc, and Error #13(type mismatch)

问题描述

我遇到了一个错误，我无法弄清楚为什么会生成它。

我在MS Access数据库中使用ADO将记录推送到使用存储的SQL服务器继续。

令我困惑的是，我使用cmd.parameters.refresh来填充ADO命令对象，因此，在我看来，不应该有任何方式导致类型不匹配错误在存储过程成功执行后裁剪。



所以我正在寻找的答案是如何成功执行程序，准确存储数据的方式从一个ADO命令对象填充来自performure的参数，仍然得到一个类型不匹配错误。



我仍​​然是一个相当新的SQL编程&使用ADO，如果我遗漏了一些简单的东西，请随意指出。



ADO程序

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

'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存储过程

SQL Stored Proc

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

这是父函数。正如我在原始问题中所说的那样，数据存储就像它应该存储的那样，并且存储的proc返回0.但是，实际上，Access仍然会抛出错误，我看不出它的原因。

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

推荐答案

初看，你没有添加参数：

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

请参阅： http://msdn.microsoft.com/en-us/library/windows/desktop/ms675869%28v=vs.85%29.aspx [ ^ ]

我发现了问题。当我执行命令时，我留下了一些代码，试图将存储过程的返回（整数）分配给记录集。这就是程序正确执行而Access仍然抛出错误的原因。

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.

这篇关于ADO，SQL存储过程和错误＃13（类型不匹配）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！