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
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools 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
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 04: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 03:28 AM
Updating datatable Nikos General .NET 1 November 4th, 2004 03:43 PM
updating db with dataAdapter/dataTable problem mm1234 ADO.NET 0 November 19th, 2003 05:49 AM



All times are GMT -4. The time now is 02:03 PM.


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