Select data from one dataset into another vb.net

[HBes]

I'm hoping someone will have a suggestion.
Basically what i am doing is reading a csv file into a table, but I only want to read in certain columns, so the idea was read the csv file into a dataset and then select the relevent columns from the dataset into another......
But I keep getting an error saying I've a syntax error in my script. I'm not sure if trying to run a fill on a new dataset referencing another one is possible:

It goes something like this......
'txtFileName.text is coming from for where user has given the filename

Dim selectcmdstr As String = "SELECT * FROM " & txtFileName.Text
Dim cd As New Odbc.OdbcCommand(selectcmdstr, cnConn) 'text.csv", cnConn) ' & txtFileName.Text, cnConn)
cnConn.Open()
dtable1Adapt.SelectCommand = cd
dtable1Adapt.Fill(dataset1, "Extract")
'this all works to here

' I generate a sql command of columns needed based on data stored in database
Dim selectFormStr ="Select [Average Cost [N]]] * [End Qty] as MarketValAvg, [End Qty] * [End Price [N]]] as MarketValue from dataset1.tables(1).ToString()"

Dim cd3 As New Odbc.OdbcCommand(selectFormStr, cnConn)
dtable3Adapt.SelectCommand = cd3
dtable3Adapt.Fill(dataset2, "Rules") 'FAILS HERE

cnConn.Close()




Any suggestions on how to approach/resolve much appreciated!

[Shashi Sadasivan]

If you dont want a particular row, you can use the remove method to remove it from the dataset.

[HBes]

If you dont want a particular row, you can use the remove method to remove it from the dataset.

The problem is I only want certain columns, but I need to keep all rows for those columns.
Just realising removing columns won't really work, since part of what I wanted to do was generate a new column from 2 columns in a dataset. Eg selecting the results of Price * Quantity into a new column Cost whilst also having the columns Price and Quantity in the new dataset.
Just remove all the columns except Price and Quantity won't do that....

[KnightHack]

'Reading data from CSV and makeing as a table format sending into dataset
'inserting data into ms-access from that dataset
Public Function export_data_from_csv_to_Access()

Try
'Csv folder path
Dim folder As String = Module1.GetMyAppSetting.Sbuzz_AppSettings(Module1. XMLLocation, "ExtractFolderpath")
'Csv file path
Dim filename As String = Module1.GetMyAppSetting.Sbuzz_AppSettings(Module1. XMLLocation, "extractFilename")
'sending into file system
Dim file As System.IO.FileInfo = New System.IO.FileInfo(folder + filename)
If file.Exists Then
'Split the csv file with (",") and making data as in table format
Dim myTable As DataTable = New DataTable("MyTable")
Dim i As Integer
Dim myRow As DataRow
Dim fieldValues As String()
Dim f As IO.File
Dim myReader As StreamReader
myReader = f.OpenText(folder + filename)
Dim strLine As String = myReader.ReadLine()
myReader.ReadLine().Remove(0, 1)
fieldValues = myReader.ReadLine().Split(New Char() {","})
'Add columns to table
For i = 0 To fieldValues.Length() - 1
myTable.Columns.Add(New DataColumn("Field" & i))
Next
myRow = myTable.NewRow
'Adding Rows to table
For i = 0 To 5
Dim k As String = fieldValues(i).ToString
Dim kk As Integer = fieldValues.Length()
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
'All rows and cloumns making as table format
While myReader.Peek() <> -1
fieldValues = myReader.ReadLine().Split(New Char() {","})
myRow = myTable.NewRow
For i = 0 To 5
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
End While
'Table sending into dataset
Dim ds As New DataSet
ds.Tables.Add(myTable)
'Deleting data from Geoip.Mdb file
Delete_Data()
For i = 0 To ds.Tables("myTable").Rows.Count - 1 '
'Insert data into Geoip.Mdb from Dataset(Made from csv file)
mdbcon = Module1.OpenMdbconnection()
'From that csv reading time we can get some charectes like {',",/} to remove those
strToRemove1 = RemoveChars(ds.Tables("myTable").Rows(i).ItemArray .GetValue(0))
strToRemove2 = RemoveChars(ds.Tables("myTable").Rows(i).ItemArray .GetValue(1))
strToRemove3 = RemoveChars(ds.Tables("myTable").Rows(i).ItemArray .GetValue(2))
strToRemove4 = RemoveChars(ds.Tables("myTable").Rows(i).ItemArray .GetValue(3))
strToRemove5 = RemoveChars(ds.Tables("myTable").Rows(i).ItemArray .GetValue(4))
strToRemove6 = RemoveChars(ds.Tables("myTable").Rows(i).ItemArray .GetValue(5))
Dim MdbCmdTxt_InsertData As String = "Insert into Geoip(beginIp,endip,beginIpNum,endIpNum,countryCod e,countryName) values('" & strToRemove1 & "','" & strToRemove2 & "','" & strToRemove3 & "','" & strToRemove4 & "','" & strToRemove5 & "','" & strToRemove6 & "')"
Dim MdbcmdIns As New OleDbCommand(MdbCmdTxt_InsertData, Mdbcon)
MdbcmdIns.ExecuteNonQuery()
MdbcmdIns.Connection.Close()
Mdbcon.Close()
Next i
End If
Module1.to_Write_Status_file("Completed from Csv to Ms-Access")
Catch ex As Exception
Module1.Error_Status = "Error From Export"
Module1.Error_type = ex.Message
End Try
End Function