Need help with declaring access fields as strings

[mliles]

Hi,

 

Im having a problem with a program I'm 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

[Aspnot]

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

Function CheckNull(ByVal inVal) as String
 If IsNull(inVal) Then
   Return ""
 Else
   Return inVal
 End If
End Function

 

It's 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

[mliles]

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

 

is this how you could put it in as .NET code? I've 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.

 

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.

[Aspnot]

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.

 

   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

 

   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.

[mliles]

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.