CODEWITHSUNDEEP
c#sql-server-2008datagridview
ZARGHAM NAZEERMALIK
ZARGHAM NAZEERMALIK

Reputation: 3

Show data in DataGridView from stored procedure in Windows Forms application in C#

I use the following code but it throws an error on SqlDataReader:

SqlConnection cn = new SqlConnection(@"Data Source=.;Initial Catalog=Employee1;Persist Security Info=True;User ID=sa;Password='786'");

SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.Date";
cmd.Parameters.AddWithValue("@DOB", Convert.ToDateTime(textBox1.Text));

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Employee ID", typeof(string)), new DataColumn("Employee Name", typeof(string)), new DataColumn("Status",typeof(char)) });
dt.Rows.Add();

try
{
    cn.Open();
    SqlDataReader res;
    res = cmd.ExecuteReader();
    MessageBox.Show("COmmand Executed Successfully");

    int i = 0;

    if (res.Read())
    {
        dt.Rows.Add(res[i].ToString(), res[++i].ToString(), res[++i].ToString());
    }

    res.Close();
    cn.Close();

    dataGridView1.DataSource = dt;
}
catch (Exception exp)
{
    MessageBox.Show(exp.StackTrace);
}

Exception is:

at System.Data.Sqlaient.Sq1Connection.OnError(SqlException exception, Boolean breakConnection, Action'1 wrapCloselnAction) at System.Data.Sq1Client.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action'1 wrapCloselnAction) at System.Data.Sq1Client.TdsParser.ThrowExceptionAndWarning(TdsParserStateObje ct stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqIClient.TdsParser.TryRun(RunBehavior runBehavior, SqICommand cmdHandler, SqIDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.Sq1Client.Sq1DataReader.TryConsumeMetaData() at System.Data.Sq1Client.Sq1DataReader.get_MetaData() at System.Data.Sq1Client.Sq1Command.FinishExecuteReader(Sq1DataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.Sq1Client.Sq1Command.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqIDataReader ds, Boolean d escri beParameterEncryptionRequest) at System.Data.Sq1Client.Sq1Command.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSourcel completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.Sq1Client.Sq1Command.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.Sq1Client.Sq1Command.ExecuteReader(CommandBehavior behavior, String method) at System.Data.Sq1Client.Sq1Command.ExecuteReader0 at Attendance.Form4.buttonS_Click(Object sender, EventArgs e) in c:\Users\admin\Desktop\Attendance\Attendance\Form4.cs:line 72

Upvotes: 0

Views: 1443

Answers (1)

Gregg
Gregg

Reputation: 625

First of all remove the single quotes from the password. Secondly when adding your rows, when using res[i] i is the index of the column so there is no need to increment this value. You could also use using blocks to close your connection and dispose of object within this loop. You also do not need dt.Rows.Add() because that is used to add rows not columns. Here is a refactoring of your method:

try
  {
      using (SqlConnection cn = new SqlConnection(@"Data Source=.; Initial Catalog=Employee1;Persist Security Info=True;User ID=sa;Password=786"))
      {
          using (SqlCommand cmd = cn.CreateCommand())
          {
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.CommandText = "dbo.Date";
              cmd.Parameters.AddWithValue("@DOB", Convert.ToDateTime(textBox1.Text));

              using (DataTable dt = new DataTable())
              {
                  dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Employee ID", typeof(string)), new DataColumn("Employee Name", typeof(string)), new DataColumn("Status", typeof(char)) });


                  cn.Open();

                  using (SqlDataReader res = cmd.ExecuteReader())
                  {
                      MessageBox.Show("COmmand Executed Successfully"); 


                      while(res.Read())
                      {                              
                          dt.Rows.Add(res[0].ToString(), res[1].ToString(), res[2].ToString());
                      }                                                  

                      dataGridView1.DataSource = dt;
                  }
              }
          }
      }
  }
  catch (Exception exp)
  {
      MessageBox.Show(exp.StackTrace);
  }

Upvotes: 0

Related Questions