none
The ConnectionString property has not been initialized - System.Data.SqlClient RRS feed

  • Question

  • This has to be a dumb error on my part but I can't find it...

    I have a routine to create a dataset:

      ' Public Const AQIConnectString = "Data Source=apcd03;Initial Catalog=AQI;Integrated Security=True" 
      Function CreateDataSet(ByRef ds As DataSet, ByRef da As SqlDataAdapter, ByRef dt As DataTable, SQL As String, Optional TableName As String = "Table0") As Boolean
        Try
          Using con = New SqlConnection(AQIConnectString)
            ds = New DataSet
            da = New SqlDataAdapter()
            da.SelectCommand = New SqlCommand(SQL, con)
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
            da.Fill(ds, TableName)
            dt = ds.Tables(0)
          End Using
        Catch ex As Exception
          MsgBox(ex.Message, , "CreateDataSet")
          Return False
        End Try
        Return True
      End Function
    

    Here's the call to populate the ADO.Net objects (CreateDataSet code in a module object, ByRef for the ADO.Net objs):

      Sub LoadForecasts()
        CreateDataSet(dsForecasts, daForecasts, dtForecasts, "SELECT * FROM AQI_Forecast ORDER BY Region_Key, Forecast_Date", "Forecasts")
        dvForecasts = New DataView(dtForecasts)
        ''dvForecasts.RowFilter = "Region_Key=''"
        dgvForecast.DataSource = dvForecasts
        dgvForecast.Columns("Region_Key").Visible = False
      End Sub
    

    The ADO.Net objects are declare at the form level.

    The data displays fine in the DataGridView control.

    I edit some data and call:

    daForecasts.Update(dtForecasts)
    

    ..and then I get the "The ConnectionString property has not been initialized" error.

    I haven't see the UpdateCommand being populated either - not sure when it is populated - when the builder is declared on when first used - in either case it is still Nothing.

    The SQL calls on a table with a two field primary key - a string and a smalldate field. I won't be using the PK in my code to do any lookups, just looking for uniqueness, so the weird field combo shouldn't be an issue. I tried using an Idenity Field but ADO.Net doesn't support them properly IMO and I never can get the ADO.Net objects to create records. The field names do not conflict with reserved names.

    The connectstring itself is obviously OK as records are returned. No idea what object is throwing the error message or what string it is refering to.

    I'm about to give up on the bound DataGridView after the 'N' time with no success....

    Anybody see the issue?

    Thanks!

     

    Monday, August 15, 2011 9:30 PM

Answers

  • Found the issue - don't say as I understand...

    I removed the Using... End Using and it now works. And the da.UpdateCommand is still Nothing at this point but evidently it gets filled in automagically at the proper time.

        Function CreateDataSet(ByRef ds As DataSetByRef da As SqlDataAdapterByRef dt As DataTable, SQL As StringOptional TableName As String = "Table0"As Boolean
            Try
                'Using con = New SqlConnection(AQIConnectString)
                Dim con = New SqlConnection(AQIConnectString)
                ds = New DataSet
                da = New SqlDataAdapter()
                con.Open()
                da.SelectCommand = New SqlCommand(SQL, con)
                Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
                da.Fill(ds) ', TableName)
                dt = ds.Tables(0)
                con.Close()
                'End Using
            Catch ex As Exception
                MsgBox(ex.Message, , "CreateDataSet")
                Return False
            End Try
            Return True
        End Function
    • Marked as answer by Bob Heitzman Tuesday, August 16, 2011 5:11 PM
    Tuesday, August 16, 2011 5:11 PM

All replies

  • Are you sure the databaseconnection is open when you commit the canges?
    Kenneth
    Tuesday, August 16, 2011 8:17 AM
  • No - but I haven't had to mess with the connection before in cases where the update has worked. I'm not celar on how I would open the connection anyway. The connection instance used to set up the objects went out of scope long ago. The data objects may have their pointer to that instance but I don't think I have access to it.

    I guess I'm facing several issues:

    0 the UpdateCommand is not getting populated by the builder - maybe populated later, unclear on that. I haven't gotten past the connection string error so I don't know if this is a problem or not.
    0 somehow the ADO.Net objects are looking for a connection string - I have no idea where. Clearly a valid connection was initally provided as records are return from the SQL Server. I do have update permission on the table in question.
    0 I'm trying to update/add from a DataGridView that marginally works with ADO.Net objects. I did add this code:

               dgvForecast.EndEdit()
                Me.Validate()
                daForecasts.Update(dtForecasts)
    

    .. which help in other cases of using DataGridView with ADO.Net objects. No joy.

    In this project I once tried using the VB.Net data controls but that failed and I deleted them (maybe there are some reamains) and tried to use the ADO.Net objects. Perhaps this confused VB.Net and it is trying to deal with the deleted objects. There was an app.config file with the conect string but I deleted that as well.

    I can update the rows and see the results in the grid and in the datatable rows. The datasets HasChanges flag is set after the grid edits. 

    Here is the code that opens the dataset (unchanged from above):

                Using con = New SqlConnection(AQIConnectString)
                    ds = New DataSet
                    da = New SqlDataAdapter()
                    da.SelectCommand = New SqlCommand(SQL, con)
                    Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
                    da.Fill(ds, TableName)
                    dt = ds.Tables(0)
                End Using

    ds, da, and dt are passed in by reference and they are declare at the form level of a form that called this code which resides in a module. builder and con are local in scope. The SQL is a SELECT * ORDER BY .. on a table I have write permisson on. There is a primary key defined. The objects are poluated but the da UpdateCommand is Nothing.

     

     

    Tuesday, August 16, 2011 4:43 PM
  • Found the issue - don't say as I understand...

    I removed the Using... End Using and it now works. And the da.UpdateCommand is still Nothing at this point but evidently it gets filled in automagically at the proper time.

        Function CreateDataSet(ByRef ds As DataSetByRef da As SqlDataAdapterByRef dt As DataTable, SQL As StringOptional TableName As String = "Table0"As Boolean
            Try
                'Using con = New SqlConnection(AQIConnectString)
                Dim con = New SqlConnection(AQIConnectString)
                ds = New DataSet
                da = New SqlDataAdapter()
                con.Open()
                da.SelectCommand = New SqlCommand(SQL, con)
                Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
                da.Fill(ds) ', TableName)
                dt = ds.Tables(0)
                con.Close()
                'End Using
            Catch ex As Exception
                MsgBox(ex.Message, , "CreateDataSet")
                Return False
            End Try
            Return True
        End Function
    • Marked as answer by Bob Heitzman Tuesday, August 16, 2011 5:11 PM
    Tuesday, August 16, 2011 5:11 PM