13 Replies - 1571 Views - Last Post: 14 July 2011 - 02:27 PM Rate Topic: -----

#1 scolty  Icon User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 259
  • Joined: 27-April 11

DataGridView

Posted 12 July 2011 - 05:06 PM

Im trying to connect to my MS SQL server (Note the connection string has worked in console apps so i dont think ive made a mistake there) Run a hardcoded SQL statement and display all 4 records from the "customers" table. The allow me to change these values and update the database. unfortunately it doesnt seem to be populating the datagridview. Any assistance would be appreciated.

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 WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        IDataAdapter _adapter;
        DataSet _dataSet;
        IDbConnection _conn;

        public Form1()
        {
            InitializeComponent();
            MessageBox.Show("Initialized");

            ButtonUpdate.Enabled = false;
            MessageBox.Show("ButtonUpdate set to False");

            CreateDataSet();
            MessageBox.Show("CreateDataSet called");

            dataGridView1.DataSource = _dataSet.Tables["Customers"];
            dataGridView1.CellEndEdit += new DataGridViewCellEventHandler(OnCellEdit);
            ButtonUpdate.Click += new EventHandler(ButtonUpdate_Click);
        }

        protected override void OnClosing(CancelEventArgs e)
        {
            _dataSet.Dispose();
            _conn.Dispose();
        }

        private DataSet CreateDataSet()
        {
            string connectionString = @"Server = OPHIUCHUS; Database = AutoLot;
                User ID = InitialConnection; Password = InitialConnection;
                Trusted_Connection = False";

            SqlConnection conn = new SqlConnection(connectionString);

            conn.Open();
            MessageBox.Show("open connection");

            SqlCommand selectCmd = new SqlCommand("SELECT * FROM CUSTOMERS", conn);

            SqlCommand updatecmd = new SqlCommand("UPDATE CUSTOMERS SET FirstName=@FirstName, LastName=@LastName WHERE CustID=@CustID", conn);


            updatecmd.Parameters.Add("@CustID", SqlDbType.Int, 5, "CustID");
            updatecmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 255, "Tom");
            updatecmd.Parameters.Add("@LastName", SqlDbType.VarChar, 255, "Blue");

            SqlDataAdapter adapter = new SqlDataAdapter();

            adapter.TableMappings.Add("FirstName", "LastName");
            adapter.SelectCommand = selectCmd;
            adapter.UpdateCommand = updatecmd;
            _conn = conn;
            _adapter = adapter;
            _dataSet = new DataSet("Customers");

            _adapter.Fill(_dataSet);
            return _dataSet;
        }

        private void OnCellEdit(object sender, DataGridViewCellEventArgs e)
        {
            ButtonUpdate.Enabled = true;
        }

        private void ButtonUpdate_Click(Object sender, EventArgs e)
        {
            _adapter.Update(_dataSet);
            ButtonUpdate.Enabled = false;
        }

    }



}



Is This A Good Question/Topic? 0
  • +

Replies To: DataGridView

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: DataGridView

Posted 12 July 2011 - 05:51 PM

Your missing the CommandBuilder:
....
            adapter.TableMappings.Add("FirstName", "LastName");
            adapter.SelectCommand = selectCmd;
            adapter.UpdateCommand = updatecmd;
            _conn = conn;
            _adapter = adapter;
            _dataSet = new DataSet("Customers");
            //Here add the command builder
            SqlCommandBuilder CMD=new SqlCommandBuilder(_adapter);
            _adapter.Fill(_dataSet);
            return _dataSet;
...



Also why your CreateDataSet() method returns DataSet if your just calling it?

This post has been edited by NoBrain: 12 July 2011 - 05:52 PM

Was This Post Helpful? 1
  • +
  • -

#3 scolty  Icon User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 259
  • Joined: 27-April 11

Re: DataGridView

Posted 12 July 2011 - 10:45 PM

I originally didnt have a return but i found a different example which had one n although i thought it was odd i gave it a shot.

Ive made the change below but im assuming ive made another stupid mistake??

Thanks again for your help


Posted Image
Was This Post Helpful? 0
  • +
  • -

#4 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4577
  • View blog
  • Posts: 8,019
  • Joined: 08-June 10

Re: DataGridView

Posted 13 July 2011 - 06:42 AM

The second error explains what's wrong. My question is, why do you have adapter and _adapter? Why do you need both? Just use one.

And the second error is saying that it can't convert from IDataAdapter to SqlDataAdapter. Make sure that you've properly declared _adapter, whatever it is.
Was This Post Helpful? 2
  • +
  • -

#5 scolty  Icon User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 259
  • Joined: 27-April 11

Re: DataGridView

Posted 13 July 2011 - 07:01 AM

Its actually from a book i bought. Ok i modified the connection string etc but the majority of the code 99% was the same.

Regarding _adapter vs Adapter, that was going to be my next question.

im assuming the line

IDataAdapter _adapter;


should actually be
SqlDataAdapter _adapter;


if i was still going to use this weird convention they had in the book rather than the simpler way u just suggested?

Thanks again Curtis
Was This Post Helpful? 0
  • +
  • -

#6 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4577
  • View blog
  • Posts: 8,019
  • Joined: 08-June 10

Re: DataGridView

Posted 13 July 2011 - 07:31 AM

Yeah, that would work. Also, you can drop the return statement and return type from your CreateDataSet method, since you're modifying a class level variable there, not creating an instance.
Was This Post Helpful? 0
  • +
  • -

#7 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: DataGridView

Posted 13 July 2011 - 07:32 AM

Ah yes your error say whats wrong. Command builder constructor get SqlDataAdapter as argument and we are sending IDataAdapter. Why are you using IDataAdapter?? Change this line to :
            //Here add the command builder
            SqlCommandBuilder CMD=new SqlCommandBuilder(adapter)



and the just use adapter instead of _adapter. your using SQL so dont use IDataAdapter use SqlDataAdapter, SqlConnection etc.
also here is tutorial about SQL in C#
Insert, Update & Delete Table from DataGridView in C#.Net
Was This Post Helpful? 1
  • +
  • -

#8 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4577
  • View blog
  • Posts: 8,019
  • Joined: 08-June 10

Re: DataGridView

Posted 13 July 2011 - 07:46 AM

Well, his book probably had him use IDataAdapter for dependency injection purposes. It's easier to change your code to use a different DB if you're abstracting things to interfaces. Also probably why it didn't have him use a CommandBuilder, which is not necessary, if he writes his own commands.

As to using adapter or _adapter, I'd go with the class level one (which is _adapter), just don't bother instantiating adapter. Work directly with _adapter.
Was This Post Helpful? 2
  • +
  • -

#9 scolty  Icon User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 259
  • Joined: 27-April 11

Re: DataGridView

Posted 13 July 2011 - 08:07 AM

Im not familar with dependency injections.

Regarding ur last comment about not instantiating adapter, by declaring adapter as an SqlDataAdapter object, i get around this but is this practice frowned upon?

Thanks again Curtis

View PostNoBrain, on 13 July 2011 - 07:32 AM, said:

Ah yes your error say whats wrong. Command builder constructor get SqlDataAdapter as argument and we are sending IDataAdapter. Why are you using IDataAdapter?? Change this line to :
            //Here add the command builder
            SqlCommandBuilder CMD=new SqlCommandBuilder(adapter)



and the just use adapter instead of _adapter. your using SQL so dont use IDataAdapter use SqlDataAdapter, SqlConnection etc.
also here is tutorial about SQL in C#
Insert, Update & Delete Table from DataGridView in C#.Net


Cheers NoBrain, + thanks for the link
Was This Post Helpful? 0
  • +
  • -

#10 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4577
  • View blog
  • Posts: 8,019
  • Joined: 08-June 10

Re: DataGridView

Posted 13 July 2011 - 08:18 AM

Why would it be? What's the point of instantiating a local variable, just to assign it to a class-level one? Nothing but extra, pointless code. Maybe one company's or book's style guide might not like it, but it's not an industry-wide standard you have to worry about.

Again, the reason your book had you do it that way is obvious to me. If you use IDataAdapter and IDBConnection, then it's easy to later change which database you use. But because you declare them as interfaces, it's easier to make a local variable of the type you want, fill it out, and then assign it to the interface variable.

If you're not worried about changing DBs (which you won't be), then skip the abstraction. Use SqlConnection and SqlDataAdapter, and skip the local variables.
Was This Post Helpful? 2
  • +
  • -

#11 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: DataGridView

Posted 13 July 2011 - 08:20 AM

I have to agree here with Curtis here and i strongly suggest for you to re-write your method that populate the DataSet.
Was This Post Helpful? 1
  • +
  • -

#12 scolty  Icon User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 259
  • Joined: 27-April 11

Re: DataGridView

Posted 13 July 2011 - 08:27 AM

Duly noted.

This post has been edited by scolty: 13 July 2011 - 08:28 AM

Was This Post Helpful? 0
  • +
  • -

#13 scolty  Icon User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 259
  • Joined: 27-April 11

Re: DataGridView

Posted 13 July 2011 - 05:55 PM

arrgh i still cant get this working. It doesnt fill the table at all + the update button is greyed out. I was just reading something which said that tablemappings.add() doesnt actually know the name of the table its communicating with in the database. Ive therefore changed it to "Table". I changed the second to "Customers". I have the same name for the table in my SQL server but i dont believe thats important.

Further down, the section which states updatecmd.Parameters.Add() i changed the last input (StringSourceColumn) on the overloaded method to the name of the columns in my table. Is this correct?

Im also unsure about the declaration for the int type.

Lastly, am i able to just call adapter.Fill(dataSet); and it will know exactly what to do as i have seen some other examples which have a for loop set up.

Basically im pretty confused :(


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 WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        SqlDataAdapter adapter;
        DataSet dataSet;
        SqlConnection conn;
        

        public Form1()
        {
            InitializeComponent();
            MessageBox.Show("Initialized");

            ButtonUpdate.Enabled = false;
            MessageBox.Show("ButtonUpdate set to False");

            CreateDataSet();
            MessageBox.Show("CreateDataSet called");

            dataGridView1.DataSource = dataSet.Tables["Customers"];
            dataGridView1.CellEndEdit += new DataGridViewCellEventHandler(OnCellEdit);
            ButtonUpdate.Click += new EventHandler(ButtonUpdate_Click);
        }

        protected override void OnClosing(CancelEventArgs e)
        {
            dataSet.Dispose();
            conn.Dispose();
        }

        private void CreateDataSet()
        {
            string connectionString = @"Server = OPHIUCHUS; Database = AutoLot;
                User ID = InitialConnection; Password = InitialConnection;
                Trusted_Connection = False";

            SqlConnection conn = new SqlConnection(connectionString);

            conn.Open();
            MessageBox.Show("open connection");

            SqlCommand selectCmd = new SqlCommand("SELECT * FROM CUSTOMERS", conn);

            SqlCommand updatecmd = new SqlCommand("UPDATE CUSTOMERS SET FirstName=@FirstName, LastName=@LastName WHERE CustID=@CustID", conn);

            
            updatecmd.Parameters.Add("@CustID", SqlDbType.Int, 5, "CustID");
            updatecmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");
            updatecmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");


            adapter = new SqlDataAdapter();
            adapter.TableMappings.Add("Table", "dataset");
            adapter.SelectCommand = selectCmd;
            adapter.UpdateCommand = updatecmd;
            
            dataSet = new DataSet("Customers");
            SqlCommandBuilder CMD = new SqlCommandBuilder(adapter);
            adapter.Fill(dataSet);
            MessageBox.Show("fill called");
            
        }

        private void OnCellEdit(object sender, DataGridViewCellEventArgs e)
        {
            ButtonUpdate.Enabled = true;
        }

        private void ButtonUpdate_Click(Object sender, EventArgs e)
        {
            adapter.Update(dataSet);
            ButtonUpdate.Enabled = false;
        }

    }
}

 

This post has been edited by scolty: 13 July 2011 - 05:56 PM

Was This Post Helpful? 0
  • +
  • -

#14 scolty  Icon User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 259
  • Joined: 27-April 11

Re: DataGridView

Posted 14 July 2011 - 02:27 PM

When you first drag the datagridview onto the windows form, a datagridview tasks window appears. What do i need to fill out here or can i set everything up in the code??

This is the only reasons i can think of regarding why its not displaying the info in the datagrid view.

Thanks again for ur help
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1