我将尝试从Access 2013 VBA执行SQL Server 2008存储过程,并获取输出变量(新的身份列)。但是,我并不完全了解所有adodb内容,并且我已经尝试了许多不同文章中的方法,但都没有成功。以下是目前的情况...
存储过程:
PROCEDURE [proc_NewClient]
@Type INT,
@PhoneIntakeID INT,
@ClientID INT = NULL,
@NewPSID INT = null OUTPUT (2 possible ways to call the proc, one does not produce an output variable)
INSERT INTO tblClientDetails (ClientID, PhoneIntakeID, Client_Status)
VALUES (@ClientID, @PhoneIntakeID, 'Applicant')
DECLARE @NewClientDetailID int
SELECT @NewClientDetailID = SCOPE_IDENTITY()
INSERT INTO tblPS_Psychosocial (ClientDetailID, Client)
VALUES (@NewClientDetailID, @ClientID)
SELECT @NewPSID = SCOPE_IDENTITY()
INSERT INTO tblPS_AbuseHistory (PsychosocialID)
VALUES (@NewPSID)
INSERT INTO tblPS_FamilyHistory(PsychosocialID) VALUES (@NewPSID)
INSERT INTO tblPS_FinancialHistory (PsychosocialID) VALUES (@NewPSID)
INSERT INTO tblPS_LegalHistory (PsychosocialID) VALUES (@NewPSID)
INSERT INTO tblPS_MedicalHistory (PsychosocialID) VALUES (@NewPSID)
INSERT INTO tblPS_PsychiatricHistory (PsychosocialID) VALUES (@NewPSID)
INSERT INTO tblPS_SocialHistory (PsychosocialID) VALUES (@NewPSID)
INSERT INTO tblPS_SpiritualHistory (PsychosocialID) VALUES (@NewPSID)
INSERT INTO tblPS_SubstanceHistory (PsychosocialID) VALUES (@NewPSID)
INSERT INTO tblVocAssessment(ClientDetailID) VALUES (@NewClientDetailID)
我从表单中得到的信息:
Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command, rs As New ADODB.Recordset, param1 As New ADODB.Parameter, param2 As New ADODB.Parameter, param3 As New ADODB.Parameter, param4 As New ADODB.Parameter
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DRIVER=SQL Server;SERVER=SRV-DB01;DATABASE=TWHClientMgmt;Trusted_Connection=Yes"
cnn.Open cnn.ConnectionString
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "[THEWOMENSHOME\jboyd].proc_NewClient"
Set param1 = cmd.CreateParameter("@Type", adinteger, adparamInput, , 2)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("@PhoneIntakeID", adinteger, adparamInput, , Me.PhoneIntakeID)
cmd.Parameters.Append param2
Set param3 = cmd.CreateParameter("@ClientID", adinteger, adparamInput, , intNewID)
cmd.Parameters.Append param3
Set param4 = cmd.CreateParameter("@NewPSID", adinteger, adParamOutput)
cmd.Parameters.Append param4
rs.open cmd
到这里为止,代码可以正常工作,新的记录被生成等等。当我从SQL Server运行存储过程时,它返回了正确的自增列号,但我已经尝试多种方式在VBA中引用输出,但最终结果总是空值。如何正确地在这里引用它?
cmd.Execute
代替rs.Open
,然后使用cmd.Parameters("@NewPSID").Value
来检索输出参数的值。注意不要改变原意,让内容更易懂。 - Gord Thompson''
或N''
,取决于字符串的类型),等等。 - Gord Thompsonselect <stored_procedure(parameters) as ret>
或先传递查询名称。是的,您无法对此类查询进行参数化处理。 - Sergey S.