Datatable

M

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.
 
R

RobinS

Is this in an Access database?

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

Robin S.
 
M

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?
Click to expand...

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

Robin S.
------------------------
meka said:
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.
Click to expand...




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

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.
--------------------------------------
 
Top