Database connection problem

[ajijacobm]

Hi,
I have database connection problem.
A part of the source is shown Below

private void Form3_Load(object sender, EventArgs e)
        {
            
            string connectionstring="Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\hospdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            SqlConnection con=new SqlConnection(connectionstring);
            DataSet ds=new DataSet();
            string command="SELECT Name,Address,Phone,Branch,Class FROM hosptab WHERE (Branch = 'SKIN')";
            SqlDataAdapter da=new SqlDataAdapter(command,con);
            da.Fill(ds,"hospital");
            DataTable dt=ds.Tables["hospital"];
            //da.Fill(hospdbDataSet, "hospital");                       
            con.Open();            
            // TODO: This line of code loads data into the 'hospdbDataSet.hosptab' table. You can move, or remove it, as needed.
            this.hosptabTableAdapter.Fill(this.hospdbDataSet.hosptab);
            con.ResetStatistics();
            con.Close();
            con.Dispose();
        }

The SQL query is not working.It is showing all the rows in the database.Please show me how to establish a connection with the database and also,how to execute that sql command which is to be shown in data grid view.Any help Please?:$
Any way thanks for Advance:)

[sknake]

Excellent post! You included a sample project plus a sample database :) Just one more thing -- when you paste code on Daniweb please use code tags.

I can't get the project to run though :( Locking up on the connection. Anyway I think I see the problem:

private void Form3_Load(object sender, EventArgs e)
        {

            //{!}string connectionstring="Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\hospdb.mdf;Integrated Security=True;Connect Timeout=5;User Instance=True";
          //{!}SqlConnection con=new SqlConnection(connectionstring);

          //{!}DataSet ds=new DataSet();
          //{!}string command="SELECT Name,Address,Phone,Branch,Class FROM hosptab WHERE (Branch = 'SKIN')";
          //{!}SqlDataAdapter da=new SqlDataAdapter(command,con);
          //{!}da.Fill(ds,"hospital");
          //{!}DataTable dt=ds.Tables["hospital"];
            //da.Fill(hospdbDataSet, "hospital");                       
          //{!}con.Open();            
            // TODO: This line of code loads data into the 'hospdbDataSet.hosptab' table. You can move, or remove it, as needed.
            this.hosptabTableAdapter.Fill(this.hospdbDataSet.hosptab);
            //{!}con.ResetStatistics();
            //{!}con.Close();
          //{!}con.Dispose();
        }

That is the only line of the code really doing work to populate the DataSet. Your grid is bound to the typed DataSet. What you need to do is go to your TableAdapter on the DataSet designer, right click on it, and "Add Select Query". You should be able to define another Fill() method, ie FillSkin(), that will run your second query. You would then replace this line:

this.hosptabTableAdapter.Fill(this.hospdbDataSet.hosptab);

this.hosptabTableAdapter.FillSkin(this.hospdbDataSet.hosptab);

[dnanetwork]

ajijacobm, did that solution worked..?

[ajijacobm]

Ya, thankkssss:$ .But if i would use another sql query condition to display on that grid view itself?How may i do..Please give me soultion...

[sknake]

That gets a little tricky. You can override the table adapters. Right click on your dataset and go to the table adapters. In this case I have a "DataSetJobs" with a "jobsTableAdapter" class. This exposes the command text:

namespace daniweb.datasets.DataSetJobsTableAdapters
{
  public partial class jobsTableAdapter
  {
    public string SqlForFill
    {
      get
      {
        if ((this._commandCollection == null)) { this.InitCommandCollection(); }
        return this._commandCollection[0].CommandText;
      }
      set
      {
        if ((this._commandCollection == null)) { this.InitCommandCollection(); }
        this._commandCollection[0].CommandText = value;
      }
    }
  }
}