Working with data tables

fredstov
fredstov used Ask the Experts™
on
I am creating a datatable.  I want to read using a select statement. I then want to be able to move data out of a column by using the column name into a field dimmed as string.  I have the table loaded with data.  I have included the code and what I am trying to do which doesn't work.
Dim PRates As New System.Data.DataTable("HoldRates")

PRates.Columns.Add("Prodnb", Type.GetType("System.String"))
PRates.Columns.Add("mem", Type.GetType("System.Double"))
PRates.Columns.Add("sp", Type.GetType("System.Double"))
PRates.Columns.Add("child", Type.GetType("System.Double"))
PRates.Columns.Add("fam", Type.GetType("System.Double"))
PRates.Columns.Add("pfactor", Type.GetType("System.Double"))

Do While rdr.Read() = True
PRates.Rows.Add()
PRates.Rows(PRates.Rows.Count - 1)("ProdNb") = rdr("prodnb")
PRates.Rows(PRates.Rows.Count - 1)("mem") = rdr("Employee")
PRates.Rows(PRates.Rows.Count - 1)("sp") = rdr("Spouse")
PRates.Rows(PRates.Rows.Count - 1)("child") = rdr("Children")
PRates.Rows(PRates.Rows.Count - 1)("fam") = rdr("Family")
PRates.Rows(PRates.Rows.Count - 1)("PFactor") = rdr("ratefactor")
Loop
Session("prodtable") = PRates
''** the following reads the table
Dim ratetable As System.Data.DataTable = Session("prodtable")
Dim dro() As System.Data.DataRow = ratetable.Select("prodnb = '100'") 'this gives syntax error
Dim tmp As String
tmp = dro("mem").ToString()

dro("mem") = "123"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
what is the error you received?

Author

Commented:
the command
tmp = dro("mem").tostring()  says it cant convert integer to string.

All I want to know is the syntax to move  data in and out of the columns by column name on the row that is found in the select statement. line number 24 and 26 don't work.

Hope that helps
Commented:
datatable.select method returns you an array of datarows. so you have to change your code something as follows:

Dim dro() As System.Data.DataRow = ratetable.Select("prodnb = '100'") 'this gives syntax error
Dim tmp As String

if dro.length()<>0 then

       tmp = dro(0).item("mem").ToString()
 
       dro(0).item("mem") = "123"

else

    ' no rows exists with prodnb = '100'

end if

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial