The problem with inserts and updates

Question

The problem with inserts and updates

I am noob in ASP.NET/C#, but I will do my best to describe my problem as best as possible.

Now I have a gridview to which the sql source is bound; It captures the right information. The information about the gridview will vary depending on what is selected in the drop-down list that is outside the grid that works. The problem is updating and pasting.

After trying to update. (As soon as I click the "Edit" button on one of the columns), I get the following error message: "DropDownList2" has a SelectedValue value, which is invalid because it does not exist in the list of items. Parameter Name: Value

I split the columns into templates as shown below, so I can manage them more easily. Drop-down list 2 is in my editing template. It is connected to a data source; this is the same source as my first dropdownmenu, which works flawlessly. Therefore, I do not believe that it will be sql behind this procedure. However, I have this dropdownlist 2 related to Doctor. Now I was told to bind it, so I don’t know how the bindings work

If I untie it, I can at least see the grid even after I click the "Edit" button, but after updating I get an error message: The procedure or function uspPatientUpdate contains too many arguments.

Now I looked at the online solution, but I can not wrap my head around the binding. If necessary, I will give the following code.

ASP:

<asp:SqlDataSource ID="sdPatient" runat="server" ConnectionString="<%$ ConnectionStrings:MedicalOfficeConnectionString %>" DeleteCommand="usp_PatientDelete" InsertCommand="uspPatientInsert" SelectCommand="uspPatientSelectByIDOrSelectAll" UpdateCommand="uspPatientUpdate" SelectCommandType="StoredProcedure" DeleteCommandType="StoredProcedure" InsertCommandType="StoredProcedure" UpdateCommandType="StoredProcedure">
                <DeleteParameters>
                    <asp:Parameter Name="ID" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="OHIP" Type="String" />
                    <asp:Parameter Name="FirstName" Type="String" />
                    <asp:Parameter Name="LastName" Type="String" />
                    <asp:Parameter DbType="Date" Name="DOB" />
                    <asp:Parameter Name="VisitsPerYear" Type="Int32" />
                    <asp:Parameter Name="DoctorID" Type="Int32" />
                    <asp:Parameter Name="Timestamp" Type="Byte"></asp:Parameter>
                </InsertParameters>
                <SelectParameters>
                    <asp:ControlParameter ControlID="DropDownList1" Name="DoctorID" PropertyName="SelectedValue" Type="Int32" DefaultValue="0" />
                </SelectParameters>
                <UpdateParameters>
                    <asp:Parameter Name="ID" Type="Int32" />
                    <asp:Parameter Name="OHIP" Type="String" />
                    <asp:Parameter Name="FirstName" Type="String" />
                    <asp:Parameter Name="LastName" Type="String" />
                    <asp:Parameter DbType="Date" Name="DOB" />
                    <asp:Parameter Name="VisitsPerYear" Type="Int32" />
                    <asp:Parameter Name="DoctorID" Type="Int32" />
                    <asp:Parameter Name="ID" Type="Int32" />
                    <asp:Parameter Name="Timestamp" Type="Byte"></asp:Parameter>
                </UpdateParameters>
            </asp:SqlDataSource>
            <p>Select Patient By Doctor:<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="sdDoctorList" DataTextField="Doctor" DataValueField="ID" AppendDataBoundItems="True">
                <asp:ListItem Value="0">All Doctors</asp:ListItem>
                </asp:DropDownList>
            <asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False" BackColor="White" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px" CellPadding="4" DataSourceID="sdPatient" GridLines="Horizontal">
                    <Columns>
                        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                        <asp:TemplateField HeaderText="OHIP" SortExpression="OHIP">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("OHIP") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label1" runat="server" Text='<%# Bind("OHIP") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="First Name" SortExpression="FirstName">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label2" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Last Name" SortExpression="LastName">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("LastName") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label3" runat="server" Text='<%# Bind("LastName") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="DOB" SortExpression="DOB">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("DOB") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label4" runat="server" Text='<%# Bind("DOB") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Visits Per Year" SortExpression="VisitsPerYear">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("VisitsPerYear") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label5" runat="server" Text='<%# Bind("VisitsPerYear") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Doctor" SortExpression="Doctor">
                            <EditItemTemplate>
                                <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="sdDoctorList" DataTextField="Doctor" DataValueField="ID"  SelectedValue='<%# Bind("Doctor") %>'>
                                </asp:DropDownList>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label6" runat="server" Text='<%# Bind("Doctor") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <FooterStyle BackColor="White" ForeColor="#333333" />
                    <HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="White" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#F7F7F7" />
                    <SortedAscendingHeaderStyle BackColor="#487575" />
                    <SortedDescendingCellStyle BackColor="#E5E5E5" />
                    <SortedDescendingHeaderStyle BackColor="#275353" />
                </asp:GridView>

Procedures:

This is for dropdowns. It gives a name as well as a meaning. * Note. This procedure works for the first drop-down list, and I believe for the second.

ALTER PROCEDURE dbo.uspDoctorList
AS
BEGIN
    SET NOCOUNT ON
    SELECT     ID, LastName +', ' + FirstName AS 'Doctor'
    FROM         Doctor
    ORDER BY 'Doctor'
END

This is an update procedure. Not sure if it's someone who is to blame or not, or if it's just my ASP

ALTER PROCEDURE dbo.uspPatientUpdate
    @ID int,
    @OHIP char(10),
    @FirstName nvarchar(20),
    @LastName nvarchar(40),
    @DOB date,
    @VisitsPerYear int,
    @DoctorID int,
    @Timestamp Timestamp

AS
BEGIN
    SET NOCOUNT OFF
    UPDATE Patient
    SET OHIP = @OHIP,
      FirstName = @FirstName, 
      LastName = @LastName,
      DOB = @DOB,
      VisitsPerYear = @VisitsPerYear,
      DoctorID = @DoctorID
    WHERE ID = @ID AND Timestamp = @Timestamp
END

I would be grateful for any help. By the way, this material is a little more than what we were taught (using stored procedures in ASP), I am learning an extra mile for bonus points. Thanks in advance.

If you need more information, please ask.

+5

sql sql-server asp.net stored-procedures

1011 1110 Apr 08 '13 at 2:16

source share

2 answers

, - ( , , asp.net ) Appcode foll

**SqlConnection con;
SqlCommand cmd;
SqlDataAdapter da;
private void Openconnection()
{
    if (con == null)
    {
        con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Nameofconn_defin in web.config file"].ConnectionString);
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        cmd = new SqlCommand();
        cmd.Connection = con;
    }
}
private void Closeconnection()
{
    if (con.State == ConnectionState.Open)
    {
        con.Close();
    }
}
private void Disposeconnection()
{
    if (con != null)
    {
        con.Dispose();
        con = null;
    }
}

public DataTable GetDatatable ( strsql) { OpenConnection(); DataTable dt = new DataTable(); cmd.CommandType = CommandType.Text; cmd.CommandText = strsql; cmd.CommandTimeout = 1000; da = SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(); Closeconnection(); Disposeconnection(); return dt; }

ASPX.CS FILE

public void BindBacecurrency()
    {
        try
        {
            string str = "select * from Currency_Master";
            DataTable dt = dut.GetDatatable(str);
            ddlbasecurrency.DataSource = dt;
            ddlbasecurrency.DataValueField = dt.Columns["Currency_Id"].ToString();
            ddlbasecurrency.DataTextField = dt.Columns["Currency_Name"].ToString();
            ddlbasecurrency.DataBind();
            ddlbasecurrency.Items.Insert(0, "-------Select-------");


        }
        catch (Exception ex)
        {
            Response.Write(ex.Message.ToString());
            ScriptManager.RegisterStartupScript(this, this.GetType(), "message", "<script> alert('System Error ! Contact Your Service Provider ');</script>", false);
        }
    }

i.e , HTML

<asp:DropDownList ID="ddBalanceType" runat="server"  BackColor="#AFC7C7" class ="small-field size5" >
               <asp:ListItem  Text="-------------Select-------------"></asp:ListItem>
               <asp:ListItem Value="1" Text="CR"></asp:ListItem>
               <asp:ListItem Value="0" Text="DR"></asp:ListItem>
            </asp:DropDownList>

, , 3- , , gridview .

 <asp:TemplateField HeaderText="Function" ItemStyle-HorizontalAlign="Center">
                        <ItemTemplate>
                            <asp:LinkButton ID="btnEdit" runat="server" ToolTip='<%# Eval("party_id") %>' onclick="btnEdit_Click" >Edit</asp:LinkButton>

                        </ItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>

------ Edit Buton ---------

       protected void btnEdit_Click(object sender, EventArgs e)
        {
            try
            {
                Blank();
                GridViewRow gvrows = (GridViewRow)(((Control)sender).NamingContainer);
                LinkButton linkbtn = (LinkButton)gvrows.FindControl("btnEdit");
    string PartyId = linkbtn.ToolTip.ToString();
     string Com = "SELECT * FROM Transport_Mode WHERE party_id = '" + PartyId.ToString() + "' ORDER BY mode_id ASC ";
                DataTable dt2 = dut.GetDatatable(Com);--check above method in bal class
}
}

-------- ------

    USE [Aesthetics1]
GO
/****** Object:  StoredProcedure [dbo].[OSP_InsUpdBankMaster]    Script Date: 04/08/2013 17:55:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[OSP_InsUpdBankMaster]
(
    @Bank_id int =null,
    @Bank_name varchar(30) =null,
    @account_no varchar(20) =null,
    @holder_name varchar(40) =null,
    @Branch varchar(25) =null,
    @status int =null,
    @add_user varchar(8) =null,
    @Result int = 0output
)
AS
BEGIN
BEGIN TRANSACTION
    IF NOT EXISTS (SELECT 1 FROM Bank_master WHERE Bank_id = @Bank_id)
    BEGIN
        INSERT INTO Bank_master (Bank_name,account_no,holder_name,Branch,status,add_date,add_user) 
        VALUES (@Bank_name,@account_no,@holder_name,@Branch,@status,GETDATE(),@add_user)

        SET @Result = 1
    END
    ELSE IF EXISTS (SELECT 1 FROM Bank_master WHERE Bank_id = @Bank_id)
    BEGIN
        UPDATE Bank_master SET Bank_name = @Bank_name,
                                account_no = @account_no,
                                holder_name = @holder_name,
                                Branch = @Branch,
                                status = @status
                    where Bank_id = @Bank_id    
                SET @Result = 2
    END
IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
BEGIN
    SET @Result = -1
    ROLLBACK TRANSACTION
END
END

+1

RajGauravJais 08 . '13 12:29

Denys Wessels · Accepted Answer · 2013-04-08T11:17:40+0000

Question 1 - Updating data in gridview

1) , , <UpdateParameters>, .

2) ,

    <UpdateParameters>
        <asp:Parameter Name="ID" Type="Int32" />
        <asp:Parameter Name="OHIP" Type="String" />
        <asp:Parameter Name="FirstName" Type="String" />
        <asp:Parameter Name="LastName" Type="String" />
        <asp:Parameter Name="DOB" DbType="DateTime" />
        <asp:Parameter Name="VisitsPerYear" Type="Int32" />
        <asp:Parameter Name="DoctorID" Type="Int32" />
    </UpdateParameters>

. , SQL Timestamp datetime Update - Patient.Timestamp = GETDATE()

3) @Timestamp ( , WHERE)

4) DataKeyNames="ID" , DataKeyNames, , , , GridView :

<asp:GridView
            ID="GridView1"
            DataKeyNames="ID"

, , , SQL Server Express 2008 ASP.NET 4.0, Google ( → , .zip)

2 - gridview

1) <InsertParameters>, (Im , )

<InsertParameters>
    <asp:ControlParameter ControlID="txtOhip" Name="OHIP" />
    <asp:ControlParameter ControlID="txtFirstName" Name="FirstName" />
    <asp:ControlParameter ControlID="txtLastName" Name="LastName" />
    <asp:ControlParameter ControlID="ddlDoctorId" Name="DoctorID" PropertyName="SelectedValue" />
</InsertParameters>

2) Insert() sql, :

<table>
    <tr>
        <td>
            OHIP
        </td>
        <td>
            <asp:TextBox ID="txtOhip" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            First name
        </td>
        <td>
            <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Last name
        </td>
        <td>
            <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Doctor
        </td>
        <td>
            :<asp:DropDownList ID="ddlDoctorId" runat="server" AutoPostBack="True" DataSourceID="sdDoctorList"
                DataTextField="Doctor" DataValueField="DoctorID" AppendDataBoundItems="True">
                <asp:ListItem Value="0">All Doctors</asp:ListItem>
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td style="text-align: right" colspan="2">
            <asp:Button ID="btnAdd" OnClick="Add" runat="server" Text="Add" />
        </td>
    </tr>
</table>
<script runat="server">
protected void Add(object sender,EventArgs e)
    {
        sdPatient.Insert();
    } 
</script>

The problem with inserts and updates

Question 1 - Updating data in gridview

2 - gridview

More articles: