Solved

DataAdapter not updating SQL Server table

Posted on 2009-04-09
3
227 Views
Last Modified: 2012-05-06
I have a routine in which I am attempting to loop through a text file using a StreamReader and import the data I mine from it to a SQL Server 2008 table called BSR.  I can populate an entire dataset with no problems (checked the dataset contents in debug mode and all is well) but it will not update to SQL Server no matter what I try.  

The dataset (ds) is of type dsBSR, which is a drag-and-drop from Server Explorer of the table BSR.  This is my first attempt at typed datasets and so far I am failing miserably.  The code is pasted below, and all it needs to do is add newly imported lines (which are in a table in the dataset) to the table in SQL Server.  Thanks in advance for your assistance!
Dim da As New SqlDataAdapter("SELECT * FROM BSR", con)

        Dim con As New SqlConnection(My.Settings.BSROnSQLServer)

        Dim ds As New dsBSR

        Dim row As dsBSR.BSRRow
 

...        
 

        Try

            If con.State = ConnectionState.Closed Then

                con.Open()

            End If
 

            'Code to populate dataset is in this range
 

            ds.AcceptChanges()

            da.FillSchema(ds, SchemaType.Mapped, "BSR")

            Dim cb As New SqlCommandBuilder(da)

            da.InsertCommand = cb.GetInsertCommand

            da.UpdateCommand = cb.GetUpdateCommand

            MessageBox.Show(da.Update(ds, "BSR") & " records imported.", "Import results")

        Catch dbex As SqlException

            MessageBox.Show("Error: " & dbex.ToString)

        Finally

            con.Close()

        End Try

        ...

Open in new window

0
Comment
Question by:pigparent
  • 2
3 Comments
 
LVL 21

Accepted Solution

by:
Craig Wagner earned 200 total points
ID: 24107815
I think the call to AcceptChanges might be part of the problem. According to the documentation:

"When you call AcceptChanges on the DataSet, any DataRow objects still in edit-mode end their edits successfully. The RowState property of each DataRow also changes; Added and Modified rows become Unchanged, and Deleted rows are removed."

That means when you call Update and the framework looks at what it has to do it finds all the rows are in an Unchanged state and says, "Nothing to do here."
0
 
LVL 1

Author Closing Comment

by:pigparent
ID: 31568536
Thank you, thank you, THANK YOU!
0
 
LVL 1

Author Comment

by:pigparent
ID: 24107885
Thanks so much!  I meant to mention in my question that I was grasping at straws, trying everything I found on the net to try to make this work.  Your solution made my day!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 117
Separate a number and letter from a string 25 26
Tabcontrol Caption? 1 24
simple mysql statement 3 11
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now