Problem in query run in datagridview C# winform

Question

1.00/5 (1 vote)

See more:

i try query in DataGridView in C#
but this query Not Work Properly i think problem in my String
Because When is use this

C#

string DataLoad = "select cu.Date, ln.SrNumber, cu.Name+' / '+cu.FatherName AS \"Details\", cu.Address+' , '+cu.Address1 AS Address, cu.MobileNo+' , '+cu.SecMobileNo AS Contect, ln.SrNumber,  ln.PaymentDate, ln.Amount  from Payment ln inner join customer cu on ln.SrNumber=cu.SrNumber where YEAR(cu.Date) =2020 and MONTH(cu.Date)=02";

this Query Work fine

What I have tried:

C#

private void LoadDataMonth()
        {
            try
            {
                string[] Data1 = MonthlyReport.setmonth.Split('/');
                string montsPart = Data1[0];
                string yearPart = Data1[1];
                textBox2.Text = montsPart;
                textBox3.Text = yearPart;
                string DataLoad = "select cu.Date, ln.SrNumber, cu.Name+' / '+cu.FatherName AS \"Details\", cu.Address+' , '+cu.Address1 AS Address, cu.MobileNo+' , '+cu.SecMobileNo AS Contect, ln.SrNumber,  ln.PaymentDate, ln.Amount  from Payment ln inner join customer cu on ln.SrNumber=cu.SrNumber where YEAR(cu.Date) ='" + textBox3.Text + "' and MONTH(cu.Date)='" + textBox2.Text + "'";
                
                String connstring = ConfigurationManager.ConnectionStrings["Data"].ConnectionString;
                using (OleDbConnection con = new OleDbConnection(connstring))
                {
                    con.Open();
                    using (OleDbDataAdapter da = new OleDbDataAdapter(DataLoad, con))
                    {
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;

                    }
                }
            }
            catch
            {
                MessageBox.Show("Data Not Found");
            }
        }

Posted 3-Jun-20 6:07am

Amar chand123

Updated 3-Jun-20 14:58pm

Rick York

v3

Add a Solution

2 solutions

Solution 2

print out the query you are building and run it manually to see if it is formatting right

Posted 3-Jun-20 9:20am

RmcbainTheThird

Comments

Dave Kreskowiak 3-Jun-20 21:59pm

First things first. Fix the "use parameters instead of string concatentation" problem and just about all of the OP's problem go away.

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)

Richard Deeming · Accepted Answer · 2020-06-03T06:49:00

Don't do it like that!

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

C#

private void LoadDataMonth()
{
    try
    {
        string[] Data1 = MonthlyReport.setmonth.Split('/');
        string montsPart = Data1[0];
        string yearPart = Data1[1];
        textBox2.Text = montsPart;
        textBox3.Text = yearPart;
        
        const string query = "select cu.Date, ln.SrNumber, cu.Name+' / '+cu.FatherName AS \"Details\", cu.Address + ' , ' + cu.Address1 AS Address, cu.MobileNo + ' , ' + cu.SecMobileNo AS Contect, ln.SrNumber, ln.PaymentDate, ln.Amount from Payment ln inner join customer cu on ln.SrNumber = cu.SrNumber where YEAR(cu.Date) = @Year and MONTH(cu.Date) = @Month";
        
        string connstring = ConfigurationManager.ConnectionStrings["Data"].ConnectionString;
        using (OleDbConnection con = new OleDbConnection(connstring))
        using (OleDbDataAdapter da = new OleDbDataAdapter(query, con))
        {
            da.SelectCommand.Parameters.AddWithValue("@Year", yearPart);
            da.SelectCommand.Parameters.AddWithValue("@Month", montsPart);
            
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Data Not Found - " + ex.Message);
    }
}

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]