Rod;
Thanks for the link. This is similar to code to load the spreadsheet
Code:
Private Sub sReadSpreadSheet(ByVal TxtFilePath)
TxtFilePath = Strings.LCase(TxtFilePath)
If (Strings.Right(TxtFilePath, 3) = "xls" Or Strings.Right(TxtFilePath, 4) = "xlsx") Then
Dim conn As OleDbConnection
Dim cmd As OleDbCommand
Dim da As OleDbDataAdapter
Dim query As String
Dim connString As String = ""
''Connection String to Excel Workbook
If Strings.Right(TxtFilePath, 3) = "xls" Then
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TxtFilePath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
Else
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & TxtFilePath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
End If
query = "SELECT [SalesOrderNumber],[ItemNumber],[PartNumber],[StatusCode],[Transdate],[DateClosedyymmdd] FROM [StationData$] ORDER BY [SalesOrderNumber], [PartNumber]"
'Create the connection object
conn = New OleDbConnection(connString)
'Open connection
cmd = New OleDbCommand(query, conn)
da = New OleDbDataAdapter(cmd)
AeroPartsDataSet = New DataSet()
Dim SalesOrderTable As DataTable = AeroPartsDataSet.Tables.Add("SalesOrderTable")
'Dim ordersTable As DataTable = customerOrders.Tables.Add("Orders")
If conn.State = ConnectionState.Closed Then
Try
conn.Open()
Try
da.Fill(SalesOrderTable)
da.Dispose() 'close data adapter
conn.Close() 'close connect to Spreadsheet
conn.Dispose() 'do cleanup of connection enviroment
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)
End Try
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)
End Try
End If
'Create the command object
query = "SELECT [Time],[Position Code],[DESCRIPTION] FROM [StatusCodes$]"
'Create the connection object
conn = New OleDbConnection(connString)
'Open connection
cmd = New OleDbCommand(query, conn)
da = New OleDbDataAdapter(cmd)
Dim StationTimeTable As DataTable = AeroPartsDataSet.Tables.Add("StationTimeTable")
If conn.State = ConnectionState.Closed Then
Try
conn.Open()
Try
da.Fill(StationTimeTable)
da.Dispose() 'close data adapter
conn.Close() 'close connect to Spreadsheet
conn.Dispose() 'do cleanup of connection enviroment
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)
End Try
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)
End Try
End If
Dim keys(1) As DataColumn
keys(0) = StationTimeTable.Columns("StatusCode")
'' Set the PrimaryKeys property to the array.
StationTimeTable.PrimaryKey = keys
'Set Relation Between SalesOrderTable and StationTimeTable
Dim obj_ParentClmn, obj_ChildClmn As DataColumn
'Get the reference of columns to create a relation between.
obj_ParentClmn = AeroPartsDataSet.Tables("SalesOrderTable").Columns("StatusCode")
obj_ChildClmn = AeroPartsDataSet.Tables("StationTimeTable").Columns("Position Code")
'Creates a relation object, Parameters required are
'New Relation Name, Object of Parent & Child column respectively.
Dim AeroPartsRelation = New DataRelation("OrdetsToTimeRelation", obj_ChildClmn, obj_ParentClmn, False)
'Adding Relation to the dataset that holds the tables.
AeroPartsDataSet.Relations.Add(AeroPartsRelation)
Else
MsgBox("Please select an excel file first", , "No File Found")
End If
End Sub
In the link they use a connection string. I have been unable to find a sample of a connection string that connects to datatables in memory.
Even though I still want to know the answer to the lead question for the thread. In an effort to complete the task I have tried a work around where I loop through "SalesOrderTable" checking one record at a time to seek if the [Time At Station] in the row exceeds the [StationTimeTable.Columns("StatusCode")] standard.
Code:
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim TblOrderStatus As DataTable = New DataTable("TblOrderSatus")
TblOrderStatus = AeroPartsDataSet.Tables("SalesOrderTable").Clone
Dim RowCount As Integer = AeroPartsDataSet.Tables("SalesOrderTable").Rows.Count
Dim nCurrentRec As Integer = 0 'first record in datatable
Dim s As String
Dim f As Decimal
Dim foundRow As DataRow
For Each row As DataRow In AeroPartsDataSet.Tables("SalesOrderTable").Rows
s = row.Item("StatusCode")
foundRow = AeroPartsDataSet.Tables("StationTimeTable").Rows.Find(s)
f = foundRow("Time")
If row.Item("Days At Station") > f Then
'merge data into TblOrderStatus
End If
Next row
grvExcelData.DataSource = TblOrderStatus 'AeroPartsDataSet.Tables("SalesOrderTable") 'bind Datasoure to Datagridview
grvExcelData.Update()
End Sub
End Class
However now I am getting a "No primary key" error in the line;
foundRow = AeroPartsDataSet.Tables("StationTimeTable").Rows.F ind(s)
Even though I though I set up a key in the first block of code.
Code:
Dim keys(1) As DataColumn
keys(0) = StationTimeTable.Columns("StatusCode")
'' Set the PrimaryKeys property to the array.
StationTimeTable.PrimaryKey = keys
I get no error but appparently also no primary key.
Do you see the primary key problem?
Would adding an Order By to the Select string for the StationTimeTable creat the need Primary Key
Well I'm going to rem the code that should create the key and try Order By next. Let you know how it goes.
Any help on either question is of course appreciated.
DataRelations within Dataviews
Work around Success
