[RESOLVED] Changing SQLConnection question

[dlscott56]

I was trying to set up a simple test to be able to switch between a remote SQL server and a local SQL server. When I run this it gets data from the remote server and if I change the value of useLocalDB to true and run the stored procedure again I still get the data from the remote.

I'm probably just not understanding the connection process?

Imports System.Data.SqlClient
Public Class Form1
    Dim tblSteps As DataTable = New DataTable
    Dim StepsBindingSource As New BindingSource
    Dim useLocalDB As Boolean = False
    Dim cnPMSQL_Remote = New SqlConnection(My.Settings.SQLConnectionStringRemote)
    Dim cnPMSQL_Local = New SqlConnection(My.Settings.SQLConnectionStringLocal)

    Private Sub ExitProgram_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitProgram.Click
        Me.Close()
    End Sub

    Private Sub RunSproc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RunSproc.Click
        Dim spSteps As SqlCommand
        If useLocalDB Then
            spSteps = New SqlCommand("sproc_shop_order_get_recipe1", cnPMSQL_Local)
        Else
            spSteps = New SqlCommand("sproc_shop_order_get_recipe1", cnPMSQL_Remote)
        End If
        spSteps.Parameters.AddWithValue("@int_shop_order", "152837000001")
        spSteps.Parameters.AddWithValue("@int_prev_so", "459761000001")
        spSteps.CommandType = CommandType.StoredProcedure
        spSteps.CommandTimeout = 15
        tblSteps.Clear()

        Try
            Dim StepsAdapter As SqlDataAdapter = New SqlDataAdapter(spSteps)
            StepsAdapter.Fill(tblSteps)
            StepsBindingSource.DataSource = tblSteps
            dgSteps.DataSource = StepsBindingSource
        Catch ex As Exception
            Trace.WriteLine(Date.Now.ToLongTimeString & "*: Database not available" & ex.Message)
            MessageBox.Show("Database not available" & Environment.NewLine & ex.Message & Environment.NewLine & ex.StackTrace)
        End Try

    End Sub

    Private Sub RemoteDB_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RemoteDB.CheckedChanged
        If RemoteDB.Checked Then
            useLocalDB = False
        Else
            useLocalDB = True
        End If
    End Sub
End Class

[techgnome]

Before you fill your datatable... check the connection... make sure the connection is correct on the Adaptor and the command objects.

Also... *how* do you *know* your'e getting data from the server and not from the local one?

-tg

[dlscott56]

I changed the stored procedure on the local database to return data in a different order for my test so I could be sure. I ran the code reversing the logic so that the first time run it connects to the local data to verify it. Which ever connection is run first is how it stays.

I checked the connection string on the SqlCommand by adding this right before the Fill statement :

MessageBox.Show(spSteps.Connection.ConnectionString)

It changes as expected but I don't know how to verify the connection on the adapter?

[GaryMazzone]

Just for grins-- Do you close the connection any where when you are done with it? What about the command? Yes I understand that when it goes out of scope that should happen automaticly but I do it myself any way.

[brucevde]

Does the BindingSource.ListChanged event fire when the DataSource is changed? If not you need to call the BindingSource.ResetBindings method to instruct bound controls to refresh and display the new data.

[dlscott56]

I don't think it has to do with the BindingSource. Stepping through the program and looking at the data in the tlbSteps table, in the Immediate Window, right after the StepsAdapter.Fill(tblSteps) statement, the data isn't changing as it should. If I run it to the local database first then any subsequent try at changing the connection always shows the data as if it comes from the local database. Running it first time through using the remote database has the same effect. Always reads from the remote.

[dlscott56]

By the way, I did add some close and dispose statements as shown below trying to clear everything. Not sure I've got them in the right places.

    Private Sub RunSproc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RunSproc.Click
        Dim spSteps As SqlCommand

        If useLocalDB Then
            spSteps = New SqlCommand("sproc_shop_order_get_recipe1", cnPMSQL_Local)
        Else
            spSteps = New SqlCommand("sproc_shop_order_get_recipe1", cnPMSQL_Remote)
        End If

        spSteps.Parameters.AddWithValue("@int_shop_order", "152837000001")
        spSteps.Parameters.AddWithValue("@int_prev_so", "459761000001")
        spSteps.CommandType = CommandType.StoredProcedure
        spSteps.CommandTimeout = 15
        tblSteps.Clear()

        Try
            Dim StepsAdapter As SqlDataAdapter = New SqlDataAdapter(spSteps)
            MessageBox.Show(spSteps.Connection.ConnectionString)
            StepsAdapter.Fill(tblSteps)
            StepsBindingSource.DataSource = tblSteps
            dgSteps.DataSource = StepsBindingSource
            StepsAdapter.Dispose()
        Catch ex As Exception
            Trace.WriteLine(Date.Now.ToLongTimeString & "*: Database not available" & ex.Message)
            MessageBox.Show("Database not available" & Environment.NewLine & ex.Message & Environment.NewLine & ex.StackTrace)
        End Try
        cnPMSQL_Local.close()
        cnPMSQL_Remote.close()
        spSteps.Dispose()
    End Sub

[dlscott56]

Ok, turns out everything is working fine. To test if it was working on the local or remote database I was changing the order that data was being sent back from the stored procedure. Turns out my column order stays the same after the first time through in the datagridview, or table, regardless of how the data is sent in subsequent tries.

I went back into one of the databases and changed the data in the table and it is in fact retrieving from whichever database I have selected.

Thanks for the responses, that was driving me crazy!

[techgnome]

Cool! If you could mark the thread as resolved (under the Thread Tools at the top just above Post#1). Thanks!

-tg

[dlscott56]

Thanks again. Just had to add tblSteps.Columns.Clear and all is well.