Issue with inserts and updates

Question

I am a noob at ASP.NET/C#, but I'll do my best to describe my problem as best as I can.

Now I have a gridview that has a sql source attached to it; It grabs the correct information. The gridview's information will change depending on what is selected in a dropdown list that is outside of the grid, which works. The problem lies within updating and inserting.

Once attempting to update. (AS soon as I click edit on one of the columns) I get the following error message: 'DropDownList2' has a SelectedValue which is invalid because it does not exist in the list of items. Parameter name: value

I split the columns into templates, as seen below,so I can manage them easier. 'Dropdown list 2' is located in my edit template. It is connected to a datasource; that being the exact same source as my first dropdownmenu which works flawlessly. So I don't believe it would be the sql behind that procedure. I do however have this dropdownlist 2 bound to Doctor. Now I was told to bound it, so I do not know how binding works

If I unbind it, I can at least see the grid still after I click edit, but after I update I get the error message: Procedure or function uspPatientUpdate has too many arguments specified.

Now I've looked online for a solution, but I can't wrap my head around binding. I'll give the following code as necessary.

ASP:

Select Patient By Doctor: All Doctors

Procedures:

This is for the dropdownlists. This gives the name as well as the value. *Note: This procedure works for the first dropdownlist, and I suppose for the second one to.

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

This is the update procedure. Not sure if this is the one at fault or not, or if it is 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'll appreciate any help. BTW this stuff is a little more advance then what we were taught (using stored procedures in ASP), I'm going the extra mile for bonus marks.. Thanks in advance.

If you need any more information, please ask

Denys Wessels · Accepted Answer

Question 1 - Update data in gridview

1) As pointed out by Andriy your first problem is that the ID is listed twice in ,remove one of them.

2) Remove Timestamp from the list of parameters, timestamps are updated automatically

NOTE: If you need to save date timestamps, in SQL change the datatype of the Timestamp column to datetime and change the Update stored procedure as - Patient.Timestamp = GETDATE()

3) Remove @Timestamp from the update stored procedure(both as a parameter and from the WHERE clause)

4) In the markup of the gridview set DataKeyNames="ID" this is very important, Use DataKeyNames property to specify the field that represents the primary key of the data source, it must be set in order for the automatic update and delete features of the GridView control to work:



Once you've done all of the above your updates will work, I've created a sample project for you using SQL Server Express 2008 and ASP.NET 4.0, you can find it here on Google drive (Just click on File -> Download to get the.zip project)

Question 2 - Insert data and refresh in gridview

1) Change  to get data from controls (Im showing an example with only four parameters, you can change this as needed)


    
    
    
    
 


2)Add insert controls to the page and call the Insert() method of your sql data source when the user clicks the add button:


    
        
            OHIP
        
        
            
        
    
    
        
            First name
        
        
            
        
    
    
        
            Last name
        
        
            
        
    
    
        
            Doctor
        
        
            :
                All Doctors

Issue with inserts and updates

Answers (2)

Question 1 - Update data in gridview

Question 2 - Insert data and refresh in gridview

Related Questions