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
– Mamta D

2011-09-23 06:20:02 +00:00
Commented Sep 23, 2011 at 6:20 — Mamta D
– Mamta D, Commented Sep 23, 2011 at 6:20

Community · Accepted Answer · 2017-05-23 12:03:35Z

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, 2017 at 12:03

CommunityBot

11 silver badge

answered Sep 23, 2011 at 6:19

Davide Piras

44.7k10 gold badges103 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

FrankSharp Over a year ago

impossible to convert from 'System.Windows.Forms.DataGridView' in 'System.Data.DataTable'

FrankSharp Over a year ago

'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 Over a year ago

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.''

Davide Piras Over a year ago

Just combine my first snippet inside the second and you are good to go.

KV Prajapati · Accepted Answer · 2011-09-23 06:24:46Z

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, 2011 at 6:24

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

2 Comments

FrankSharp Over a year ago

Thanks work well! Why in the video work well without an instance of dataTable?

KV Prajapati Over a year ago

Yes in video DataSet is used. DataSet itself a collection of DataTables and Relations. Take a look at @Davide Piras's post.

RoccoC5 · Accepted Answer · 2011-09-23 06:20:08Z

0

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];
}

answered Sep 23, 2011 at 6:20

RoccoC5

4,21320 silver badges20 bronze badges

Comments

Xavibel · Accepted Answer · 2011-10-24 13:49:32Z

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, 2011 at 13:49

answered Sep 23, 2011 at 6:39

Xavibel

113 bronze badges

2 Comments

Davide Piras Over a year ago

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.

Xavibel Over a year ago

It's true, I fixed assuming the instance of cs created before.

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related