[2008] Updating database using SqlAdapter

[lasota]

hi,

Using SqlAdapter and "SELECT ... RIGHT OUTER JOIN .... BY .... " I'm creating datatable1 Tabela1 which consist of 4 columns. First two columns came from dbo.prierwsza another 2 columns from dbo.druga.
The Question is how can I update my secound datasource dbo.druga after changing some data in Tabela1? The Database is on MS Server 2005.

My cod is as follows:

Dim sqlConn As New SqlClient.SqlConnection
        sqlConn.ConnectionString = "server = SLY-C7BD4217D27; database = nowa; integrated security = true"
        
        Try
            sqlConn.Open()
        Catch ex As Exception
            ListBox1.Items.Add(ex.Message)
            MessageBox.Show(ex.Message, "Verbindung mit dem Datenbank koennte nich aufgebaut werden")
        End Try

        If sqlConn.State = 1 Then
            Me.Text = "Connected!!!!"
            Me.BackColor = Color.Chocolate
            ListBox1.Items.Add("Connected!")
        End If

        

        Dim sqlcomm As SqlCommand

        tabela1 = New DataTable("tabela1")
        'Dim artikel As New DataColumn("artikel")
        'artikel = New DataColumn()


        sqlcomm = sqlConn.CreateCommand()
        sqlcomm.CommandType = CommandType.Text
        sqlcomm.CommandText = "SELECT dbo.pierwsza.artikel, dbo.druga.klucz, dbo.pierwsza.abc, dbo.druga.ggg FROM dbo.pierwsza LEFT OUTER JOIN dbo.druga ON dbo.pierwsza.artikel = dbo.druga.klucz"


        

       
        
        Dim sqlAdapt As New SqlDataAdapter(sqlcomm)
        sqlAdapt.Fill(tabela1)

        mydataset.Tables.Add(tabela1)
        
        DataGridView1.DataSource = mydataset.Tables("tabela1")
        
       

        'sqlConn.Close()

[jmcilhinney]

You need two DataAdapters: one for each table. You can use the adapter you already have to save changes to one of the tables. Configure its DeleteCommand, InsertCommand and UpdateCommand properties appropriately and set its AcceptChangesDuringUpdate property to False. That way the RowState of all your DataRows will not be reset to Unchanged. You now use your second DataAdapter, with appropriately configured commands, to save the changes to the second table. This time you do want to accept changes. You would want to use the same connection for both adapters and wrap the whole operation in a transaction.

[jmcilhinney]

Note that your query must return the primary keys from both tables to enable you to uniquely identify records in each in order to update or delete existing records.

[lasota]

Would You mind giving me some pice of sample code, please?

[jmcilhinney]

I meant to say before, follow the Data Access link in my signature for an example of configuring a DataAdapter, amongst other things.

[lasota]

I redesigned my program and I get an error "The SelectCommand property has not been initialized before calling 'Fill' " in this line

sqladapt.fill(tabela1)


I don't understand what is not initialized? sqladapt? or tabela1? i wrote

sqlAdapt.InitializeLifetimeService

but it didn't help... :/

My code:

public class form1
.
.
.
Dim sqlConn As New SqlClient.SqlConnection
    Dim sqlAdapt As New SqlDataAdapter(sqlcomm)
    Dim sqlcomm As SqlCommand
.
.
.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

              sqlConn.ConnectionString = "server = SLY-C7BD4217D27; database = nowa; integrated security = true"
        
        Try
            sqlConn.Open()
        Catch ex As Exception
            ListBox1.Items.Add(ex.Message)
            MessageBox.Show(ex.Message, "Verbindung mit dem Datenbank koennte nich aufgebaut werden")
        End Try

        If sqlConn.State = 1 Then
            Me.Text = "Connected!!!!"
            Me.BackColor = Color.Chocolate
            ListBox1.Items.Add("Connected!")
        End If

        



        tabela1 = New DataTable("tabela1")
        'Dim artikel As New DataColumn("artikel")
        'artikel = New DataColumn()


        sqlcomm = sqlConn.CreateCommand()
        sqlcomm.CommandType = CommandType.Text
        sqlcomm.CommandText = "SELECT dbo.pierwsza.artikel, dbo.druga.klucz, dbo.pierwsza.abc, dbo.druga.ggg FROM dbo.pierwsza LEFT OUTER JOIN dbo.druga ON dbo.pierwsza.artikel = dbo.druga.klucz"


        

        'Dim mydataset As New DataSet("DataSetName")


        sqlAdapt.Fill(tabela1)

        mydataset.Tables.Add(tabela1)
        
        DataGridView1.DataSource = mydataset.Tables("tabela1")
        
        'mydataset.Tables("tabela1").Rows(0)("artikel") = "ssssss"

        'sqlConn.Close()



    End Sub
.
.
.
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click


        Dim delete As New SqlCommand("DELETE FROM dbo.druga WHERE klucz = @klucz", sqlConn)
        Dim insert As New SqlCommand("INSERT INTO dbo.druga (ggg, klucz) VALUES (@ggg, @klucz)", sqlConn)
        Dim update As New SqlCommand("UPDATE dbo.druga SET ggg = @ggg, klucz = @klucz WHERE klucz = @klucz", sqlConn)


        delete.Parameters.Add("@klucz", SqlDbType.NChar, 10, "klucz")

        insert.Parameters.Add("@klucz", SqlDbType.NChar, 10, "klucz")

        insert.Parameters.Add("@ggg", SqlDbType.NChar, 10, "ggg")




        update.Parameters.Add("@klucz", SqlDbType.NChar, 10, "klucz")

        update.Parameters.Add("@ggg", SqlDbType.NChar, 10, "ggg")

        

        sqlAdapt.DeleteCommand = delete

        sqlAdapt.InsertCommand = insert

        sqlAdapt.UpdateCommand = update



        sqlAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey

        sqlAdapt.Update(tabela1)
    End Sub

end class

[jmcilhinney]

The problem is that when you create sqlAdapt, sqlcomm is Nothing. That means that sqlAdapt is created without a SelectCommand. You create a Command later and assign it to sqlcomm, but that doesn't mean that sqlAdapt knows anything about that Command. You need to either create the DataAdapter after you create the Command or else you must assign the Command to the DataAdapter's SelectCommand explicitly.

On a different note, I'm a big believer in consistency in coding and now is the time to get into good habits. Here's a snippet of your code:

Dim sqlConn As New SqlClient.SqlConnection
    Dim sqlAdapt As New SqlDataAdapter(sqlcomm)
    Dim sqlcomm As SqlCommand

Why is one type qualified when two others in the same namespace aren't? Why is one variable name all lower case when the other two aren't? If you care about the detail then you'll end up writing better code. If you're sloppy when it comes to the detail your code will be sloppy.

[lasota]

After some digging in Internet I figure it out I decided to use the second option which is assigning the Command to the DataAdapter's SelectCommand explicitly what I belive I'm doing using following code:

sqlComm = sqlConn.CreateCommand()
        sqlComm.CommandType = CommandType.Text
        sqlComm.CommandText = "SELECT dbo.pierwsza.artikel, dbo.druga.klucz, dbo.pierwsza.abc, dbo.druga.ggg FROM dbo.pierwsza LEFT OUTER JOIN dbo.druga ON dbo.pierwsza.artikel = dbo.druga.klucz"


        

        'Dim mydataset As New DataSet("DataSetName")
        
        sqlAdapt.SelectCommand = sqlComm

        sqlAdapt.Fill(tabela1)

        mydataset.Tables.Add(tabela1)
        
        DataGridView1.DataSource = mydataset.Tables("tabela1")

I'm not sure if it was that what You meant but it seams to do the trick becouse I can load data to my table as I was able before.
But for some reason I still can not update dbo.druga using sub Button5_Click.
The line

sqlAdapt.Update(tabela1)

gives me following error "DBConcurrencyException was unhandled. Concurrency violation: the UpdateCommand affected 0 of excepted 1 records"

For any tips how to solve this problem I would be greatful.

[lasota]

I have noticed, that although this error my data source (dbo.druga) is being updated, but only to the first row, which didn't exist before. If I make some changes but I'm not trying to create and update the dbo.druga with new rows there is no error. But if sqlAdapt.update has to create some new rows in data source (dbo.druga) this problem occurs.

[lasota]

Anybody? it must be something simple. What am I missing in my code?

[lasota]

I think the problem is as follows:

1. My table in Database on MS Sql Server (dbo.druga) has primarykey set to "ggg" Column.
2. When I'm trying to update this table (dbo.druga on server) from tabela1 (from my app) everything is fine as long as dataadapter.update method can find in dbo.druga the same primarykey which is in tabela1
3. Sooner or later it will not be able to find the primarykey in dbo.druga because tabela1 has some new records with some new primarykeys which are supposed to be added to dbo.druga (this is why I'm making the update...) and this is a reason why they are missing in dbo.druga
4.So how can I overcame this? "ggg" column (which has primarykey) is not simple ID column so autoincrement solution will not work. I need something which will force the update method, everytime if it can't find corresponding "ggg" record (primarykey record), to create a new row and fill it with data including primarykey record which was missing. Is there no parameter for update which will force this?

[lasota]

The problem was that I imported to datatable a table which was a result of "select ... join ..." sql statment so the new rows were already added as I filled the table. As a result rowstate in tabela1 wasn't changed to "added" and sqladapter.update didn't know that it schould add some rows. I fixed it using setadded()

[jmcilhinney]

Are you saying that you have a DataTable that contains some new rows and you need to insert that data into two tables in the database, but when you try to do so they are only inserted into the first one? If so then I have already addressed your issue in my very first reply.

You can use the adapter you already have to save changes to one of the tables. Configure its DeleteCommand, InsertCommand and UpdateCommand properties appropriately and set its AcceptChangesDuringUpdate property to False.

The adapter saving first should not call AcceptChanges on the DataTable so the changes are still available to the second adapter. The second adapter should call AcceptChanges.