DataSets and inserting data

Hello:

I have a Visual Basic Program and have created a DataSet through the GUI which represents my three table database in SQL Server with all the defined relationships.

I have a bunch of raw data read from spreadsheets and populated to arrays. I want to take this array information and now add records, or update if they already exist.

I do not care to see any datagrids for this information, just populate the data. Is there not a way to just use the current 'JobSheetDataSet.xsd' file to insert the data? I am a little confused, and would not mind an overview of DataSets, DataTables and Data Adapters so I can understand how to accomplish this. I have probably asked similar questions about this before, so my apologies ahead of time.

It seems to me if I am able to see this data model through the DataSet, I should be able to just reference those objects and not have to write any kind of connection string, since this is how the dataset was created to begin with. Everything I've read online takes me through the steps of writing the connection string.

Thanks for all the great help on this forum!

With an untyped DataSet, you use one data adapter per DataTable, where the Fill method invokes the SelectCommand to retrieve data and the Update method invokes the InsertCommand, UpdateCommand and DeleteCommand as required to save changes. Each of those command objects contains a connection object - usually the same one - that determines the database they are executed against. A typed DataSet is just like an untyped DataSet - it inherits the same classes - but has some extra members that are specific to your data. This makes writing code easier as you get help from Intellisense and compile-time checking of names, types,etc. A table adapter is just a wrapper for a data adapter that is customised for your data too. If you follow the CodeBank link in my signature below, you'll find one or two threads that deal with data access that may help.

Each DataRow in your DataTables has a RowState property that indicates what needs to be done when you call Update. Unchanged means that there's nothing to do, Added means execute the InsertCommand, Modified means execute the UpdateCommand and Deleted means execute the DeleteCommand. Your problem is that, if you add rows from your spreadsheet, they will all have a RowState of Added and thus the table/data adapter will try to insert them. There are several ways to make it work the way you want:

1. Retrieve all the data from the database into the DataTables first, then modify the existing rows if there's a match or else add a new row. You'll then get the Modified rows to execute the UpdateCommand.
2. After adding all the rows, loop through the DataTable and call SetModified on those rows that need to be updated rather than inserted. How you would determine which those are is up to you. You may need to call AcceptChanges first (not sure) and you have to not use optimistic concurrency.
3. Modify the SQL code in the InsertCommand to check for an existing row first and execute an UPDATE statement if one is found, otherwise execute an INSERT statement. That may not be possible for some databases, e.g. Access.

Thank you!

So my concern is that I am not pulling the data I want to place in the database from a database, but from a variety of places.

Code:

---

      ' Populate JobInfo Table
        Dim JobNo As String = ar(0, 0)
        Dim ProjectItem As String = ar(0, 3)
        Dim DrawingNo As String = ar(0, 7)
        Dim Started As String = ar(1, 0)
        Dim Completed As String = ar(1, 3)
        Dim Engineer As String = ar(1, 7)
        Dim Designer As String = ar(3, 0)
        Dim EstHrs As String = ar(3, 3)
        Dim Checker = ar(2, 7)
        Dim ShipTo As String = ar(7, 0)
        Dim ShipDate As String = ar(7, 5)

        ' Populate JobDetail Table
        ' Dim JobNo As String = ar(0, 0)
        Dim PrintPickSheet_New As String = ar(9, 8)
        Dim CheckJobLetter_new As String = ar(10, 8)
        Dim CheckJobLetter_rw As String = ar(10, 9)
        Dim ObtainSerialNumber_new As String = ar(11, 8)
        Dim CheckIssueTracker_new As String = ar(12, 8)
        Dim CheckInterference_new As String = ar(13, 8)
        Dim CheckInterference_rw As String = ar(13, 9)
        Dim ProcessingComplete_new As String = ar(14, 8)
        Dim ProcessingComplete_rw As String = ar(14, 9)
        Dim CheckDestQtyAndRev_new As String = ar(15, 8)
        Dim CheckDestQtyAndRev_rw As String = ar(15, 9)
        Dim CheckReleasedState_new As String = ar(16, 8)
        Dim CheckReleasedState_rw As String = ar(16, 9)
        Dim CheckMfst_Producability_new As String = ar(17, 8)
        Dim CheckMfst_Producability_rw As String = ar(17, 9)
        Dim CheckPrintAndManifest_Peer_new As String = ar(18, 8)
        Dim CheckPrintAndManifest_Peer_rw As String = ar(18, 9)
        Dim SendPrintAndMfst_ToAdmin_new As String = ar(19, 8)
        Dim SendPrintAndMfst_ToAdmin_rw As String = ar(19, 9)
        Dim CompleteChanges_AfterPE_new As String = ar(20, 8)
        Dim CompleteChanges_AfterPE_rw As String = ar(20, 9)
        Dim UpdateFML_new As String = ar(21, 8)
        Dim UpdateFML_rw As String = ar(21, 9)
        Dim UpdateCustomerCD_new As String = ar(22, 8)
        Dim UpdateCustomerCD_rw As String = ar(22, 9)
        Dim UpdatePrintAndMfst_new As String = ar(23, 8)
        Dim UpdatePrintAndMfst_rw As String = ar(23, 9)

        ' Populate JobHrs
        ' Dim JobNo As String = ar(0, 0)
        Dim ActHrs As String = ar(3, 5)
        Dim ReworkHrs = ar(3, 7)
        Dim ToProduction As String = ar(1, 5)

---

That's irrelevant. The data is still saved exactly the same way. You create an instance of your typed DataSet, get the appropriate DataTable from the appropriate property, add rows to it and then save that data by calling Update on the appropriate table adapter. Where the data comes from has no impact on that. This is a common problem people have: they can't separate unrelated parts of a problem. Where you get data from and where you send it to are generally unrelated and can be treated separately, bar a little bit of massaging in the middle.

I do not know if my connection string is wrong, or if I've missed a step.

Code:

---

        Me.Validate()
        Me.BindingSource1.EndEdit()

        ' Dim sqlCn As String = "Data Source=CAD5;Initial Catalog=JobSheet;Integrated Security=SSPI;persist security info=True; providerName=System.Data.SqlClient"
        Dim sqlCn As String = "Server=CAD5\SQLEXPRESS;Database=JobSheet;Trusted_Connection=True;"
        Dim sqlStr As String
        Dim da As DataAdapter

        ' Table Adapter Update
        sqlStr = "SELECT * FROM dbo.JobInfo"
        da = New SqlDataAdapter(sqlStr, sqlCn)
        da.Update(ds)

        ' Table Adapter Update
        sqlStr = "SELECT * FROM dbo.JobDetail"
        da = New SqlDataAdapter(sqlStr, sqlCn)
        da.Update(ds)

        ' Table Adapter Update
        sqlStr = "SELECT * FROM dbo.JobHrs"
        da = New SqlDataAdapter(sqlStr, sqlCn)
        da.Update(ds)

---

The result is: Update unable to find TableMapping['Table'] or DataTable 'Table'

You need to make up your mind whether you are using a typed DataSet or and untyped DataSet. If we don't know what question we're answering, it's kinda hard to provide an answer. If you have a typed DataSet for the database you want to save data to then use it. Do what I said: create a DataSet instance, get the appropriate DataTable, populate it and then save the data by calling Update on the appropriate table adapter. Do you have a typed DataSet or not? Do you want to use it or not?

The dataset setup using data connection controls in vb.net

I need a data adapter to actually update the data from this to sql, as I understand things.

Source data is in an array
Target data is in sql

So my connection is trying to deal with getting the data to the target.

Thanks for your patience...

Quote:

---

Originally Posted by ssabc

The dataset setup using data connection controls in vb.net

I need a data adapter to actually update the data from this to sql, as I understand things.

---

No, you don't. If you have created a typed DataSet then that includes table adapters, which are effectively typed data adapters. They're all in the Toolbox.

So just dataset update?

I have the dataset:

Code:

---

        Dim ds As New JobSheetDataSet

        ds.CaseSensitive = False

        Dim JobInfoRow As DataRow = ds.JobInfo.NewRow
        With JobInfoRow
            .Item("JobNo") = JobNo
            .Item("ProjectItem") = ProjectItem
            .Item("DrawingNo") = DrawingNo
            .Item("Started") = Started ' date
            .Item("Completed") = Completed ' date
            .Item("ToProd") = Completed ' date
            .Item("Engineer") = Engineer
            .Item("Designer") = Designer
            .Item("EstHrs") = EstHrs ' decimal
            .Item("Checker") = Checker
            .Item("ShipTo") = ShipTo
            .Item("ShipDate") = ShipDate ' date

        End With

        ds.JobInfo.Rows.Add(JobInfoRow)

---

I have no table adapters set up...

This runs fine, it just does not put anything in the database.

Using one data adapter per table, the syntax is asking for a connection string.

Code:

---

da = New SqlDataAdapter(sqlStr, sqlCn)

---

Since you're using a typed dataset, the adaptors are built-in. So you don't need to worry about it. All you need to do is create your datarows (which you know how to do, as the code above shows) ... add them to your table (again, which you know how to do, as the code above shows) ... then when you're done, you call the .UpdateAll method of the dataset to save the changes back to the database.

-tg

UpdateAll appears to live with the data adapter. If I do not need a data adapter, because it requires a connection string and I'm using a typed dataset, how to I use this?

I found the controls that already existed for data tables and data adapters. I nee no option for UpdateAll. The code below runs well, but still no data is being populated into my table:

JobSheetDataSet is the typed dataset. Instantiating this does not seem to work out...

Code:

---

        Dim JobInfoRow As DataRow = ds.JobInfo.NewRow
        With JobInfoRow
            .Item("JobNo") = JobNo
            .Item("ProjectItem") = ProjectItem
            .Item("DrawingNo") = DrawingNo
            .Item("Started") = Started ' date
            .Item("Completed") = Completed ' date
            .Item("ToProd") = Completed ' date
            .Item("Engineer") = Engineer
            .Item("Designer") = Designer
            .Item("EstHrs") = EstHrs ' decimal
            .Item("Checker") = Checker
            .Item("ShipTo") = ShipTo
            .Item("ShipDate") = ShipDate ' date

        End With

        ' JobSheetDataSet.JobInfo.Rows.Add(JobInfoRow) ' Says Row already exists...

        Dim ta_JobInfo As JobSheetDataSetTableAdapters.JobInfoTableAdapter = New JobSheetDataSetTableAdapters.JobInfoTableAdapter
        Dim dt_JobInfo As JobSheetDataSet.JobInfoDataTable = New JobSheetDataSet.JobInfoDataTable

        ta_JobInfo.Fill(dt_JobInfo)
        ta_JobInfo.Update(JobSheetDataSet)

---

Thanks!

The UpdateAll actually is the method you call from the TableadapterManager. If you dragged a table onto a form from the datasource window, a TableAdapterManasger would have been added automatically. You can also add one manually.

But you don't have to use one, you can just call the Update method on each TableAdapter.

Code:

---

        Dim ds As New JobSheetDataSet

        ds.CaseSensitive = False

        dim ta as new JobSheetDataSetTasbleAdapter.JobInfoTableAdapter  'just guessing at your database table names
        ta.Fill(ds.JobInfo)

        Dim JobInfoRow As DataRow = ds.JobInfo.NewRow
        With JobInfoRow
            .Item("JobNo") = JobNo
            .Item("ProjectItem") = ProjectItem
            .Item("DrawingNo") = DrawingNo
            .Item("Started") = Started ' date
            .Item("Completed") = Completed ' date
            .Item("ToProd") = Completed ' date
            .Item("Engineer") = Engineer
            .Item("Designer") = Designer
            .Item("EstHrs") = EstHrs ' decimal
            .Item("Checker") = Checker
            .Item("ShipTo") = ShipTo
            .Item("ShipDate") = ShipDate ' date

        End With

        ds.JobInfo.Rows.Add(JobInfoRow)

        ta.Update(ds.JobInfo)

---

I just typed this free hand and it wasn't tested but should show you what your missing.

Also, you've been talking about a relation between tables, working with just one table at a time ignores that issue.

Thank you. Worked perfectly.

Just one more thing, Please and Thank You!

It was my understanding that the ta.Update updated any changes, but I do not see this happening. Because the JobNo field is unique, we cannot add rows, which is fine, but is there another command to update changes?

Code:

---

ds.JobInfo.Rows.Add(JobInfoRow)
ta.Update(ds.JobInfo)

---

What value does that Update call return?

?

Just as an example, the table of the dataset. Do I need to manually remove the rows that have changed? Do I need to create a data adapter with a query that checks for this, and then have an update query? I thought with ADO.NET that Update just overwrote any changes in the source data.

Thanks!

Code:

---

        Dim ta_JobHrs As JobSheetDataSetTableAdapters.JobHrsTableAdapter = New JobSheetDataSetTableAdapters.JobHrsTableAdapter
        ta_JobHrs.Fill(ds.JobHrs)

        Dim JobHrsRow As DataRow = ds.JobHrs.NewRow
        With JobHrsRow
            .Item("JobNo") = JobNo
            .Item("ActualHrs") = ActualHrs ' decimal
            .Item("ReworkHrs") = ReworkHrs ' decimal
            .Item("ToProd") = ToProd ' date

        End With

        Try
            ds.JobHrs.Rows.Add(JobHrsRow)

        Catch

        End Try

        ta_JobHrs.Update(ds.JobHrs)

---

Update will save any changes in the specified DataTable to the database, i.e. new rows get inserted, modified rows get updated and deleted rows get deleted.

This is all wrong for a typed DataSet:

vb.net Code:

Dim JobHrsRow As DataRow = ds.JobHrs.NewRow
        With JobHrsRow
            .Item("JobNo") = JobNo
            .Item("ActualHrs") = ActualHrs ' decimal
            .Item("ReworkHrs") = ReworkHrs ' decimal
            .Item("ToProd") = ToProd ' date
 
        End With
 
        Try
            ds.JobHrs.Rows.Add(JobHrsRow)
 
        Catch
 
        End Try

That's all code for an untyped DataSet. If you're going to use one then use it. The DataTable has a method that will create a new typed DataRow. It will be named NewJobHrsRow or similar in your case. That typed DataRow has a property for each column, so you don;t have to use magic strings to identify columns. The DataTable itself then has a method to add that row. This is the whole point of using a typed DataSet: it contains types specific to your data.

Again, what value does Update return? Just as Fill returns the number of rows retrieved, Update returns the number of rows saved, where saved can be inserted, updated or deleted.

Yes I want a typed dataset!

From what I cal tell, this would then require a data adapter, which requires a sql query and a connection, but which we already have. This methodology is looking at existing table adapters that come with the dataset as created in VS.

Sorry, not sure why this posted twice.

I could not tell you the value returned by Update, because obviously I have the wrong syntax. After reading several articles, including your reference last week and searching books on Safari, I see no example where Update just takes care of everything. I assume to keep my dataset typed, I cannot instantiate it as ds, but need to refer to it by name.

jmc, is there a book you recommend? I do want to keep the dataset typed.

I will say that every example I have read uses not a table adapter that automatically exists with my typed dataset, but a data adapter I am supposed to create with a sql statement. Because this already exists in the table, it seems redundant.

Thanks

Yes "someTableAdfapter.Update" will modify the database with all of the changes to the datatable, as long as the database table has a Primary Key. If you want to use an UnTyped Dataset and a Dataadapter because you can find more examples then fine, but don't use both.

Code:

---

        Dim JobHrsRow As JobSheetDataSet..JobHrsRow = ds.JobHrs.NewJobHrsRow
        With JobHrsRow
            .Item("JobNo") = JobNo ' IF THIS IS AN IDENTITY/AUTONUMBER FIELD THEN DON'T ADD ANYTHING TO THE FIELD, THE DATABASE WILL DO IT FOR YOU.  SEE BELOW
            .Item("ActualHrs") = ActualHrs ' decimal
            .Item("ReworkHrs") = ReworkHrs ' decimal
            .Item("ToProd") = ToProd ' date

        End With

        Try
            ds.JobHrs.Rows.Add(JobHrsRow)

        Catch

        End Try

        ta_JobHrs.Update(ds.JobHrs)


        With JobHrsRow
            .Item("ActualHrs") = ActualHrs ' decimal
            .Item("ReworkHrs") = ReworkHrs ' decimal
            .Item("ToProd") = ToProd ' date

        End With

---

I have to say, I do not know where I am using logic for typed datasets and where I am using logic for untyped datasets. I do have TableAdapter.Update, which by the way is not working. Once the data is placed with .AddRow, it does not change with .Update when I change it manually in the database and re-run the code.

The JobNo field is a unigue value, primary key, not auto-numbered but a string value '18-1863A' as an example. There are no Autonumber fields.

I would love an article or book that really explains this. All 20 plus references I have found just do not really explain this! I am not looking to populate any grids (yet), or anything, but just taking data from about 150 Excel spreadsheets which have been placed in Arrays, to SQL Server.

Thank you!

Quote:

---

Originally Posted by ssabc

I have to say, I do not know where I am using logic for typed datasets and where I am using logic for untyped datasets.

---

Then you should stop what you're doing and learn.

jcm: I agree! I believe I am asking for resources here more than answers! Every article I have looked at, every book on Safari as well is quite unclear! I also believe my questions in these posts are very specific!

table adapters vs data adapters, etc.

AND BELIEVE ME, I HAVE STOPPED WHAT I'M DOING!

jcm:

I'm a little discouraged here. You should know that I am not a professional developer. I work in the manufacturing engineering field supporting and enhancing CAD tools and other information. Obviously I have asked too many questions and worn out my welcome on this forum. I do not think my questions are unreasonable. I think we are not connecting well, speaking perhaps different languages or something, to which you have interpreted as me just asking for answers instead of learning. THIS IS HARDLY THE CASE!

I think you should have one of your colleagues review your posts in answer to my questions.

If you like, if I am such a burden to you, I am happy to leave the forum.

I do thank you for what you have tried to answer regarding this post.

It's hard to provide help on typed datasets because they are tied to the specific database and table structures that they represent. It's also something most of us don't use very often for a variety of reasons. Most of the time, a majority of us will use an untyped datatable and write the SQL ourselves and do most of the heavy data lifting manually. That's one of the reasons you're not getting some of the help that you seem to think you need. It's also why your searching isn't turning up what you're looking for.

We also deal with threads like this every. single. day. and it gets tiresome at times... which is why we come across as short and direct... we're here on our own time. somedays are better than others... some days are really bad... you just got to take yoru chances sometimes. Also, unless you clue us into your background upfront, we have no idea what it is, but we;ll assume you have some development skills and some training... if you don't, you need to tell us that. It's important because, yeah, it's going to change how we deal with you. We're more likely to treat you like a thrid grader and not an 8th grader that we think you should be.

That said, I know you're not interested yet in a displaying a grid, but maybethat's where you should start... start a new project... put a grid on it, and then start writing the code to load the data from the source and displaying it in the grid. Here's why: until you know that you can load and display it properly, there's no point in trying to save it anywhere. I've long since discovered that no matter how sure you are about your data, there's always a row or a bit of data in it that's screwed up somewhere that's going to be messed up that's going to prevent it from being saved properly. If you can load it and display it, you stand a chance at being able to see it up front before you try to stuff it into a database.

Second, I also recommend going through the process of learning how to write the code of a connection, a command, a datatable, and a dataset, as well as a dataadaptor to get the data loaded and into the grid... once you get it loaded, then you can then look to getting it into a different database. I should note that using a typed dataset to load dataset is fine, but if the intent it to move it to a new location (ie, from Excel to SQL Server)... it's not quite so easy.. that's because the typed datasets are closely tied to their sources... you would have to have two typed datasets for everything... one for Excel source, one for SQL Server destination and they aren't going to be compatible with each other... so you may be better off with using untyped datatables right from the start. Depending on your data, there may also be other alternatives too, but that's for another conversation.


-tg

On the subject of resources, I don't have any. I haven't used a typed DataSet for a long time and, even when I did, I didn't have a library of links lying around. If I was to direct you to some web site with information about typed DataSets then I'd have to search for it first and then read it to make sure that it's appropriate. You're just as capable as I am of doing that so it's not something I'm inclined to do.

That said, I would ask one question. Have you used the Help menu in VS? Maybe you have but, in my experience, the people who make the most noise about not being able to find information haven't used it, which tends to make me somewhat less sympathetic. Back in the days that I did use typed DataSets, the Microsoft documentation, accessible via the Help menu in VS, would have been one of the, if not THE, primary resources I used. I know that because the Microsoft documentation is ALWAYS one of the, if not THE, primary resources I use. Back when I started answering question here, almost 15 years ago, it's how I was able to answer questions that I had no prior experience with the topic of. I guess those people didn't use the Help menu.