VB.Net OleDb连接到上一个源

Question

我有一个简单的应用程序，它使用相同的OleDb提供程序从两个不同的源检索数据。如果我点击btnSource1它应该会从url1检索数据如果我点击btnSource2它应该会从url2检索数据。每个源在ConnectionString中都有不同的数据源、密码和用户ID。

第一个连接工作正常，但每当我从一个源切换到另一个源时，连接失败，登录无效。然后，如果我进行第二次尝试，第二次尝试将正常工作并查询正确的源。每次我切换源，第一次尝试失败，但所有后续尝试工作。

Debugging显示正在使用正确的连接字符串，但它肯定在使用先前尝试的连接。我错过了什么明显的东西吗?

Private Sub btnSource1_Click(sender As Object, e As EventArgs) Handles btnSource1.Click
Dim conn As New OleDbConnection("Provider=myprovider;Data Source=url1;Password=password1;User ID=userid1")
Dim cmd As New OleDbCommand("select count(*) as num from table", conn)
Dim dt As New DataTable
Dim da As New OleDbDataAdapter(cmd)
Try
da.Fill(dt)
dgvResults.DataSource = dt
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
conn.Dispose()
End Try
End Sub
Private Sub btnSource2_Click(sender As Object, e As EventArgs) Handles btnSource2.Click
Dim conn As New OleDbConnection("Provider=myprovider;Data Source=url2;Password=password2;User ID=userid2")
Dim cmd As New OleDbCommand("select count(*) as num from table", conn)
Dim dt As New DataTable
Dim da As New OleDbDataAdapter(cmd)
Try
da.Fill(dt)
dgvResults.DataSource = dt
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
conn.Dispose()
End Try
End Sub

编辑:

根据评论中的建议，我重新配置了使用Using语句的东西。问题仍然存在。

Sub btnSource1_Click(sender As Object, e As EventArgs) Handles btnSource1.Click
Dim cs As String = "Provider=myprovider;Data Source=url1;Password=password1;User ID=userid1"
Dim dt As New DataTable
Using cn As New OleDbConnection(cs)
Using cmd As New OleDbCommand("select count(*) as num from mytable", cn)
Try
With cmd
.Connection.Open()
End With
dt.Load(cmd.ExecuteReader())
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Using
End Using
End Sub
Private Sub btnSource2_Click(sender As Object, e As EventArgs) Handles btnSource2.Click
Dim cs As String = "Provider=myprovider;Data Source=url2;Password=password2;User ID=userid2"
Dim dt As New DataTable
Using cn As New OleDbConnection(cs)
Using cmd As New OleDbCommand("select count(*) as num from mytable", cn)
Try
With cmd
.Connection.Open()
End With
dt.Load(cmd.ExecuteReader())
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Using
End Using
End Sub

Answer 1

如果我们能看到这一点更接近实际的代码，也许我们可以看到错误。无论如何，如果您只检索计数，请使用ExecuteScalar.

您可以使用。

将连接和命令组合在一起。

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim cs As String = "Provider=myprovider;Data Source=url1;Password=password1;User ID=userid1"
Dim Count As Integer
Try
Using cn As New OleDbConnection(cs),
cmd As New OleDbCommand("select count(*) as num from mytable", cn)
cn.Open()
Count = CInt(cmd.ExecuteScalar())
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Label1.Text = Count.ToString
End Sub