如何使用asp经典的参数化sql查询和vb脚本一次在两个表中插入记录？

Question

我正在使用ASP经典和VBScript。我有两张桌子。第一个表包含像用户名和密码这样的列，第二个表包含像name和email这样的列。这两个表都是相关的(table1.id = table2.Id)。

如何使用参数化SQL查询一次在两个表中插入记录。我的密码在下面。

<%
'for table 1  
Dim objRS, objCmd, str

Set objCmd  = Server.CreateObject("ADODB.Command")
Set Objrs   = Server.CreateObject("ADODB.Recordset")

str = "insert into admin (Ausr, Apwd)values(?,?)"

With objCmd
    .ActiveConnection = MM_connDUdirectory_STRING
    .CommandText = str
    .CommandType = adCmdText

    .Parameters.Append(.CreateParameter("@sa1", adVarChar, adParamInput, len(username)))
    .Parameters.Append(.CreateParameter("@sa2", adVarChar, adParamInput, len(password)))

    .Parameters("@sa1").Value = username
    .Parameters("@sa2").Value = password
end with 
Set objRS = objCmd.Execute()

%>

对于table2，我在从上面跟踪插入的记录的ID时遇到了问题。在下面的代码中，我无法检索ID：

<%
'for table 2
Dim objRS1, objCmd1, str1

Set objCmd1  = Server.CreateObject("ADODB.Command")
Set Objrs1   = Server.CreateObject("ADODB.Recordset")

str1 = "insert into detail (ID, Aname, Aemail)values(?,?,?)"

With objCmd1
    .ActiveConnection = MM_connDUdirectory_STRING
    .CommandText = str1
    .CommandType = adCmdText
    .Parameters.Append(.CreateParameter("@sa3", adInteger, adParamInput, ,ID)) 
    .Parameters.Append(.CreateParameter("@sa4", adVarChar, adParamInput, len(vname)))
    .Parameters.Append(.CreateParameter("@sa5", adVarChar, adParamInput, len(email)))

    .Parameters("@sa3").Value = ID
    .Parameters("@sa4").Value = vname
    .Parameters("@sa5").Value = email
end with 
Set objRS1 = objCmd1.Execute()

%>

请帮助我解决上述问题，或如果有任何其他方式插入记录在2个表中使用参数化查询，请告诉我。

@Tomalak，按照您的代码，我尝试使用下面的表来处理它

<%
Function InsertAdmin(Ausr, Apwd, Acdate, ATid, Astate, Acity, Acenterid, Aname, gender, Acontact, Aemail, ACreatedBy, suspend, allow, approve, Staff_Aid, Staff_EId, Staff_Fname, Staff_Add, Staff_Padd, Staff_Edu, Staff_doc) 

Dim objRS, objCmd, str

Set objCmd  = Server.CreateObject("ADODB.Command")
Set Objrs   = Server.CreateObject("ADODB.Recordset")

With objCmd
    .ActiveConnection = MM_connDUdirectory_STRING
    .CommandType = adCmdText
    .CommandText = Join(Array( _
        "DECLARE @id INT", _
        "insert into admin (Ausr, Apwd, Acdate, ATid, Astate, Acity, Acenterid, Aname, gender, Acontact, Aemail, ACreatedBy, suspend, allow, approve)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", _
        "SELECT @id = SCOPE_IDENTITY()", _
        "insert into Staff_detail (Staff_Aid, Staff_EId, Staff_Fname, Staff_Add, Staff_Padd, Staff_Edu, Staff_doc)values(@id,?,?,?,?,?,?)"), vbNewLine)


.Parameters.Append(.CreateParameter("@sa1", adVarChar, adParamInput, len(StrUsr)))
.Parameters.Append(.CreateParameter("@sa2", adVarChar, adParamInput, len(md5(StrPwd))))
.Parameters.Append(.CreateParameter("@sa3", adVarChar, adParamInput, len(dt)))
.Parameters.Append(.CreateParameter("@sa4", adInteger, adParamInput, ,atypeid))
.Parameters.Append(.CreateParameter("@sa5", adInteger, adParamInput, ,StrState))
.Parameters.Append(.CreateParameter("@sa10", adInteger, adParamInput, ,StrCity))
.Parameters.Append(.CreateParameter("@sa11", adInteger, adParamInput, ,StrCenter))
.Parameters.Append(.CreateParameter("@sa118", adVarChar, adParamInput, len(stname)))
.Parameters.Append(.CreateParameter("@sa119", adVarChar, adParamInput, len(stgender)))
.Parameters.Append(.CreateParameter("@sa110", adVarChar, adParamInput, len(stcontact)))
.Parameters.Append(.CreateParameter("@sa111", adVarChar, adParamInput, len(stemail)))
.Parameters.Append(.CreateParameter("@sa6", adVarChar, adParamInput, len(Request.Cookies("Vape")("User"))))
.Parameters.Append(.CreateParameter("@sa7", adInteger, adParamInput, ,susp))
.Parameters.Append(.CreateParameter("@sa8", adInteger, adParamInput, ,allowd)) 
.Parameters.Append(.CreateParameter("@sa9", adInteger, adParamInput, ,approved)) 
'.Parameters.Append(.CreateParameter("@sa101", adInteger, adParamInput, ,@ID))
.Parameters.Append(.CreateParameter("@sa102", adVarChar, adParamInput, len(stcode)))
.Parameters.Append(.CreateParameter("@sa103", adVarChar, adParamInput, len(stfname)))
.Parameters.Append(.CreateParameter("@sa104", adVarChar, adParamInput, len(stadd)))
.Parameters.Append(.CreateParameter("@sa105", adVarChar, adParamInput, len(stpadd)))
.Parameters.Append(.CreateParameter("@sa106", adVarChar, adParamInput, len(stedu)))
.Parameters.Append(.CreateParameter("@sa107", adVarChar, adParamInput, len(stdoc)))

End With
Set objRS = objCmd.Execute() 
End Function
call InsertAdmin(StrUsr, md5(StrPwd), dt, 5, StrState, StrCity, StrCenter, stname, stgender, stcontact, stemail, a_name, 0, 1, 0, stcode, stfname, stadd, stpadd, stedu, stdoc) 
%>

但是它显示出一个错误，它说：

Microsoft VBScript运行时(0x800A01C2)参数数目错误或属性分配无效：“InsertAdmin”

Answer 1

我建议您使用一个函数，在一个步骤中执行插入操作。这个返回新插入的行的ID：

Function InsertAdmin(username, password, vname, email) 
    With Server.CreateObject("ADODB.Command")
        .ActiveConnection = MM_connDUdirectory_STRING
        .CommandType = adCmdText
        .CommandText = Join(Array( _
            "DECLARE @id INT", _
            "INSERT INTO admin (Ausr, Apwd) VALUES (?, ?)", _
            "SELECT @id = SCOPE_IDENTITY()", _
            "INSERT INTO detail (ID, Aname, Aemail) VALUES (@id, ?, ?)", _
            "SELECT @id NewId" _
        ), vbNewLine)

       .Parameters.Append .CreateParameter(, adVarChar, , Len(username), username)
       .Parameters.Append .CreateParameter(, adVarChar, , Len(password), password)
       .Parameters.Append .CreateParameter(, adVarChar, , Len(vname), vname)
       .Parameters.Append .CreateParameter(, adVarChar, , Len(email), email)

       With .Execute
           InsertAdmin = .Fields("NewId").Value
       End With
   End With 
End Function 

使用BEGIN TRANSACTION和COMMIT TRANSACTION来额外地确保插入是否成功。

或者，您可以简单地将两个表合并为一个表。我不认为为这个用例保留两个有1:1关系的独立表有什么好的理由。