Writing data back to a table
-
This is probably very simple to answer, and I must be doing something very silly, but I just don't seem to be able to get changes in a dataset to write back to a table in the database. I have used stored procs up 'til now but this is a quick fix needed to join two columns, date and time, into one datetime column, and I just cannot get it to work. Here is the code: Dim dsetData As New DataSet Dim dteTemp1 As Date Dim dteTemp2 As Date Dim intRowCount As Integer = 1 Using sqlcnnData As New SqlConnection(m_strConnect) Dim sqlcmdData As New SqlCommand("SELECT * FROM DDI_tbl_SystemDemandData", sqlcnnData) sqlcmdData.CommandType = CommandType.Text Dim sqldaData As New SqlDataAdapter(sqlcmdData) Dim builder As SqlCommandBuilder = New SqlCommandBuilder(sqldaData) builder.QuotePrefix = "[" builder.QuoteSuffix = "]" sqldaData.Fill(dsetData, "Dates") If dsetData.Tables(0).Rows.Count > 0 Then Dim drowData As DataRow For Each drowData In dsetData.Tables(0).Rows dteTemp1 = DateValue(drowData("DataDateTime")) dteTemp2 = drowData("DataTime") dteTemp1 = dteTemp1.AddHours(Hour(dteTemp2)) If Minute(dteTemp2) > 0 Then dteTemp1 = dteTemp1.AddMinutes(Minute(dteTemp2)) End If drowData("DataDateTime") = dteTemp1 drowData.AcceptChanges() Label1.Text = "Processed row " & intRowCount.ToString Application.DoEvents() intRowCount = intRowCount + 1 Next dsetData.AcceptChanges() intRowCount = intRowCount - 1 Label1.Text = "Completed. Processed " & intRowCount.ToString & " rows" sqldaData.Update(dsetData, "Dates") Else Label1.Text = "FAILED" End If sqldaData.Dispose() sqlcmdData.Dispose() End Using dsetData.Dispose() I don't get any errors, it runs OK, but just does not do it! I any one can point me in the right direction I would be most grateful. David Loring !! Keep Music Live !!
-
This is probably very simple to answer, and I must be doing something very silly, but I just don't seem to be able to get changes in a dataset to write back to a table in the database. I have used stored procs up 'til now but this is a quick fix needed to join two columns, date and time, into one datetime column, and I just cannot get it to work. Here is the code: Dim dsetData As New DataSet Dim dteTemp1 As Date Dim dteTemp2 As Date Dim intRowCount As Integer = 1 Using sqlcnnData As New SqlConnection(m_strConnect) Dim sqlcmdData As New SqlCommand("SELECT * FROM DDI_tbl_SystemDemandData", sqlcnnData) sqlcmdData.CommandType = CommandType.Text Dim sqldaData As New SqlDataAdapter(sqlcmdData) Dim builder As SqlCommandBuilder = New SqlCommandBuilder(sqldaData) builder.QuotePrefix = "[" builder.QuoteSuffix = "]" sqldaData.Fill(dsetData, "Dates") If dsetData.Tables(0).Rows.Count > 0 Then Dim drowData As DataRow For Each drowData In dsetData.Tables(0).Rows dteTemp1 = DateValue(drowData("DataDateTime")) dteTemp2 = drowData("DataTime") dteTemp1 = dteTemp1.AddHours(Hour(dteTemp2)) If Minute(dteTemp2) > 0 Then dteTemp1 = dteTemp1.AddMinutes(Minute(dteTemp2)) End If drowData("DataDateTime") = dteTemp1 drowData.AcceptChanges() Label1.Text = "Processed row " & intRowCount.ToString Application.DoEvents() intRowCount = intRowCount + 1 Next dsetData.AcceptChanges() intRowCount = intRowCount - 1 Label1.Text = "Completed. Processed " & intRowCount.ToString & " rows" sqldaData.Update(dsetData, "Dates") Else Label1.Text = "FAILED" End If sqldaData.Dispose() sqlcmdData.Dispose() End Using dsetData.Dispose() I don't get any errors, it runs OK, but just does not do it! I any one can point me in the right direction I would be most grateful. David Loring !! Keep Music Live !!
I always use stored procs so this may be incorrect but! drowData.AcceptChanges() This sets the changed rows to current rows and removes the changed flag on the row sqldaData.Update(dsetData, "Dates") This presumably updates the change rows (which you have set to current) into the databse - it cannot find any changed rows! I suggest you remove drowData.AcceptChanges()
-
I always use stored procs so this may be incorrect but! drowData.AcceptChanges() This sets the changed rows to current rows and removes the changed flag on the row sqldaData.Update(dsetData, "Dates") This presumably updates the change rows (which you have set to current) into the databse - it cannot find any changed rows! I suggest you remove drowData.AcceptChanges()