VB.Net mas-access dsum

Is it possible to use dsum as part of OleDbCommand

Page 1 of 1

0 Replies - 1301 Views - Last Post: 07 October 2008 - 02:18 PM Rate Topic: -----

#1 jonathanm5   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 07-October 08

VB.Net mas-access dsum

Post icon  Posted 07 October 2008 - 02:18 PM

I want to add fields from various "sub" records and insert this total into the master record. I have a query in access which works. Is it possible to use this code in the codebehind page of my vb.net (asp.net 2.0) application

Sub secondstage()
	   
	  
		Dim MyConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/fpdb/passliabcapture.mdb")
			
		Dim myCommand As New OleDbCommand("UPDATE Results SET results.TotalAnnualSASRIA = DSUM(resultsvehicle.TotalAnnualSASRIA)  FROM resultsvehicle WHERE resultsvehicle.status = 'D' AND resultsvehicle.Polno = results.ID)  ", New OleDbConnection(MyConnection))
			   
		myCommand.Connection.Open()
		myCommand.ExecuteNonQuery()
		myCommand.Connection.Close()
	   

	
	 
	End Sub


My access query is

UPDATE Results SET Results.TotalAnnualSASRIA = DSum("[resultsvehicle.TotalAnnualSASRIA]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.TotalMonthlySASRIA = DSum("[resultsvehicle.TotalMonthlySASRIA]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.TotalAnnualBrok = DSum("[resultsvehicle.TotalAnnualBrok]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.TotalMonthlyBrok = DSum("[resultsvehicle.TotalMonthlyBrok]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.TotalAnnualPremium = DSum("[resultsvehicle.TotalAnnualPremium]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.TotalMonthPremium = DSum("[resultsvehicle.TotalMonthPremium]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.noseats = DSum("[resultsvehicle.noseats]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.liab = DSum("[resultsvehicle.liab]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.pa = DSum("[resultsvehicle.pa]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.fun = DSum("[resultsvehicle.fun]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.brokfee = DSum("[resultsvehicle.brokfee]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.ppa = DSum("[resultsvehicle.ppa]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.pliab = DSum("[resultsvehicle.pliab]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.fun1 = DSum("[resultsvehicle.fun1]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.premium = DSum("[resultsvehicle.premium]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID]), Results.mnthpremium = DSum("[resultsvehicle.mnthpremium]","resultsvehicle","resultsvehicle.PolicyNo =  " & [results.ID])
WHERE status="D";



Is This A Good Question/Topic? 0
  • +

Page 1 of 1