Return null values from database

Question

0.00/5 (No votes)

See more:

Hello Guys am select a data from database and second time when i select data from with where clause dt1 so i get return null values am not getting any error but it's return me null values

What I have tried:

con_string.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source =|DataDirectory|\Restaurant.accdb;Persist Security Info=False";
            con_string.Open();
            DataSet dsa2 = new DataSet();
            DataTable dt2 = new DataTable();
            dsa2.Tables.Add(dt2);
            OleDbDataAdapter da2 = new OleDbDataAdapter();
            da2 = new OleDbDataAdapter(string.Format("SELECT column2 FROM Total  Where [Date] between #{0}# AND #{1}#  Group By column2", dateTimePicker1.Text, dateTimePicker2.Text), con_string);
            da2.Fill(dt2);
            con_string.Close();
            //--------------------------------------------------------------------------------
            con_string.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source =|DataDirectory|\Restaurant.accdb;Persist Security Info=False";
            con_string.Open();
            DataSet dsa = new DataSet();
            DataTable dt1 = new DataTable();
            dsa.Tables.Add(dt1);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da = new OleDbDataAdapter(string.Format(string.Format("SELECT column2,Sum(MediumVal) As [Dine In], Sum(LargeVal) As [Deliery], Sum(RoyalVal) As [Take Away] From ( SELECT column2 As [column2], Switch(column3 like 'DineIn%', 1,True,0) As [MediumVal], Switch(column3 like 'Delivery%',1,True,0) As [LargeVal], Switch(column3 like 'TakeAway%', 1,True,0) As [RoyalVal] FROM Total  Where [Date] between #{{0}}# AND #{{1}}# AND [column2] IN('{0}') ) Group By column2", dt2.Columns["column2"]), dateTimePicker1.Text, dateTimePicker2.Text), con_string);
            da.Fill(dt1);
            dataGridView1.DataSource = dt1;
            con_string.Close();
            dataGridView1.Columns[0].Width = 286;
            dataGridView1.Columns[1].Width = 180;
            dataGridView1.Columns[2].Width = 180;
            dataGridView1.Columns[3].Width = 180;

Posted 3-Jul-17 7:05am

Member 9983063

Updated 3-Jul-17 9:03am

Add a Solution

Comments

[no name] 3-Jul-17 13:55pm

If you are getting null from the database, did you validate if the database has the values?

Member 9983063 3-Jul-17 14:25pm

db has values

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-07-03T08:11:00

And the reason is either the date formats don't match for whatever reason, so SQL is getting them wrong, or there is no data which falls in the range.

Start by not concatenating strings to form SQL commands: use a parameterised query and past the DateTimePicker DateTime values directly instead of converting them to strings first. That way, the actual DateTime is used by SQL without any risk of misinterpretation when the string is processed. You should never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

Then check your DB: make sure you are storing the [Date] column as a DATE, DATETIME, or DATETIME2 value, not as a string. Yes, yes - I know. But you'd be surprised how many people do store dates as NVARCHAR and then act all surprised when comparisons like BETWEEN use NVARCHAR comparison rules not DATE and they don't get the results they wanted.

Once that's all checked, use the debugger to look at the date ranges you are passing: SQL / Access will not "reorder" the start and end to make them sensible, so if the later date is first, you won't get any results. If that's all fine, look directly at the database table and manually verify that dates in that range do really exist.

Sorry, but we can't do any of that for you!

Bryian Tan · Answer 2 · 2017-07-03T09:03:00

Solution 2

Part of this query could be the issue. The code have double curly brackets instead of one.

SQL

FROM Total  Where [Date] between #{{0}}# AND #{{1}}# AND [column2]

It should be

SQL

FROM Total  Where [Date] between #{0}# AND #{1}# AND [column2]

Posted 3-Jul-17 9:03am

Bryian Tan

Updated 3-Jul-17 9:05am

v2