• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1299
  • Last Modified:

C# Databinding

I have a search form that displays customer name in a listbox, when a user double clicks on a customer name in the Listbox it returns back to the main form with the counter for that customer, then populates 4 text boxes. i also have a  datagridview and i would like to know how i get to populate the gridview with firstname, lastname etc. I can populate the text box's easily but i dont know how to populate the datagridview. Does this make sense? I suppose you could say im trying to split the results into text boxes and a datagridview. The code below is how far i have got. Im new to C# and i have looked into this however all the resources i have read about just give examples of populating just to a gridview without any parametres or other controls i.e SELECT * From Customer...and populate datagridview, which is not what i want to do

private void btnSearchCustomer_Click(object sender, EventArgs e)
        {
            using (frmSearchCustomer form = new frmSearchCustomer())
            {
                if (form.ShowDialog() == DialogResult.OK)
                {
                    //Clears Controls when bringing object in from frmSearchCustomer
                        txtCustomerName.Clear();
                        txtCustomerAddress.Clear();
                        txtCustomerTel.Clear();
                        txtPostCode.Clear();
 
                        System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection();
                        cnn.ConnectionString = @"Data Source=<SQLInstance>;Initial Catalog=<datasource>;Integrated Security=True";
                        //setup SqlCommand and assign SQL query in command
                        System.Data.SqlClient.SqlCommand cmd = cnn.CreateCommand();                        
                        cmd.CommandType = CommandType.Text;
                        cnn.Open();
                        cmd.CommandText = "SELECT CustomerName, Address, PostCode, MainTelephone, FirstName, LastName, Telephone, Mobile, Department, Position FROM spCustomersandContacts WHERE CustomerCounter = " + form.Counter + "";
                        System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();
                        recordAdp.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        //Load Datagrid
                        recordAdp.Fill(ds, "spCustomersandContacts");
                        datagridview1.DataSource = ds;
                        datagridview1.DataMember = "spCustomersandContacts.FirstName"; //help with syntax?
                        //Reads Data into txt boxes
                        SqlDataReader reader = cmd.ExecuteReader();                        
                        while (reader.Read())
                        {
                           //I can populate these text box's with no problem
                            txtCustomerName.Text = (String)reader["CustomerName"];
                            txtCustomerAddress.Text = (String)reader["Address"];
                            txtPostCode.Text = (String)reader["postCode"];
                            txtCustomerTel.Text = (String)reader["MainTelephone"];                            
                        }                    
 
                       reader.Close();
                       recordAdp.SelectCommand = cmd;
                       System.Data.DataSet recordSet = new System.Data.DataSet();
                       recordAdp.Fill(recordSet);                   
                                                               
                                                                  
                        cmd.Dispose();
                        cnn.Close();                    
                }
            }
        }

Open in new window

0
wilko100
Asked:
wilko100
1 Solution
 
SimonFerrisCommented:
I'm just looking round but I think you need to set the DataMember to just be "FirstName" I'll try and get some code together if you need it.
0
 
wilko100Author Commented:
Just tried that suggestion...

datagridview1.DataMember = "FirstName";

but got "Child list for field FirstName cannot be created."

Would of made my day if it was just that lol, cheers anyway
0
 
expertsoulCommented:
You can add bound field to GridView just as below:

Here DataField needs to be specified as column name in your dataset.


BoundField boundField = new BoundField();
boundField.DataField = "FirstName";
boundField.HeaderText = "First Name";
datagridview1.Columns.Add(boundField);

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
KelevraCommented:
Hi,

I think your missing the : GridView1.DataBind(); in ONE.

or

there is another solution:

in the HTML:TWO
there you have one datasource that have a methode to get the date you need.
then the gridview that get populated with this datasource.

in the C# code:THREE
there you have, the object data source, with its parameters and the methode it use to get specific data from your database,
here, an Employee (Highly Typed object) created for the occasion.
With the Id the methode return an employee.
And then populate the gridView.

Hope this will help.

protected void Page_Load(object sender, EventArgs e)
 
{
 
 
 
string strSQLconnection = "Data Source=dbServer;Initial Catalog=testDB;Integrated Security=True";
 
SqlConnection sqlConnection = new SqlConnection(strSQLconnection);
 
SqlCommand sqlCommand = new SqlCommand("select * from table1", sqlConnection);
 
sqlConnection.Open();
 
 
ONE:
 
SqlDataReader reader = sqlCommand.ExecuteReader();
 
        
 
GridView1.DataSource = reader;
 
GridView1.DataBind();
 
}
 
TWO:HTML
<asp:GridView ID="GridView" runat="server" AllowPaging="True" AutoGenerateColumns="False"
        DataSourceID="MyDataSource"></asp:GridView>
 
<asp:ObjectDataSource ID="MyDataSource" runat="server" SelectMethod="MyMethode"
        TypeName="Ctrl">
        <SelectParameters>
            <asp:Parameter DefaultValue='' Name="EmployeeId" Type="String" />
            <asp:Parameter DefaultValue="false" Name="IsWorking" Type="Boolean" />
        </SelectParameters>
    </asp:ObjectDataSource>
 
THREE
 
          MyDataSource.SelectParameters["EmployeeId"] = new Parameter("EmployeeId");
 
public Articles MyMethode(string EmployeeId, bool IsWorking)
    {
        Employee employee = new Employee();
        employee.GetEmployyeById(EmployeeId, IsWorking);
        return Employee;
    }

Open in new window

0
 
wilko100Author Commented:
ExpertsSoul and Kelevra...is the DatatBind and Bound part of the System.Web.UI.WebControls used  to dynamically bind an  gridview in ASP.NET? i have a winform using a datagridview control
Im new to C# so could be wrong ?
0
 
SimonFerrisCommented:
I used the following:

namespace ExpertsExchangeWindowsCode
{
    using System;
    using System.Data;
    using System.Windows.Forms;
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var ds = new DataSet();
            ds.Tables.Add(new DataTable());
            ds.Tables[0].Columns.Add(new DataColumn("FirstName"));
            ds.Tables[0].Columns.Add(new DataColumn("LastName"));

            var dr = ds.Tables[0].NewRow();
            dr[0] = "FirstName";
            dr[1] = "LastName";
            ds.Tables[0].Rows.Add(dr);

            textBox1.Text = ds.Tables[0].Columns[0].ToString();
            textBox2.Text = ds.Tables[0].Columns[1].ToString();

            dataGridView1.DataSource = ds.Tables[0];
        }
    }
}
0
 
wilko100Author Commented:
Thats great execpt but i have already created the datagridview through the designer, the above example is created through code and puts the actual word "Firstname" and Lastname in the txt box's and datagridview which is a good example exept i just need to kow how to pass the  SQL result into the datagridview? does that make sense?
0
 
SimonFerrisCommented:
Below will bind the DataTable to the GridView - you'll need to 'tweak' the command text for the parameter value and connectionstring. The previous code was just a piece to create a DataSet without connections etc and bind it.

Once you have DataSet you can use textBox1.Text = ds.Tables[0].Columns[0].ToString();
            textBox2.Text = ds.Tables[0].Columns[1].ToString(); // or something similar to get the txt boxes populated

var conn = new SqlConnection(@"Data Source=ftc-dev-sql02\sql2005;Initial Catalog=EcommerceTest;Integrated Security=True");
            var comm = new SqlCommand("Select * from tutor");
            var da = new SqlDataAdapter();
            var ds =new DataSet();
            comm.Connection = conn;
            da.SelectCommand = comm;
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];

Open in new window

0
 
wilko100Author Commented:
i see. Will give it a try later today, but yes from waht i can see this looks like it will sort it
0
 
wilko100Author Commented:
Ok did a bit of tweaking, well more like shoe horning! (I will tidy it up once i got it nailed) lol but i got it kinda got it working except the grid is returning everything in the vew, So for example the Counter object comes back to the Main form, populates the txtCustomerName, txtCustomerAddress, txtPostCode and txtCustomerTel as it should, however ,the datagridview also brings these items back ,as well as firstname, Lastname etc. Is there a filter or something? Getting there so thanks for that.
System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection();
                    var conn = new SqlConnection(@"Data Source=<InstanceName>;Initial Catalog=<DataSource>;Integrated Security=True");
                    var comm = new SqlCommand("SELECT CustomerName, Address, PostCode, MainTelephone, FirstName, LastName, Telephone, Mobile, Department, Position FROM vwCustomersandContacts WHERE CustomerCounter = " + form.Counter + "");                    
                    var da = new SqlDataAdapter();
                    var ds = new DataSet();
                    comm.Connection = conn;
                    da.SelectCommand = comm;
                    da.Fill(ds);
                    datagridview1.DataSource = ds.Tables[0];
                    System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();
                    conn.Open();
                    recordAdp.SelectCommand = comm;
                    SqlDataReader reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        txtCustomerName.Text = (String)reader["CustomerName"];
                        txtCustomerAddress.Text = (String)reader["Address"];
                        txtPostCode.Text = (String)reader["postCode"];
                        txtCustomerTel.Text = (String)reader["MainTelephone"];
                    }
 
                    reader.Close();
                    comm.Dispose();
                    cnn.Close();            

Open in new window

0
 
SimonFerrisCommented:
Try something along these lines where you are setting the datagrid propreties before you data bind it
datagridview1.AutoGenerateColumns = false;
var col1 = new DataGridViewColumn();
col1.DataPropertyName = "Title";
col1.Name = "Title";
var cell1 = new DataGridViewTextBoxCell();
col1.CellTemplate = cell1;
datagridview1.Columns.Add(col1);
var col2 = new DataGridViewColumn();
col2.DataPropertyName = "Description";
col2.Name = "Description";
var cell2 = new DataGridViewTextBoxCell();
col2.CellTemplate = cell2;
datagridview1.Columns.Add(col2);
datagridview1.DataSource = ds.Tables[0];

Open in new window

0
 
wilko100Author Commented:
Sorted it, your example works a god damn treat! Thanks for that
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now