Solved

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

Posted on 2012-04-11
4
286 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
Comment Utility
here is a step by step tutorial:-

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

Author Comment

by:emacy85
Comment Utility
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
Comment Utility
0
 

Author Comment

by:emacy85
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 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

10 Experts available now in Live!

Get 1:1 Help Now