Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 2012 > Visual Basic 2012 General Discussion
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Visual Basic 2012 General Discussion For any discussions about Visual Basic 2012topics which aren't related to a specific Wrox book
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Visual Basic 2012 General Discussion section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 16th, 2014, 04:21 PM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default DataTable not Updating

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
Reply With Quote
  #2 (permalink)  
Old May 19th, 2014, 10:30 AM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

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.
Reply With Quote
  #3 (permalink)  
Old May 21st, 2014, 08:18 PM
Friend of Wrox
Points: 528, Level: 8
Points: 528, Level: 8 Points: 528, Level: 8 Points: 528, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2014
Posts: 136
Thanks: 1
Thanked 10 Times in 10 Posts
Default

Call AcceptChanges on the ds or table after the update is method is called
Reply With Quote
  #4 (permalink)  
Old May 21st, 2014, 08:25 PM
Friend of Wrox
Points: 528, Level: 8
Points: 528, Level: 8 Points: 528, Level: 8 Points: 528, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2014
Posts: 136
Thanks: 1
Thanked 10 Times in 10 Posts
Default

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 );

Last edited by mmorgan30; May 21st, 2014 at 09:00 PM..
Reply With Quote
  #5 (permalink)  
Old May 21st, 2014, 08:36 PM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

There is an update command. DaAsbExtract.Update.
Reply With Quote
  #6 (permalink)  
Old May 21st, 2014, 09:02 PM
Friend of Wrox
Points: 528, Level: 8
Points: 528, Level: 8 Points: 528, Level: 8 Points: 528, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2014
Posts: 136
Thanks: 1
Thanked 10 Times in 10 Posts
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating a datatable associated with grid karveajit C# 0 December 5th, 2008 05:03 AM
Datatable Limno .NET Framework 1.x 0 May 28th, 2008 02:37 PM
how to use DataTable.Select Salte C# 2 December 22nd, 2004 04:28 AM
Updating datatable Nikos General .NET 1 November 4th, 2004 04:43 PM
updating db with dataAdapter/dataTable problem mm1234 ADO.NET 0 November 19th, 2003 06:49 AM



All times are GMT -4. The time now is 05:50 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.