Solved

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

Posted on 2012-04-11
4
293 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now