sql查询返回空记录集

dl5txlt9  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(333)

我正在尝试学习如何从excel数据库连接到sqlserverdb。我试着从一开始就把代码简化为非常简单的代码。我已经看了几个相关问题的答案,但是,我不明白为什么这不起作用。它一直执行(此处显示的代码有点匿名。)
查询将查找数据库,因为如果表名无效,它将抛出一个错误。但是,它总是返回record count=-1。我可以在mssms中查看表格,它有数据。db中其他表的结果相同。

Public Sub ADOtest1()

Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnString As String

strConnString = "Provider='SQLOLEDB'" & ";" & _
               "Data Source='XXX-XPS\SQLEXPRESS'" & ";" & _
               "Initial Catalog='XXXXX'" & ";" & _
               "Integrated Security='SSPI'"

Set Conn = New ADODB.Connection
Conn.Open strConnString

' the query finds the DB, because if the table name is incorrect, it throws an error
strSQLString = "SELECT * from t300_XXXX"

Set rs = Conn.Execute(strSQLString)
wrkRecordCount = rs.RecordCount

'--- just some test breakpoints
If wrkRecordCount = -1 Then
    a = ""  '--- code keeps arriving here
Else
    a = ""
End If

rs.Close
Conn.Close

End Sub

斯里尼卡的回答如下:

Set rs = Conn.Execute(strSQLString)
rs.Close
rs.CursorLocation = adUseClient
rs.Open
b5buobof

b5buobof1#

我将张贴两个例子,所以请参考。
第一个例子

Sub ExampleSQL()

   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strSQL As String

   Set cnn = New ADODB.Connection

   'Set the provider property to the OLE DB Provider for ODBC.
   'cnn.Provider = "MSDASQL"
   'cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
   'cnn.Provider = "MSOLAP"
   'cnn.Provider = "SQLOLEDB.1"
   ' Open a connection using an ODBC DSN.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=severname;uid=sa;pwd=password;database=test"

    Set rs = New ADODB.Recordset

    strSQL = "SELECT * FROM [your Table] "

    rs.Open strSQL, cnn.ConnectionString, adOpenForwardOnly, adLockReadOnly, adCmdText

    cnn.Open
    If cnn.State = adStateOpen Then
    Else
       MsgBox "Sever is not connected!! "
       Exit Sub
    End If

    If Not rs.EOF Then
         With Ws
            .Range("a4").CurrentRegion.ClearContents
            For i = 0 To rs.Fields.Count - 1
               .Cells(4, i + 1).Value = rs.Fields(i).Name
            Next
            .Range("a5").CopyFromRecordset rs
            .Columns.AutoFit
        End With
    Else
        MsgBox "No Data!!", vbCritical
    End If
    rs.Close
    Set rs = Nothing

    cnn.Close
    Set cnn = Nothing
End Sub

第二个例子

Sub getDataFromServer()
    Dim con As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset

    Dim i As Integer

    con.ConnectionString = "Provider=SQLOLEDB.1;" _
             & "Server=(local);" _
             & "Database=TEST;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;"

    con.Open

    Set cmd.ActiveConnection = con
    cmd.CommandText = "SELECT * FROM [your Table]"
    Set rs = cmd.Execute
    Range("A1").CopyFromRecordset rs

    con.Close
    Set con = Nothing

End Sub

相关问题