向数据表用newid方式插入GUID问题

Question

我在用VB6.0向sqlserver数据库Pub_Exam的Pub_Exam表写入数据，其中一字段Exam_GUID不能为空，且是自动生成的GUID（类似16进制的代码，中间有分隔，无大括号），我在使用newid()的方式写入数据，但是使用数据库连接的.execute不能使用（Selectsqlbd2.Execute这一段），编译时提示必选参数，麻烦帮看看。
以下是vb6.0代码：

Private Function Selectsqlbd2(SQL As String) As ADODB.Recordset
   Dim ConnStr As String
    Set rrsbd2 = New ADODB.Recordset
    Set connbd2 = New ADODB.Connection
    On Error GoTo MyErr:
ConnStr = "Provider=SQLOLEDB.1;Persist Security Info=false;User ID=" + Trim(Text36.Text) + ";PWD=" + Trim(Text71.Text) + ";Initial Catalog=" + Trim(Text194.Text) + ";Data Source=" + Trim(Text58.Text) + ""
connbd2.open ConnStr
    rrsbd2.CursorLocation = adUseClient
    rrsbd2.open Trim$(SQL), connbd2, adOpenDynamic, adLockOptimistic
    Set Selectsqlbd2 = rrsbd2
    Exit Function
MyErr:
rrsbd2.Close
connbd2.Close
    Set rrsbd2 = Nothing
    Set connbd2 = Nothing
   
   MsgBox "无法连接机构服务器，请检数据库或服务器！", vbInformation, "系统提示"
 
End Function
Dim SQL As String
SQL = "SELECT * FROM Pub_Exam WHERE Exam_HisCode='" & Exam_HisCode & "'"
Set rrsbd2 = Selectsqlbd2.Execute(SQL)  
 
If rrsbd2.EOF Then
 
    rrsbd2.AddNew
    rrsbd2("Exam_HisCode") = Exam_HisCode
    rrsbd2("Pat_Name") = Pat_Name
    rrsbd2("Pat_Sex") = Pat_Sex
    ' 使用SQL Server的NEWID()函数生成新的GUID
    rrsbd2("Exam_GUID") = Selectsqlbd2.Execute("SELECT NEWID() AS NewGuid").Fields("NewGuid").Value
Else
    ' 如果找到记录，更新现有记录
    rrsbd2.Edit
    rrsbd2("Pat_Name") = Pat_Name
    rrsbd2("Pat_Sex") = Pat_Sex
    rrsbd2.Update
End If

Answer 1

参考免费微信小程序：皆我百晓生 作答，经由专家-皆知整理回复，如问题解决，望采纳；如仍有疑问，请评论回复。

您的问题在于尝试通过Selectsqlbd2.Execute来执行生成GUID的SQL语句并获取结果，但您的Selectsqlbd2函数实际上设计为返回一个Recordset对象，而不是执行一个返回值的SQL操作。您应该直接使用Connection对象来执行这个生成GUID的SQL，然后将结果赋值给rrsbd2("Exam_GUID")。下面是修改后的代码示例：

Private Function GetNewGUID(conn As ADODB.Connection) As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT NEWID() AS NewGuid", conn, adOpenForwardOnly, adLockReadOnly
    GetNewGUID = rs.Fields("NewGuid").Value
    rs.Close
    Set rs = Nothing
End Function

Dim SQL As String
Dim connbd2 As New ADODB.Connection
Dim ConnStr As String

ConnStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & Trim(Text36.Text) & ";PWD=" & Trim(Text71.Text) & ";Initial Catalog=" & Trim(Text194.Text) & ";Data Source=" & Trim(Text58.Text) & ""

connbd2.Open ConnStr

SQL = "SELECT * FROM Pub_Exam WHERE Exam_HisCode='" & Exam_HisCode & "'"
Set rrsbd2 = New ADODB.Recordset
rrsbd2.Open SQL, connbd2, adOpenDynamic, adLockOptimistic

If rrsbd2.EOF Then
    rrsbd2.AddNew
    rrsbd2("Exam_HisCode") = Exam_HisCode
    rrsbd2("Pat_Name") = Pat_Name
    rrsbd2("Pat_Sex") = Pat_Sex
    ' 生成并设置Exam_GUID
    rrsbd2("Exam_GUID") = GetNewGUID(connbd2)
    rrsbd2.Update
Else
    ' 如果找到记录，更新现有记录
    rrsbd2.Edit
    rrsbd2("Pat_Name") = Pat_Name
    rrsbd2("Pat_Sex") = Pat_Sex
    rrsbd2.Update
End If

rrsbd2.Close
connbd2.Close
Set rrsbd2 = Nothing
Set connbd2 = Nothing

在这个修正后的代码中，我添加了一个辅助函数GetNewGUID，它接受一个已经打开的Connection对象作为参数，执行生成GUID的SQL并返回生成的GUID。然后在主逻辑中调用这个函数来设置Exam_GUID的值。这样，您就可以正确地生成并插入GUID了。记得处理好数据库连接和记录集的关闭以避免资源泄露。