Datatable

9 Mar 2007 5:59 AM

meka

For i = 0 To dtSubOpt.Rows.Count - 1

intOptNo = dtSubOpt.Rows(i)(1)

strSelectSubOpt = "select tblOptions.strOptName as
OptionName,tblOptions.intParent ,tblMOMT.strMOTag as
MessageSentFromServer,tblMOMT.strMTTag as MessageSentToServer from
tblOptions inner join tblMOMT on tblMOMT.intOptId=tblOptions.intOptId
where intParent=" & intOptNo & " order by OptionName"

dtOptName = dbCon.GetRecordSet(strSelectSubOpt, "tblOptions")

Next

In my above coding i need to keep all the records retrieved through
looping the query in the same datatable.
In my coding every time intOptNo has changed and retrieve the records
corresponding to that particular intOptNo . The loop executed every
times and get the records ,but the records overwrite the records
already in a datatable this is my problem .So to avoid this I need to
increment the index of the datatable row .But I don't know how to
change the row index depending upon the number of rows returned by
the query .Is there any way to find the row index of the datatable and
to increment that no to keep the next records orelse tell me some
other way to keep all the records retrieved through looping the
query in the same datatable or in the same DataSet.

Kindly expecting the reply,
meka.

9 Mar 2007 6:30 PM

RobinS

Is this in an Access database?

I'm asking because the dbCon.GetRecordSet( ) looks like DAO or Access.

Robin S.
------------------------
Show quote

"meka" <mekalagunaseka***@gmail.com> wrote in message
news:1173419978.833928.120220@64g2000cwx.googlegroups.com...
> For i = 0 To dtSubOpt.Rows.Count - 1
>
> intOptNo = dtSubOpt.Rows(i)(1)
>
> strSelectSubOpt = "select tblOptions.strOptName as
> OptionName,tblOptions.intParent ,tblMOMT.strMOTag as
> MessageSentFromServer,tblMOMT.strMTTag as MessageSentToServer from
> tblOptions inner join tblMOMT on tblMOMT.intOptId=tblOptions.intOptId
> where intParent=" & intOptNo & " order by OptionName"
>
> dtOptName = dbCon.GetRecordSet(strSelectSubOpt, "tblOptions")
>
> Next
>
> In my above coding i need to keep all the records retrieved through
> looping the query in the same datatable.
> In my coding every time intOptNo has changed and retrieve the records
> corresponding to that particular intOptNo . The loop executed every
> times and get the records ,but the records overwrite the records
> already in a datatable this is my problem .So to avoid this I need to
> increment the index of the datatable row .But I don't know how to
> change the row index depending upon the number of rows returned by
> the query .Is there any way to find the row index of the datatable and
> to increment that no to keep the next records orelse tell me some
> other way to keep all the records retrieved through looping the
> query in the same datatable or in the same DataSet.
>
> Kindly expecting the reply,
> meka.
>

12 Mar 2007 12:16 PM

meka

no i am using sql database.And dbCon is the object created for one class to access the functions within it. dbCon.GetRecordSet( ) is the function in which i have written coding to open the connection and close the connection and to do some other processes also . I have declared GetRecordSet() as a datatable ,so it returns the values in the form of table.

Public Function GetRecordSet(ByVal query As String, ByVal tab As String)     As DataTable
Dim connectionString As String = "User ID=sa;Initial Catalog=dbSMSApp;Data Source=IWSERVER"
            Info=False;User ID=sa;Initial Catalog=dbSathyam;Data Source=strDbPath"

            Dim Dt As DataTable = ExecuteQuery(connectionString, query, tab)
            Return Dt
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

    Public Function ExecuteQuery(ByVal ConnectionString As String, ByVal query As String, ByVal tableName As String) As DataTable

        Try
            Dim myConnection As SqlConnection = New SqlConnection(ConnectionString)
            Dim myAdapter As SqlDataAdapter = New SqlDataAdapter(query, myConnection)
            Dim ds As DataSet = New DataSet
            myAdapter.Fill(ds, tableName)
            ds.Tables(0).TableName = tableName
            Return ds.Tables(0)
            Return ds.Tables(0)
        Catch ex As System.Data.OleDb.OleDbException
            'MsgBox(ex.Message)
        End Try
    End Function

>Is this in an Access database?

I'm asking because the dbCon.GetRecordSet( ) looks like DAO or Access.

Robin S.
------------------------
Show quote

"meka" <mekalagunaseka***@gmail.com> wrote in message
news:1173419978.833928.120220@64g2000cwx.googlegroups.com...
> For i = 0 To dtSubOpt.Rows.Count - 1
>
> intOptNo = dtSubOpt.Rows(i)(1)
>
> strSelectSubOpt = "select tblOptions.strOptName as
> OptionName,tblOptions.intParent ,tblMOMT.strMOTag as
> MessageSentFromServer,tblMOMT.strMTTag as MessageSentToServer from
> tblOptions inner join tblMOMT on tblMOMT.intOptId=tblOptions.intOptId
> where intParent=" & intOptNo & " order by OptionName"
>
> dtOptName = dbCon.GetRecordSet(strSelectSubOpt, "tblOptions")
>
> Next
>
> In my above coding i need to keep all the records retrieved through
> looping the query in the same datatable.
> In my coding every time intOptNo has changed and retrieve the records
> corresponding to that particular intOptNo . The loop executed every
> times and get the records ,but the records overwrite the records
> already in a datatable this is my problem .So to avoid this I need to
> increment the index of the datatable row .But I don't know how to
> change the row index depending upon the number of rows returned by
> the query .Is there any way to find the row index of the datatable and
> to increment that no to keep the next records orelse tell me some
> other way to keep all the records retrieved through looping the
> query in the same datatable or in the same DataSet.
>
> Kindly expecting the reply,
> meka.
>

___ Newsgroups brought to you courtesy of www.dotnetjohn.com

13 Mar 2007 5:08 AM

RobinS

Okay, got it. So you're repeatedly reading a table for the records that go
with some key into another table, i.e. you're loading dtOptName for a bunch
of values of intOptNo.

And what you want is to end up with a datatable that has all the matching
records rather than just the last set loaded.

The easiest thing to do is to call the Fill method on the data adapter for
each subquent query. This will not replace the records in the data table,
but add to them. (By the way, you have Return ds.Tables(0) in your
ExecuteQuery function twice.) This will, unfortunately, require some
changes to your back-and-forth logic. You would have to pass the data
table to your data layer and get it back with the additional records in it.

The other thing you can look at is the Merge command. So the first time,
create a data table, the second time, merge the new records in to the first
table, and so on.

Hope this helps.
Robin S.
--------------------------------------
Show quote

"meka" <mekalagunaseka***@gmail.com> wrote in message
news:uyl5JBKZHHA.4308@TK2MSFTNGP05.phx.gbl...
> no i am using sql database.And dbCon is the object created for one class
> to access the functions within it. dbCon.GetRecordSet( ) is the function
> in which i have written coding to open the connection and close the
> connection and to do some other processes also . I have declared
> GetRecordSet() as a datatable ,so it returns the values in the form of
> table.
>
> Public Function GetRecordSet(ByVal query As String, ByVal tab As String)
> As DataTable
> Dim connectionString As String = "User ID=sa;Initial
> Catalog=dbSMSApp;Data Source=IWSERVER"
>            Info=False;User ID=sa;Initial Catalog=dbSathyam;Data
> Source=strDbPath"
>
>            Dim Dt As DataTable = ExecuteQuery(connectionString, query,
> tab)
>            Return Dt
>        Catch ex As Exception
>            MsgBox(ex.Message)
>        End Try
>    End Function
>
>    Public Function ExecuteQuery(ByVal ConnectionString As String, ByVal
> query As String, ByVal tableName As String) As DataTable
>
>        Try
>            Dim myConnection As SqlConnection = New
> SqlConnection(ConnectionString)
>            Dim myAdapter As SqlDataAdapter = New SqlDataAdapter(query,
> myConnection)
>            Dim ds As DataSet = New DataSet
>            myAdapter.Fill(ds, tableName)
>            ds.Tables(0).TableName = tableName
>            Return ds.Tables(0)
>            Return ds.Tables(0)
>        Catch ex As System.Data.OleDb.OleDbException
>            'MsgBox(ex.Message)
>        End Try
>    End Function
>
>>Is this in an Access database?
>
> I'm asking because the dbCon.GetRecordSet( ) looks like DAO or Access.
>
> Robin S.
> ------------------------
> "meka" <mekalagunaseka***@gmail.com> wrote in message
> news:1173419978.833928.120220@64g2000cwx.googlegroups.com...
>> For i = 0 To dtSubOpt.Rows.Count - 1
>>
>>   intOptNo = dtSubOpt.Rows(i)(1)
>>
>> strSelectSubOpt = "select tblOptions.strOptName as
>> OptionName,tblOptions.intParent ,tblMOMT.strMOTag as
>> MessageSentFromServer,tblMOMT.strMTTag as MessageSentToServer from
>> tblOptions inner join tblMOMT on tblMOMT.intOptId=tblOptions.intOptId
>> where intParent=" & intOptNo & "   order by OptionName"
>>
>> dtOptName = dbCon.GetRecordSet(strSelectSubOpt, "tblOptions")
>>
>> Next
>>
>> In my above coding i need to keep all the records retrieved through
>> looping the query in the same datatable.
>> In my coding every time intOptNo has changed and retrieve the records
>> corresponding to that particular intOptNo . The loop executed every
>> times and get the records ,but the records overwrite the records
>> already in a datatable this is my problem .So to avoid this I need to
>> increment the index of the datatable row .But I don't know how to
>> change the row index depending upon the number of rows returned by
>> the query .Is there any way to find the row index of the datatable and
>> to increment that no to keep the next records orelse tell me some
>> other way to keep all the records retrieved through looping the
>> query in the same datatable or in the same DataSet.
>>
>> Kindly expecting the reply,
>> meka.
>>
>
>
>
>
> ___
> Newsgroups brought to you courtesy of www.dotnetjohn.com