Category Archives: Linq

[RESOLVED] CRUD on GridView Populated with MySQL

Hello Friends

After much frustration I managed with the help of (RivDiv and others) to get the Data in the MySQL database to display the way I want it.

Now however I want to implement CRUD functionality in the GridView. I can do this with MS SQL Server and LinqDataSources, but I don't know how to do it with MySQL < since as far as I know you can't have LinqDataSources with MySQL.

I would greatly appreciate it if you could link me to articles that go through this process clearly. Or if you could show me on here. I did some google searching, but got nothing useful. :(

Thank You.

I would do all CRUD operations in the code-behind with OleDB command & connection objects.   This tutorial explains in detail:

http://www.dreamincode.net/forums/topic/33908-oledb-basics-in-vbnet/

 

That was Very Confusing ! Cry 

Any other much clearer tutorials on this ? C# preferably.

Thank You.

Hi Zizhuoye

That second link is really good, sadly it doesn't explain much about the GridView. How do I get my GridView to show them Edit | Update | Delete options.

[img]http://www.codeproject.com/KB/database/MySQLinASPNET/Image2.jpg[/img]

Remember I'm on MySQL and I don't get any tick boxes when I click on my GridView in Design Mode.

Right now, my grid looks like this:

[img]http://i52.tinypic.com/25ajihi.jpg[/img]

Thank You.

Hi,

You can use GridView CommandField Template or create your own Item Template to implement Update, Edit, Delete button in GridView Rows.

Check these links, I think you can get some idea from them:

http://www.beansoftware.com/ASP.NET-Tutorials/GridView-Control.aspx

http://www.codeproject.com/KB/webforms/Editable_GridView.aspx

http://programming.top54u.com/post/ASP-Net-Gridview-Edit-Update-Cancel-Commands.aspx

Thanks Zizhuoye

It appears that for CRUD purposes I have to turn off AutoGenerateColumns Cry

Here is my GridView code:

<asp:GridView ID="MembersGridView" DataKeyNames="Customer_ID" runat="server" AutoGenerateColumns="false" OnRowDataBound="MembersGridView_RowDataBound" onrowediting="MembersGridView_RowEditing" onrowcancelingedit="MembersGridView_RowCancelingEdit"
        onrowupdating="MembersGridView_RowUpdating" >
        <Columns>
            <asp:BoundField HeaderText="ID" DataField="Customer_ID" ReadOnly="True"></asp:BoundField>
        </Columns>
    </asp:GridView>


and here is the code behind content:

public partial class Memberships : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                bindGridView();
            }
        }

        public void bindGridView()
        {
            // TODO: Update the ConnectionString and CommandText values for your application   
            string ConnectionString = @"driver={MySQL ODBC 3.51 Driver};Server=kreativity.homeftp.net;Database=intproject;Uid=group15;Pwd=swimmingpool15;";
            string CommandText = "SELECT Customer_ID AS ID, FName, LName, DOB, Gender, DateRegistered, InitialPass, Email, Left(Password,10) AS Password FROM CUSTOMER";

            OdbcConnection myConnection = new OdbcConnection(ConnectionString);
            OdbcCommand myCommand = new OdbcCommand(CommandText, myConnection);

            myConnection.Open();

            MembersGridView.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            MembersGridView.DataBind();

            // close the connection   
            myConnection.Close();
        }

        protected void MembersGridView_RowDataBound(Object sender, GridViewRowEventArgs e)
        {

            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate)
                {
                    DateTime DOBdateString = DateTime.Parse(e.Row.Cells[3].Text, null);
                    DateTime RegisteredDateString = DateTime.Parse(e.Row.Cells[5].Text, null);
                    e.Row.Cells[3].Text = string.Format("{0:dd MMM yyyy}", DOBdateString);
                    e.Row.Cells[5].Text = string.Format("{0:dd MMM yyyy}", RegisteredDateString);
                }
            }

        }

        protected void MembersGridView_RowEditing(object sender, GridViewEditEventArgs e)
        {
            MembersGridView.EditIndex = e.NewEditIndex;
            bindGridView();
        }

        protected void MembersGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            MembersGridView.EditIndex = -1;
            bindGridView();
        }

        protected void MembersGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            
        }



    }


I am getting the following error:

[IMG]http://i54.tinypic.com/2rw9j6b.jpg[/IMG]

I don't know why I'm getting that error, in the MySQL database, the column { Customer_ID } does exist !

Thanks

[RESOLVED] Loading a usercontrol into a webpage

Guys: asp.net c# question

I ma trying to load a usercontrol into a table. My usercontrol contains some literal controls.

I can't set the text of these literal controls to anythign, as in my code-behind of my control, whenever I try to access my controls, they come back as null.

My user control code:

<div style="color: Black; background-color: White;" mce_style="color: Black; background-color: White;">
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="OfferControl.ascx.cs"
Inherits="EOwner.Site.French.Controls.OfferControl" %>
<link rel="Stylesheet" href="../styles/OfferControl.css" type="text/css" />
<div id="all" style="background-color: #FFFFF0; height:100%;">
<div id="LeftPanel" style="float: left; width: 65%;">
<div id="title">
<asp:Literal ID="litTitle" runat="server" Text="TitleLabel"></asp:Literal>
</div>
<div id="Blurb">
<asp:Literal ID="litBlurb" runat="server" Text="BlurbLabel" ></asp:Literal>
</div>
<div id="Button">
<asp:ImageButton ID="btnViewOffer" runat="server"
ImageUrl="~/images/ViewOfferButton.jpg" ImageAlign="AbsMiddle" />
</div>
</div>
<div id="RightPanel" style="float: right; width: 35%;">
<div id="address">
<asp:Literal ID="litAddress" runat="server" Text="AddressLabel"></asp:Literal>
</div>
<div id="map">
Map
</div>
</div>
</div>

</div>


and the .cs

<div style="color: Black; background-color: White;" mce_style="color: Black; background-color: White;">
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using EOwner.Contracts.Entities;

namespace EOwner.Site.French.Controls
{
public partial class OfferControl : System.Web.UI.UserControl
{
protected void Page_Load(object sender, EventArgs e)
{

}

public Literal lit_Title
{
get { return litTitle; }
set { litTitle = value; }
}

public Literal lit_Blurb
{
get { return litBlurb; }
set { litBlurb = value; }
}

public Literal lit_Address
{
get { return litAddress; }
set { litAddress = value; }
}

public ImageButton btn_ViewOffer
{
get { return btnViewOffer; }
set { btnViewOffer = value; }
}
}
}
</div>



And the code that creates the control

 

 

<div style="color: Black; background-color: White;" mce_style="color: Black; background-color: White;">
foreach (OfferEntity o in offers)
{
OfferControl oc = new OfferControl();
oc.LoadControl("~/Controls/OfferControl.ascx");//GridViewRow gr = new GridViewRow(offers.IndexOf(o), offers.IndexOf(o), DataControlRowType.DataRow, DataControlRowState.Normal);
oc.ID = "oc_" + o.OfferId.ToString();
oc.Attributes.Add("OfferId", o.OfferId.ToString());
oc.lit_Title.Text = o.Title; //Request.QueryString["Title"].ToString();
oc.lit_Blurb.Text = o.Blurb;//Request.QueryString["Blurb"].ToString();
oc.btn_ViewOffer.Attributes.Add("OfferId", o.OfferId.ToString());
//address town postcode
oc.lit_Address.Text = o.Address + Environment.NewLine + o.Town + Environment.NewLine + o.Postcode + Environment.NewLine + o.WebAddress;

TableRow tr = new TableRow();
TableCell tc = new TableCell();
//pass in offer id, list is stored in the session, use the id in the usercontrol to get the data

tc.Controls.Add(oc);
tr.Cells.Add(tc);
tOffers.Rows.Add(tr);

//tbl.Rows.AddAt(gvOrderDetail.Rows.Count + 1, row);
}
</div>

 

Despite having the get/sets for every control, I can't seem to set the values of the controls, keep getting a nullreferenceexception. Even if I try to set the values of the controls in the usercontrol itself, I still can't access the controls.

Any pointers?


Andrew Berry - Software analyst/Developer

[RESOLVED] Listbox problem

hello everyone, i hereby undergoing a problm with listbox web control 


i got first listbox..which contains all the information for faculty

Then i have a second lisbox which should display the building for related faculty.(on selectedindexchanged display its respective building name)..

theN from the second listbox when I do selectedindexchanged it should display me a datagrid containing information about the particular building..


but its not wokring as it should be...anyone got the codes for this problem..

help me out please ..:(:(


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Timetable.aspx.cs" Inherits="Availability_of_grid.Timetable" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>View Timetable</title>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
        

        <asp:Table ID="TlbChoose" runat="server" BorderStyle="None" Height="65px"
            Width="313px">
            <asp:TableRow>
                <asp:TableCell>
                    <asp:Label ID="LblFaculty" runat="server" Text="Choose Faculty" Font-Bold="true" Width="150px"></asp:Label>
                </asp:TableCell>
                <asp:TableCell>
                          <asp:ListBox ID="ListFaculty" runat="server" AppendDataBoundItems="true" onselectedindexchanged="ListFaculty_SelectedIndexChanged" AutoPostBack="true" Height="100px" Width="250px">
                    </asp:ListBox>
                </asp:TableCell>
                <asp:TableCell>
                      
                </asp:TableCell>
                <asp:TableCell>
                    <asp:Label ID="LblBuilding" runat="server" Text="Choose Building" Font-Bold="true" Width="150px" ></asp:Label>
                </asp:TableCell>
                <asp:TableCell>
                    <asp:ListBox ID="ListBuilding" runat="server" onselectedindexchanged="ListBuilding_SelectedIndexChanged" Height="100px" Width="250px">
                    </asp:ListBox>
                </asp:TableCell>
            </asp:TableRow>
        

        </asp:Table>
        <br /><br />
          <asp:GridView ID="Grd_booking" DataKeyNames="Booking_ID" runat="server"
               AutoGenerateColumns="false" Height="234px" Width="545px"
               onrowdatabound="Grd_booking_RowDataBound">
        <Columns>
            <asp:TemplateField HeaderText="Day">
                <ItemTemplate>
                    <asp:Label ID="LblDay" runat="server" Text='<%#Eval("Booking_Day")%>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
              <asp:TemplateField HeaderText="Timeslot">
                <ItemTemplate>
                         
                    <asp:Label ID="LblDay" runat="server" Text='<%#Eval("Timeslot_Name")%>'></asp:Label>
                    <br />
                    <asp:Label ID="LblStime" runat="server" Text='<%#Eval("Booking_Starttime")%>'></asp:Label>
                    -
                    <asp:Label ID="LblEtime" runat="server" Text='<%#Eval("Booking_Endtime")%>'></asp:Label>
                 </ItemTemplate>
            </asp:TemplateField>
             <asp:TemplateField HeaderText="Room">
                <ItemTemplate>
                 

                    <asp:Label ID="LblRname" runat="server" Text='<%#Eval("Room_Name")%>'></asp:Label>
                 </ItemTemplate>
            </asp:TemplateField>
             <asp:TemplateField HeaderText="Staff Member">
                <ItemTemplate>
                    <asp:Label ID="Lblfname" runat="server" Text='<%#Eval("Staff_fname")%>'></asp:Label>
                    <asp:Label ID="LblStime" runat="server" Text='<%#Eval("Staff_lname")%>'></asp:Label>
                 </ItemTemplate>
            </asp:TemplateField>
        

        </Columns>
        </asp:GridView>
        

    </div>
    </form>
</body>
</html>

/////this one is my code behind

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

namespace Availability_of_grid
{
    public partial class Timetable : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            LblBuilding.Visible = false;
            ListBuilding.Visible = false;
            
            if(!IsPostBack)
            {              
                PopulateFaculty();
               // BindData();

            }
        }

        protected void PopulateFaculty()
        {
            SqlConnection Con;
            SqlCommand Cmd;
            SqlDataAdapter Da;

            DataSet Ds = new DataSet();
            string Query;
            Con = new SqlConnection("Server=sainalini-PC\\sqlexpress;Initial Catalog=Class;Integrated Security=True;");

            Query = "SELECT * FROM Faculty";
            Cmd = new SqlCommand(Query, Con);

            try
            {
                Con.Open();
                Da = new SqlDataAdapter(Cmd);
                Da.Fill(Ds);
                Cmd.ExecuteNonQuery();
                ListFaculty.DataSource = Ds;
                ListFaculty.DataValueField = "Faculty_ID";
                ListFaculty.DataTextField = "Faculty_Name";
                ListFaculty.DataBind();

            }

            finally
            {
                Con.Close();
            }

        }
       
        protected void ListFaculty_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlConnection Con;
            SqlDataAdapter Da;
            DataSet Ds = new DataSet();
            SqlCommand Cmd = new SqlCommand();
            string Query;

            Con = new SqlConnection("Server=sainalini-PC\\sqlexpress;Initial Catalog=Class;Integrated Security=True;");

            Query = @"SELECT * FROM Building
                    INNER JOIN Faculty ON Building.Faculty_ID=Faculty.Faculty_ID                                                                          
                    WHERE Faculty.Faculty_ID='" + ListFaculty.SelectedItem.Value + "'";

            Cmd = new SqlCommand(Query, Con);

            try
            {
                Con.Open();
                Da = new SqlDataAdapter(Cmd);
                Da.Fill(Ds);
                Cmd.ExecuteNonQuery();               
                ListBuilding.DataSource = Ds;
                ListBuilding.DataValueField = "Building_ID";
                ListBuilding.DataTextField = "Building_Name";
                ListBuilding.DataBind();
                LblBuilding.Visible = true;
                ListBuilding.Visible = true;

            }

            finally
            {
                Con.Close();
            }
        }

       
        protected void ListBuilding_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlConnection Con;
            SqlDataAdapter Da;
            DataSet Ds = new DataSet();
            SqlCommand Cmd = new SqlCommand();
            string Query;

            Con = new SqlConnection("Server=sainalini-PC\\sqlexpress;Initial Catalog=Class;Integrated Security=True;");



            Query = @"SELECT * FROM Booking
                    INNER JOIN Timeslot ON Booking.Timeslot_ID=Timeslot.Timeslot_ID
                    INNER JOIN Staff ON Booking.Staff_ID=Staff.Staff_ID
                    INNER JOIN Resource ON Booking.Resource_ID= Resource.Resource_ID 
                    INNER JOIN Room ON Booking.Room_ID= Room.Room_ID                      
                    INNER JOIN Building ON Booking.Building_ID = Building.Building_ID
                    INNER JOIN Semester ON Booking.Semester_ID=Semester.Semester_ID
                    INNER JOIN StatusType ON Booking.StatusType_ID=StatusType.StatusType_ID                                                                     
                    WHERE Building.Building_ID='" + ListBuilding.SelectedItem.Value+ "'";

            Cmd = new SqlCommand(Query, Con);

            try
            {
                Con.Open();
                Da = new SqlDataAdapter(Cmd);
                Da.Fill(Ds);
                Cmd.ExecuteNonQuery();
                Grd_booking.DataSource = Ds;
                Grd_booking.DataKeyNames = new string[] { "Booking_ID" };
                Grd_booking.DataBind();               
            }

            finally
            {
                Con.Close();
            }
        }

        protected void Grd_booking_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {

                e.Row.ToolTip = "Booking Title: " +
             Convert.ToString(DataBinder.Eval(e.Row.DataItem,
             "Booking_Title")) + "\n" + "Room Capacity: " +
             Convert.ToString(DataBinder.Eval(e.Row.DataItem,
             "Room_Capacity")) + "\n" + "Resource Name: " +
             Convert.ToString(DataBinder.Eval(e.Row.DataItem,
             "Resource_Name"));

            }


        }
    }
}





There is a property "AutoPostBack" for listbox. Set this property to true for Building listbox in same way as you did for Faculty listbox.

This means your list box code will looklike

<asp:ListBox ID="ListBuilding" runat="server" onselectedindexchanged="ListBuilding_SelectedIndexChanged" Height="100px" Width="250px"  AutoPostBack="true"></asp:ListBox>



Thank you so much it is solved:-)

Have a good day ahead:-D

My pleasure! :)

Please don't forget to mark the post as Answer if a post has helped you.


Happy Coding!

So can you specify exactly what do not work. Try by setting AutoPostBack="true" on your listboxes. And one advice do not concatenate your sql queries (WHERE Building.Building_ID='" + ListBuilding.SelectedItem.Value+ "'";   ) Instead this your parameters http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx .

[RESOLVED] Datagrid columns like Excel

I have a requirement to display alphabets above each datagrid column names (like shown in excel). The datagrid columns (and header text) are dynamically created at run time and the alphabets on top could grow like excel A, B, C,....Z, AA, AB, AC,.....AZ etc. Any suggestions on how to get the alphabets showing for each columns?

Thanks

I did following sample, Here you go... Following sample is just to test the gridview look as per your requirement.

"GetGridViewHeaders()" function can be optimized not to use many loops. I just wrote 5 loops to get 5 level combinations of alphabets to test the grid.

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView runat="server" ID="GridView1">
            <Columns>
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    private int NumberOfColumns { get; set; }

    protected void Page_Load(object sender, EventArgs e)
    {
        NumberOfColumns = 10;
        PrepareGridView();
    }

    private void PrepareGridView()
    {
        AddHeadersToGridView();
        AddDataToGridView();
    }

    private void AddHeadersToGridView()
    {
        List<string> headers = GetGridViewHeaders(NumberOfColumns);
        
        foreach (var headerText in headers)
        {
            BoundField nameColumn = new BoundField();
            nameColumn.HeaderText = headerText;
            GridView1.Columns.Add(nameColumn);           
        }
    }

    private void AddDataToGridView()
    {
        //Add Data.
    }

    // THis function can be optimized not to use many loops. 
    //I just wrote 5 loops to get 5 level combinations of alphabets to test the grid.
    private List<string> GetGridViewHeaders(int nuOfColumns)
    {
        List<string> alphas = new List<string> 
                { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

        List<string> headers = new List<String>();
        int i = 0;

        foreach (var alpha in alphas)
        {
            if (i++ >= nuOfColumns)
                break;
            headers.Add(alpha);
        }

        foreach (var alpha1 in alphas)
        {
            foreach (var alpha2 in alphas)
            {
                foreach (var alpha3 in alphas)
                {
                    foreach (var alpha4 in alphas)
                    {
                        foreach (var alpha5 in alphas)
                        {
                            if (i++ >= nuOfColumns) 
                                break;
                            headers.Add(alpha1 + alpha2 + alpha3 + alpha4 + alpha5);
                        }
                        if (i++ >= nuOfColumns) break;
                    }
                    if (i++ >= nuOfColumns) break;
                }
                if (i++ >= nuOfColumns) break;
            }
            if (i++ >= nuOfColumns) break;
        }
        return headers;
    }
}