A better way to access data in a database

[LearnerDave]

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:

[CODE=VB]
Delegate Function OutputDataReade r(ByRef dr As SqlDataReader) As Boolean

Public Overloads Function RunCMD(ByVal sSQLSTR As String, ByVal datafetch As OutputDataReade r) As Boolean

This is how we use it:

Public Function LoadAffordabili tyPercentsFromC onnection() As Boolean
Try
If Not mvar_DataFetche d Then
If Not DS.RunCMD("EXEC Get_df_Affordab ilityPercents", AddressOf LoadAffordabili tyPercents) Then
MsgBox("Problem fetching Affordability Percents!", MsgBoxStyle.Cri tical, "Object Build failure")
Exit Function
End If
End If

Catch ex As Exception
Throw New Exception("Load AffordabilityPe rcentsFromConne ction had an error " + ex.Message)
End Try

End Function Public Function LoadAffordabili tyPercents(ByRe f dr As SqlDataReader) As Boolean
Try
If dr.Read Then
BasicSalaryPerc = CStr(dr.GetValu e(0))
OverTimePerc = CStr(dr.GetValu e(1))
RecurringAllowa ncesConfirmedPe rc = CStr(dr.GetValu e(2))
NonRecurringAll owancesPerc = CStr(dr.GetValu e(3))
CommissionPerc = CStr(dr.GetValu e(4))
OtherIncomePerc = CStr(dr.GetValu e(5))
IncomeTaxPerc = CStr(dr.GetValu e(6))
PaySlipLoanRepa ymentsPerc = CStr(dr.GetValu e(7))
PaySlipOtherDed uctionsPerc = CStr(dr.GetValu e(8))

CommitmentsNLRP erc = CStr(dr.GetValu e(9))
CommitmentsCCAP erc = CStr(dr.GetValu e(10))
LivingExpensesP erc = CStr(dr.GetValu e(11))
MaintenancePerc = CStr(dr.GetValu e(12))
MunicipalServic esPerc = CStr(dr.GetValu e(13))
OtherIncomeNotO nPaySlipPerc = CStr(dr.GetValu e(14))
DisplayPercents = CBool(dr.GetVal ue(15))
RentPerc = CStr(dr.GetValu e(16))
MedicalPerc = CStr(dr.GetValu e(17))
WaterAndLightsP erc = CStr(dr.GetValu e(18))
EducationPerc = CStr(dr.GetValu e(19))
SavingsPerc = CStr(dr.GetValu e(20))
InsurancePerc = CStr(dr.GetValu e(21))
TransportPerc = CStr(dr.GetValu e(22))
PensionAndSavin gsPerc = CStr(dr.GetValu e(23))
ActiveDebtCommi tmentsPerc = CStr(dr.GetValu e(24))
OtherDeductions NotOnPaySlipPer c = CStr(dr.GetValu e(25))
minLivingExpens es = CStr(dr.GetValu e(26))

mvar_DataFetche d = True
End If

Return True
Catch ex As Exception
Throw New Exception("Load AffordabilityPe rcents had an error " + ex.Message)
Return False
End Try
End Function

[/CODE]

My issue is that BasicSalaryPerc needs to be global variables. I would like to pass a class as reference and use that class within LoadAffordabili tyPercentsFromC onnection. I don't like having tons of global variables but this is a very generric way of returing data from a database. Any advice

[Dököll]

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:

[CODE=VB]

(1) You'll need Microsoft DAO 3.6 reference
(2) Connection to Microsoft Access database reference

'this is searching for existing data in access database

Private Sub Seek_Click()
Dim my_database As Database

'dimension database as database so program knows where to look for data
Dim my_record As Recordset
Dim test As String
test = Text1(1).Text
Set my_database = OpenDatabase("C :\DataGram\Data _Central.mdb")

'this function will open the database (provided that it is closed access)
Set my_record = my_database.Ope nRecordset("SEL ECT * FROM LIBRARY WHERE Your_Price ='" & Text1(0).Text & "'")

' this is used to search by name, only if data already exists
Do While Not my_record.EOF 'this function will keep searching for fields matching each textbox

Text1(0).Text = my_record.field s("Your_Price ")
Text1(1).Text = my_record.field s("Name")
Text1(2).Text = my_record.field s("Type")
Text1(3).Text = my_record.field s("Crime_Rate_1 ")
Text1(4).Text = my_record.field s("Crime_Rate_2 ")

my_record.MoveN ext
Loop
my_database.Clo se
End Sub

[/CODE]

Hope this helps!