Datatable not added to Dataset

Hi Folks,

I have a situation where I am trying to add multiple data tables to a dataset, but the last table I try to add is not being added or is alway blank or is saying table 1 already exists.

Any ideas would be appriciated. :)

My code:

Private Sub btnEditOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditOrder.Click

' make sure there are selected items
If lvCurrentOrders.SelectedIndices.Count <> 0 Then
' step through each selected item
For Each MyRowCounter In lvCurrentOrders.SelectedIndices

OrderID = CInt(Mid(lvCurrentOrders.Items.Item(MyRowCounter).ToString, 16).Replace("}", ""))
pnlAddOrder.Visible = False
pnlEditOrder.Visible = True
pnlEditOrder.Location = (New System.Drawing.Point(0, 196))

'Reset the Dataset and tables
MyDataset.Tables.Clear()
StockTable.Dispose()
OrderItemsTable.Dispose()
OrderTable.Dispose()
CustomerTable.Dispose()

MyDataset = New DataSet

Try
MyConnection = New SqlConnection(MyConStr)
'Fill Order and Order Items Stock table with Orders and order Items Stock and adds to datasets
MySQLStr = "SELECT * FROM tbl_Stock Where Stock_State <> 'Stock Discontinued'"
MyAdapter = New SqlDataAdapter(MySQLStr, MyConnection)
MyDataset.Tables.Add(StockTable)
MyAdapter.Fill(StockTable)
MySQLStr = "SELECT * FROM tbl_Orders Where Order_ID = '" + OrderID.ToString + "'"
MyAdapter = New SqlDataAdapter(MySQLStr, MyConnection)
MyDataset.Tables.Add(OrderTable)
MyAdapter.Fill(OrderTable)
MySQLStr = "SELECT * FROM tbl_Order_Item Where Order_ID = '" + OrderID.ToString + "'"
MyAdapter = New SqlDataAdapter(MySQLStr, MyConnection)
MyDataset.Tables.Add(OrderItemsTable)
MyAdapter.Fill(OrderItemsTable)
CustomerID = MyDataset.Tables(1).Rows(0)("Customer_ID").ToString
MySQLStr = "SELECT First_Name,Surname FROM tbl_Customers Where Customer_ID = " + CustomerID + ""
MyAdapter = New SqlDataAdapter(MySQLStr, MyConnection)
MyDataset.Tables.Add(CustomerTable)
MyAdapter.Fill(CustomerTable)
dgv1.DataSource = MyDataset.Tables(3) 'to remove
MyConnection.Close()
Catch ex As Exception
txtsqlstring.Text = ex.ToString
MyConnection.Close()
MsgBox("Failure")
End Try

'txtEditOrderCustomerName.Text = (MyDataset.Tables("CustomerTable").Rows(0)("First_Name").ToString).Trim + " " + (MyDataset.Tables("CustomerTable").Rows(0)("Surname").ToString).Trim

'Populate the Combobox with the Current Stock
cbxEditOrderSelectStock.Text = "Select a Stock Item"
For Each MyDataRow In StockTable.Rows()
cbxEditOrderSelectStock.Items.Add(MyDataRow("Stock_Code") & " " & MyDataRow("Stock_Description"))
Next

TotalOrderCost = MyDataset.Tables(1).Rows(0)("Order_Amount").ToString
txtEditOrderTotalOrderCost.Text = TotalOrderCost

lvEditOrderItems.View = View.Details

lvEditOrderItems.Columns.Add("Stock Code", -2, HorizontalAlignment.Center).Width = 135
lvEditOrderItems.Columns.Add("Stock Description", -2, HorizontalAlignment.Left).Width = 258
lvEditOrderItems.Columns.Add("Stock Qty", -2, HorizontalAlignment.Left).Width = 135
lvEditOrderItems.Columns.Add("Stock Cost", -2, HorizontalAlignment.Left).Width = 135
lvEditOrderItems.Columns.Add("Stock Total Cost", -2, HorizontalAlignment.Left).Width = 135

MyRowCounter2 = 0
While (OrderItemsTable.Rows.Count > MyRowCounter2)

StockCode = MyDataset.Tables(2).Rows(MyRowCounter2)("Stock_Code")
lvEditOrderItems.Items.Add(StockCode)
StockDescription = MyDataset.Tables(2).Rows(MyRowCounter2)("Description")
lvEditOrderItems.Items(lvEditOrderItems.Items.Count - 1).SubItems.Add(StockDescription)
Quantity = MyDataset.Tables(2).Rows(MyRowCounter2)("Quantity")
lvEditOrderItems.Items(lvEditOrderItems.Items.Count - 1).SubItems.Add(Quantity)
Cost = MyDataset.Tables(2).Rows(MyRowCounter2)("Cost")
lvEditOrderItems.Items(lvEditOrderItems.Items.Count - 1).SubItems.Add(Cost)
TotalCost = MyDataset.Tables(2).Rows(MyRowCounter2)("Total_Cost")
lvEditOrderItems.Items(lvEditOrderItems.Items.Count - 1).SubItems.Add(TotalCost)
MyRowCounter2 = MyRowCounter2 + 1
End While

Next
Else
MsgBox("You have not selected and item to Edit", MsgBoxStyle.OkOnly)
End If

End Sub

Regards
Andy
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories