Need help with declaring access fields as strings

M

mliles

New member
Joined
Apr 12, 2005
Messages
3
Hi,

Im having a problem with a program Im trying to write. What i want to do is read a database and where the date is in a specified range add the donation amount. I have 2 types of donations, i want my program to look at which donation type it is and then add it to a total. What im having a problem with is trying to set the data fields = to a string. I may be going about this all wrong, here is exact error i get

An unhandled exception of type System.InvalidCastException occurred in microsoft.visualbasic.dll

Additional information: Cast from type DBNull to type String is not valid


and heres the snipet of code.

myOleDbDataAdapter.Fill(myDataSet, "Donations")
myDataTable = myDataSet.Tables("Donations")
Dim rowDonations As DataRow = myDataTable.NewRow
Resources = rowDonations("Resources Cash/In-Kind")
datemade = rowDonations("Date Donation Made")
donationsamount = rowDonations("Donation Amount")

With myDataSet.Tables
While Not EOF(1)
Input(1, "Resources Cash/In-Kind")
Input(1, "Date Donation Made")
Input(1, "Donation Amount")

If Resources = "Resources In-Kind" Then
donationsinkindtotal = donationsinkindtotal + donationsamount
Else
donationscashtotal = donationscashtotal + donationsamount
End If

End While
End With

The Red line is where the error is given. Ive been playing with it and searching around the web to try and figure this out but im stumped now. Any help would be much appreciated.

Michelle
 
From first look, it appears that you are getting back a Null value from the database for the Resources Cash/In-Kind field. I would run the query in Access or SQL Query Analyzer (not sure what DB you are using) and look to see if there is a Null value.

To handle the Null value, I would write a function similiar to
Code: 
Function CheckNull(ByVal inVal) as String
  If IsNull(inVal) Then
    Return ""
  Else
    Return inVal
  End If
End Function

Its been a while since I wrote something like that in VB.NET, but that should get you close.

Have you considered adjusting your query so that the DB does the work for you? I would recommend using a query similiar to:

Select [Resources Cash/In-Kind], Sum([Donation Amount]) FROM TableName WHERE [Resources Cash/In-Kind] IN ("Resources In-Kind", "Resources Cash") GROUP BY [Resources Cash/In-Kind]

That should return you return you something like:
Resources Cash 110,000.00
Resources In-Kind 100,000.00
 
Aspnot said:
Select [Resources Cash/In-Kind], Sum([Donation Amount]) FROM TableName WHERE [Resources Cash/In-Kind] IN ("Resources In-Kind", "Resources Cash") GROUP BY [Resources Cash/In-Kind]
Click to expand...

is this how you could put it in as .NET code? Ive been messing with this and have done some searches on how to use SQL statments in .net as code, but im still at a loss. Now when i run my program i get no errors but the report that is saved shows my SQL code.

Code: 
Dim inkindSQL As String = "SELECT *, SUM Donation Amount FROM Donations WHERE Resources Cash/In-Kind = Resources In-Kind AND Date Donation Made BETWEEN report_start AND report_end"

Dim cashSQL As String = "SELECT *, SUM Donation Amount FROM Donations WHERE Resources Cash/In-Kind = Resources Cash AND Date Donation Made BETWEEN report_start AND report_end"

i know its printing the code because its in " " but i tried your example above and it didnt work and in other examples i found they show to use the " " i have it doing it twice because i couldnt figure out how to get each total out to print it in the spot i wanted. Anymore help would be much appreciated.
 
I am not sure of exactly what you are going to do with the dataset once you have it back, so this follows the example you were using above.

Code: 
    Dim myOleDbConnection As Data.OleDb.OleDbConnection
    Dim myOleDbDataAdapter As Data.OleDb.OleDbDataAdapter
    Dim myOleDbCommand As Data.OleDb.OleDbCommand
    Dim myDataSet As DataSet
    Dim myDataTable As DataTable

    Dim inkindSQL As String = "SELECT *, SUM([Donation Amount]) FROM Donations WHERE [Resources Cash/In-Kind] = Resources In-Kind AND [Date Donation Made] BETWEEN report_start AND report_end"
    Dim cashSQL As String = "SELECT *, SUM([Donation Amount]) FROM Donations WHERE [Resources Cash/In-Kind] = Resources Cash AND [Date Donation Made] BETWEEN report_start AND report_end"

    With myOleDbConnection
      .ConnectionString = "Connection String Text"
      If .State = ConnectionState.Closed Then .Open()
    End With

    With myOleDbCommand
      .CommandText = inkindSQL
      .CommandType = CommandType.Text
      .Connection = myOleDbConnection
    End With

    myOleDbDataAdapter.SelectCommand = myOleDbCommand
    myOleDbDataAdapter.Fill(myDataSet)


If you are only going to read the data, I would remove the dataset and the dataadapter and go with a OleDbDataReader and replace the block above with

Code: 
    With myOleDbCommand
      .CommandText = inkindSQL
      .CommandType = CommandType.Text
      .Connection = myOleDbConnection
      myOleDbDataReader = .ExecuteReader
    End With

If you could use all of the data in one read from the database, I would look into the earlier example I gave with the Group By clause in it. With this, you would have to run it twice for each SQL string you made.
 
ok thanks. I will mess with it some more, what i need to do is get the totals of in-kind donations and totals of cash donations and have them print in a certain spot on a report.
 
Back
Top