Datatable return null values

Question

2.00/5 (1 vote)

See more:

Hello Guys, i am selecting value from database but datatable return null values

What I have tried:

connection.Open();
            DataSet dsa1 = new DataSet();
            DataTable dt1 = new DataTable();
            dsa1.Tables.Add(dt1);
            OleDbDataAdapter da1 = new OleDbDataAdapter();
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                da1 = new OleDbDataAdapter("SELECT [Price] from [Purchase Record] where [Product Name] = '" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "'", connection);
                da1.Fill(dt1);
                dataGridView2.DataSource = dt1;
                connection.Close();
            }

Posted 2-Jan-17 14:23pm

Member 9983063

Updated 3-Jan-17 9:24am

Add a Solution

Comments

David_Wimbley 2-Jan-17 20:34pm

So what issue are you encountering with the null values from your DB?

Without much information, a simple solution would be to run update statements on your DB to prevent any null columns from being returned. EX: If Price IS NULL set Price == 0.

Another solution would be, based on the fact you are using an embedded query, use ISNULL([Price], 0) instead of just [Price].

Then the other thought is that your product name doesn't exist in your DB which is why you get nulls because you can't select something that doesn't exist.

See how not having enough information about your problem isn't very useful?

Member 9983063 2-Jan-17 20:38pm

my problem is i have 2 table in my database one is sale and another is purchase i want to show purchase price in sale form in c#

Arunprasath Natarajan 3-Jan-17 2:49am

Have you tried ADD

4 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)

K-SIS · Answer 1 · 2017-01-02T15:45:00

Solution 1

There is logical error.
1. You are Filling in a loop so after every fill it will wipe previous filled data. (Data is replaced not appended.)
2. connection is closed in loop so after first iteration the table object will be null.

Posted 2-Jan-17 15:45pm

K-SIS

Comments

Maciej Los 3-Jan-17 2:07am

Good catch!
5.

Member 9983063 3-Jan-17 5:01am

but i need datagridview all rows value i think it's can be done with loop if not so please suggest me how can i do this??

Pradeep Prasanna Ekanayaka · Answer 2 · 2017-01-03T06:36:00

your code is modified.. use this
this may be work for u

connection.Open();
            DataSet dsa1 = new DataSet();
            DataTable dt1 = new DataTable();
            dsa1.Tables.Add(dt1);
            OleDbDataAdapter da1 = new OleDbDataAdapter();
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                da1 = new OleDbDataAdapter("SELECT [Price] from [Purchase Record] where [Product Name] = '" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "'", connection);
                da1.Fill(dt1);
                int y=dataGridView1.Rows.Add();
                dataGridView1.Rows[y].Cells[0].Value=dt1.Rows[0].Field<double>(0);
                dt1.Clear();
                connection.Close();
            }

Wendelius · Answer 3 · 2017-01-03T09:24:00

I'm not sure if I understand the situation correctly, but...

As already pointed out, don't close the connection inside the loop, otherwise the second fetch will fail. Also if you constantly replace the datasource of the grid, only the last one is shown, all other fetches are in vain.

So this looks like you're trying to fill the data in the grid in two phases, first you fetch the initial set of data and the you try fetch prices for the rows previously fetched.

If this is the case, normally these are not done as separate operations. Instead the data is typically fetched in one call and a join is used to correctly fetch data from two tables at the same time. Have a look at Visual Representation of SQL Joins[^]

K-SIS · Answer 4 · 2017-01-03T03:06:00

First remove out connection.close out of loop
Try this construct in loop

INSERT INTO table1 / curser SELECT [Price] from [Purchase Record] where [Product Name] = '" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "'"

unless fields of table1 match with selected records this will not work.
This Construct will give desired result in loop with all required data.
Only Logic is posted not exact command hope this may help you.

Datatable return null values

4 solutions

Solution 1

Solution 3

Solution 4

Solution 2

Add your solution here

Preview 0