Displaying result of query

Question

I am trying to display the data in the datagridview in windows form.

I have following code to fetch data from the database.

    public DataSet GetUser(string custName)
    {
        string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Amrit\\Desktop\\Database.accdb ;Persist Security Info=False;";
        DataSet dataSet = new DataSet();
        OleDbConnection oleConn = new OleDbConnection(connString);

        try
        {
            oleConn.Open();
            string sql = "SELECT * FROM [Customer] WHERE [Customer's Ebayname]=" + custName;
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, oleConn);
            dataAdapter.Fill(dataSet, "Customer");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
        finally
        {
            oleConn.Close();
        }
        if (dataSet.Tables.Count <= 0)
            return null;
        else
            return dataSet;
    }

Above I am passing the value of custName from the textBox in the windows form. This will display all the rows containing that name.

Here, how I am trying to display the data.

    private void button1_Click(object sender, EventArgs e)
    {
        DataSet ds = GetUser(textBox1.Text);
        dataGridView1.DataSource = ds;
    }

Can somebody point out where I am wrong?

score 2 · Answer 1 · edited May 23 '17 at 11:56

First you need to check if your Dataset contains any result for you command. Then do not use string concatenation in that way to build command, but always parametrized queries. This will allow to avoid Sql Injections and parsing problems on string, dates and numeric decimals.

Have you tried what happens when your custName variable contains a single quote?

....
using(OleDbConnection oleConn = new OleDbConnection(connString))
{
    try
    {
        oleConn.Open();
        string sql = "SELECT * FROM [Customer] WHERE [Customer's Ebayname]=@cust";
        OleDbCommand cmd = new OleDbCommand(sql, oleConn);
        cmd.Parameters.AddWithValue("@cust", custName);
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
        dataAdapter.Fill(dataSet, "Customer");
    }
}
....

score 1 · Answer 2 · answered Feb 13 '13 at 15:52

1

Bind your DataGirdView to DataTable returned.

private void button1_Click(object sender, EventArgs e)
{
    DataSet ds = GetUser(textBox1.Text);
    if (ds == null)
    {
         return;
    }
    dataGridView1.DataSource = ds.Tables["Customer"];
}

answered Feb 13 '13 at 15:52

gzaxx

17,312
2
36
54

i have already tried that and its throwing me null reference exception – Amrit Sharma Feb 13 '13 at 15:54
when i put break point at dataGridView1.DataSource = ds.Tables["Customer"];, it doesnot get hit, even though the value i enter exist in database – Amrit Sharma Feb 13 '13 at 16:07

spajce · Accepted Answer · 2013-02-13T16:12:12.673

0

How about return the Function as DataTable?

public DataTable GetUser(string custName)
{
   //..codes
   return dataSet.Tables["Customer"];
}

and I suspect this is your problem

string sql = "SELECT * FROM [Customer] WHERE [Customer's Ebayname]=" + custName;

notice that the custName the DataType is Text and your Where clause something's not right so its should be like

string sql = "SELECT * FROM [Customer] WHERE Ebayname = '" + custName + "'";

P.S.: Don't forget to follow the advice of Mr. Steve

edited Feb 13 '13 at 16:12

answered Feb 13 '13 at 15:58

spajce

7,044
5
29
44

what do you mean mr. steve? – spajce Feb 13 '13 at 16:18
If custName contains a single quote the sql is invalid and you get a syntax error. Do not SUGGEST to use string concatenation. NEVER. – Steve Feb 13 '13 at 16:21
1

if you mean about the single quote.. then try it, i just looking straight about OP's problem. sometimes OPs not concerned about the `Parameters` because that's the way their teacher teaches on how to manipulating the data(if am correct) they teach as simple as they can. i hope you understand. `:D` – spajce Feb 13 '13 at 16:32
1

No, I don't understand how it is possible to 'teach' a very bad practice and why to perpetuate the error on a website that is supposed to give correct answers. Question closed for me. – Steve Feb 13 '13 at 16:42
yes i know that.. why I mentioned your name in my answer because for that matter of `sql injection`. about the __bad practice__ some teacher are not so expert to teach _or_ concerned about `sql injection`.. so that is why every day like this question is appear has no `parameters` i hope you get what i mean `:)` – spajce Feb 13 '13 at 16:50

Displaying result of query

3 Answers3