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

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
emacy85Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BuggyCoderCommented:
here is a step by step tutorial:-

http://msdn.microsoft.com/en-us/library/aa479344.aspx
0
emacy85Author Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emacy85Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.