Gridview edit and insert in same page

saloj
saloj used Ask the Experts™
on
Hi EE
I have Gridview which has Edit button which function properly with update value on sql procedure.
On the sql procedure i have written and insert and update sql and set function like if it  has value update else insert.
Update works fine but Insert does not and showing error "Object cannot be cast from DBNull to other types. ".
In this case how can I insert and update on gridview using sql procedure

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="urlid" DataSourceID="SqlDataSource2" EnableModelValidation="True">
        <Columns>
            <asp:TemplateField HeaderText="name" SortExpression="name">
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Eval("name") %>'></asp:Label>
                </ItemTemplate>               
            </asp:TemplateField>
            <asp:BoundField DataField="url" HeaderText="url" SortExpression="url" />
            <asp:CommandField HeaderText="Edit" ShowEditButton="True" ShowHeader="True"/>            
            <asp:CommandField HeaderText="Insert" ShowInsertButton="true" ShowHeader="True"/>
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionTEST %>"         
        SelectCommand="select r.name, a.url,r.id,a.urlid as urlid from report_url r left join auto_reports a on a.urlid=r.id and a.art_id=@artid
 order by r.name"         
        UpdateCommand= "spAddUpdate_Reports" 
        UpdateCommandType="StoredProcedure">
        <SelectParameters>
            <asp:QueryStringParameter Name="artid" 
                QueryStringField="aid" />
        </SelectParameters>
        <UpdateParameters>            
            <asp:QueryStringParameter Name="artid" QueryStringField="aid" Type="Int32" DefaultValue="5863" />
            <asp:Parameter Name="urlid" Type="Int32" />            
            <asp:Parameter Name="url" Type="String"/>
        </UpdateParameters>
    </asp:SqlDataSource>

Open in new window


sQL
PROCEDURE [dbo].[spAddUpdate_Reports]
	@artid int, 
	@urlid int,
	@url varchar(500)
	
AS
declare @count int
Set @count =0
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here        
    select @count = count(*) from auto_reports where urlid=@urlid and art_id=@artid
    if @count > 0
    Begin
    Update auto_reports Set SysDate=getdate(),url=@url Where urlid=@urlid and art_id=@artid
    End
    Else
    Begin
    INSERT auto_reports(sysdate,urlid,art_id,url)values(getdate(),@urlid,@artid,@url)
    End
	
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2008

Commented:
I might be missing something, but I don't see how you are getting any insert operations without an InsertCommand for the SqlDataSource.

SqlDataSource.InsertCommand Property
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insertcommand.aspx

<asp:sqldatasource
        id="SqlDataSource1"
        runat="server"
        connectionstring="<%$ ConnectionStrings:MyNorthwind %>"
        selectcommand="SELECT CompanyName,ShipperID FROM Shippers"
        insertcommand="INSERT INTO Shippers (CompanyName,Phone) VALUES (@CoName,@Phone)">
          <insertparameters>
            <asp:formparameter name="CoName" formfield="CompanyNameBox" />
            <asp:formparameter name="Phone"  formfield="PhoneBox" />
          </insertparameters>
      </asp:sqldatasource>

Open in new window

Author

Commented:
I am using insert from sql procedure, please check sql code.
I am displaying following sql data on my Gridview and I would like to create function to edit which has record and insert new record which has no data on database.
I would like to do it on same page and on same with edit, cancle function so I thought I might
get insert from the sql procedure with update command.


select r.name, a.url,r.id as id,a.urlid as urlid from url r left join reports a on a.urlid=r.id and a.art_id=@artid
 order by r.name

Open in new window

Most Valuable Expert 2012
Top Expert 2008

Commented:
That is confusing, as you have an Insert button, so I would expect an UpdateCommand.

       <asp:CommandField HeaderText="Insert" ShowInsertButton="true" ShowHeader="True"/>
   
I would think that values are not getting set for the UpdateCommand parameters, when you are doing an Insert operation.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Commented:
Ah sorry I don't have Insert button, that was mistaken! corrected below.

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="urlid" DataSourceID="SqlDataSource2" EnableModelValidation="True">
        <Columns>
            <asp:TemplateField HeaderText="name" SortExpression="name">
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Eval("name") %>'></asp:Label>
                </ItemTemplate>               
            </asp:TemplateField>
            <asp:BoundField DataField="url" HeaderText="url" SortExpression="url" />
            <asp:CommandField HeaderText="Edit" ShowEditButton="True" ShowHeader="True"/> 
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionTEST %>"         
        SelectCommand="select r.name, a.url,r.id,a.urlid as urlid from url r left join reports a on a.urlid=r.id and a.art_id=@artid
 order by r.name"         
        UpdateCommand= "Reports" 
        UpdateCommandType="StoredProcedure">
        <SelectParameters>
            <asp:QueryStringParameter Name="artid" 
                QueryStringField="aid" />
        </SelectParameters>
        <UpdateParameters>            
            <asp:QueryStringParameter Name="artid" QueryStringField="aid" Type="Int32" DefaultValue="5863" />
            <asp:Parameter Name="urlid" Type="Int32" />            
            <asp:Parameter Name="url" Type="String"/>
        </UpdateParameters>
    </asp:SqlDataSource>

Open in new window

Most Valuable Expert 2012
Top Expert 2008

Commented:
Do you have any idea which value is DBNull?  If not, you could attach an event handler to the Updating event, and check the values.

SqlDataSource.Updating Event
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.updating.aspx

Author

Commented:
good

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial