Add a query in crystal report.

[domingo]

hi,

I found on the net that to make a query one needs to create a dataset. therefore i have created a datatable to this dataset with 3 columns into into. Now i have created a query to display the information according to the query. the problem is that i cannot find have to fill the datacolumns with the value of the datareader.

DatabaseConnection.conn.Open();
            cmd = new SqlCommand("Select Username,Password FROM tblUser where Username = 'asd'",DatabaseConnection.conn);
            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                ds.Receipt_No.dcUserColumn.DefaultValue = rdr[0].ToString();
            }
            rdr.Close();
            SqlDataAdapter da = new SqlDataAdapter(sql,DatabaseConnection.conn);
            da.Fill(ds, "Product");
            MessageBox.Show (ds.Tables[1].Rows.Count.ToString());
            DatabaseConnection.conn.Close();
            CrystalReport1 rcp = new CrystalReport1();
            rcp.SetDataSource(ds.Tables[1]);
            crystalReportViewer1.ReportSource = rcp;
            crystalReportViewer1.Refresh();

[kvprajapati]

rep.SetDataSource(ds);

[domingo]

thanks for the fast reply but i want to edit the data column value of the dataset. is it possible?

[kvprajapati]

>i want to edit the data column value of the dataset. is it possible?

Yes.

ds.Tables["Product"].Rows[0]["Put_columnName"]=value_here;
rep.SetDataSource(ds);

[domingo]

still not working

[sknake]

>> the problem is that i cannot find have to fill the datacolumns with the value of the datareader.

I don't understand what you're trying to do. If you are only using a single table then consider using a DataTable over a DataSet. Regarding the data reader -- are you wanting to load the contents of the entire data reader in to a data table, or do it column by column? Here are two ways to use the data reader:

const string connStr = "Data Source=apex2010sql;Initial Catalog=master;Integrated Security=True;";
    const string query = "Select * From sys.databases";

    private static DataTable LoadEntireQuery()
    {
      DataTable result;
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlTransaction txn = conn.BeginTransaction())
        {
          using (SqlCommand cmd = conn.CreateCommand())
          {
            cmd.CommandText = query;
            cmd.Transaction = txn;
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
              result = new DataTable();
              result.Load(dr);
            }
          }
        }
        conn.Close();
      }
      return result;
    }

    private static DataTable LoadEntireQueryByHand()
    {
      DataTable result;
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlTransaction txn = conn.BeginTransaction())
        {
          using (SqlCommand cmd = conn.CreateCommand())
          {
            cmd.CommandText = query;
            cmd.Transaction = txn;
            //Note the command behavior. SchemaOnly loads the schema (columns) but no rows
            using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly)) 
            {
              result = new DataTable();
              result.Load(dr); //This loads the schema (columns) but no rows
            }
          }
          using (SqlCommand cmd = conn.CreateCommand())
          {
            cmd.CommandText = query;
            cmd.Transaction = txn;
            //Forward read-only access for performance
            using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) //Note the command behavior
            {
              while (dr.Read())
              {
                object[] colData = new object[result.Columns.Count];
                int colsRead = dr.GetValues(colData);
                System.Diagnostics.Debug.Assert(colsRead == result.Columns.Count);
                result.Rows.Add(colData);
              }
            }
          }
        }
        conn.Close();
      }
      return result;
    }

    private void button1_Click(object sender, EventArgs e)
    {
      DataTable dt1 = LoadEntireQuery();
      DataTable dt2 = LoadEntireQueryByHand();
    }

[domingo]

thanks solved my problem