Member Avatar for mwaqas1990

Actually i want data from database in DataGridView, OnLoad of Form. I have write this code but this is not working. I have checked that connection established correclty and query is also wrtie but where is problem?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;

namespace DISM_Project1
{
    public partial class AvalForm : Form
    {
        private SqlConnection xSqlConnection;
        private SqlCommand xSqlCommand;
        private SqlDataAdapter xSqlDataAdapter;

        public AvalForm()
        {
            InitializeComponent();
        }

        private void AvalForm_Load(object sender, EventArgs e)
        {
            try
            {
                //Making Connection
                xSqlConnection = new SqlConnection("Data Source=WaQaS-PC;Initial Catalog=BookingSystem;User ID=sa;Password=123");
                xSqlConnection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("" + ex);
            }
            xSqlCommand = new SqlCommand("Select Pno, PStatus from Unit1_Table where PStatus = 'Availible' UNION Select Pno, PStatus from Unit2_Table where PStatus = 'Availible'", xSqlConnection);
            //SqlDataReader xSqlDataReader = null;
            //xSqlDataReader = xSqlCommand.ExecuteReader();
            //var objDataGridView = new DataGridView();
            dataGridView1.DataSource = xSqlCommand;

        }
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            //dataGridView1.DataSource = xSqlCommand;
        }
    }
}

Hi,

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;

namespace DISM_Project1
{
    public partial class AvalForm : Form
    {
        private SqlConnection xSqlConnection;
        private SqlCommand xSqlCommand;
        private SqlDataAdapter xSqlDataAdapter;

        public AvalForm()
        {
            InitializeComponent();
        }

        private void AvalForm_Load(object sender, EventArgs e)
        {
            try
            {
                //Making Connection
                xSqlConnection = new SqlConnection("Data Source=WaQaS-PC;Initial Catalog=BookingSystem;User ID=sa;Password=123");
                xSqlConnection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("" + ex);
            }
            xSqlCommand = new SqlCommand("Select Pno, PStatus from Unit1_Table where PStatus = 'Availible' UNION Select Pno, PStatus from Unit2_Table where PStatus = 'Availible'", xSqlConnection);
               SqlDataAdapter Adapter = new SqlDataAdapter(xSqlCommand);
                DataTable dt = new DataTable();
                Adapter.Fill(dt);
                BindingSource bs = new BindingSource();
                bs.DataSource = dt;
                dataGridView1.DataSource = bs;

        }
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            //dataGridView1.DataSource = xSqlCommand;
        }
    }
}

I don't know if the above method is the best way to do it, but it works for me.

Cheers,
Ionut

If you catch an exception during creating/connecting an SqlConnection you should NOT attempt to instantiate an SqlCommand . The exception could only be a bad connection string or failure to connect. Since you're specifying a user and password you should specify "Integrated Security=False" in your connection string:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace daniweb
{
  public partial class frmSqlGrid : Form
  {
    private DataTable dt;

    public frmSqlGrid()
    {
      InitializeComponent();
    }

    public static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }
    public static string BuildSqlNativeConnStr(string server, string database, string username, string password)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=False;User Id={2};Password={3};",
        server,
        database,
        username,
        password);
    }

    private void frmSqlGrid_Load(object sender, EventArgs e)
    {
      string connStr = BuildSqlNativeConnStr("WaQaS-PC", "BookingSystem", "sa", "123");
      const string query = @"Select Pno, PStatus from Unit1_Table where PStatus = 'Availible' UNION Select Pno, PStatus from Unit2_Table where PStatus = 'Availible'";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            if (dt != null)
              dt.Dispose();
            dt = new DataTable();
            dt.Load(dr);
            dataGridView1.DataSource = dt;
          }
        }
      }
    }
  }
}
Member Avatar for mwaqas1990

Thank You Guyz! Thank You so much

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.