PDA Database questions

[m2m]

I am currently almost complete with a PDA database that i have written in VB 2008.. However 2 problems still persist, i will start with what i think is the easiest to resolve.:
my database consists of the following columns:

date

provider

charge

allowed amount

discount

applied to deductible

co-pay

yearly deductible

maximum

other

payment

,,,,,,its a very simple database but remember it is a compact database(it does not have all the functionality of the full blown version) anyway
what i am attempting to do is read the database with the following sql command:
Dim cmdPayment As New System.Data.SqlServerCe.SqlCeCommand("SELECT Provider,Date,Payment FROM InsuranceTracker2008 WHERE payment is NOT NULL", conn)


so what i want to do here is read the database when Payment is NOT NULL then insert the following into a listbox
date, provider,payment????????? this is where i am stuck
I do not know how to parse the data and insert into list box?????

Thanks a bunch
m2m

[Hack]

Try

Dim reader As SqlDataReader = cmdPayment.ExecuteReader()

        While reader.Read()
            ListBox1.Items.Add(reader.GetString(0) & " " & reader.GetString(1))           
        End While
    End Sub

The SqlDataReader you use might be something like SqlDataReaderCe.

[m2m]

I knew there was a good reason why I joined this forum:
as you mentioned the reader fro the compact database is
SqlCeDataReader!!
Any way i get the following erron on this line of code:

ListBox1.Items.Add(reader.GetString(0) & " " & reader.GetString(1))

"Invalid cast exception",
with this stack trace:

at System.Data.SqlServerCe.SqlCeDataReader.GetString(Int32 ordinal) at Insurance2008.Form1.TabControl1_SelectedIndexChanged(Object sender, EventArgs e) at System.Windows.Forms.TabControl.OnSelectedIndexChanged(EventArgs e) at System.Windows.Forms.TabControl.WnProc(WM wm, Int32 wParam, Int32 lParam) at System.Windows.Forms.Control._InternalWnProc(WM wm, Int32 wParam, Int32 lParam) at Microsoft.AGL.Forms.EVL.EnterMainLoop(IntPtr hwnMain) at System.Windows.Forms.Application.Run(Form fm) at Insurance2008.Form1.Main()


once again many thanks
m2m

[Hack]

the reader fro the compact database is
SqlCeDataReader!!

I was close!

Any way i get the following erron on this line of code:

ListBox1.Items.Add(reader.GetString(0) & " " & reader.GetString(1))

That tells me the item in question is a number, not a string. For strings, use Reader.GetString.

For numbers use

ListBox1.Items.Add(reader.GetInt32(0).ToString())

[m2m]

Thanks again i get the same error with the following:

ListBox1.Items.Add(reader.GetString(0) & " " & reader.GetInt32(1).ToString() & " " & reader.GetInt32(2).ToString())

the first "field" is a string(provider), the second a string(date??) and the third a number(double)

does that help???

thanks so much
m2m

[eclipsed4utoo]

Thanks again i get the same error with the following:

ListBox1.Items.Add(reader.GetString(0) & " " & reader.GetInt32(1).ToString() & " " & reader.GetInt32(2).ToString())

the first "field" is a string(provider), the second a string(date??) and the third a number(double)

does that help???

thanks so much
m2m

then your code needs to be...

ListBox1.Items.Add(reader.GetString(0) & " " & reader.GetString(1) & " " & reader.GetDouble(3).ToString);

you need to match the datatypes of the database fields to the function Get[Datatype]().

[m2m]

Thanks tried that still same error:
when i designed the database the first field provider was definatly a string(ntext)
the next field date was also a string(datetime)
and the other column payment (money) does that hep or make a difference here ?????????????


ListBox1.Items.Add(reader.GetString(0) & " " & reader.GetString(1) & " " & reader.GetDouble(2).ToString())


m2m

[m2m]

disregard i answered my own question
you all rock thanks for such great help!!!!!!!!!!!!!

[Hack]

What did you wind up with?

It could help someone else.

[m2m]

Sure anytime here it is this works fine:

ListBox1.Items.Add(reader.GetString(0) & "-- " & reader.GetDateTime(1) & "-- " & FormatCurrency(reader.GetSqlMoney(2).ToString()))

[Hack]

Whoa...GetSqlMoney - I didn't know that one was there.

I'm glad I asked you to post your solution. I might actually need that one sometime.

Thanks!