Extracting numeric value with int

[Mikesinfo]

Hi everyone,

Hopefully this is the right forum to use.

I am programming in C# [Win forms] and I've been racking my brain and searching for the right answer to extract my information according to after a certain input date. the problem is that when I hard-code the numeric date, there is no problem at all and all requested information is displayed correctly. But when I try to pass the variable (parameter) as an integer as in the code that follows, the syntax generates this error "No value given for one or more required parameters".

Guess what I'm trying to say is what is the right code that I am missing to extract the tables. MS Access is the db and I am using C#. The fustrating Integer is "Start_Date ". There is also a blocked out code where I do have success with the hard-coded date.

Thank you all for any and all help.

public void Extract_Close_Table_All_By_Date(int Start_Date)
{
try
{
OleDbConnection DB_Connection = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\HOST 000.MDB");
DB_Connection.Open();
//TO CONTAIN RELATED DATA TABLES, ROWS, & COLUMNS
DataSet DB_DataSet = new DataSet();
//EXTRACT TABLE
OleDbDataAdapter Adapter_Zone = new OleDbDataAdapter("SELECT * FROM [CLOSED_TABLE_ZONE] WHERE [TABLE_START_DATE] >= Start_Date", DB_Connection);
//    OleDbDataAdapter Adapter_Zone = new OleDbDataAdapter("SELECT * FROM [CLOSED_TABLE_ZONE] WHERE [TABLE_START_DATE] = 20070423", DB_Connection);
Adapter_Zone.Fill(DB_DataSet, "CLOSED_TABLE_ZONE");
//EXTRACT TABLE ITEMS
OleDbDataAdapter Item_Zone = new OleDbDataAdapter("SELECT * FROM [CLOSED_TABLE_ITEMS]", DB_Connection);
Item_Zone.Fill(DB_DataSet, "CLOSED_TABLE_ITEMS");
//RELATION
DataRelation Table_Relations = DB_DataSet.Relations.Add("CLOSED_TABLE_ITEMS", DB_DataSet.Tables["CLOSED_TABLE_ZONE"].Columns["TABLE_ID"], 
DB_DataSet.Tables["CLOSED_TABLE_ITEMS"].Columns["TABLE_ID"],false);
//
Table_Relations.Nested = true;
 

[Plater]

Well it looks like you have some string creation issues.
In this line:
OleDbDataAdapte r Adapter_Zone = new OleDbDataAdapte r("SELECT * FROM [CLOSED_TABLE_ZO NE] WHERE [TABLE_START_DAT E] >= Start_Date", DB_Connection);
You are actually passing the WORD "Start_Date " instead of the value of Start_Date

[Mikesinfo]

Thank you for your reponse, but do you or anyone know where I can find the information to pass by value instead?

[vekipeki]

Why is your Start_Date an integer? Do you know how to get "20070423" from your int number?

E.g. if Start_Date was a DateTime variable, you could easily convert it:

DateTime Start_Date = new DateTime(2007, 04, 23);
String Start_Date_Text = Start_Date.ToString("yyyyMMdd");
 

If Start_Date is (int)20070423, then you can also write:

String Start_Date_Text = Start_Date.ToString();
 

[Mikesinfo]

Hi Vekipeki,

Unfortuntily I am struggling with trying to extract my number (dates) from the databes with my integers (Start_Date). I don't know the right syntax.

The reason that I made the value an integer is because of doing various date extractions. ie everything after and including that particular date (a range of dates). So that is why I made the MS database value as a number and not text and in the format in 20070423. This would be great if I could just hard code the extraction date, but the date would change upon every users query. Or even if I was looking for a name (string), but alas I am not.

If that makes sense.

[Plater]

Here is what I suggest:
http://www.google.com/search?source=...arch&aq=-1&oq=

[vekipeki]

@Mikesinfo
If this works:

String sqlQuery =
   "SELECT * FROM [CLOSED_TABLE_ZONE]
    WHERE [TABLE_START_DATE] = 20070423";

Then I don't see a reason why this shouldn't work:

String sqlQuery =
   "SELECT * FROM [CLOSED_TABLE_ZONE]
    WHERE [TABLE_START_DATE] = " + Start_Date.ToString();

Presuming that (Start_Date == 20070423).

Note that this is a quick way, but NOT a recommended (safe) way to do it (although if Start_Date is an integer then I cannot think of a possible injection attack). Check this for an example: http://www.csharp-station.com/Tutori.../Lesson06.aspx

Examples with hard-coded values are usually given for demonstration only - SQL queries are intended to be built in run-time.

[Mikesinfo]

Txs Plater,

I have been to some of the sites, and will look at more of the ones you linked through google, but the ones that I've previously been too ie .w3schools.com, when it comes to numeric values, they hard-code the values in:

Unfortunitly, I am not looking to hard code dates in because they do change per query. But once again, thank you & I'll keep searching the google link and hopefully come up the the solution.

This is correct:SELECT * FROM Persons WHERE Year=1965
This is wrong:SELECT * FROM Persons WHERE Year='1965'

[Mikesinfo]

Ahhhhh Vekipeki,

You did it, after all this head-aches you've done it, thank you very much. It was just the "+" that was missing. ie "+ Start_Date". You have made my day and much appreciated.

Unfortunitly all these hassles would be avoided if they only but real time solutions in these programming books.

thank you and all others once again
Mike

[vekipeki]

I recommend you read this article anyway, that is a proper way to do it.

Otherwise, simple concatenating strings might allow an attacker to insert his own SQL code and compromise your data. Check this link for an explanation: SQL Injection Attack.