Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DataAdapter not updating SQL Server table

Posted on 2009-04-09
3
Medium Priority
?
238 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 800 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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 13 hours left to enroll

580 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