[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1578
  • Last Modified:

Creating an editable gridview in ASP.NET from scratch

I have asked this same question in different ways, and I just can't seem to get the answer i'm looking for, so I've decided to ask again, but in the most distilled plain-vanilla way I can possibly think of. I am desperate for help here.

I am using ASP.NET with a VB.NET backend. **Please** give me help in vb.net if possible, or if it has to be in C# give me some guidance as to how to convert it.

What I want to do should be relatively commonplace (which is why I'm so confused as to how no one has been able to help me).  I've never done this before, so while I don't like the thought of others doing my work for me, I need BASIC INSTRUCTIONS here.

1) I want to create a gridview. This gridview is populated by a stored procedure, which selects off a table and joins another table to it.
2) This gridview needs to be editable, so that rows can be edited in real time, one row at a time.
3) When the row is updated, it needs to fire an update stored procedure.

That's it, end of line. The only caveats are, NO INLINE SQL. The stored procs are already written and ready to accept parameters. I don't care if I use a sqldatasource, a sqldataadapter, some databind function (and ive had all these suggested to me, and then thats the end of the help I get), I'll do it in whatever way is easiest! Please help!

Thanks :)
0
nuvium
Asked:
nuvium
  • 11
  • 9
1 Solution
 
SQL_SERVER_DBACommented:
0
 
Salim FayadCommented:
I have used the GridView a lot in my latest project, so if you have some code that I can fix, it would be great.

But if you want to do that from scratch:
1. You create a SqlDataSource object, and you set the Select stored procedure and the Edit stored procedure, and if you want to be able to delete, then set also the Delete stored procedure
2. Set the DataSourceId of your GridView to the Id of the SqlDataSource object
3. Select the columns that you want and the ones that you don't want (I prefer to use the "Source" view)
4. Add in the <columns> tag the following line:
              <asp:CommandField  ShowEditButton="True" />

Note that if you want to put some template fields and to do something more advanced, let me know what you want to do and I will give you some guidlines
0
 
nuviumAuthor Commented:
Thanks, but way too high level. I appreciate the effort.  I wish I had the time to do some complete tutorial on data interaction, unfortunately theres a big deadline looming and im the only one around to do it.

Why is it when I ask the most obtuse questions on this site I get 50 million answers, yet when I ask the simplest "why dont i just give you the points on a silver platter" questions, absolutely nothing. This is killing me, if anyone else can please lend a hand, I would seriously appreciate it. There has to be at least *one* vb.net developer on this network that has created an editable gridview before successfully. Thanks!
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
nuviumAuthor Commented:
That last comment was to SQL_SERVER_DBA (whose contribution IS appreciated), not you The_eagle. I didnt even see your comment. Let me try out these steps and see if I can give you a checkpoint where perhaps you can offer some more guidance.
0
 
nuviumAuthor Commented:
Okay, I am definitely moving in the right direction. I need a little more assistance though. Currently the way I have things set up, the gridview is being created properly. The edit button when clicked, converts the whole row into editable textboxes. The update link, when clicked, tells me I am specifying too many parameters. This is what I still need help with :
1) Can I specify a specific cell the update, rather than the whole row? Lets say I have 3 columns that get returned x, y, and z. I want x and y to stay the same and be uneditable, but z should be able to be? How can I do tihs?
2) Please help me tie in my update stored procedure into this gridview. How do I programatically set the parameters that are going into the stored procedure?

Pasted below is my code, based on your suggestions, the_eagle. All of my code currently is done on the frontend, as all the assistance I've recieved so far doesnt really go into any codebehind stuff.

Thanks!


   <asp:GridView ID="gvCurrentPops" runat="server"  AutoGenerateColumns="False" visible="true"
                ShowHeader="True" BorderColor="#000000" BorderStyle="solid" BorderWidth="1px" Width="500px" DataSourceID="SDSTest">
        <EmptyDataTemplate>
        No pops currently set for this site or program.
        </EmptyDataTemplate>
        <Columns>
            <asp:CommandField ShowEditButton="true" />
            <asp:BoundField DataField="PopType" HeaderText="Pop Type" ItemStyle-BackColor="#ffffff" />
            <asp:BoundField DataField="PopURL" HeaderText="Pop URL" ItemStyle-BackColor="#ffffff" />
        </Columns>
        </asp:GridView>
        <asp:sqlDataSource ID="SDSTest"
        SelectCommandType="StoredProcedure"
        SelectCommand = "uspGetCurrentSitePops"
        UpdateCommandType="StoredProcedure"
        UpdateCommand = "uspUpdateSitePops"
        ConnectionString="<%$ ConnectionStrings:Conn %>"
        RunAt="server">
 
  <SelectParameters>
    <asp:Parameter Name="SiteID" Type="Int32" DefaultValue="1" />
    <asp:Parameter Name="ProgramID" Type="Int32" DefaultValue="56" />
  </SelectParameters>
  <UpdateParameters>
    <asp:Parameter Name="SiteID" Type="Int32" DefaultValue="1" />
    <asp:Parameter Name="PopURLExit" Type="String" DefaultValue="" />
    <asp:Parameter Name="PopURLUnder" Type="String" DefaultValue="" />
    <asp:Parameter Name="PopURLDefault" Type="String" DefaultValue="" />
    <asp:Parameter Name="ProgramID" Type="Int32" DefaultValue="56" />
  </UpdateParameters>
 
</asp:sqlDataSource>
0
 
Salim FayadCommented:
Here are the answers of your 2 questions:
1. You add the attribute ReadOnly="true" to your BoundField that you don't want to be modified
2. On the event "RowUpdating" of your gridview, put the code :
             e.NewValues["PopURLDefault"] = NewValue
0
 
nuviumAuthor Commented:
1. Problem solved, that worked fine.
2. All this code does is give me an error "property access must assign to the property or use its value".

One step closer.... I just need a bit more help to get this updating working correclty and I can call it a day on this damn thing.
0
 
nuviumAuthor Commented:
Believe me I want the simplest answer possible for this problem, but half the reason I get so confused is when I do a search on EE for "gridview" and "rowupdating", I get a million results that are all different. It seems like the code you are giving me is extremely simplified (which is great if it works, trust me!) For example one that I just read about was this :

Private Sub GridView_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
   Dim m_RowIndex As Integer
   m_RowIndex = e.NewEditIndex

   Dim m_ColumnText As String
   m_ColumnText = GridViewID.Rows(m_RowIndex).Cells(3).Text
End Sub

Do I have to do any of this stuff?
0
 
Salim FayadCommented:
Don't worry nuvium, I will help you till the end.

Do not use the "RowEditing" event, but the "RowUpdating" event that I told you before. But I did a small mistake (sorry for that), my code was in C#:

e.NewValues("PopURLDefault") = "The new value"
0
 
nuviumAuthor Commented:
That gets rid of the error, but how do I replace "The new value" with whatever was edited in the cell?
0
 
Salim FayadCommented:
Whatever was edited in the cell will be edited by itself since the GridView is binded to the SqlDataSource which already has an update stored procedure.  But if you want to edit by yourself (using the code) a specific value, then you do that.
0
 
Salim FayadCommented:
Let me elaborate more:
In your GridView, you are binding 2 fields: PopType and PopURL. Those 2 fields are not in the  update stored procedure of your SqlDataSource. This means that non of them will be updated.
In order to update those 2 values (or 1 of them), you have to add them to the parameters of your update stored procedure. But you want to update them for a specific SiteId (I guess since I think it is the primary key), so add the following attribute to your GridView: DataKeyNames="SiteId"
0
 
nuviumAuthor Commented:
I think we are almost there, The_eagle. I understand what you just said, but let *me* elaborate more. I suppose I should have mentioned this in my initial post. It should explain exactly how these stored procs work. This app, if you havent guessed it by the variable names, sets up popup windows for a site. These pops exist in 3 categories, under, exit, and default (sort of a failover). Now when they are in the appropriate table, the url is kept in a generic "PopURL" field, which is where the new editable ones need to go as well. The reason why the params being fed into the update stored proc are split into the different names is this : There is another column in the table called PopTypeID. This corresponds to an int value of "1, 2 or 3". These values correspond to the pop type, "exit, under, or default". So, I pass in a specific URL into one of the three params, and on the stored proc side, it inserts the URL into the generic PopURL field, and then assigns it a 1, 2 or 3 type based on which param it was in (does that make sense? If the PopURLExit came in as "www.microsoft.com", it would set that URL in the PopURL field, and give it a 1 as PopType, because it came through as an exit url.

So with this in mind, to edit the gridview (and thus edit the table behind it), I have to take the readonly poptype field, then take the edited generic popurl, and feed it back into the update procedure based on what the poptype field is. If poptype = exit, then the edited field will populate the parameter of "PopURLExit", etc etc. I didn't want to get confusing at first, which is why I didnt go into that much detail in the beginning. Now that I'm seeing the light at the end of the rainbow, I need to figure out how to get this edit/update working.

Let me know if I can give you anymore info.
0
 
Salim FayadCommented:
Now, I see what you want to do. One more question: do you want in the edit  mode of the row to have the Prop Type as a drop down list?
0
 
nuviumAuthor Commented:
That would actually be great, if its not going to add too much difficulty. I was going to mention that I didnt want there to be two of any type, but thats actually changing down the road, where they are going to be allowed to set multiple pops to each type. So sure, that would be great.
0
 
Salim FayadCommented:
Ok, here is how you do this:
1. In the GridView, add the attribute: DataKeyNames="SiteId,ProgramId"
2. Replace the following code inside the <Columns> of the GridView:
         <asp:BoundField DataField="PopType" HeaderText="Pop Type" ItemStyle-BackColor="#ffffff" />
    By a template field (where you can put the control that you want):
                         <asp:TemplateField HeaderText="Pop Type" ItemStyle-BackColor="#ffffff">
                    <EditItemTemplate>
                        <asp:DropDownList ID="ddlPopType" runat="server" SelectedValue='<%#Bind("PopTypeId") %>'>
                            <asp:ListItem Text="Exit" Value="1" />
                            <asp:ListItem Text="Under" Value="2" />
                            <asp:ListItem Text="Default" Value="3" />
                        </asp:DropDownList>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblPopType" runat="server" Text='<%# Eval("PopType") %>' />
                    </ItemTemplate>
                </asp:TemplateField>

3. On the "RowUpdating" event of the GridView, add the following code:

        Dim ddlPopType As DropDownList = GridView1.Rows(e.RowIndex).FindControl("ddlPopType")
        If (ddlPopType.SelectedValue.Equals("1")) Then
            e.NewValues("PopURLExit") = GridView1.Rows(e.RowIndex).Cells(1).Text
        ElseIf (ddlPopType.SelectedValue.Equals("2")) Then
            e.NewValues("PopURLUnder") = GridView1.Rows(e.RowIndex).Cells(1).Text
        ElseIf (ddlPopType.SelectedValue.Equals("3")) Then
            e.NewValues("PopURLDefault") = GridView1.Rows(e.RowIndex).Cells(1).Text
        End If

0
 
Salim FayadCommented:
One thing, is your PopUrl BoundField ReadOnly? or not?
0
 
nuviumAuthor Commented:
Hey I think ive almost got it. One last bug I need to squish. Let me first let you knowwhat I changed. To keep the confusion down, I rewrote the stored procedure to accept these parameters :
 SiteID, PopURL, PopTypeID, and ProgramID. I removed the different PopURL specifically named paramters, and instead just passed in the poptypeid to differentiate them.

So, I took what you gave me above and rewrote some of it to reflect these changes. Everything works fine, except no matter what I do, I end up with the sqlexception error that the stored proc has 'too many arguments specified'. The parameters I have set in the UpdateParameters are EXACTLY what the stored proc is expecting, nothing more, so I'm not sure why im getting this error. Any ideas? Is something getting submitted or set twice or something? I believe that once I get this issue fixed, the rest should fall into place nicely and I can put this behind me.

FRONT END---------------------------------
<asp:GridView ID="gvCurrentPops" runat="server"  AutoGenerateColumns="False" visible="true"
                ShowHeader="True" BorderColor="#000000" BorderStyle="solid" BorderWidth="1px" Width="500px" DataSourceID="SDSTest" DataKeyNames="PopsID">
        <EmptyDataTemplate>
        No pops currently set for this site or program.
        </EmptyDataTemplate>
        <Columns>
            <asp:CommandField ShowEditButton="true" />
           <asp:TemplateField HeaderText="Pop Type" ItemStyle-BackColor="#ffffff">
                    <EditItemTemplate>
                        <asp:DropDownList ID="ddlPopType" runat="server" SelectedValue='<%#Bind("PopTypeID") %>'>
                            <asp:ListItem Text="Under" Value="1" />
                            <asp:ListItem Text="Exit" Value="2" />
                            <asp:ListItem Text="Default" Value="3" />
                        </asp:DropDownList>
                    </EditItemTemplate>
                        <ItemTemplate>
                        <asp:Label ID="lblPopType" runat="server" Text='<%# Eval("PopType") %>' />
                    </ItemTemplate>
               </asp:TemplateField>
            <asp:BoundField DataField="PopURL" HeaderText="Pop URL" ItemStyle-BackColor="#ffffff" />
        </Columns>
        </asp:GridView>
        <asp:sqlDataSource ID="SDSTest"
        SelectCommandType="StoredProcedure"
        SelectCommand = "uspGetCurrentSitePops"
        UpdateCommandType="StoredProcedure"
        UpdateCommand = "uspUpdateSitePops"
        ConnectionString="<%$ ConnectionStrings:Conn %>"
        RunAt="server">
 
  <SelectParameters>
    <asp:Parameter Name="SiteID" Type="Int32" DefaultValue="1" />
    <asp:Parameter Name="ProgramID" Type="Int32" DefaultValue="56" />
  </SelectParameters>
  <UpdateParameters>
    <asp:Parameter Name="SiteID" Type="Int32" DefaultValue="1" />
    <asp:Parameter Name="PopURL" Type="String" DefaultValue="" />
    <asp:Parameter Name="PopTypeID" Type="Int32" DefaultValue="" />
    <asp:Parameter Name="ProgramID" Type="Int32" DefaultValue="56" />
  </UpdateParameters>
 
</asp:sqlDataSource>


BACK END ----------------------------------
Protected Sub gvCurrentPops_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs) Handles gvCurrentPops.RowUpdating
 
        Dim ddlPopType As DropDownList = gvCurrentPops.Rows(e.RowIndex).FindControl("ddlPopType")
        e.NewValues("PopTypeID") = ddlPopType.SelectedValue
        e.NewValues("PopURL") = gvCurrentPops.Rows(e.RowIndex).Cells(1).Text

    End Sub
0
 
nuviumAuthor Commented:
If it helps, I turned off ALL the rowupdating code on the backend and hardcoded in default values to everything in the update parameters like so :
<UpdateParameters>
    <asp:Parameter Name="SiteID" Type="Int32" DefaultValue="1" />
    <asp:Parameter Name="PopURL" Type="String" DefaultValue="www.test.com" />
    <asp:Parameter Name="PopTypeID" Type="Int32" DefaultValue="2" />
    <asp:Parameter Name="ProgramID" Type="Int32" DefaultValue="56" />
  </UpdateParameters>

Its still telling me that the stored proc is accepting too many arguments. This is very strange.
0
 
Salim FayadCommented:
Good job nuvium. I wanted to tell you to do those modifications, but I wasn't sure if there is another part that is using this way or not.

Anyway, we are one step to solve this, so let's go ahead:
The problem is with the "DataKeyNames" attribute of your GridView. This property shows the keys of each row of your GridView. And those keys (columns) will be passed into your update stored procedure. Don't forget that you are not passing SiteID and ProgramID, but what you are passing is PopsID.

What I suggest is the following:
1. Change the value of DataKeyNames to the following: DataKeyNames="SiteID,ProgramId"
2. Or change your update stored procedure by removing the ProgramId and SiteId parameters and put PopsId as a parameter

0
 
nuviumAuthor Commented:
The_eagle, I had to make a few minor adjustments on the backend to get it to work, but long story short IT WORKS. Without your help, I wouldn;t have been able to do it. You singlehandedly just kept me from getting rid of my paid membership to this site, as I have been trying to get someone to help me with this for awhile now. You're the only person that came through, and stuck with me all the way through the issue. THANK YOU. If I could give you 5000 points for this, I would.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now