Category Archives: LinqDataSource

[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

Custom paging in ASP.NET GridView control

currently it works if i remove the PagerSetting or remove the PagerTemplate so if have both (PagerSetting & PagerTemplate) then my page number not display.

my qeustion is: how can i display both (PagerTemplate and PagerSetting) togather at the bottom of the Gridview ? plese see the below source code.

 

<asp:GridView ID="gvTable" runat="server" ShowHeader="true"
  PageSize="5" AllowPaging="true" AllowSorting="true"
  DataSourceID="myLinqDataSource" AutoGenerateColumns="false"
  OnRowDataBound="GridView_DataBound">
  <Columns>
    <asp:BoundField DataField="Edited" HeaderText="Date" DataFormatString="{0:d}" />
    <asp:BoundField DataField="Activity" HeaderText="Notes" />
  </Columns>
<PagerStyle CssClass="pager-row" />
                    <RowStyle CssClass="row" />
                    <PagerSettings Mode="NumericFirstLast" PageButtonCount="7" FirstPageText="«" LastPageText="»" />
                   **<PagerTemplate>**
                        <div style="float: left; margin-left: 12px;">
                            <div style="float: left; margin: 4px 6px 0px 0px;">Page Size</div>
                            <asp:DropDownList ID="ddlPageSizeChange" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSizeChange">
                                <asp:ListItem>15</asp:ListItem>
                                <asp:ListItem>25</asp:ListItem>
                                <asp:ListItem>50</asp:ListItem>
                                <asp:ListItem>100</asp:ListItem>
                            </asp:DropDownList>
                        </div>
                        <div class="gridCount" runat="server" id="divGridCount"><b>1</b> Items Found  </div>
                    </PagerTemplate>
</asp:GridView>


 

You have to code the same behavior than the NumericFisrtLast in the PagerTemplate. Ex : http://sureshsharmaaspdotnet.wordpress.com/2008/07/07/paging-in-gridview-using-pagertemplate/

[RESOLVED] adding value in dropdownlist manully

hi, i've added a dropdownlist filled with linq data on my page, works perfectly. However when i want to add an extra item listed with selected=true its doesn't show in the list.

appreciate your help!

<asp:DropDownList ID="frmCity1" runat="server"
                    DataSourceID="LinqDataSource1" DataTextField="CityName"
                    DataValueField="CityName">
                    <asp:ListItem Value="0" Enabled="true">Selecteer city</asp:ListItem>
                </asp:DropDownList>
                <asp:LinqDataSource ID="LinqDataSource1" runat="server"
                    ContextTypeName="CitiesDataContext" EntityTypeName="" OrderBy="CityName"
                    TableName="sys_cities">
                </asp:LinqDataSource>

in .cs

    protected void Page_Load(object sender, EventArgs e)
    {
        ListItem newItem = new ListItem();
        newItem.Text = "select city";
        newItem.Value = "all";
        newItem.Selected = true;
        frmCity1.Items.Add(newItem);

    }
 

<asp:DropDownList ID="frmCity1" runat="server"
                    DataSourceID="LinqDataSource1" DataTextField="CityName"
                    DataValueField="CityName" AppendDataBoundItems="true">
                    <asp:ListItem Value="0" Selected="true">Selecteer city</asp:ListItem>
                </asp:DropDownList>

thanks limno!

[RESOLVED] First value of dropdownlist, sql, asp.net, c#

 down votefavorite  Hello all

I have the next code of ddl (asp.net , c#):

<asp:DropDownList ID="ddl1" runat="server" AutoPostBack="True"
    DataSourceID="LinqDataSource1" DataTextField="CarName1"
    DataValueField="id" onselectedindexchanged="ddl1_SelectedIndexChanged"
 

<asp:LinqDataSource ID="LinqDataSource1" runat="server"
    ContextTypeName="DataClassesDataContext"  EntityTypeName="" TableName="CarNames">
</asp:LinqDataSource>


as you see , the ddl get the values from sql table... i want that the first value display --select car-- ...

I have tried many options and answers here, include the listitem option with AppendDataBoundItems = "true" but the page shown me an error .. somthing about int32, obj .... when i succed that was with the code behind:

ddl1.Items.Insert(1, "Select One");

(just for the record - the onselectedindexchanged="ddl1_SelectedIndexChanged" is for cascading anothers ddl)..

thanks !
 

but that works and put the --select car-- at the second place after the value from the sql .... when i put ddl1.Items.Insert(0, "Select One");
that's shown me an erroe about operator 32int...

oshrib

but that works and put the --select car-- at the second place after the value from the sql .... when i put ddl1.Items.Insert(0, "Select One");
that's shown me an erroe about operator 32int...

Here is a way of doing it in DropDownList's databound event..

<asp:DropDownList ID="ddl1" runat="server" AutoPostBack="True"
DataSourceID="LinqDataSource1" DataTextField="CarName1"
DataValueField="id" onselectedindexchanged="ddl1_SelectedIndexChanged"
OnDataBound="ddl1_DataBound" >
protected void ddl1_DataBound(object sender, EventArgs e)
{
ddl1.Items.Insert(0, new ListItem("Select One", "0"));
}

Hi, thanks

thats give me an error:

protected void ddl1_DataBound(object sender, EventArgs e)
{
ddl1.Items.Insert(0, new ListItem("Select One", "0"));
}

but that works, (but give me the "select one" at the second place:

protected void ddl1_DataBound1(object sender, EventArgs e)
{
ddl1.Items.Insert(1, new ListItem("Select One", "0"));
}

the error:

פרטי חריג: System.Web.Query.Dynamic.ParseException: האופרטור '==' אינו תואם לסוגי האופרנד 'Int32' ו- 'Object'


Try this:

<asp:DropDownList ID="ddl1" runat="server" AutoPostBack="True"
    DataSourceID="LinqDataSource1" DataTextField="CarName1"
    DataValueField="id" onselectedindexchanged="ddl1_SelectedIndexChanged"
    AppendDataBoundItems="true"">
            <asp:ListItem Selected="True"" Text="(select something)" Value="WhatEver" />
</asp:DropDownList>

Hi,

This will also work.


<asp:DropDownList ID="ddl1" runat="server" AutoPostBack="True" 
    DataSourceID="LinqDataSource1" DataTextField="CarName1" 
    DataValueField="id" onselectedindexchanged="ddl1_SelectedIndexChanged"
    AppendDataBoundItems="true"">
            <asp:ListItem>--Select One--</asp:ListItem>
</asp:DropDownList>

Hi, thanks.

i been tried that, but i got that error: Input string was not in a correct format.

i "succed" at other way:

protected void ddl1_DataBound(object sender, EventArgs e) 


Convert.ToInt32(ddl1.SelectedValue);
ddl1.Items.Insert(0, new ListItem("-Choose car-", "-Choose car-"  
));


and that ok - the  "-choose-" is in the first place but the problem now is that if i have values, for example, the dropdownlist show like that:

-Choose car-
Subaro
Fiat
Honda

 the first value that display is the Subaro, and to see the -choose car- the user need to open the dropdownlist and then he will see the -choose car- at the first place. how can i do that from the start, from the page load - the -choose car- will display at the ddl from the page load. where i wrong at the code ?

 

I prefer not to do something in code when it is simple to do in markup.  Thus I recommend the kind of approach that Amit Prasad and I have posted.  If you try that again, and get the same error message (or any other), post your markup and we'll look at it.

Otherwise, if you really want to go with an approach that uses code, you should do it immediately after your DropDownList is databound (i.e. in its .DataBound event handler).

If that doesn't help, could you please post all your related markup and code, and tell us the exact error message you are getting?

(It makes things a lot more readable if you use the Insert Code tool. If you need instructions, see this How to use the Forums' "Insert Code" tool post.)

WombatEd

I prefer not to do something in code when it is simple to do in markup.  Thus I recommend the kind of approach that Amit Prasad and I have posted.  If you try that again, and get the same error message (or any other), post your markup and we'll look at it.

Otherwise, if you really want to go with an approach that uses code, you should do it immediately after your DropDownList is databound (i.e. in its .DataBound event handler).

If that doesn't help, could you please post all your related markup and code, and tell us the exact error message you are getting?

(It makes things a lot more readable if you use the Insert Code tool. If you need instructions, see this How to use the Forums' "Insert Code" tool post.)

 

<div class="frstbtmbox">  <div class="CarName">
    <asp:DropDownList ID="ddl1"  runat="server" AutoPostBack="True"
        DataSourceID="LinqDataSource1" DataTextField="CarName1"
        DataValueField="id" AppendDataBoundItems="true"  onselectedindexchanged="ddl1_SelectedIndexChanged"
            > <asp:ListItem Selected="True" Text="(select something)" Value="1" />
</asp:DropDownList>

        <asp:LinqDataSource ID="LinqDataSource1" runat="server"
            ContextTypeName="DataClassesDataContext" EntityTypeName="" TableName="CarNames">
        </asp:LinqDataSource>

the error:
האופרטור '==' אינו תואם לסוגי האופרנד 'Int32' ו- 'Object' (i'm even don't know how to translate that to english..maybe: Operator '==' is incompatible with operand types 'Int32' and 'Object').

i have 4 cascading ddl's. car name, car type, car year, items.  all the ddl's are enabled = false (only the ddl1 enabled=true), and turn to true by click at the previuos ddl.
(i don't think that info is important for my question, but anyway, that part from my code) i.e :

    protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
    {

        
        ListBox2.Enabled = true;
    }

The ddl's get their values from sql, with LINQ. the others ddl's (ex: car type) get their value by the id of the previous ddl, i made that with WHERE, operator == and Control at the source.

 

        <asp:DropDownList ID="ddl2" runat="server"  AutoPostBack="True" 
            DataSourceID="LinqDataSource2" DataTextField="CarType1" 
            DataValueField="id" Enabled="False" 
            onselectedindexchanged="ddl2_SelectedIndexChanged" AppendDataBoundItems="false" >
            <asp:ListItem Text = "בחר סוג רכב" Value = ""></asp:ListItem>
        </asp:DropDownList>
        
        <asp:LinqDataSource ID="LinqDataSource2" runat="server" 
            ContextTypeName="DataClassesDataContext" EntityTypeName="" TableName="CarTypes" 
            Where="CarNameId == @CarNameId">
            <WhereParameters>
                <asp:ControlParameter ControlID="ListBox1" DefaultValue="1" Name="CarNameId" 
                    PropertyName="SelectedValue" Type="Int32" />
            </WhereParameters>

thats works !!!! yesss after 2 days !!! thank you all... here's my code:

 

<asp:DropDownList ID="ddl2" runat="server"  AutoPostBack="True"
            DataSourceID="LinqDataSource2" DataTextField="CarType1"
            DataValueField="id" Enabled="False"
            onselectedindexchanged="ddl2_SelectedIndexChanged" AppendDataBoundItems="true" >
            <asp:ListItem Text = "בחר סוג רכב" Value = "0"></asp:ListItem>
        </asp:DropDownList>
        
        <asp:LinqDataSource ID="LinqDataSource2" runat="server"
            ContextTypeName="DataClassesDataContext" EntityTypeName="" TableName="CarTypes"
            Where="CarNameId == @CarNameId">
            <WhereParameters>
                <asp:ControlParameter ControlID="ddl1" DefaultValue="1" Name="CarNameId"
                    PropertyName="SelectedValue" Type="Int32" />
            </WhereParameters>
        </asp:LinqDataSource>
   </div> <div class="CarYear"">
        <asp:DropDownList ID="ddl" runat="server" DataSourceID="LinqDataSource3"
            DataTextField="CarYear1" DataValueField="id" Enabled="False"
            onselectedindexchanged="ddl_SelectedIndexChanged" AutoPostBack="True" AppendDataBoundItems="true">            <asp:ListItem Selected = "True" Text = "בחר שנת רכב" Value = ""></asp:ListItem>
        <asp:ListItem Text="בחר שנה" Value="" />   </asp:DropDownList>
        <asp:LinqDataSource ID="LinqDataSource3" runat="server"
            ContextTypeName="DataClassesDataContext" EntityTypeName="" TableName="CarYears"
            Where="CarTypeId == @CarTypeId">
            <WhereParameters>
                <asp:ControlParameter ControlID="ddl2" DefaultValue="1" Name="CarTypeId"
                    PropertyName="SelectedValue" Type="Int32" />
            </WhereParameters>
        </asp:LinqDataSource>
    </div> </div>
    
    
    <div class="thirdbtmbox">
    <div class="matzber">
        <asp:DropDownList ID="ddl" runat="server" DataSourceID="LinqDataSource4"
            DataTextField="MatzberName" DataValueField="id"  Enabled="False" AppendDataBoundItems="true"> <asp:ListItem Text="התאם מצבר" Value="0" />
        </asp:DropDownList>
        <asp:LinqDataSource ID="LinqDataSource4" runat="server"
            ContextTypeName="DataClassesDataContext" EntityTypeName="" TableName="Matzbers"
            Where="CarTypeId == @CarTypeId">
            <WhereParameters>
                <asp:ControlParameter ControlID="ddl2" Name="CarTypeId"
                    PropertyName="SelectedValue" Type="Int32" />
            </WhereParameters>
        </asp:LinqDataSource>

i just made on all the ddl's AppendDataBoundItem=true and to make them listitem with value="0" !!!

you fixed one thing and see other problem .. :)

now the problem is:

if i select at ddl1: subaro ... the ddl2 show : subaro-type ... great untill here... now..after that select - i want to choose Fiat at the ddl1.. i'm select Fiat, and ddl2 show now: subaro-type, fiat-type. as you see... the subaro-type still there... after i select fiat i want that the subaro will disappear..
more clearly explain of the problem:
first selection:
ddl1:           ddl2:
subaro         subaro-type

second selection:
ddl1:   ddl2:
Fiat     subaro-type
           Fiat-type

maybe i use wrong cascading way? i tried at the past AJAX and other things, but i like to work with sql and LINQ. someone have an idea?

<div class="mcePaste" id="_mcePaste" style="position: absolute; width: 1px; height: 1px; overflow: hidden; top: 0px; left: -10000px;"></div>

yes !!!!! also that fixed...here how:

protected void Page_Load(object sender, EventArgs e)
    {

        if (IsPostBack)
        {
            ddl2.Items.Clear();
        }
}

[RESOLVED] Click at one page and get the result at gridview at other page (asp.net c#)

Hello all,

i have 2 aspx pages:  1.aspx , 2.aspx ...

1.aspx include some links with the code:

 

<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "2.aspx?ItemId=" + Eval("Id").ToString() %>' >For more info click here</asp:HyperLink>

i want that at 2.aspx i will get the values by id, and to show the values at gridview that i have there.

 

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        BackColor="Black" BorderColor="Black" BorderStyle="None" BorderWidth="1px" 
        CellPadding="3" DataKeyNames="id" DataSourceID="LinqDataSource1" 
        GridLines="Vertical">
        <Columns>

            <asp:BoundField DataField="AmperHour"  HeaderText="אמפר שעה" 
                SortExpression="AmperHour" >
            <HeaderStyle BackColor="Black" />
            <ItemStyle BackColor="#FFFF99" />
            </asp:BoundField>
            <asp:BoundField DataField="RC" HeaderText="RC" SortExpression="RC" >
            <HeaderStyle BackColor="Black" />
            <ItemStyle BackColor="#FFFF99" />
            </asp:BoundField>
            <asp:BoundField DataField="Metah" HeaderText="מתח (וולט)" 
                SortExpression="Metah" >
            <HeaderStyle BackColor="Black" />
            <ItemStyle BackColor="#FFFF99" />
            </asp:BoundField>
            <asp:BoundField DataField="ZeremCCA" HeaderText="זרם התנעה CCA" 
                SortExpression="ZeremCCA" >
            <HeaderStyle BackColor="Black" />
            <ItemStyle BackColor="#FFFF99" />
            </asp:BoundField>
            <asp:BoundField DataField="Yemin" HeaderText="ימין" SortExpression="Yemin" />
            <asp:BoundField DataField="Smol" HeaderText="שמאל" SortExpression="Smol" />
            <asp:BoundField DataField="Height" HeaderText="גובה" SortExpression="Height" >
            <HeaderStyle BackColor="Black" />
            <ItemStyle BackColor="#FFFF99" />
            </asp:BoundField>
            <asp:BoundField DataField="Width" HeaderText="רוחב" SortExpression="Width" >
            <HeaderStyle BackColor="Black" />
            <ItemStyle BackColor="#FFFF99" />
            </asp:BoundField>
            <asp:BoundField DataField="Oreh" HeaderText="אורך" SortExpression="Oreh" >
            <HeaderStyle BackColor="Black" />
            <ItemStyle BackColor="#FFFF99" />
            </asp:BoundField>
            <asp:BoundField DataField="Benzin" HeaderText="בנזין" SortExpression="Benzin" />
            <asp:BoundField DataField="Soler" HeaderText="סולר" SortExpression="Soler" />
        </Columns>
        <FooterStyle BackColor="White" ForeColor="#000066" />
        <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" 
            HorizontalAlign="Center" VerticalAlign="Middle" />
        <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
        <RowStyle BackColor="#FFCC99" BorderColor="#333300" BorderWidth="1px" 
            ForeColor="#000066" HorizontalAlign="Center" VerticalAlign="Middle" />
        <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#F1F1F1" />
        <SortedAscendingHeaderStyle BackColor="#007DBB" />
        <SortedDescendingCellStyle BackColor="#CAC9C9" />
        <SortedDescendingHeaderStyle BackColor="#00547E" />
    </asp:GridView>
    <asp:LinqDataSource ID="LinqDataSource1" runat="server" 
        ContextTypeName="DataClassesDataContext" EntityTypeName="" TableName="Technis">
    </asp:LinqDataSource>

 

at 1.aspx i get the values by table: "items" with id

at 2.aspx i get the values by table : "Items_Details" and i made relatshionship by premier key itemsId...

 

Here's an example using the ADO.NET way..

In your 2.aspx page you can do something like this:

private string GetConnectionString()
{
    //DBConnection is the name of the connection string that was set up from the web config file
    return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
}


private void BindGridView(string field)
{
    
    DataTable dt = new DataTable();
    SqlConnection connection = new SqlConnection(GetConnectionString());
    try {
        connection.Open();
        string sqlStatement = "SELECT * FROM TableName WHERE ColumnName = @Value1";
        SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
        sqlCmd.Parameters.AddWithValue("@Value1", field);
        SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
        sqlDa.Fill(dt);
        if (dt.Rows.Count > 0) {
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
        else {
        // NO RECORDS FOUND
        }

    }
    finally {
        connection.Close();
    }
}

protected void Page_Load(object sender, EventArgs e) {
    if (!IsPostBack) {
         if(Request.QueryString["ItemId"] != null){
             BindGridView(Request.QueryString["ItemId"].ToString());
         }
    }
}

You can also use SqlDataSource and bind the GridView in a configurative manner.

<SqlDataSource ConnectionString="" SelectCommand="Select statement">
<SelectParameters>