Inserting variables into corresponding column of a datatable vb.net

0
votes

I am parsing through each line of a fixed length text file. I am retrieving 13 values from this text file and inserting them into variables of multiple datatypes. Eventually I will need to add these values to a row in a datatable, but the problem is I don't know how to do this. I tried looping through the columns and tried inserting the right variable in the right column. How do I add each of the variables below to the corresponding column in a datatable all in the same row?

Here are my variables:

Dim vendorName As String 'a
Dim vendorNum As Integer 'b
Dim CheckNum As String 'c - with checkNum and batch no combined
Dim depositDate As String 'd
Const crAccount As Integer = 102006 'e
Dim totCount As Integer ' f
Const blank As String = "" 'g
Const num As Integer = 1 'h
Dim qtr As Integer 'i
Dim custName As String 'j
Dim acctNum As Integer 'k
Dim headerAmount(headerCounter) As Double ' '// Not in the list
Dim detailAmount As Double 'l

Dim state As String 'm
Dim batchNo As String 'n -set this to string to retrieve the 00 or 01 or however many batches there is.
Dim checkNo As String 'o

Here are my columns:

    dt.Columns.Add("Vendor #")
    dt.Columns.Add("Vendor Name")
    dt.Columns.Add("Check #")
    dt.Columns.Add("Date")
    dt.Columns.Add("102006")
    dt.Columns.Add("#")
    dt.Columns.Add("Blank")
    dt.Columns.Add("1")
    dt.Columns.Add("12QTR")
    dt.Columns.Add("Client Name")
    dt.Columns.Add("Acct No")
    dt.Columns.Add("Amt")
    dt.Columns.Add("State")
    dt.Columns.Add("Batch No")
    dt.Columns.Add("Check Number")

Here is the loop I tried, but it is adding each variable to it's own row:

 For Each col As DataColumn In dt.Columns
                If col.ColumnName = "Vendor #" Then
                    dt.Rows.Add(vendorNum)
                ElseIf col.ColumnName = "Vendor Name" Then
                    dt.Rows.Add(vendorName)
                ElseIf col.ColumnName = "Check #" Then
                    dt.Rows.Add(checkNo)
                ElseIf col.ColumnName = "Date" Then
                    dt.Rows.Add(depositDate)
                ElseIf col.ColumnName = "102006" Then
                    dt.Rows.Add(crAccount)
                ElseIf col.ColumnName = "#" Then
                    dt.Rows.Add(totCount)
                ElseIf col.ColumnName = "Blank" Then
                    dt.Rows.Add(blank)
                ElseIf col.ColumnName = "1" Then
                    dt.Rows.Add(num)
                ElseIf col.ColumnName = "12QTR" Then
                    dt.Rows.Add(qtr)
                ElseIf col.ColumnName = "Client Name" Then
                    dt.Rows.Add(custName)
                ElseIf col.ColumnName = "Acct No" Then
                    dt.Rows.Add(acctNum)
                ElseIf col.ColumnName = "Amt" Then
                    dt.Rows.Add(detailAmount)
                ElseIf col.ColumnName = "State" Then
                    dt.Rows.Add(state)
                ElseIf col.ColumnName = "Batch No" Then
                    dt.Rows.Add(batchNo)
                ElseIf col.ColumnName = "Check Number" Then
                    dt.Rows.Add(CheckNum)
                End If
                'dt.Rows.Add(vendorNum & vendorName & checkNo & depositDate & 
                'crAccount & totCount & blank & num & qtr & custName & acctNum 
                '& detailAmount & state & batchNo & CheckNum)
                '// I also tried concatenating each of the parameters together
                ' and when I do this it is only trying to insert in the Vendor # column.
            Next

This doesn't seem like it would be too difficult, but I can't find any examples of this. Any help would be appreciated. Thanks

2
vb.netdatatable

2 Answers

1
votes

Since you want them to be all in one row, you need to call the .Add()-method only once. .Add()-method accepts an array as input. The elements in the array must be in the same order as the correspondending Columnheaders.

PS: I've never heard of DataTable before. I mostly use DataSet or BindingList(of T) and bind them to a DataGridView (if GUI necessary).

You can i.e. create a class with propertys vendorName, vendorNum, ... and add instances of that class to a BindingList. Then setting the dataSource Property of the DataGridView is all what's left to do.

0
votes

This allows me to insert that value on that particular column. Just needed a datarow...

So if you create your datatable in vb.net with a static list of column names, you are able to create a datarow as a new [insert datatable name here].row, associate the column you are looking to add a value too, and set it equal to the variable you need.

Below is the code for the example in my question:

Dim dr As DataRow = dt.NewRow()
dr("Vendor #") = vendorNum
dr("Vendor Name") = vendorName
dr("Check #") = CheckNum
dr("Date") = depositDate
dr("102006") = crAccount
dr("#") = totCount
dr("Blank") = blank
dr("1") = num
dr("12QTR") = qtr
dr("Client Name") = custName
dr("Acct No") = acctNum
dr("Amt") = detailAmount
dr("State") = state
dr("Batch No") = batchNo
dr("Check Number") = checkNo
dt.Rows.Add(dr)