Creating a DataSet and DataTable then adding a record. Help!

  • Thread starter Thread starter David Hearn
  • Start date Start date
D

David Hearn

I am using the code below to create a new dataset, add a new table to it
called "Inventory" and then add a new row to the table with some data in it.
I am using this to create a temporary dataset so that I can bind a datagrid
to it. All goes well until I try to update it. When it gets to the last line
and tries to update, I get the error Invalid object name 'Inventory'. Can
someone please tell me what I am doing wrong?

Thanks in advance!

Dim MyConnection As SqlConnection

Dim MySQLDataAdapter As SqlDataAdapter

Dim bAdd As Boolean

Dim aryParts As Array

Dim aryQuantities As Array

Dim i As Integer

Dim PrimaryLocationName, SecondaryLocationName As String

Dim PrimaryLocationCode, SecondaryLocationCode As String

aryParts = Parts.Split(",")

aryQuantities = Quantities.Split(",")

PrimaryLocationCode = Location1

SecondaryLocationCode = Location2

PrimaryLocationName = GetLocation(Location1).Trim

SecondaryLocationName = GetLocation(Location2).Trim

MyConnection = CartFunctions.GetConnection

MyConnection.Open()

'Create a new dataset

Dim MyDataSet As New DataSet

'Add a new datatable to the dataset to store our records in

Dim dtInventory As New DataTable("Inventory")

Dim dc As New DataColumn

dc = New DataColumn("PartNumber", System.Type.GetType("System.String"))

dtInventory.Columns.Add(dc)

dc = New DataColumn("OrderQuantity", System.Type.GetType("System.String"))

dtInventory.Columns.Add(dc)

dc = New DataColumn(PrimaryLocationName,
System.Type.GetType("System.String"))

dtInventory.Columns.Add(dc)

dc = New DataColumn(SecondaryLocationName,
System.Type.GetType("System.String"))

dtInventory.Columns.Add(dc)

dc = New DataColumn("AllLocations", System.Type.GetType("System.String"))

dtInventory.Columns.Add(dc)

MyDataSet.Tables.Add(dtInventory)

Dim MyDataRow As DataRow

MySQLDataAdapter = New SqlDataAdapter("SELECT * FROM Inventory",
MyConnection)

Dim MyDataRowsCommandBuilder As SqlCommandBuilder = New
SqlCommandBuilder(MySQLDataAdapter)

MySQLDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

'Now loop through and get the data

For i = 0 To UBound(aryParts)

'Get inventory for Primary Location

Dim strSQL As String = "SELECT * FROM location_inventory WHERE location_code
= '" & PrimaryLocationCode & "' AND part_num = '" & aryParts(i) & "'"

Dim cmdReader As SqlCommand

Dim dtrList As SqlDataReader

cmdReader = New SqlCommand(strSQL, MyConnection)

dtrList = cmdReader.ExecuteReader

Do While dtrList.Read

MyDataRow = MyDataSet.Tables("Inventory").NewRow

MyDataRow("PartNumber") = aryParts(i)

MyDataRow("OrderQuantity") = aryQuantities(i)

MyDataRow(PrimaryLocationName) = dtrList("Quantity")

Loop

dtrList.Close()

MyDataSet.Tables("Inventory").Rows.Add(MyDataRow)

MySQLDataAdapter.Update(MyDataSet, "Inventory")

Next
 
Dear David

You havent added the DataTable to the DataSet. You have just declared a new table

put the lin

MyDataSet.Tables.Add(dtInventory

after declaring the table

hope it helps

----- David Hearn wrote: ----

I am using the code below to create a new dataset, add a new table to i
called "Inventory" and then add a new row to the table with some data in it
I am using this to create a temporary dataset so that I can bind a datagri
to it. All goes well until I try to update it. When it gets to the last lin
and tries to update, I get the error Invalid object name 'Inventory'. Ca
someone please tell me what I am doing wrong

Thanks in advance

Dim MyConnection As SqlConnectio

Dim MySQLDataAdapter As SqlDataAdapte

Dim bAdd As Boolea

Dim aryParts As Arra

Dim aryQuantities As Arra

Dim i As Intege

Dim PrimaryLocationName, SecondaryLocationName As Strin

Dim PrimaryLocationCode, SecondaryLocationCode As Strin

aryParts = Parts.Split(","

aryQuantities = Quantities.Split(","

PrimaryLocationCode = Location

SecondaryLocationCode = Location

PrimaryLocationName = GetLocation(Location1).Tri

SecondaryLocationName = GetLocation(Location2).Tri

MyConnection = CartFunctions.GetConnectio

MyConnection.Open(

'Create a new datase

Dim MyDataSet As New DataSe

'Add a new datatable to the dataset to store our records i

Dim dtInventory As New DataTable("Inventory"

Dim dc As New DataColum

dc = New DataColumn("PartNumber", System.Type.GetType("System.String")

dtInventory.Columns.Add(dc

dc = New DataColumn("OrderQuantity", System.Type.GetType("System.String")

dtInventory.Columns.Add(dc

dc = New DataColumn(PrimaryLocationName
System.Type.GetType("System.String")

dtInventory.Columns.Add(dc

dc = New DataColumn(SecondaryLocationName
System.Type.GetType("System.String")

dtInventory.Columns.Add(dc

dc = New DataColumn("AllLocations", System.Type.GetType("System.String")

dtInventory.Columns.Add(dc

MyDataSet.Tables.Add(dtInventory

Dim MyDataRow As DataRo

MySQLDataAdapter = New SqlDataAdapter("SELECT * FROM Inventory"
MyConnection

Dim MyDataRowsCommandBuilder As SqlCommandBuilder = Ne
SqlCommandBuilder(MySQLDataAdapter

MySQLDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKe

'Now loop through and get the dat

For i = 0 To UBound(aryParts

'Get inventory for Primary Locatio

Dim strSQL As String = "SELECT * FROM location_inventory WHERE location_cod
= '" & PrimaryLocationCode & "' AND part_num = '" & aryParts(i) & "'

Dim cmdReader As SqlComman

Dim dtrList As SqlDataReade

cmdReader = New SqlCommand(strSQL, MyConnection

dtrList = cmdReader.ExecuteReade

Do While dtrList.Rea

MyDataRow = MyDataSet.Tables("Inventory").NewRo

MyDataRow("PartNumber") = aryParts(i

MyDataRow("OrderQuantity") = aryQuantities(i

MyDataRow(PrimaryLocationName) = dtrList("Quantity"

Loo

dtrList.Close(

MyDataSet.Tables("Inventory").Rows.Add(MyDataRow

MySQLDataAdapter.Update(MyDataSet, "Inventory"

Nex
 
Ranganh,

Thanks for the reply, but if you will look closer in my code, you will
see that I am adding the table just as you suggested.

David Hearn
 
Back
Top