Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Loop SQL statements

Status
Not open for further replies.
lrfcbabe

lrfcbabe

Programmer
Jul 19, 2001
108
US
After the first loop I get an error on command.executereader(), OleDbException occured, IErrorInfo.GerDescription failed with E)FAIL(0x80004005), error code -2147467259
Code: 
    Private Sub RefreshQuery(ByVal StrList As String)

      Dim Tempconn As New OleDbConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString"))
      Dim SQLTxt As String
      Try
        Tempconn.Open()
        QueryCombo1.Items.Clear()
        QueryCombo2.Items.Clear()
        QueryCombo3.Items.Clear()
        QueryCombo4.Items.Clear()
        QueryCombo5.Items.Clear()
        QueryCombo6.Items.Clear()
        QueryCombo7.Items.Clear()
        QueryCombo8.Items.Clear()
        QueryCombo9.Items.Clear()
        QueryCombo1.Text = Nothing
        QueryCombo2.Text = Nothing
        QueryCombo3.Text = Nothing
        QueryCombo4.Text = Nothing
        QueryCombo5.Text = Nothing
        QueryCombo6.Text = Nothing
        QueryCombo7.Text = Nothing
        QueryCombo8.Text = Nothing
        QueryCombo9.Text = Nothing

        Do Until StrList = "UNK"

          SQLTxt = "SELECT DISTINCT " & StrList & " FROM GHPAS4TG ORDER BY " & StrList & ";"

          command = New OleDbCommand(SQLTxt, Tempconn)
          dataReader = command.ExecuteReader()
          If dataReader.HasRows Then
            Select Case StrList
              Case "Own"
                Do While dataReader.Read
                  QueryCombo9.Items.Add(dataReader(StrList))
                Loop
                StrList = "UNK"
              Case "TestDate"
                Do While dataReader.Read
                  QueryCombo8.Items.Add(dataReader(StrList))
                Loop
                StrList = "Own"
              Case "Coat"
                Do While dataReader.Read
                  QueryCombo7.Items.Add(dataReader(StrList))
                Loop
                StrList = "TestDate"
              Case "LgSm"
                Do While dataReader.Read
                  QueryCombo6.Items.Add(dataReader(StrList))
                Loop
                StrList = "Coat"
              Case "SMYS"
                Do While dataReader.Read
                  QueryCombo5.Items.Add(dataReader(StrList))
                Loop
                StrList = "LgSm"
              Case "MAOP"
                Do While dataReader.Read
                  If dataReader(StrList).ToString = "-1" Then
                    QueryCombo4.Items.Add("UNK")
                  Else
                    QueryCombo4.Items.Add(dataReader(StrList).ToString)
                  End If
                Loop
                StrList = "SMYS"
              Case "WallThk"
                Do While dataReader.Read
                  If dataReader(StrList).ToString = "-1" Then
                    QueryCombo3.Items.Add("UNK")
                  Else
                    QueryCombo3.Items.Add(dataReader(StrList).ToString)
                  End If
                Loop
                StrList = "MAOP"
              Case "Size"
                Do While dataReader.Read
                  If dataReader(StrList).ToString = "-1" Then
                    QueryCombo2.Items.Add("UNK")
                  Else
                    QueryCombo2.Items.Add(dataReader(StrList).ToString)
                  End If
                Loop
                StrList = "WallThk"
              Case "Designation"
                Do While dataReader.Read
                  QueryCombo1.Items.Add(dataReader(StrList))
                Loop
                StrList = "Size"
            End Select
          Else
            MessageBox.Show("Custom Query Data Error!", "Data Error!", MessageBoxButtons.OK)
          End If

        Loop

      Catch ex As Exception
        MessageBox.Show("Custom Query!" + Environment.NewLine + ex.Message, "Data Error!", MessageBoxButtons.OK)
        'Debug.Print(ex.Message)
      Finally
        If command IsNot Nothing Then command = Nothing
        If dataReader IsNot Nothing Then dataReader = Nothing
        If Tempconn IsNot Nothing Then Tempconn.Close()
        If Tempconn IsNot Nothing Then Tempconn = Nothing
       End Try

    End Sub
 
Sort by date Sort by votes
Is it because DataReader is read only / forward only way to get date from your DB?
So if you read from the dataReader once, you are done.
If you try to read from DR again -> error.

Try to use a table instead, or read your data from DR once into an array, or other structure, and then you can use this new structure to loop thru as many times as you want.

Have fun.

---- Andy
 
Upvote 0 Downvote
So I tried this but after the first loop again I get the same error on da.fill(ds, "Table")
Code: 
    Private Sub RefreshQuery(ByVal StrList As String)

      Dim Tempconn As New OleDbConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString"))
      Dim SQLTxt As String
      Dim da As OleDbDataAdapter
      Dim ds As DataSet
      Dim dt As DataTable
      Dim i As Integer

      Try
        Tempconn.Open()
        QueryCombo1.Items.Clear()
        QueryCombo2.Items.Clear()
        QueryCombo3.Items.Clear()
        QueryCombo4.Items.Clear()
        QueryCombo5.Items.Clear()
        QueryCombo6.Items.Clear()
        QueryCombo7.Items.Clear()
        QueryCombo8.Items.Clear()
        QueryCombo9.Items.Clear()
        QueryCombo1.Text = Nothing
        QueryCombo2.Text = Nothing
        QueryCombo3.Text = Nothing
        QueryCombo4.Text = Nothing
        QueryCombo5.Text = Nothing
        QueryCombo6.Text = Nothing
        QueryCombo7.Text = Nothing
        QueryCombo8.Text = Nothing
        QueryCombo9.Text = Nothing

        Do Until StrList = "UNK"

          SQLTxt = "SELECT DISTINCT " & StrList & " FROM GHPAS4TG ORDER BY " & StrList & ";"

          command = New OleDbCommand(SQLTxt, Tempconn)
          ds = New DataSet()
          dt = New DataTable
          da = New OleDbDataAdapter(command)
          da.Fill(ds, "Table")
          dt = ds.Tables("Table")
          If dt.Rows.Count > 0 Then

            Select Case StrList
              Case "Own"
                For i = 0 To dt.Rows.Count - 1
                  QueryCombo9.Items.Add(dt.Rows(i)(0).ToString)
                Next
                StrList = "UNK"
              Case "TestDate"
                For i = 0 To dt.Rows.Count - 1
                  QueryCombo8.Items.Add(dt.Rows(i)(0).ToString)
                Next
                StrList = "Own"
              Case "Coat"
                For i = 0 To dt.Rows.Count - 1
                  QueryCombo7.Items.Add(dt.Rows(i)(0).ToString)
                Next
                StrList = "TestDate"
              Case "LgSm"
                For i = 0 To dt.Rows.Count - 1
                  QueryCombo6.Items.Add(dt.Rows(i)(0).ToString)
                Next
                StrList = "Coat"
              Case "SMYS"
                For i = 0 To dt.Rows.Count - 1
                  QueryCombo5.Items.Add(dt.Rows(i)(0).ToString)
                Next
                StrList = "LgSm"
              Case "MAOP"
                If dt.Rows(i)(0).ToString = "-1" Then
                  QueryCombo4.Items.Add("UNK")
                Else
                  QueryCombo4.Items.Add(dt.Rows(i)(0).ToString)
                End If
                StrList = "SMYS"
              Case "WallThk"
                If dt.Rows(i)(0).ToString = "-1" Then
                  QueryCombo3.Items.Add("UNK")
                Else
                  QueryCombo3.Items.Add(dt.Rows(i)(0).ToString)
                End If
                StrList = "MAOP"
              Case "Size"
                If dt.Rows(i)(0).ToString = "-1" Then
                  QueryCombo2.Items.Add("UNK")
                Else
                  QueryCombo2.Items.Add(dt.Rows(i)(0).ToString)
                End If
                StrList = "WallThk"
              Case "Designation"
                For i = 0 To dt.Rows.Count - 1
                  QueryCombo1.Items.Add(dt.Rows(i)(0).ToString)
                Next
                StrList = "Size"
            End Select
          Else
            MessageBox.Show("Custom Query Data Error!", "Data Error!", MessageBoxButtons.OK)
          End If
        Loop

      Catch ex As Exception
        MessageBox.Show("Custom Query!" + Environment.NewLine + ex.Message, "Data Error!", MessageBoxButtons.OK)
        'Debug.Print(ex.Message)
      Finally
        If Tempconn IsNot Nothing Then Tempconn.Close()
        If Tempconn IsNot Nothing Then Tempconn = Nothing
        da = Nothing
        ds = Nothing
        dt = Nothing
      End Try

    End Sub
 
Upvote 0 Downvote
If you go thru your loop several times, you create several ds and several dt called 'Table".

I would move stuff around and try something like this:

Code: 
...
QueryCombo9.Text = Nothing

ds = New DataSet()
dt = New DataTable

Do Until StrList = "UNK"

  SQLTxt = "SELECT DISTINCT " & StrList & " FROM GHPAS4TG ORDER BY " & StrList & ";"

  command = New OleDbCommand(SQLTxt, Tempconn)

  da = New OleDbDataAdapter(command)

  If Not ds.Tables("Table") Is Nothing Then
    ds.Tables.Remove("Table")
  End If

  da.Fill(ds, "Table")
  dt = ds.Tables("Table")
  If dt.Rows.Count > 0 Then

    Select Case StrList
...

Just a guess...

Have fun.

---- Andy
 
Upvote 0 Downvote
That didn't work either
This didn't work either
Code: 
        Do Until StrList = "UNK"
          Dim ds As DataSet = New DataSet()
          Dim dt As DataTable = New DataTable
          Dim SQLTxt As String

          If StrList = "Size" Then
            SQLTxt = "SELECT " & StrList & " FROM GHPAS4TG ASC;"
          Else
            SQLTxt = "SELECT DISTINCT " & StrList & " FROM GHPAS4TG ORDER BY " & StrList & ";"
          End If
          Dim i As Integer
          Dim comm As OleDbCommand = New OleDbCommand(SQLTxt, Tempconn)
          Dim da As OleDbDataAdapter = New OleDbDataAdapter(comm)
          da.Fill(ds, "Table")
          dt = ds.Tables("Table")
          If dt.Rows.Count > 0 Then
            Select Case StrList
.....
            End Select
            If Not ds.Tables("Table") Is Nothing Then ds.Tables.Remove("Table")
I don't know why I do anything..Lord
 
Upvote 0 Downvote
OK I am back on this again, I think I almost have it. I am getting this in the immmediate window.
SELECT Distinct Designation FROM GHPAS4TG ORDER BY Designation;
SELECT Distinct Size FROM GHPAS4TG ORDER BY Size;
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
SELECT Distinct WallThk FROM GHPAS4TG ORDER BY WallThk;
SELECT Distinct MAOP FROM GHPAS4TG ORDER BY MAOP;
SELECT Distinct SMYS FROM GHPAS4TG ORDER BY SMYS;
SELECT Distinct LgSm FROM GHPAS4TG ORDER BY LgSm;
SELECT Distinct Coat FROM GHPAS4TG ORDER BY Coat;
SELECT Distinct TestDate FROM GHPAS4TG ORDER BY TestDate;
SELECT Distinct Own FROM GHPAS4TG ORDER BY Own;

All of the comboboxes are being populated accept size.
This is the new code. I wish I could start this thread over.
Code: 
   Private Sub BuildQueryCombos(ByVal objRef As String)

      Dim Tempconn As New OleDbConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString"))
      Dim SQLTxt As String
      Dim StrList As String = objRef
      Dim dblWALL As Double
      Dim dblMAOP As Double
      Dim strActualDate As String

      Try
        Tempconn.Open()
        Tempconn.Close()
        Do Until StrList = "UNK"
          SQLTxt = "SELECT Distinct " + StrList + " FROM GHPAS4TG"
          If QueryCombo1.Text.Length > 0 Then SQLTxt = SQLTxt + " WHERE Designation=""" + QueryCombo1.Text.ToString + ""
          If QueryCombo2.Text.Length > 0 Then
            If QueryCombo1.Text.Length > 0 Then
              SQLTxt = SQLTxt + " AND Size=" + QueryCombo2.Text
            Else
              SQLTxt = SQLTxt + " WHERE Size=" + QueryCombo2.Text
            End If
          End If
          If QueryCombo3.Text.Length > 0 Then
            If QueryCombo3.Text = "UNK" Then
              dblWALL = -1
            Else
              dblWALL = Convert.ToDouble(QueryCombo3.Text)
            End If
            If QueryCombo1.Text.Length > 0 Or _
              QueryCombo2.Text.Length > 0 Then
              SQLTxt = SQLTxt + " AND WallThk=""" + dblWALL.ToString + ""
            Else
              SQLTxt = SQLTxt + " WHERE WallThk=""" + dblWALL.ToString + ""
            End If
          End If
          If QueryCombo4.Text.Length > 0 Then
            If QueryCombo4.Text = "UNK" Then
              dblMAOP = -1
            Else
              dblMAOP = Convert.ToDouble(QueryCombo4.Text)
            End If
            If QueryCombo1.Text.Length > 0 Or _
              QueryCombo2.Text.Length > 0 Or _
              QueryCombo3.Text.Length > 0 Then
              SQLTxt = SQLTxt + " AND MAOP=""" + dblMAOP.ToString + ""
            Else
              SQLTxt = SQLTxt + " WHERE MAOP=""" + dblMAOP.ToString + ""
            End If
          End If
          If QueryCombo5.Text.Length > 0 Then
            If QueryCombo1.Text.Length > 0 Or _
              QueryCombo2.Text.Length > 0 Or _
              QueryCombo3.Text.Length > 0 Or _
              QueryCombo4.Text.Length > 0 Then
              SQLTxt = SQLTxt + " AND SMYS=""" + QueryCombo5.Text.ToString + ""
            Else
              SQLTxt = SQLTxt + " WHERE SMYS=""" + QueryCombo5.Text.ToString + ""
            End If
          End If
          If QueryCombo6.Text.Length > 0 Then
            If QueryCombo1.Text.Length > 0 Or _
              QueryCombo2.Text.Length > 0 Or _
              QueryCombo3.Text.Length > 0 Or _
              QueryCombo4.Text.Length > 0 Or _
              QueryCombo5.Text.Length > 0 Then
              SQLTxt = SQLTxt + " AND LgSm=""" + QueryCombo6.Text.ToString + ""
            Else
              SQLTxt = SQLTxt + " WHERE LgSm=""" + QueryCombo6.Text.ToString + ""
            End If
          End If
          If QueryCombo7.Text.Length > 0 Then
            If QueryCombo1.Text.Length > 0 Or _
              QueryCombo2.Text.Length > 0 Or _
              QueryCombo3.Text.Length > 0 Or _
              QueryCombo4.Text.Length > 0 Or _
              QueryCombo5.Text.Length > 0 Or _
              QueryCombo6.Text.Length > 0 Then
              SQLTxt = SQLTxt + " AND Coat=""" + QueryCombo7.Text.ToString + ""
            Else
              SQLTxt = SQLTxt + " WHERE Coat=""" + QueryCombo7.Text.ToString + ""
            End If
          End If
          If QueryCombo8.Text.Length > 0 Then
            If QueryCombo8.Text = "UNK" Then
              strActualDate = "1900"
            Else
              strActualDate = QueryCombo8.Text
            End If
            If QueryCombo1.Text.Length > 0 Or _
              QueryCombo2.Text.Length > 0 Or _
              QueryCombo3.Text.Length > 0 Or _
              QueryCombo4.Text.Length > 0 Or _
              QueryCombo5.Text.Length > 0 Or _
              QueryCombo6.Text.Length > 0 Or _
              QueryCombo7.Text.Length > 0 Then
              SQLTxt = SQLTxt + " AND TestDate=""" + strActualDate + ""
            Else
              SQLTxt = SQLTxt + " WHERE TestDate=""" + strActualDate + ""
            End If
          End If
          If QueryCombo9.Text.Length > 0 Then
            If QueryCombo1.Text.Length > 0 Or _
              QueryCombo2.Text.Length > 0 Or _
              QueryCombo3.Text.Length > 0 Or _
              QueryCombo4.Text.Length > 0 Or _
              QueryCombo5.Text.Length > 0 Or _
              QueryCombo6.Text.Length > 0 Or _
              QueryCombo7.Text.Length > 0 Or _
              QueryCombo8.Text.Length > 0 Then
              SQLTxt = SQLTxt + " AND Own=""" + QueryCombo9.Text.ToString + ""
            Else
              SQLTxt = SQLTxt + " WHERE Own=""" + QueryCombo9.Text.ToString + ""
            End If
          End If
          SQLTxt = SQLTxt + " ORDER BY " + StrList + ";"
          Debug.Print(SQLTxt)
          PopulateQueryCombos(SQLTxt, StrList)
          Select Case StrList
            Case "Own"
              StrList = "UNK"
            Case "TestDate"
              StrList = "Own"
            Case "Coat"
              StrList = "TestDate"
            Case "LgSm"
              StrList = "Coat"
            Case "SMYS"
              StrList = "LgSm"
            Case "MAOP"
              StrList = "SMYS"
            Case "WallThk"
              StrList = "MAOP"
            Case "Size"
              StrList = "WallThk"
            Case "Designation"
              StrList = "Size"
          End Select
        Loop

      Catch ex As Exception
        MessageBox.Show("Custom Query!" + Environment.NewLine + ex.Message, "Data Error!", MessageBoxButtons.OK)
        'Debug.Print(ex.Message)
      Finally
        If Tempconn IsNot Nothing Then Tempconn.Close()
        If Tempconn IsNot Nothing Then Tempconn = Nothing
      End Try

    End Sub

    Private Sub PopulateQueryCombos(ByVal SQLstring As String, ByVal strlist As String)

      Dim Tempconn As New OleDbConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString"))

      Try
        Tempconn.Open()

        Dim MyCommand As OleDbCommand
        Dim MyDatareader As OleDbDataReader

        MyCommand = New OleDbCommand(SQLstring, Tempconn)
        MyDatareader = MyCommand.ExecuteReader()
        If MyDatareader.HasRows Then
          Do While MyDatareader.Read
            Select Case strlist
              Case "Own"
                QueryCombo9.Items.Add(MyDatareader("Own"))
              Case "TestDate"
                QueryCombo8.Items.Add(MyDatareader("TestDate"))
              Case "Coat"
                QueryCombo7.Items.Add(MyDatareader("Coat"))
              Case "LgSm"
                QueryCombo6.Items.Add(MyDatareader("LgSm"))
              Case "SMYS"
                QueryCombo5.Items.Add(MyDatareader("SMYS"))
              Case "MAOP"
                QueryCombo4.Items.Add(MyDatareader("MAOP"))
              Case "WallThk"
                QueryCombo3.Items.Add(MyDatareader("WallThk"))
              Case "Size"
                QueryCombo2.Items.Add(MyDatareader("Size"))
              Case "Designation"
                QueryCombo1.Items.Add(MyDatareader("Designation"))
            End Select
          Loop
        End If
        If MyCommand IsNot Nothing Then MyCommand = Nothing '.Dispose()
        If MyDatareader IsNot Nothing Then MyDatareader = Nothing '.Dispose()

      Catch ex As Exception
        MessageBox.Show("Custom Query!" + Environment.NewLine + ex.Message, "Data Error!", MessageBoxButtons.OK)
      Finally
        If Tempconn IsNot Nothing Then Tempconn.Close()
      End Try

    End Sub
 
Upvote 0 Downvote
The brackets get me past the first hurdle. Size is a structure in dotnet.

SELECT Distinct Designation FROM GHPAS4TG ORDER BY Designation;
SELECT Distinct [Size] FROM GHPAS4TG ORDER BY [Size];
SELECT Distinct WallThk FROM GHPAS4TG ORDER BY WallThk;
SELECT Distinct MAOP FROM GHPAS4TG ORDER BY MAOP;
SELECT Distinct SMYS FROM GHPAS4TG ORDER BY SMYS;
SELECT Distinct LgSm FROM GHPAS4TG ORDER BY LgSm;
SELECT Distinct Coat FROM GHPAS4TG ORDER BY Coat;
SELECT Distinct TestDate FROM GHPAS4TG ORDER BY TestDate;
SELECT Distinct Own FROM GHPAS4TG ORDER BY Own;
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Locked
  • Question
USE find instead to loop
Replies
2
Views
201
Andrzejek
Andrzejek
sal21
  • Locked
  • Question
LOOP trought cildren nodes 1
Replies
7
Views
99
sal21
sal21
sal21
  • Locked
  • Question
FIND IN DICTIONARY WHITOUTH loop 1
Replies
4
Views
148
strongm
strongm
Niki_S
  • Locked
  • Question
How to insert excel sheet into sql server using vb 6?
Replies
6
Views
186
Andrzejek
Andrzejek
Niki_S
  • Locked
  • Question
How to use a combo box to get data from SQL? 1
Replies
1
Views
93
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top