index > Windows Forms Data Controls and Databinding > Problem with SQLDataAdapter update method with DataTable

Problem with SQLDataAdapter update method with DataTable

Hello Every one,

Their is very strange problem happening with SqlDataAdapter Update method.

First we will see the code than I will show you the problem.

So hear is the code

Code1:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim qry = "Select * from test"
Dim DaObj As New SqlDataAdapter(qry, "Data Source=localhost;Initial Catalog=extra;Persist Security Info=True;User ID=sa;Password=password")

Dim CmdBuilObj As New SqlCommandBuilder(DaObj)
Dim DtObj As New DataTable
DaObj.Fill(DtObj)


qry = "Select * from test1"

Dim DaObj1 As New SqlDataAdapter(qry, "Data Source=localhost;Initial Catalog=extra;Persist Security Info=True;User ID=sa;Password=password")

Dim CmdCevaBuilObj As New SqlCommandBuilder(DaObj1)
Dim DtObj1 As New DataTable
        For Each dr As DataRow In DtObj.Rows
            DtObj1.ImportRow(dr)
        Next

Dim result As Integer = DaObj1.Update(DtObj1)
'' Result is 0, It's not updating physical DataBase, it show data in DtObj1 [DataTable] but not update SqlDataAdapter [physical database]
End Sub

Code 2 :

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim qry = "Select * from test" Dim DaObj As New SqlDataAdapter(qry, "Data Source=localhost;Initial Catalog=extra;Persist Security Info=True;User ID=sa;Password=password") Dim CmdBuilObj As New SqlCommandBuilder(DaObj) Dim DtObj As New DataTable DaObj.Fill(DtObj) qry = "Select * from test1" Dim DaObj1 As New SqlDataAdapter(qry, "Data Source=localhost;Initial Catalog=extra;Persist Security Info=True;User ID=sa;Password=password") Dim CmdCevaBuilObj As New SqlCommandBuilder(DaObj1) Dim DtObj1 As New DataTable DtObj1= DtObj.copy() Dim result As Integer = DaObj1.Update(DtObj1) '' Result is 0, It's not updating physical DataBase, it show data in DtObj1 [DataTable] but not update SqlDataAdapter [physical database] End Sub

Code 3:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim qry = "Select * from test" Dim DaObj As New SqlDataAdapter(qry, "Data Source=localhost;Initial Catalog=extra;Persist Security Info=True;User ID=sa;Password=password") Dim CmdBuilObj As New SqlCommandBuilder(DaObj) Dim DtObj As New DataTable DaObj.Fill(DtObj) qry = "Select * from test1" Dim DaObj1 As New SqlDataAdapter(qry, "Data Source=localhost;Initial Catalog=extra;Persist Security Info=True;User ID=sa;Password=password") Dim CmdCevaBuilObj As New SqlCommandBuilder(DaObj1) Dim DtObj1 As New DataTable Dim row As DataRow For Each dr As DataRow In DtObj.Rows row = DtObj1.NewRow() row("Name") = "Hello" CevaDtObj.Rows.Add(row) Next Dim result As Integer = DaObj1.Update(DtObj1) '' Result is 1, It's updating physical DataBase, it show data in DtObj1 [DataTable] and even update SqlDataAdapter [physical database] End Sub

Problemin Code 1 & Code 2:

Physical DataBase is not updating infact DataTable has all the values. InShort 'DaObj1 returns 0, means result =0'
If we transfered the data through regular method means throughcode 3 [Please seecode 3]then data are transfered succesfully.

Why it so ? I am reallyfollowing all the concept of .Net Framework and according to the code 1 and 2 should work. But unfortunately it is not working

If any one has solution or any sugesstion please help me out.

Thanks,
Abhishek Joshi.

Edited byabhi2185 Friday, July 24, 2009 1:23 PM
Edited byabhi2185 Friday, July 24, 2009 1:22 PM

abhi2185 Friday, July 24, 2009 12:40 PM

Hi,

Seems you want to copy the data from one table to another table.

I test on my side.

If only import rows into the datatable, the datatable don’t have any column, this makes no sense. And the bound datagridview will show nothing. The datatable really has the rowcount number, but there is no data can be accessed.

If datatable has columns before importing the data, you can get the data if the column names are same as the original datatable. So if you want to get all data use ImportRow method, make sure the imported datatable has the same column names as the orginal datatable.

Dim datatable01 As DataTable = New DataTable()

Dim datatable02 As DataTable = New DataTable()

Dim datatable03 As DataTable = New DataTable()

datatable01.Columns.Add("col01")

datatable01.Columns.Add("col02")

datatable01.Rows.Add("11", "aa")

datatable01.Rows.Add("22", "bb")

datatable03.Columns.Add("col01") ' same name

datatable03.Columns.Add("col03")

For Each dr As DataRow In datatable01.Rows

datatable02.ImportRow(dr)

datatable03.ImportRow(dr)

Console.WriteLine("datatable02:" & datatable02.Rows.Count) ' datatable02.rows.count =2

Console.WriteLine("datatable02:" & datatable02.Columns.Count) ' datatable02.columns.count = 0

DataGridView1.DataSource = datatable02 ' no data

DataGridView2.DataSource = datatable03 ' only col01 has data

The copy method will copy all the data including rows and columns to another datatable. You can test the following code. I don’t know why there is nothing on your side. Please check whether the DtObj table gets the data correctly.

Dim datatable01 As DataTable = New DataTable()

Dim datatable02 As DataTable = New DataTable()

datatable01.Columns.Add("col01")

datatable01.Columns.Add("col02")

datatable01.Rows.Add("11", "aa")

datatable01.Rows.Add("22", "bb")

datatable02 = datatable01.Copy()

Console.WriteLine("first:" & datatable02.Rows.Count)

Console.WriteLine("first:" & datatable02.Columns.Count)

DataGridView1.Columns.Add(New DataGridViewTextBoxColumn())

DataGridView1.DataSource = datatable02 ' You can see all the data of datatable01

The third method is the common way to add rows to datatable.

If I misunderstood you, or you have further questions, please feel free to tell me.

Best regards,

Ling Wang

Please remember to click “Mark as Answer�?on the post that helps you, and to click “Unmark as Answer�?if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Marked As Answer byLing WangMSFT, ModeratorSunday, August 02, 2009 12:55 PM

Ling Wang Wednesday, July 29, 2009 10:05 AM

for Code1:

Calling ImportRow preserves the existing DataRowState along with other values in the row.

You need to call .SetAdded() for imported Row.

-@SuDhiR@-

_SuDhiR_ Friday, July 24, 2009 12:55 PM

Hi

Thanks for your reply
I have write the code as per you say. Is it write ?

If it is than it will still not affect the database.

Even all the data stored in DataTable but the problem is it's not affect while i call DataAdapter Update Method.

        For Each dr As DataRow In DtObj.Rows
            DtObj1.ImportRow(dr)
            dr.SetAdded()
        Next

Thanks,

Abhishek Joshi

Edited byabhi2185 Friday, July 24, 2009 1:25 PM

abhi2185 Friday, July 24, 2009 1:22 PM

   For Each dr As DataRow In DtObj.Rows
            dr.SetAdded()
            DtObj1.ImportRow(dr)
     Next

Try like this?

Please let me know if it helps you.

-@SuDhiR@-

_SuDhiR_ Friday, July 24, 2009 4:32 PM

Hi,

Row are only imported not the columns. It' sreally strange.

But i don't want to set single items at a time as it is time consuming Even the code 2 also not work as destination data table have both Row and columns with the data

abhi2185 Saturday, July 25, 2009 6:00 AM

Hi,

Seems you want to copy the data from one table to another table.

I test on my side.

Dim datatable01 As DataTable = New DataTable()

Dim datatable02 As DataTable = New DataTable()

Dim datatable03 As DataTable = New DataTable()

datatable01.Columns.Add("col01")

datatable01.Columns.Add("col02")

datatable01.Rows.Add("11", "aa")

datatable01.Rows.Add("22", "bb")

datatable03.Columns.Add("col01") ' same name