[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 732
  • Last Modified:

ConnectionString error....

Everything was working fine until I added the 4th SQL connection.  For some reason I get the error "The ConnectionString property has not been initialized. Line 236: SqlConnection1.Open()".  What am I missing?  I'm sure there is a more simple way to execute 4 SQL statements one right after the other.

Here is my code:

Private Sub Button1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.ServerClick
        Dim ds2 As New DataSet
        Dim sqlCommand3 As New SqlClient.SqlCommand
        Dim sqlCommand4 As New SqlClient.SqlCommand

        'clear adapter
        SqlDataAdapter1.Dispose()

        'new sql statement for returning values
        SqlSelectCommand2.CommandText = "SELECT Lots.LotNumber as 'Lot Number', Lots.LotDispo as 'Disposition', Lots.LotWeight as 'Weight', Lots.DateShipped as 'Date', Lots.CustomerNumber as 'Customer', Batches.BatchWeight as 'Batch Weight' FROM Lots, Batches where Lots.LotNumber like '" & Left(lotNumber.SelectedItem.Text, 4) & "%" & Mid(lotNumber.SelectedItem.Text, 6, 9) & "%' and Batches.BatchID=Lots.Batch ORDER BY DateShipped;"
        SqlConnection1.Open()

        'datagrid
        DataGrid1.DataSource = SqlSelectCommand2.ExecuteReader
        DataGrid1.DataBind()
        SqlConnection1.Close()
        SqlDataAdapter1.Dispose()

        'sql statement to get total LotWeight
        sqlCommand3.CommandText = "select sum(LotWeight) from Lots where Lots.LotNumber Like '" & Left(lotNumber.SelectedItem.Text, 4) & "%" & Mid(lotNumber.SelectedItem.Text, 6, 9) & "%'"
        sqlCommand3.Connection = SqlConnection1
        SqlConnection1.Open()

        'UsedGrid
        UsedGrid.DataSource = sqlCommand3.ExecuteReader
        UsedGrid.ShowHeader = False
        UsedGrid.DataBind()
        SqlConnection1.Close()
        SqlConnection1.Dispose()

        'sql statement to get left weight
        sqlCommand4.CommandText = "select sum(Lots.LotWeight)-sum(Batches.BatchWeight) from Lots, Batches where Lots.LotNumber Like '" & Left(lotNumber.SelectedItem.Text, 4) & "%" & Mid(lotNumber.SelectedItem.Text, 6, 9) & "%' =Lots.Batch"
        sqlCommand4.Connection = SqlConnection1
        SqlConnection1.Open()

        'LeftGrid
        LeftGrid.DataSource = sqlCommand4.ExecuteReader
        LeftGrid.ShowHeader = False
        LeftGrid.DataBind()
        SqlConnection1.Close()
        SqlConnection1.Dispose()

        Label3.Visible = True
        Label4.Visible = True
        Label5.Visible = True
        Label6.Visible = True

    End Sub
0
johnqtr
Asked:
johnqtr
1 Solution
 
LacutahCommented:
Because you disposed of the connection immediately before the fourth sql statement:

...

        SqlConnection1.Dispose() ------>  Now, SqlConnection1 is nothing!  Remove this line.

        'sql statement to get left weight
        sqlCommand4.CommandText = "select sum(Lots.LotWeight)-sum(Batches.BatchWeight) from Lots, Batches where Lots.LotNumber Like '" & Left(lotNumber.SelectedItem.Text, 4) & "%" & Mid(lotNumber.SelectedItem.Text, 6, 9) & "%' =Lots.Batch"
        sqlCommand4.Connection = SqlConnection1  -----> SqlConnection1 is still nothing....
        SqlConnection1.Open()  ---->  Error here....


As a side note, don't close the connection after each statement is run - instead leave it open until all statements are run.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now