By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Extracting numeric value with int

P: 6
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.
Expand|Select|Wrap|Line Numbers
  1. public void Extract_Close_Table_All_By_Date(int Start_Date)
  2. {
  3. try
  4. {
  5. OleDbConnection DB_Connection = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\HOST 000.MDB");
  6. DB_Connection.Open();
  7. //TO CONTAIN RELATED DATA TABLES, ROWS, & COLUMNS
  8. DataSet DB_DataSet = new DataSet();
  9. //EXTRACT TABLE
  10. OleDbDataAdapter Adapter_Zone = new OleDbDataAdapter("SELECT * FROM [CLOSED_TABLE_ZONE] WHERE [TABLE_START_DATE] >= Start_Date", DB_Connection);
  11. //    OleDbDataAdapter Adapter_Zone = new OleDbDataAdapter("SELECT * FROM [CLOSED_TABLE_ZONE] WHERE [TABLE_START_DATE] = 20070423", DB_Connection);
  12. Adapter_Zone.Fill(DB_DataSet, "CLOSED_TABLE_ZONE");
  13. //EXTRACT TABLE ITEMS
  14. OleDbDataAdapter Item_Zone = new OleDbDataAdapter("SELECT * FROM [CLOSED_TABLE_ITEMS]", DB_Connection);
  15. Item_Zone.Fill(DB_DataSet, "CLOSED_TABLE_ITEMS");
  16. //RELATION
  17. DataRelation Table_Relations = DB_DataSet.Relations.Add("CLOSED_TABLE_ITEMS", DB_DataSet.Tables["CLOSED_TABLE_ZONE"].Columns["TABLE_ID"], 
  18. DB_DataSet.Tables["CLOSED_TABLE_ITEMS"].Columns["TABLE_ID"],false);
  19. //
  20. Table_Relations.Nested = true;
  21.  
Feb 12 '09 #1
Share this Question
Share on Google+
10 Replies


Plater
Expert 5K+
P: 7,872
Well it looks like you have some string creation issues.
In this line:
OleDbDataAdapter Adapter_Zone = new OleDbDataAdapter("SELECT * FROM [CLOSED_TABLE_ZONE] WHERE [TABLE_START_DATE] >= Start_Date", DB_Connection);
You are actually passing the WORD "Start_Date" instead of the value of Start_Date
Feb 12 '09 #2

P: 6
Thank you for your reponse, but do you or anyone know where I can find the information to pass by value instead?
Feb 13 '09 #3

vekipeki
Expert 100+
P: 229
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:
Expand|Select|Wrap|Line Numbers
  1. DateTime Start_Date = new DateTime(2007, 04, 23);
  2. String Start_Date_Text = Start_Date.ToString("yyyyMMdd");
  3.  
If Start_Date is (int)20070423, then you can also write:
Expand|Select|Wrap|Line Numbers
  1. String Start_Date_Text = Start_Date.ToString();
  2.  
Feb 13 '09 #4

P: 6
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.
Feb 13 '09 #5

Plater
Expert 5K+
P: 7,872

vekipeki
Expert 100+
P: 229
@Mikesinfo
If this works:

Expand|Select|Wrap|Line Numbers
  1. String sqlQuery =
  2.    "SELECT * FROM [CLOSED_TABLE_ZONE]
  3.     WHERE [TABLE_START_DATE] = 20070423";
Then I don't see a reason why this shouldn't work:

Expand|Select|Wrap|Line Numbers
  1. String sqlQuery =
  2.    "SELECT * FROM [CLOSED_TABLE_ZONE]
  3.     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.
Feb 13 '09 #7

P: 6
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'
Feb 13 '09 #8

P: 6
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
Feb 13 '09 #9

vekipeki
Expert 100+
P: 229
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.
Feb 13 '09 #10

P: 6
Thanks Vekipeki will do.

Mike
Feb 13 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.