?
Solved

c# gridview insert row not working when inserting into mysql db

Posted on 2009-04-21
3
Medium Priority
?
1,928 Views
Last Modified: 2013-12-17
I have a gridview in which I have items that can be updated, deleted, and inserted. The update and delete works fine, but for some reason I cannot get the Insert to work. Any help would be much appreciated. I believe it has something to do with the list parameter not being properly variablized, but not sure how to fix it.
//BACKEND CODE//
 
using System;
using System.Data;
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;
using System.Data.Odbc;
using System.Collections.Generic;
//using System.Data.SqlClient;
using System.Data.Common;
 
public partial class maintenance : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["Connection"].ToString();
 
        using (OdbcConnection con = new OdbcConnection(ConnStr))
 
        //using (OdbcCommand cmd = new OdbcCommand("Select distinct projectleader from  projectmanager order by projectleader", con))
        using (OdbcCommand cmd = new OdbcCommand("Select ID, maintenanceType as Maint from maintenance order by maintenanceType", con))
        {                                     
 
          con.Open();
        
            using (OdbcDataReader dr = cmd.ExecuteReader())
            if (!IsPostBack)
                {
                    drpMaintenance.DataSource = dr;
                    drpMaintenance.DataMember = "Maint";
                    drpMaintenance.DataValueField = "Maint";
                    drpMaintenance.DataBind();
                    drpMaintenance.Items.Insert(0, new ListItem("SELECT", "SELECT"));
                    dr.Close();
                }
                con.Close();
        }
    }
 
 
    protected void drpMaintenance_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drpMaintenance.SelectedValue == "Classification Master")
        {
            Response.Redirect("classificationMaster.aspx");
        }
        if (drpMaintenance.SelectedValue == "Priority Master")
        {
            Response.Redirect("PriorityMaster.aspx");
        }
        if (drpMaintenance.SelectedValue == "Region Master")
        {
            Response.Redirect("PriorityMaster.aspx");
        }
        if (drpMaintenance.SelectedValue == "Username Master")
        {
            Response.Redirect("PriorityMaster.aspx");
        }
    }
 
    private List<OdbcParameter> insertParameters = new List<OdbcParameter>();
 
    protected void maintenance_RowCommand(object sender, GridViewCommandEventArgs e)
    {
 
        if (e.CommandName == "NoDataInsert")
        {
 
            TextBox MaintenanceType = mainte.Controls[0].Controls[0].FindControl("NoDataMaintenanceType") as TextBox;
 
            OdbcParameter maintenanceType = new OdbcParameter("@maintenanceType", OdbcType.VarChar, 30);
 
            maintenanceType.Direction = ParameterDirection.Input;
 
            maintenanceType.Value = MaintenanceType.Text;
 
            insertParameters.Add(maintenanceType);
 
 
            SqlDataSource1.Insert();
 
        }
 
        else if (e.CommandName == "InsertNew")
        {
 
            TextBox MaintenanceType = mainte.FooterRow.FindControl("InsertMaintenanceType") as TextBox;
 
            OdbcParameter mType = new OdbcParameter("@MaintenanceType", OdbcType.VarChar, 30);
 
            mType.Direction = ParameterDirection.Input;
            mType.Value = MaintenanceType.Text;
            insertParameters.Add(mType);
 
            SqlDataSource1.Insert();
 
        }
 
    }
    //protected void Sqldatasource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    //{
        //e.Command.Parameters.Clear();
        //foreach (OdbcParameter p in insertParameters)
            //e.Command.Parameters.Add(p);
    //}
 
    protected void Sqldatasource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        insertParameters.Clear();
    }
}
 
//FRONT END CODE//
 
<body style="font-family:Arial;font-size:12px;color:#000000;">
    <form id="form2" runat="server">
    <div style="background-image: url(images/head-bg.jpg); background-repeat: repeat-x; background-color: transparent; text-align: center" title="Tracking">
        <uc1:Menu ID="Menu1" runat="server" />
    </div>
    <br />
    <br />
    <table cellspacing="0" cellpadding="0" width="800">
        <tr>
            <td style="width:200px;height:40px;">What would you like to maintain? </td>
        </tr>
        <tr>
            <td style="width:600px;"><asp:DropDownList ID="drpMaintenance" runat="server" AutoPostBack="True" OnSelectedIndexChanged="drpMaintenance_SelectedIndexChanged" TabIndex="3" Width="109px">
            </asp:DropDownList></td>
        </tr>
    </table>
    <br />
    <br />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:etcopsprojmgrConnectionString %>"
        ProviderName="<%$ ConnectionStrings:etcopsprojmgrConnectionString.ProviderName %>"
        SelectCommand="SELECT ID, maintenanceType FROM maintenance order by maintenanceType"
        InsertCommand="Insert into maintenance(maintenanceType) values (@maintenanceType)"
        UpdateCommand="Update maintenance set maintenanceType=? where ID=?"
        DeleteCommand="Delete from maintenance where ID=?" OnInserted="Sqldatasource1_Inserted">
    </asp:SqlDataSource>
    <asp:GridView ID="mainte" runat="server" AutoGenerateColumns="False"
 
        DataSourceID="SqlDataSource1" DataKeyNames="ID" ShowFooter="true"
 
        onrowcommand="maintenance_RowCommand">
 
        <Columns>
 
            <asp:TemplateField HeaderText="ID">
 
                <ItemTemplate>
 
                    <%# Eval("ID") %>
 
                </ItemTemplate>
 
            </asp:TemplateField>
 
            <asp:TemplateField HeaderText="Maintenance Type">
 
                <ItemTemplate>
 
                    <%# Eval("maintenanceType") %>
 
                </ItemTemplate>
 
                <EditItemTemplate>
 
                    <asp:TextBox runat="server" ID="EditMaintenanceType" Text='<%# Bind("maintenanceType") %>' />
 
                </EditItemTemplate>
 
                <FooterTemplate>
 
                    <asp:TextBox runat="server" ID="InsertMaintenanceType" Text='<%# Bind("maintenanceType") %>' />
 
                </FooterTemplate>
 
            </asp:TemplateField>
 
 
 
            <asp:TemplateField HeaderText="Commands">
 
                <ItemTemplate>
 
                    <asp:Button runat="server" ID="Edit" Text="Edit" CommandName="Edit" />
 
                    <asp:Button runat="server" ID="Delete" Text="Delete" CommandName="Delete" />
 
                </ItemTemplate>
 
                <EditItemTemplate>
 
                    <asp:Button runat="server" ID="Update" Text="Update" CommandName="Update" />
 
                    <asp:Button runat="server" ID="Cancel" Text="Cancel" CommandName="Cancel" />
 
                </EditItemTemplate>
 
                <FooterTemplate>
 
                    <asp:Button runat="server" ID="Insert" Text="Insert" CommandName="InsertNew" />
 
                    <asp:Button runat="server" ID="Cancel" Text="Cancel" CommandName="CancelNew" />
 
                </FooterTemplate>
 
            </asp:TemplateField>
 
        </Columns>
 
        <EmptyDataTemplate>
 
            ID:<asp:TextBox runat="server" ID="NoDataID" />
 
            MaintenanceType:<asp:TextBox runat="server" ID="NoDataMaintenanceType" />
 
            <asp:Button runat="server" ID="NoDataInsert" CommandName="NoDataInsert" Text="Insert"/>
 
        </EmptyDataTemplate>
 
    </asp:GridView> 
    </form>
</body>

Open in new window

0
Comment
Question by:bschave2
3 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24202746
What is happening?  Are you getting exceptions or nothing happening?  ASP.NET framework version?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 1500 total points
ID: 24202920
You need to add the parameters to the InsertParameters collection of the SqlDataSource1 not a general list variable. So replace this line
insertParameters.Add(mType);

with
SqlDataSource1.InsertParameters.Add(mType);
0
 

Author Comment

by:bschave2
ID: 24316960
no, the issue was that the variable @maintenanceType should have been just maintenanceType. thanks for your help though.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 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