Will the data in DataTable be updated according to continuous query?

[candice]

Hello guys,
I'm now wirting a program that is expected to excute a conditional SQL query.
following is part of my code:

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] method()[/SIZE]
[SIZE=2][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable = GetData([/SIZE][SIZE=2][COLOR=#800000]"[SIZE=2][COLOR=#800000]
select lotid from actl where eqpid='S103SIG' and lotid not in (select lotid from rpt_del_lot)
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000]"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2]dt.Rows(r).Item([/SIZE][SIZE=2][COLOR=#800000]"lotid"[/COLOR][/SIZE][SIZE=2])[/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Is[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2]GetData([/SIZE][SIZE=2][COLOR=#800000]"SQL goes on here[/COLOR][/SIZE][SIZE=2][COLOR=#800000]"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]else [/SIZE]
[SIZE=2]GetData([SIZE=2][COLOR=#800000]"SQL goes on here[/COLOR][/SIZE][SIZE=2][COLOR=#800000]"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][/COLOR][/SIZE][/SIZE][/SIZE]

[SIZE=2][COLOR=#0000ff]
Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] GetData([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] selectCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][COLOR=#008000][/COLOR][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connectionString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = _
[/SIZE][SIZE=2][COLOR=#800000]" Data Source=singdb;User ID=mfg_rpt;Password=sentosa;Unicode=True;"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' Create a new data adapter based on the specified query. 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleDataAdapter(selectCommand, connectionString)
[/SIZE][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] commandBuilder [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleCommandBuilder([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataAdapter)
[/SIZE][SIZE=2][COLOR=#008000]' Populate a new data table and bind it to the BindingSource. 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable()
dt.Locale = System.Globalization.CultureInfo.InvariantCulture
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataAdapter.Fill(dt)
[/SIZE][SIZE=2][COLOR=#0000ff]Return[/COLOR][/SIZE][SIZE=2] dt
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OracleException
MessageBox.Show([/SIZE][SIZE=2][COLOR=#800000]"To run this example, replace the value of the "[/COLOR][/SIZE][SIZE=2] + _
[/SIZE][SIZE=2][COLOR=#800000]"connectionString variable with a connection string that is "[/COLOR][/SIZE][SIZE=2] + _
[/SIZE][SIZE=2][COLOR=#800000]"valid for your system."[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function
[/COLOR][/SIZE]

I'm wondering if this code can run as expected. In other words, whether the previous data in datatable can be updated by the data got from the next "GetData" statement. And whether this condition goes the same in datagridview.
Thank you very much!!

 

[cjard]

This post doesnt make much sense to me, but i offer the following tips:

*TableAdapters ususally clear a datatable before they fill it

*If you write a code that fills a datatable, then skips over the datatable, filling more data, it may never finish

*Dont write sqls like this:
select lotid from actl where eqpid='S103SIG' and lotid not in (select lotid from rpt_del_lot)
Write them like this instead:

[COLOR=#800000]select [/COLOR]
[COLOR=#800000] a.lotid [/COLOR]
[COLOR=#800000]from [/COLOR]
[COLOR=#800000] actl a[/COLOR]
[COLOR=#800000] left outer join[/COLOR]
[COLOR=#800000] rpt_del_lot r[/COLOR]
[COLOR=#800000] on [/COLOR]
[COLOR=#800000]   r.lotid = a.lotid[/COLOR]
 
[COLOR=#800000]where [/COLOR]
[COLOR=#800000] a.eqpid='S103SIG' and [/COLOR]
[COLOR=#800000] r.lotid is null[/COLOR]

why? you want all lotids where eqpid is something, and those lots dont appear in rpt_del_lot. so, you should join the two tables together with a left join, where rpt_del_lot will be blank if it doesnt exist, and then look for that as a condition

The optimizer can work with queries like this much more easily than a nOT IN.
IN can be very inefficient. Dont use it unless it is for a list so small that you would be prepared to type it by hand.

 

[candice]

Thanks cjard,
Ya, I realize my post makes no much sense too...But now I encounter a problem.
My logic is:1, query data using SQL1
2, if is not no rows selected then query data using SQL2
3, Else query data using SQL3
4, end if
And I modified my function like following, which will return nothing when the exception being caught.

Public Function GetData(ByVal selectCommand As String) As DataTable 
        Dim dt As New DataTable() 
        Try 
            Dim connectionString As String = _ 
           " Data Source=singdb;User ID=mfg_rpt;Password=sentosa;Unicode=True;" 
 
            Me.dataAdapter = New OracleDataAdapter(selectCommand, connectionString) 
 
            Dim commandBuilder As New OracleCommandBuilder(Me.dataAdapter) 
            dt.Locale = System.Globalization.CultureInfo.InvariantCulture 
            Me.dataAdapter.Fill(dt) 
 
       Catch ex As OracleException 
            dt = Nothing 
        End Try 
        Return dt 
    End Function

The submethod is:

  Dim dt As DataTable 
        dt = GetData("SQL1") 
        If Not dt Is Nothing Then 
            dt = GetData("SQL2 goes on here") 
        Else 
            dt = GetData("SQL3 goes on here") 
        End If

The result is if the 1st SQL got something, the 2nd SQL will run. And it works well.
But if the 1st SQL got nothing, the 3rd SQL never carry on, I finnally got nothing. (The 3rd SQL can get something, it runs well by itself.)
Why does this happen? Doea that mean once the OracleException is caught, the following getdata can't carry on? Or is there sth. wrong with my logic?
Thank you for your help!!

 

[cjard]

Erm

I'm not sure but I think youre assuming that no_rows_returned is the same as nothing?

It is perfectly possible that a query will return no rows, so you will get a datatable object which is Something, but it will have 0 rows

You might do this:

        Dim dt As New DataTable() 
        Try 
            Dim connectionString As String = _ 
           " Data Source=singdb;User ID=mfg_rpt;Password=*****;Unicode=True;" 
 
            Me.dataAdapter = New OracleDataAdapter(selectCommand, connectionString) 
 
            Dim commandBuilder As New OracleCommandBuilder(Me.dataAdapter) 
            dt.Locale = System.Globalization.CultureInfo.InvariantCulture 
            Me.dataAdapter.Fill(dt) 
 
       Catch ex As OracleException 
            MessageBox.Show(ex.Message) 'never hide the errors! log them!
        End Try 
        Return dt 
    End Function

and then:

        dt = GetData("SQL1") 
        If dt.Rows.Count > 0 Then 
            dt = GetData("SQL2 goes on here") 
        Else 
            dt = GetData("SQL3 goes on here") 
        End If

But remember that whatever data is gotten by SQL1 is lost!


Question.. why are you doing your data access with untyped datasets and connection strings code in your button handlers? Oracle works with DataSet Designer!