DataTableAdapters add multiple rows

[jpskiller]

Hi,

Not sure if this is correct forum for this

I am very new to vb net and to database programming. I have done some VB6.

I have been going round in circles for last 2 days, I have read MSN and googled all over place about the issue but to be honest it is not making much sense.

What I am trying to do should be easy but I cant get any code to work.

my thinking is build array then write this to database.

I hav a form which ask several questions, this information is submitted to the server then repeats until logout

I want to build up a table with rows after a user clicks submit button then say after 20 new rows added or a time period passes then this data is to be added to the server sqldatabase using the dataadapter update method, reason is to reduces hits on server

I am using vb net 2010

i have a dataset named dataset1.xsd

dataadpater name = main_Testadapter
datatable = main_Test
columns = ID (auto unique), FirstName, Surname, Ref etc


I am currently adding 1 row at a time, which works but there are many users and this could cause a hit on the server in future and I know it can be done but I cant get any where close to making it work, I know meant to use rows but Im stuck

this is working for adding row when submit
Me.Tbl_Main_TestAdapter.AddNew(FirstName, Surname, Ref, etc)


any help appreciated.

 

[jpskiller]

I looked at this
http://stackoverflow.com/questions/4...s-to-a-dataset

I have tried this
Dim Row As DataRow = DataSet1.Tables("Main_Test").NewRow()

For i = 0 To 9
Row("Department") = "Depart " & i.ToString
Row("VerintID") = "123456" & i.ToString
DataSet1.Tables("Main_Test").Rows.Add(Row)
Next
Main_TestAdapter.Update(DataSet1.Main_Test)

but I get an error : this row already belongs to this table.

 

[jmcilhinney]

Firstly, if you have a typed DataSet then use the typed DataSet. Don't use untyped DataSet methods, etc. This:

Dim Row As DataRow = DataSet1.Tables("Main_Test").NewRow()

is treating your DataSet as untyped. Use the properties and methods that were generated for your data. That should be something like this:

Dim row As Main_TestDataRow = DataSet1.Main_Test.NewMain_TestRow()

The actual names might be slightly different but hopefully you get the idea. The whole point of generating a typed DataSet is that it know about your data, so make use of that knowledge. Populating the row and adding the row to the table then looks something like this:

row.Department = "Depart " & i.ToString
row.VerintID = "123456" & i.ToString
DataSet1.Main_Test.Add(row)

As for your issue, the error message should have clued you in. You can't add the same row to a table twice. If you want to add ten rows to a table then you have to create ten rows and add each one to the table, not create one row and then try to add it to the table ten times. The solution to your problem is to move the first line, which declares the row variable and creates the row object, into the loop. That way, you're creating a new row, populating it and adding it to the table on each iteration.

 

[jpskiller]

Thanks that's helped a lot

I have done this now which is working and follows the type dataset method

'typed dataset method
For i = 0 To 9
Dim Row As DataSet1.Main_TestRow
Row = DataSet1.Main_Test.NewMain_TestRow

Row.Department = "Depart " & i.ToString
Row.VerintID = "123456" & i.ToString
DataSet1.Main_Test.Rows.Add(Row)
Next
Main_TestAdapter.Update(DataSet1.Main_Test)


and this will do same untyped method

' treat as untyped dataset method

For i = 0 To 9
Dim Row As DataRow = DataSet1.Tables("Main_Test").NewRow()

Row("Department") = "Depart " & i.ToString
Row("VerintID") = "123456" & i.ToString
DataSet1.Tables("Main_Test").Rows.Add(Row)
Next
Main_TestAdapter.Update(DataSet1.Main_Test)


Please correct me if any of this is wrong

 

[jmcilhinney]

Firstly, please format your code snippets when posting for readability. I did it in my post but you didn't take a cue from that.

        'typed dataset method
        For i = 0 To 9
            Dim Row As DataSet1.Main_TestRow
            Row = DataSet1.Main_Test.NewMain_TestRow

            Row.Department = "Depart " & i.ToString
            Row.VerintID = "123456" & i.ToString
            DataSet1.Main_Test.Rows.Add(Row)
        Next
        Main_TestAdapter.Update(DataSet1.Main_Test)

 ' treat as untyped dataset method

        For i = 0 To 9
            Dim Row As DataRow = DataSet1.Tables("Main_Test").NewRow()

            Row("Department") = "Depart " & i.ToString
            Row("VerintID") = "123456" & i.ToString
            DataSet1.Tables("Main_Test").Rows.Add(Row)
        Next
        Main_TestAdapter.Update(DataSet1.Main_Test)

As for the code itself, this line is still not right for a typed DataSet:

DataSet1.Main_Test.Rows.Add(Row)

You don't use the Rows collection of a typed DataTable. The DataTable itself has a method for adding a typed DataRow, just as it has a method for creating one. Also, this line is not right for an untyped DataSet:

Main_TestAdapter.Update(DataSet1.Main_Test)

If your DataSet is untyped then it won't have a property to refer to a DataTable. Your own code shows you how to refer to an untyped DataTable:

Dim Row As DataRow = DataSet1.Tables("Main_Test").NewRow()

 

[jpskiller]

Thank you for pointing out the mistakes, as you can see I don't have a full grasp of this and this was the only way I could get it to work.

Is it possible you can correct the code to show what you mean by 'You don't use the Rows collection of a typed DataTable. The DataTable itself has a method for adding a typed DataRow, just as it has a method for creating one. Also, this line is not right for an untyped DataSet:'

Thanks again for the help.

 

[JohnH]

What do you see when writing DataSet1.Main_Test.Add...?

DataSet1.Main_Test.AddMain_TestRow(Row)

 

[jpskiller]

What do you see when writing DataSet1.Main_Test.Add...?

DataSet1.Main_Test.AddMain_TestRow(Row)

Thank you because I was using the .Rows I did not see that.

 

[jpskiller]

I have a couple of questions.

I know I can use the Datasource wizard to create my dataset with caching!

1) How do you save a hard coded table in the dataset within the application.

I know I can Create a Table in the Dataset wizard and populate it, What I am trying to achieve is the ability to create some hard coded tables which store the information inside the program file.

1) I have a few tables with 10-20 items each - I want to be able to read data once from server and then fill manually created tables and store these within the application.
2) Be able to save a temp table within the application - reason, when close application if server down it saves inside for security, then when open check if data there and push to server.


Thanks