Retrieving Information From Database

[Lex@SG]

Hi, how do i retrieve information from database? If let's say my database has 3 tax rates, 1 in 2003, 1 in 2004 and 1 in 2010, how do i get the rate that is applicable at present? i.e omit the rate applicable in the past or future

[nemaroller]

Mechanics aside... let's nail down exactly what you want first.

From your post, its sounds like you want to keep a history of previous tax rates for some other purpose. So now, the real issue is you want your program to go to some table that has a list of tax rates, and pick the most current tax rate, correct?

IF so, lets get to the mechanics.
First you need to tell us which database your using, and based on that, we can then ask if you're using stored procedures...

The table itself would have a datetime stamp column(probably not autogenerated, since you would want the ability to put the tax rate for 2006 in before the present date is actually 2006), and also a TaxRate column.

[Lex@SG]

Mechanics aside... let's nail down exactly what you want first.

From your post, its sounds like you want to keep a history of previous tax rates for some other purpose. So now, the real issue is you want your program to go to some table that has a list of tax rates, and pick the most current tax rate, correct?

IF so, lets get to the mechanics.
First you need to tell us which database your using, and based on that, we can then ask if you're using stored procedures...

The table itself would have a datetime stamp column(probably not autogenerated, since you would want the ability to put the tax rate for 2006 in before the present date is actually 2006), and also a TaxRate column.

well, i'm using MS Access as my database... i dun have any stored procedures.. i just wish to noe how do i go about doing the sql statement because i need to retrieve the latest taxrate in my asp.net web app... it's a school assignment though.. but i've no idea how to do it...

[nemaroller]

The database stores the tax rates.

You need to create a connection to the database - System.Data.OleDb.OleDbConnection

You need to create a command that will execute within that connection.
System.Data.OleDb.OleDbCommand

You then need to set the properties of the command... like which connection it will use (the one you created obviously), what type of command it is (Text, since there are no stored procs in Access), and finally and most important, the sql syntax (i.e... "Select*from someTable") that will be run by the database.

Then you call the executeScalar method of the command object, which will return the first row of data (there is only one row returned because we are only going to ask for 1 value - the most current tax rate) - and we coerce this data into a Double variable.

Now, the Sql Syntax that we will assign to the CommandText property of the OleDbCommand object is obviously rather important, otherwise we will get errors, wrong data, or simply a big headache.

The syntax of the Sql statement depends on the structure of the table in the database. I assume there is probably one two columns in the table we want to inspect - TaxRateDate (the year that the tax rate corresponds to), and TaxRate (the actual rate itself).

But before we go further, are you writing in C# or VB?

[Lex@SG]

The database stores the tax rates.

You need to create a connection to the database - System.Data.OleDb.OleDbConnection

You need to create a command that will execute within that connection.
System.Data.OleDb.OleDbCommand

You then need to set the properties of the command... like which connection it will use (the one you created obviously), what type of command it is (Text, since there are no stored procs in Access), and finally and most important, the sql syntax (i.e... "Select*from someTable") that will be run by the database.

Then you call the executeScalar method of the command object, which will return the first row of data (there is only one row returned because we are only going to ask for 1 value - the most current tax rate) - and we coerce this data into a Double variable.

Now, the Sql Syntax that we will assign to the CommandText property of the OleDbCommand object is obviously rather important, otherwise we will get errors, wrong data, or simply a big headache.

The syntax of the Sql statement depends on the structure of the table in the database. I assume there is probably one two columns in the table we want to inspect - TaxRateDate (the year that the tax rate corresponds to), and TaxRate (the actual rate itself).

But before we go further, are you writing in C# or VB?

VB.net