retrieve data from sql to datagrid view

Question

this code wont run it supposed to fill the datagridview some data but all i got was the rows of how many data was retrieve but no values on each cells

    public void refDGV()
    {

        con.OpenConnections();
        SqlCommand cmd = new SqlCommand();
        SqlConnection cn = new SqlConnection();
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.Text;
        cn.ConnectionString = con.connections1;
        cmd.CommandText = "Select  dtransdate, ntotal_pass, nincomeday, ndiesel_exp,  nstartkm, nendkm  from ROUTE2 where ccontrol_no = '" +txtCN.Text + "'";
        da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ds = new DataSet();
        da.Fill(ds, "Data");
        dgroute.DataSource = ds;
        dgroute.DataMember = "Data";
       dgroute.Columns[0].HeaderText = "Date";
       dgroute.Columns[1].HeaderText = "Total Passenger";
       dgroute.Columns[2].HeaderText = "Income Day";
       dgroute.Columns[3].HeaderText = "Diesel (w/ reciept)";
       dgroute.Columns[4].HeaderText = "Start";
       dgroute.Columns[5].HeaderText = "End";




    }

Try removing "Data" from this line it's not a DataTable it's a DataSet da.Fill(ds, "Data"); also are you Debugging this code or just running it ?? — MethodMan
– MethodMan, Commented Dec 5, 2012 at 4:23

MethodMan · Accepted Answer · 2012-12-05 04:33:32Z

3

you could always make a DataBase Class and if you need to refactor this Class to pass in Connection String or read Connection string from .Config File you can use this as a template to get started plus it's a lot cleaner Notice that I am returning a DataTable you can use this if you like just a suggestion

public class ClassDataManagement 
{ 
   public DataTable GetData(string sqlcmdString, string connString)
   {
       SqlConnection con = new SqlConnection(connString);
       SqlCommand cmd = new SqlCommand(sqlcmdString, cn);
       SqlDataAdapter da = new SqlDataAdapter(cmd);
       con.Open();
       DataTable dt = new DataTable();
       da.Fill(dt);
       return dt;
   } 
}

if you want to use DataSet instead of DataTable replace where i have DataTable with or change the method to return a DataSet like this below

   public DataSet GetData(string sqlcmdString, string connString)
   {
       SqlConnection con = new SqlConnection(connString);
       SqlCommand cmd = new SqlCommand(sqlcmdString, cn);
       SqlDataAdapter da = new SqlDataAdapter(cmd);
       con.Open();
       DataSet ds = new DataSet();
       da.Fill(ds);
       return ds;
   }

after returning the ds you will need to bind it like this

   dgroute.DataSource = ds;
   dgroute.DataBind();

also for you dgroute Header I think the code should read like this when assigning Data to the ColumnHeader

   dgroute.Rows[0].Column["Heading"] = "Date";
   dgroute.Rows[1].Column["Heading"] = "Total Passenger";
   dgroute.Rows[2].Column["Heading"] = "Income Day";
   dgroute.Rows[3].Column["Heading"] = "Diesel (w/ reciept)";
   dgroute.Rows[4].Column["Heading"] = "Start";
   dgroute.Rows[5].Column["Heading"] = "End";

**Refactor your Sql Select Statement as well to allow for Parameters you are setting yourself up for SQL Injection just a constructive suggestion

edited Dec 5, 2012 at 4:33

answered Dec 5, 2012 at 4:21

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user974015 Over a year ago

thanks for the help...it help a lot.. and also your explanation very clear.. :D

MethodMan Over a year ago

If this works please provide an UpVote thanks ..glad that I was able to provide you with some help

MethodMan Over a year ago

user974015 there is a DataBind() method I need to correct the typo.. sorry

Cocoa Dev Over a year ago

Which way is better? DataTable or DataSet?

MethodMan Over a year ago

Cocoa Dev I think it boils down to personal preference and need.. personally I like to use List<T> Collection to bind data to a datagridview as well as dynamically building / binding on DataBound Event.. there are so many different ways to do this.. I have shown the OP 2 options based on their existing code.

|

Collectives™ on Stack Overflow

retrieve data from sql to datagrid view

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related