473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

A better way to access data in a database

Hi guys

We connect to a database using a sub (RUNCMD) which we created.
It takes 2 parameters a string which is executed and a delegate function
of this type:

Expand|Select|Wrap|Line Numbers
  1. Delegate Function OutputDataReader(ByRef dr As SqlDataReader) As Boolean
  2.  
  3. Public Overloads Function RunCMD(ByVal sSQLSTR As String, ByVal datafetch As OutputDataReader) As Boolean
  4.  
  5. This is how we use it:
  6.  
  7.  Public Function LoadAffordabilityPercentsFromConnection() As Boolean
  8.         Try
  9.             If Not mvar_DataFetched Then
  10.                 If Not DS.RunCMD("EXEC Get_df_AffordabilityPercents", AddressOf LoadAffordabilityPercents) Then
  11.                     MsgBox("Problem fetching Affordability Percents!", MsgBoxStyle.Critical, "Object Build failure")
  12.                     Exit Function
  13.                 End If
  14.             End If
  15.  
  16.         Catch ex As Exception
  17.             Throw New Exception("LoadAffordabilityPercentsFromConnection had an error " + ex.Message)
  18.         End Try
  19.  
  20.     End Function  Public Function LoadAffordabilityPercents(ByRef dr As SqlDataReader) As Boolean
  21.         Try
  22.            If dr.Read Then
  23.                 BasicSalaryPerc = CStr(dr.GetValue(0))
  24.                 OverTimePerc = CStr(dr.GetValue(1))
  25.                 RecurringAllowancesConfirmedPerc = CStr(dr.GetValue(2))
  26.                 NonRecurringAllowancesPerc = CStr(dr.GetValue(3))
  27.                 CommissionPerc = CStr(dr.GetValue(4))
  28.                 OtherIncomePerc = CStr(dr.GetValue(5))
  29.                 IncomeTaxPerc = CStr(dr.GetValue(6))
  30.                 PaySlipLoanRepaymentsPerc = CStr(dr.GetValue(7))
  31.                 PaySlipOtherDeductionsPerc = CStr(dr.GetValue(8))
  32.  
  33.                 CommitmentsNLRPerc = CStr(dr.GetValue(9))
  34.                 CommitmentsCCAPerc = CStr(dr.GetValue(10))
  35.                 LivingExpensesPerc = CStr(dr.GetValue(11))
  36.                 MaintenancePerc = CStr(dr.GetValue(12))
  37.                 MunicipalServicesPerc = CStr(dr.GetValue(13))
  38.                 OtherIncomeNotOnPaySlipPerc = CStr(dr.GetValue(14))
  39.                 DisplayPercents = CBool(dr.GetValue(15))
  40.                 RentPerc = CStr(dr.GetValue(16))
  41.                 MedicalPerc = CStr(dr.GetValue(17))
  42.                 WaterAndLightsPerc = CStr(dr.GetValue(18))
  43.                 EducationPerc = CStr(dr.GetValue(19))
  44.                 SavingsPerc = CStr(dr.GetValue(20))
  45.                 InsurancePerc = CStr(dr.GetValue(21))
  46.                 TransportPerc = CStr(dr.GetValue(22))
  47.                 PensionAndSavingsPerc = CStr(dr.GetValue(23))
  48.                 ActiveDebtCommitmentsPerc = CStr(dr.GetValue(24))
  49.                 OtherDeductionsNotOnPaySlipPerc = CStr(dr.GetValue(25))
  50.                 minLivingExpenses = CStr(dr.GetValue(26))
  51.  
  52.                 mvar_DataFetched = True
  53.             End If
  54.  
  55.             Return True
  56.         Catch ex As Exception
  57.             Throw New Exception("LoadAffordabilityPercents had an error " + ex.Message)
  58.             Return False
  59.         End Try
  60.     End Function
  61.  
  62.  
My issue is that BasicSalaryPerc needs to be global variables. I would like to pass a class as reference and use that class within LoadAffordabilityPercentsFromConnection. I don't like having tons of global variables but this is a very generric way of returing data from a database. Any advice
Nov 20 '08 #1
1 1507
Dököll
2,364 Expert 2GB
Hey there!

I could not figure out the problem, surely simple. Hoever I know of something that works, if you care to try it, or hang on, someone savvier can have a look for ya:

Expand|Select|Wrap|Line Numbers
  1.  
  2. (1) You'll need Microsoft DAO 3.6 reference
  3. (2) Connection to Microsoft Access database reference
  4.  
  5. 'this is searching for existing data in access database
  6.  
  7. Private Sub Seek_Click()
  8. Dim my_database As Database   
  9.  
  10. 'dimension database as database so program knows where to look for data
  11. Dim my_record As Recordset
  12. Dim test As String
  13. test = Text1(1).Text
  14. Set my_database = OpenDatabase("C:\DataGram\Data_Central.mdb")   
  15.  
  16. 'this function will open the database (provided that it is closed access)
  17. Set my_record = my_database.OpenRecordset("SELECT * FROM LIBRARY WHERE Your_Price ='" & Text1(0).Text & "'")    
  18.  
  19. ' this is used to search by name, only if data already exists
  20.    Do While Not my_record.EOF  'this function will keep searching for fields matching each textbox
  21.  
  22.         Text1(0).Text = my_record.fields("Your_Price")
  23.         Text1(1).Text = my_record.fields("Name")
  24.         Text1(2).Text = my_record.fields("Type")
  25.         Text1(3).Text = my_record.fields("Crime_Rate_1")
  26.         Text1(4).Text = my_record.fields("Crime_Rate_2")
  27.  
  28.    my_record.MoveNext
  29.    Loop
  30.    my_database.Close
  31. End Sub
  32.  
  33.  
Hope this helps!
Nov 24 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
2
by: clwoods | last post by:
I would like to ask is thier a better way to store data other than access tables, I would still like to keep access as the front end. My main quest I suppose is for more speed and maybe security....
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
10
by: Willem | last post by:
Looking for some opinions on alternatives to programming with Access. I find that quite often I need to loop through my recordsets (first to last) performing calculations and was wondering if...
0
by: bettervssremoting | last post by:
To view the full article, please visit http://www.BetterVssRemoting.com Better VSS Remote Access Tool including SourceOffSite, SourceAnyWhere and VSS Remoting This article makes a detailed...
43
by: Rob R. Ainscough | last post by:
I realize I'm learning web development and there is a STEEP learning curve, but so far I've had to learn: HTML XML JavaScript ASP.NET using VB.NET ..NET Framework ADO.NET SSL
0
by: D | last post by:
I am developing a reuseable data access library for Access, MSSQL, MySQL, Oracle and etc, for some of other projects. Initially I build up classes for each database, and a client application is...
0
by: dord | last post by:
I am developing a reuseable data access library for Access, MSSQL, MySQL, Oracle and etc, for some of other projects. Initially I build up classes for each database, and a client application is...
1
by: JustMe | last post by:
Hi all, Although I've got quite a lot of experience with php and (M/My)SQL, I only used both within an online php script. These last few months I bought an option with my webhost to access...
19
by: Mary Pegg | last post by:
There's got to be a better way: if (isset($c)) echo $c."<br>"; if (isset($c)) echo $c."<br>"; if (isset($c)) echo $c."<br>"; if (isset($c)) echo $c."<br>"; if (isset($c)) echo $c."<br>"; but...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.