DataTable not Updating

jmss66 · May 16th, 2014, 04:21 PM

I am new to the .NET environment and coming from VB 6. I am now using VB 2013 and am having a hard time transitioning. I am trying to read two tables (ASB_Formula_Info) and (AnnualSupplementalExtract). After various calculations, I need to update the AnnualSupplementalExtract table with the results from the calculation. I have searched the internet on how to go about doing this and have applied what I think how it is done. Seems like I am doing something wrong since the table is not getting updated.

Please help me if I am missing something on my code. I have tried several ways and I am not able to get the table AnnualSupplementalExtract to update. I have checked if the records are being read and calculated and I see that they are.

Code:

 
Public Class Form1
 
 
Dim MDBConnString AsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\MyPath\ASB.mdb"
Dim strTableName AsString
Dim sqlString AsString
Dim intASBYear AsInteger
 
 
Dim cnn As OleDb.OleDbConnection
Dim cmd As OleDb.OleDbCommand
Dim daInfo As OleDb.OleDbDataAdapter
Dim daASBExtract As OleDb.OleDbDataAdapter
 
 
 
 
PrivateSub btnStart_Click(sender AsObject, e AsEventArgs) Handles btnStart.Click
       strTableName = "ASB_Formula_Info"
       sqlString = "SELECT * FROM " & strTableName & " WHERE ASB_Year = (" & dtpPricing.Value.Year & " - 1)"
 
 
       cnn = New OleDb.OleDbConnection(MDBConnString)
       cnn.Open()
       cmd = New OleDb.OleDbCommand(sqlString, cnn)
       daInfo = New OleDb.OleDbDataAdapter(cmd)
 
Dim dsASB AsNewDataSet("ASB")
 
       daInfo.Fill(dsASB, strTableName)
 
Dim tblInfo AsDataTable
       tblInfo = dsASB.Tables(strTableName)
 
Dim drCurrent AsDataRow
 
       intASBYear = tblInfo.Rows(0).Item("ASB_Year")
 
 
       txtActualBenefitAllocation.Text = intASBYear
 
 
       strTableName = "AnnualSupplementalExtract"
       sqlString = "SELECT * FROM  " & strTableName
 
'Dim dsASBExtract As New DataSet
       cnn = New OleDb.OleDbConnection(MDBConnString)
       cnn.Open()
       cmd = New OleDb.OleDbCommand(sqlString, cnn)
       daASBExtract = New OleDb.OleDbDataAdapter(cmd)
       daASBExtract.Fill(dsASB, strTableName)
 
Dim tblASBExtract AsDataTable
       tblASBExtract = dsASB.Tables(strTableName)
 
' Compute Total Years Since Retirement
 
Dim dblTotalAge AsDouble
 
For i AsInteger = 0 To tblASBExtract.Rows.Count - 1
If IsNumeric(tblASBExtract.Rows(i).Item("Years Since Retirement")) Then
               dblTotalAge = Math.Round(dblTotalAge + tblASBExtract.Rows(i).Item("Years Since Retirement"), 2)
EndIf
Next
 
' Compute percentage of allocation
 
Dim dblAllocation AsDouble
 
'       For i As Integer = 0 To tblASBExtract.Rows.Count - 1
'On Error Resume Next
'With tblASBExtract.Rows(i)
' dblAllocation = Math.Round((tblASBExtract.Rows(i).Item("Years Since Retirement") / dblTotalAge), 8)
' .Item("Allocation") = dblAllocation
'.Item("Benefit Based on Retirement Date") = Math.Round((dblAllocation * tblInfo.Rows(0).Item("ASB_Total_Benefit_Allocation")), 2)
'daASBExtract.Update(dsASB, strTableName)
'End With
'Next
 
For i AsInteger = 0 To tblASBExtract.Rows.Count - 1
OnErrorResumeNext
 
           drCurrent = tblASBExtract.Rows(i)
If IsNumeric(drCurrent("Years Since Retirement")) Then
               dblAllocation = Math.Round((drCurrent("Years Since Retirement") / dblTotalAge), 8)
               drCurrent.BeginEdit()
               drCurrent("Allocation") = CDbl(Math.Round((drCurrent("Years Since Retirement") / dblTotalAge), 8))
               drCurrent("Benefit Based on Retirement Date") = CDbl(Math.Round((drCurrent("Allocation") * tblInfo.Rows(0).Item("ASB_Total_Benefit_Allocation")), 2))
               drCurrent.EndEdit()
 
               daASBExtract.Update(dsASB, "AnnualSupplementalExtract")
EndIf
 
Next
 
       cnn.Close()
 
       txtPovertyLevel.Text = dblTotalAge
EndSub

Thanks

jmss66 · May 19th, 2014, 10:30 AM

Anybody? I am using an ACCESS database. if that will help. Am I missing a namespace? The code above is everything. No imports system....Should I be adding one in? Are you as baffled as I am why the tables are not getting updated?

Any help will be appreciated.

Thanks.

mmorgan30 · May 21st, 2014, 08:18 PM

Call AcceptChanges on the ds or table after the update is method is called

mmorgan30 · May 21st, 2014, 08:25 PM

Scratch that last idea, your problem is that you have not supplied an update command to the data adapter prior to calling update. The only command that you have given the data adapter is the select command.

here is an example in C#; usage is exactly the same in vb.net, it also has command parameter examples, the way you have your select command set up is vulnerable to sql injection attacks.

Code:

// create connection
var connection = new System.Data.OleDb.OleDbConnection("my Connection string");

// create selelct command
var selectCommand = new System.Data.OleDb.OleDbCommand( "Select * from table where tableId = ?", connection );
selectCommand.CommandType = System.Data.CommandType.Text; 
selectCommand.Parameters.Add( tablIdValue ); // parms must be added in the order in which they appear in cmd text


// create update command
var updateCommand = new System.Data.OleDb.OleDbCommand( "Update table where tableId = ?" );
updateCommand.CommandType = System.Data.CommandType.Text;
updateCommand.Parameters.Add( tableIdValue ); // parms must be added in the order in which they appear in cmd text


// create data adatpter
var da = new System.Data.OleDb.OleDbDataAdapter( selectCommand );
da.UpdateCommand = updateCommand;

// update data set
da.Update( myDs );

jmss66 · May 21st, 2014, 08:36 PM

There is an update command. DaAsbExtract.Update.

mmorgan30 · May 21st, 2014, 09:02 PM

are you asking me if there is an UpdateCommand or are you telling me you set an update command from what i am seeing you never set the update command that the Update method is to call on the dataset. please take a look at the example in my previous post i have update it.