Solved

How to create a gridview table in C# that displays data through a trusted SQL connection

Posted on 2012-04-11
4
314 Views
Last Modified: 2012-08-14
Hello experts. Please help a new programmer create an ASP.Net page with a gridview table in C# that displays data through a trusted SQL connection. I have attached the files I am working with. This is my first stab at ASP.Net and C# so some of the code may or may not be using the right commands. I'm just hoping someone can steer me in the right direction. Thank you in advance!
Default.aspx
Default.aspx.cs
Web.config
0
Comment
Question by:emacy85
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37833715
here is a step by step tutorial:-

http://msdn.microsoft.com/en-us/library/aa479344.aspx
0
 

Author Comment

by:emacy85
ID: 37833780
Thank you for your reply. I don't think this is what I'm looking for. The sample you provided does not show how to add,edit, or delete data from within the gridview. Are there any other examples you can share? I'd appreciate it very much.
0
 
LVL 20

Accepted Solution

by:
BuggyCoder earned 500 total points
ID: 37834085
0
 

Author Comment

by:emacy85
ID: 37838784
Thanks! The second set of examples you provided are what i was looking for. After following the step by step instructions I have received a couple errors on my .cs page. Could you help me figure out why theyre showing up? Thank you so much! you've really been a jobsaver :)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


public partial class _Default : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {

        if (!IsPostBack)
        {

            BindGridView();

        }

    }



    private string GetConnectionString()
    {

        return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

    }



    #region Bind GridView

    private void BindGridView()
    {

        DataTable dt = new DataTable();

        SqlConnection connection = new SqlConnection(GetConnectionString());

        try
        {

            connection.Open();

            string sqlStatement = "SELECT * FROM LT_WEB_DONATIONS_EXTRA_INFO_TEMP";

            SqlCommand cmd = new SqlCommand(sqlStatement, connection);

            SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);



            sqlDa.Fill(dt);

            if (dt.Rows.Count > 0)
            {

                GridView1.DataSource = dt;

                GridView1.DataBind();

            }

        }

        catch (System.Data.SqlClient.SqlException ex)
        {

            string msg = "Fetch Error:";

            msg += ex.Message;

            throw new Exception(msg);

        }

        finally
        {

            connection.Close();

        }

    }
}
    #endregion

#region Insert New or Update Record

    private void UpdateOrAddNewRecord(string customer_no, string id_key, string order_no, string member_no, string cust_full_name, string gift_type, string gift_amt, string corp_name, string corp_contact, string monthly_giving, string acknowledgment, string remembrance_type, string request_card, string card_to, string address, string city, string state, string zip, string message, string company_match, string company_name, string company_ratio, string ref_no, string last_4, string cvv, string designation, bool isUpdate)

    {

        SqlConnection connection = new SqlConnection(GetConnectionString());

        string sqlStatement = string.Empty;

 

        if (!isUpdate)

        {

            sqlStatement = "INSERT INTO LT_WEB_DONATIONS_EXTRA_INFO_TEMP"+

"(customer_no,id_key,order_no,member_no,cust_full_name,gift_type,gift_amt,corp_name,corp_contact,monthly_giving,acknowledgment,remembrance_type,honoree_name,request_card,card_to,address,city,state,zip,message,company_match,company_name,company_ratio,ref_no,last_4,cvv,designation)" +

"VALUES (@customer_no,@id_key,@order_no,@member_no,@cust_full_name,@gift_type,@gift_amt,@corp_name,@corp_contact,@monthly_giving,@acknowledgment,@remembrance_type,@honoree_name,@request_card,@card_to,@address,@city,@state,@zip,@message,@company_match,@company_name,@company_ratio,@ref_no,@last_4,@cvv,@designation)";

        }

        else

        {

            sqlStatement = "UPDATE LT_WEB_DONATIONS_EXTRA_INFO_TEMP" + 
                            "SET"
                           id_key = @id_key,
                           order_no = @order_no,
                           member_no = @member_no,
                           cust_full_name = @cust_full_name,
                           gift_type = @gift_type,
                           gift_amt = @gift_amt,
                           corp_name = @corp_name,
                           corp_contact = @corp_contact,
                           monthly_giving = @monthly_giving,
                           acknowledgment = @acknowledgment,
                           remembrance_type = @remembrance_type,
                           honoree_name = @honoree_name,
                           request_card = @request_card,
                           card_to = @card_to,
                           address = @address,
                           city = @city,
                           state = @state,
                           zip = @zip,
                           message = @message,
                           company_match = @company_match,
                           company_name = @company_name,
                           company_ratio = @company_ratio,
                           ref_no = @ref_no,
                           last_4 = @last_4,
                           cvv = @cvv,
                           designation = @designation, + WHERE Customer_no = @Customer_no;
        }

        try

        {

            connection.Open();

            SqlCommand cmd = new SqlCommand(sqlStatement, connection);

            cmd.Parameters.AddWithValue("@Customer_no", Customer No);

            cmd.Parameters.AddWithValue("@id_key", ID Key);

            cmd.Parameters.AddWithValue("@order_no", Order No);

            cmd.Parameters.AddWithValue("@member_no", Member No);

            cmd.Parameters.AddWithValue("@cust_full_name", Cust Full Name);

            cmd.Parameters.AddWithValue("@gift_type", Gift Type);

            cmd.Parameters.AddWithValue("@gift_amt", Gift Amt);

            cmd.Parameters.AddWithValue("@corp_name", Corp Name);

            cmd.Parameters.AddWithValue("@corp_contact", Corp Contact);

            cmd.Parameters.AddWithValue("@monthly_giving", Monthly Giving);

            cmd.Parameters.AddWithValue("@acknowledgment", Acknowledgment);

            cmd.Parameters.AddWithValue("@remembrance_type", Remembrance Type);

            cmd.Parameters.AddWithValue("@honoree_name", Honoree Name);

            cmd.Parameters.AddWithValue("@request_card", Request Card);

            cmd.Parameters.AddWithValue("@card_to", Card To);

            cmd.Parameters.AddWithValue("@address", Address);

            cmd.Parameters.AddWithValue("@city", City);

            cmd.Parameters.AddWithValue("@state", State);

            cmd.Parameters.AddWithValue("@zip", Zip);

            cmd.Parameters.AddWithValue("@message", Message);

            cmd.Parameters.AddWithValue("@company_match", Company Match);

            cmd.Parameters.AddWithValue("@company_name", Company Name);

            cmd.Parameters.AddWithValue("@company_ratio", Company Ratio);

            cmd.Parameters.AddWithValue("@ref_no", Ref No);

            cmd.Parameters.AddWithValue("@last_4", Last 4);

            cmd.Parameters.AddWithValue("@cvv", CVV);
            
            cmd.Parameters.AddWithValue("@designation", Designation);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Insert/Update Error:";

            msg += ex.Message;

            throw new Exception(msg);

 

        }

        finally

        {

            connection.Close();

        }

    }

    #endregion

protected void Button1_Click(object sender, EventArgs e)

    {

        UpdateOrAddNewRecord(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text, TextBox8.Text, TextBox9.Text, TextBox10.Text, TextBox11.Text, TextBox12.Text, TextBox13.Text, TextBox14.Text, TextBox15.Text, TextBox16.Text, TextBox17.Text, TextBox18.Text, TextBox19.Text, TextBox20.Text, TextBox21.Text, TextBox22.Text, TextBox23.Text, TextBox24.Text, TextBox25.Text, TextBox26.Text, TextBox27.Text, false);
        //Re Bind GridView to reflect changes made

        BindGridView();

    }
 
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

        GridView1.EditIndex = e.NewEditIndex; // turn to edit mode

        BindGridView(); // Rebind GridView to show the data in edit mode

}

 

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

        GridView1.EditIndex = -1; //swicth back to default mode

        BindGridView(); // Rebind GridView to show the data in default mode

}

 

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

        //Accessing Edited values from the GridView

        string customer_no = GridView1.Rows[e.RowIndex].Cells[0].Text; //Customer No

        string id_key = GridView1.Rows[e.RowIndex].Cells[1].Text; //ID Key

        string order_no = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text; //Order No

        string member_no = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text; //Member No

        string cust_full_name = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text; //Cust Full Name

        string gift_type = ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text; //Gift Type

        string gift_amt = ((TextBox)GridView1.Rows[e.RowIndex].Cells[6].Controls[0]).Text; //Gift Amt

        string corp_name = ((TextBox)GridView1.Rows[e.RowIndex].Cells[7].Controls[0]).Text; //Corp Name

        string corp_contact = ((TextBox)GridView1.Rows[e.RowIndex].Cells[8].Controls[0]).Text; //Corp Contact

        string monthly_giving = ((TextBox)GridView1.Rows[e.RowIndex].Cells[9].Controls[0]).Text; //Monthly Giving

        string acknowledgment = ((TextBox)GridView1.Rows[e.RowIndex].Cells[10].Controls[0]).Text; //Acknowledgment

        string remembrance_type = ((TextBox)GridView1.Rows[e.RowIndex].Cells[11].Controls[0]).Text; //Remembrance Type

        string honoree_name = ((TextBox)GridView1.Rows[e.RowIndex].Cells[12].Controls[0]).Text; //Honoree Name

        string request_card = ((TextBox)GridView1.Rows[e.RowIndex].Cells[13].Controls[0]).Text; //Request Card

        string card_to = ((TextBox)GridView1.Rows[e.RowIndex].Cells[14].Controls[0]).Text; //Card To

        string address = ((TextBox)GridView1.Rows[e.RowIndex].Cells[15].Controls[0]).Text; //Address

        string city = ((TextBox)GridView1.Rows[e.RowIndex].Cells[16].Controls[0]).Text; //City

        string state = ((TextBox)GridView1.Rows[e.RowIndex].Cells[17].Controls[0]).Text; //State

        string zip = ((TextBox)GridView1.Rows[e.RowIndex].Cells[18].Controls[0]).Text; //Zip

        string message = ((TextBox)GridView1.Rows[e.RowIndex].Cells[19].Controls[0]).Text; //Message

        string company_match = ((TextBox)GridView1.Rows[e.RowIndex].Cells[20].Controls[0]).Text; //Company Match

        string company_name = ((TextBox)GridView1.Rows[e.RowIndex].Cells[21].Controls[0]).Text; //Company Name

        string company_ratio = ((TextBox)GridView1.Rows[e.RowIndex].Cells[22].Controls[0]).Text; //Company Ratio

        string ref_no = ((TextBox)GridView1.Rows[e.RowIndex].Cells[23].Controls[0]).Text; //Ref No

        string last_4 = ((TextBox)GridView1.Rows[e.RowIndex].Cells[24].Controls[0]).Text; //Last 4

        string cvv = ((TextBox)GridView1.Rows[e.RowIndex].Cells[25].Controls[0]).Text; //CVV

        string designation = ((TextBox)GridView1.Rows[e.RowIndex].Cells[26].Controls[0]).Text; //Designation

 
        UpdateOrAddNewRecord(id,company,name,title,address,country,true); // call update method

        GridView1.EditIndex = -1;

        BindGridView(); // Rebind GridView to reflect changes made

}

#region Delete Record

    private void DeleteRecord(string ID)

    {

        SqlConnection connection = new SqlConnection(GetConnectionString());

        string sqlStatement = "DELETE FROM LT_WEB_DONATIONS_EXTRA_INFO_TEMP WHERE id_key = @id_key";

        try

        {

            connection.Open();

            SqlCommand cmd = new SqlCommand(sqlStatement, connection);

            cmd.Parameters.AddWithValue("@id_key", ID key);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Deletion Error:";

            msg += ex.Message;

            throw new Exception(msg);

 

        }

        finally

        {

            connection.Close();

        }

    }

#endregion

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

   string id = GridView1.Rows[e.RowIndex].Cells[0].Text; get the id of the selected row

   DeleteRecord(id);//call delete method

   BindGridView();//rebind grid to reflect changes made

}
 

Open in new window

0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question