Adding rows to table in Dataset
VB 2005 express beta 2
I've set up a dummy project to learn how to insert rows into a database, and i'm having some trouble. You'll see the naming convention right away....
Here's the code that looks like it should work... the warning that pops up is that the variable NewTestRow is used before it has been assigned a value. i want it to be a new row in the table, but can't figure out how to set it as a new row.
| |
PrivateSub TextBox1_Leave(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles TextBox1.Leave
Dim NewTestRowAs TestDataSetDataSet.TestTableRow
NewTestRow.TestColumn ="TestData"
EndSub
|
i assume after this, i can run a tableadapter.update to make the changes in my database... or so is my logic.Am i doing something wrong? is there a better way? i really just need to insert a row into a database with about a dozen columns, and with certain values. no need to verify anything or read anything. just insert rows. is there an easier way to do this? there's a tableadapter.insert command, but the help document is empty at the moment...
I guess you just created a new row, but did not add it to you table. So add it to your table and then execute the update or insert statement.
 Milothicus wrote: |
| VB 2005 express beta 2 I've set up a dummy project to learn how to insert rows into a database, and i'm having some trouble. You'll see the naming convention right away.... Here's the code that looks like it should work... the warning that pops up is that the variable NewTestRow is used before it has been assigned a value. i want it to be a new row in the table, but can't figure out how to set it as a new row.
|
|
Private Sub TextBox1_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.Leave
Dim NewTestRow As TestDataSetDataSet.TestTableRow
NewTestRow.TestColumn = "TestData"
End Sub
|
i assume after this, i can run a tableadapter.update to make the changes in my database... or so is my logic.Am i doing something wrong? is there a better way? i really just need to insert a row into a database with about a dozen columns, and with certain values. no need to verify anything or read anything. just insert rows. is there an easier way to do this? there's a tableadapter.insert command, but the help document is empty at the moment... |
|
There is soo much stuff wrong with that.
First of all what is TestDataSet.TestTableRow? is it a Data Type ?
Secondly whenever you define a variable to be of a new object you have to instantiate the object BEFORE USING IT. e.g.
Dim NewRow as new DataRow
MyDataSet.Tables(0).Rows.Add(NewRow)
etc.
well, i started with the assumption i needed to initialize a variable to store the row info... and it should be a sub-variable (can't think of the word) of the dataset, so i started typing "dim NewTestRow as " and one of the options in the namespace was my dataset, which is 'Testdatasetdataset', so i chose that, then below that, there was a 'testtablerow', and since 'testtable' is my table, i figured the right type would be 'testtablerow'. as far as i can tell, the program is happy with that. no warnings, errors, nothing.
i think what i need to do is tell that variable which row of the table to point to, so it knows which row to associate my new data with. so... i need it to point to a new row, but don't know how to do that.
initializing as 'DataRow' didn't seem to work.... not sure why.
ms-help://MS.VSCC.2003/MS.MSDNQTR.2005JAN.1033/cpref/html/frlrfSystemDataDataRowClassTopic.htm
| |
Private Sub CreateNewDataRow()
' Use the MakeTable function below to create a new table.
Dim myTable As DataTable
myTable = MakeNamesTable()
' Once a table has been created, use the NewRow to create a DataRow.
Dim myRow As DataRow
myRow = myTable.NewRow()
' Then add the new row to the collection.
myRow("fName") = "John"
myRow("lName") = "Smith"
myTable.Rows.Add(myRow) Dim dc As DataColumn
For Each dc in myTable.Columns
Console.WriteLine(dc.ColumnName)
Next
DataGrid1.DataSource=myTable
End Sub
Private Function MakeNamesTable() As DataTable
' Create a new DataTable titled 'Names.'
Dim namesTable As DataTable = new DataTable("Names")
' Add three column objects to the table.
Dim idColumn As DataColumn = new DataColumn()
idColumn.DataType = System.Type.GetType("System.Int32")
idColumn.ColumnName = "id"
idColumn.AutoIncrement = True
namesTable.Columns.Add(idColumn)
Dim fNameColumn As DataColumn = New DataColumn()
fNameColumn.DataType = System.Type.GetType("System.String")
fNameColumn.ColumnName = "Fname"
fNameColumn.DefaultValue = "Fname"
namesTable.Columns.Add(fNameColumn)
Dim lNameColumn As DataColumn = new DataColumn()
lNameColumn.DataType = System.Type.GetType("System.String")
lNameColumn.ColumnName = "LName"
namesTable.Columns.Add(lNameColumn)
' Create an array for DataColumn objects.
Dim keys(0) As DataColumn
keys(0) = idColumn
namesTable.PrimaryKey = keys
' Return the new DataTable.
MakeNamesTable = namesTable
End Function
|
That all looks more complex than i need. I found a 'HowTo', but don't know how to get it to work.
more background info:
my connection filename is TestDataSet.mdf
Dataset filename is TestDataSetDataSet.xsd
table within the dataset is TestTable
table has one column: TestColumn
Here's where i started:
http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_raddata/html/78ebbb43-c402-49cf-81da-0715289487bf.asp
and here's their sample VB Code:
| |
Dim newCustomersRow as DataSetClass.Customers.CustomersRow
newCustomersRow = DataSetInstance.Customers.NewCustomersRow()
newCustomersRow.CustomerID = "ALFKI"
newCustomersRow.CompanyName = "Alfreds Futterkiste"
DataSetInstance.Customers.Rows.Add(newCustomersRow)
|
My DataSet IS a typed dataset, so this should work, but...
What do i need to replace "DataSetClass" with?
What do i need to replace "DataSetInstance" with?
Why does it look so much more complicated in the post above?
| Milothicus wrote: |
| VB 2005 express beta 2 I've set up a dummy project to learn how to insert rows into a database, and i'm having some trouble. You'll see the naming convention right away.... Here's the code that looks like it should work... the warning that pops up is that the variable NewTestRow is used before it has been assigned a value. i want it to be a new row in the table, but can't figure out how to set it as a new row.
|
|
Private Sub TextBox1_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.Leave
Dim NewTestRow As TestDataSetDataSet.TestTableRow
NewTestRow.TestColumn = "TestData"
End Sub
|
i assume after this, i can run a tableadapter.update to make the changes in my database... or so is my logic.Am i doing something wrong? is there a better way? i really just need to insert a row into a database with about a dozen columns, and with certain values. no need to verify anything or read anything. just insert rows. is there an easier way to do this? there's a tableadapter.insert command, but the help document is empty at the moment... |
|
then you probably need to have
Dim NewTestRow As NEW TestDataSetDataSet.TestTableRow sorry for being to complicated try looking at this:
Dim myRow As DataRow
myRow = myTable.NewRow()
' Then add the new row to the collection.
myRow("fName") = "John"
myRow("lName") = "Smith"
myTable.Rows.Add(myRow)
Maybe a simpler example of the terms would make more sense. Consider the following code:
Integer is the class and i is the instance.
In the MSDN sample code, they're talking about a line of code that would have appeared earlier in the sample app:
| |
Dim DataSetInstance As New DataSetClass()
|
DataSetClass is the class for your strongly typed DataSet. DataSetInstance is the instance of that class.
The code example you've referenced is correct. You create the row by calling the NewMyTableRow method on your DataTable, apply values to various properties on the new row and then add it to your DataTable by calling ...Rows.Add(newRow).
That information is now cached as a pending change to the database. When you want to submit the pending change, you would call:
| |
MyTableAdapter.Update(MyDataTable)
|
I hope this information proves helpful.
David Sceppa
ADO.NET Program Manager
Microsoft
I just re-read the thread and wanted to add a little more information.
There are a couple shortcuts in the strongly typed DataSet that may help. First off, there's a strongly-typed NewRow method that will accept new values and add the new row to the DataTable in one line of code.
Here's the "long way" you referenced earlier:
| |
Dim newCustomersRow as DataSetClass.Customers.CustomersRow
newCustomersRow = DataSetInstance.Customers.NewCustomersRow()
newCustomersRow.CustomerID = "ALFKI"
newCustomersRow.CompanyName = "Alfreds Futterkiste"
DataSetInstance.Customers.Rows.Add(newCustomersRow)
|
Here's the "short way" approach:
| |
DataSetInstance.Customers.NewCustomersRow("ALFKI", "Alfreds Futterkiste")
|
As I noted before, adding the row to the DataTable is only the first step. You'd still need to call TableAdapter.Update to submit the change.
But, you had also mentioned that you really didn't care about the DataRow. You simply wanted to add the new row to the database. There's new functionality in the TableAdapter to simplify this process. The TableAdapter has Insert, Update, and Delete methods that contain parameters that match the structure of the DataTable. The TableAdapter takes those values and executes a query to insert, update, or delete the corresponding row in the database. With the Northwind Customers example I've been using in this post, you could use the TableAdapter to submit a new Customer in one line of code:
| |
CustomersTableAdapter.Insert("ALFKI", "Alfreds Futterkiste")
|
I hope this information proves helpful.
David Sceppa
ADO.NET Program Manager
Microsoft
| David Sceppa wrote: |
With the Northwind Customers example I've been using in this post, you could use the TableAdapter to submit a new Customer in one line of code:
| |
CustomersTableAdapter.Insert("ALFKI", "Alfreds Futterkiste")
|
|
|
I tried this first, but it didn't seem to actually make any changes in the database.
Actually, now that i type that, i think that was before i read another post about the connection referencing one database, while the solution explorer references another...
I'll try that again...
thanks for the reminder!
