Problem Retrieving Data from SQL Server using C# and ADO.Net window form

Question

I took an video from you tube to Retrieving Data from SQL Server using C# and ADO.Net

http://www.youtube.com/watch?v=4kBXLv4h2ig&feature=related

I Do the same as him in the video... I want to show data from an sql database in a DataGridView.

I get an error whit

da.Fill(dg);
dg.DataSource = dg.Tables[0];

I name my DataGridView dg...

Complete code

using System.Data.SqlClient;

namespace SQLconnection
 {
 public partial class Form1 : Form
{
    SqlConnection cs = new SqlConnection("Data Source=FRANK-PC\\SQLEXPRESS; Initial Catalog=Forc#; Integrated Security=TRUE");
    SqlDataAdapter da = new SqlDataAdapter();

    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        da.InsertCommand= new SqlCommand ("INSERT INTO tblContacts VALUES (@FirstName,@LastName)",    cs );
        da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
        da.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastname.Text;

        cs.Open();
        da.InsertCommand.ExecuteNonQuery();
        cs.Close();
    }

    // Display data in dg
    private void button2_Click(object sender, EventArgs e)
    {
        da.SelectCommand = new SqlCommand("SELECT * FROM tblContacts", cs);
        da.Fill(dg);


        dg.DataSource = dg.Tables[0];
    }


}

}

What is the error you are getting? – Mamta D Sep 23 '11 at 06:20 — Mamta D, Sep 23 '11 at 06:20

score 3 · Answer 1 · edited May 23 '17 at 12:03

3

you should open the connection before filling the table with the data adapter, add this:

cs.Open();

DataSet ds = new DataSet();
da.Fill(ds);
cs.Close();

dg.DataSource = ds.Tables[0];

note that this is anyway a bad practice, there are trillions of examples here in SO on how to handle the SQLConnections, you should use a using block so that it gets closed and disposed immediately after usage and do not have connections or adapters or data tables or sqlcommand global to all form but create them only when/where needed.

You should actually move out all data access logic from the UI to a separated class, Business Logic or Data layer.

Edit:

you should do something like this:

using(SQLConnection conn = 'connection string here')
{
    using(SQLCommand cmd = new ('sql query', conn))
    {
        //execute it blah blah
    }
}

check out this question: Closing SqlConnection and SqlCommand c#

edited May 23 '17 at 12:03

Community

1
1

answered Sep 23 '11 at 06:19

Davide Piras

43,984
10
98
147

impossible to convert from 'System.Windows.Forms.DataGridView' in 'System.Data.DataTable' – FrankSharp Sep 23 '11 at 06:24
'System.Windows.Forms.DataGridView' no contain définition for'Tables' no method extension 'Tables' to accept a first argument type 'System.Windows.Forms.DataGridView' nothing found (a directive using or référence d'assembly miss ?) C:\Users\Frank\Documents\Visual Studio 2008\Projects\SQLconnection\SQLconnection\Form1.cs 42 32 SQLconnection – FrankSharp Sep 23 '11 at 06:26
Is its posible to get complete code in a situation for your point '' you should use a using block so that it gets closed and disposed immediately after usage and do not have connections or adapters or data tables or sqlcommand global to all form but create them only when/where needed.'' – FrankSharp Sep 23 '11 at 06:54
1

Just combine my first snippet inside the second and you are good to go. – Davide Piras Sep 23 '11 at 06:59

score 2 · Accepted Answer · answered Sep 23 '11 at 06:24

2

The Fill method open/close connection implicitly but the problem is in name of dataGrdiView and DataTable/DataSet reference variable - dg

private void button2_Click(object sender, EventArgs e)
    {
        da.SelectCommand = new SqlCommand("SELECT * FROM tblContacts", cs);
        DataTable dt=new DataTable();
        da.Fill(dt);

        dg.DataSource = dt;
    }

answered Sep 23 '11 at 06:24

KV Prajapati

93,659
19
148
186

Thanks work well! Why in the video work well without an instance of dataTable? – FrankSharp Sep 23 '11 at 06:31
1

Yes in video DataSet is used. DataSet itself a collection of DataTables and Relations. Take a look at @Davide Piras's post. – KV Prajapati Sep 23 '11 at 06:35

score 0 · Answer 3 · answered Sep 23 '11 at 06:20

I'm guessing since you didn't include the exception you are receiving, but you need to open your SqlConnection prior to using it:

private void button2_Click(object sender, EventArgs e)
{
   da.SelectCommand = new SqlCommand("SELECT * FROM tblContacts", cs);

   cs.Open();
   da.Fill(dg);
   cs.Close();

   dg.DataSource = dg.Tables[0];
}

Xavibel · Answer 4 · 2011-10-24T13:49:32.903

0

Try it this, but is important know what kind of exception throws.

private void button2_Click(object sender, EventArgs e)
{
        cs.Open();
        using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM tblContacts", cs))
        {
            DataTable t = new DataTable();
            a.Fill(t);

            dg.DataSource = t;
        }
    }

edited Oct 24 '11 at 13:49

answered Sep 23 '11 at 06:39

Xavibel

11
3

This is NOT correct. The using will dispose cs but cs will be still available as it was created before but will be not usable. – Davide Piras Sep 23 '11 at 06:58
It's true, I fixed assuming the instance of cs created before. – Xavibel Oct 24 '11 at 13:50

Problem Retrieving Data from SQL Server using C# and ADO.Net window form

4 Answers4