• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

Datagrid example with a delete button (ASP.NET, VB.NET, MS Access)

Hi,
I need to show results from a database query in a datagrid and attach a delete button to
detete a specific record.

I tried to get the data from the MS Access database as:

            Dim cnss As New OleDbConnection
            Dim sss As String = Server.MapPath("UST.mdb")
            cnss.ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0; data source = " + sss
            cnss.Open()
            Dim dass As New OleDbDataAdapter("SELECT  UST_ID, COURSE_ID, SEMESTER, YEAR FROM COURSES_TAKEN WHERE UST_ID = " & User_ID, cnss)

            Dim dsss As New DataSet
            dass.Fill(dsss, "COURSES_TAKEN_DELETE")
            delete_crs_dg.DataSource = dsss
            delete_crs_dg.DataMember = "COURSES_TAKEN_DELETE"
            delete_crs_dg.DataBind()

            cnss.Close()


The data types are: UST_ID (number), COURSE_ID (text) SEMESTER (text), YEAR (number).

I tried to create a datagrid as follows:

<asp:datagrid id="delete_crs_dg" runat="server" AutoGenerateColumns="False"
                  OnItemCommand="DeleteCourse"
      BorderColor="Tan" BorderWidth="1px" BackColor="LightGoldenrodYellow" CellPadding="2" GridLines="None"
      ForeColor="Black" Font-Names="Verdana" Font-Size="Smaller">
      <SelectedItemStyle ForeColor="GhostWhite" BackColor="DarkSlateBlue"></SelectedItemStyle>
                                          <AlternatingItemStyle BackColor="PaleGoldenrod"></AlternatingItemStyle>
                                          <HeaderStyle Font-Bold="True" BackColor="Tan"></HeaderStyle>
                                          <FooterStyle BackColor="Tan"></FooterStyle>
      <PagerStyle HorizontalAlign="Center" ForeColor="DarkSlateBlue" BackColor="PaleGoldenrod"></PagerStyle>
      

<Columns>
                                                <asp:TemplateColumn HeaderText="Course Information">
                                                      <ItemTemplate>
      <asp:Button CommandName="Delete" Text="Delete" Runat="server" ID="Button1"></asp:Button>
                 <asp:Label ID="UST_id_lbl" Runat=server text= '<%#Container.DataItem , ("UST_ID")%>'>
      </asp:Label>
      <asp:Label ID="Crs_id_lbl" Runat=server text= '<%#Container.DataItem , ("COURSE_ID")%>'>
      </asp:Label>
                                                <asp:Label ID="Semester_lbl" Runat=server text= '<%#Container.DataItem , ("SEMESTER")%>'>
                                          </asp:Label>
                                                            <asp:Label ID="Year_lbl" Runat=server text= '<%#Container.DataItem , ("YEAR")%>'>
                                                            </asp:Label>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
      </Columns>
</asp:datagrid>

My sub is:

Protected Sub DeleteCourse(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
        If e.CommandName = "Delete" Then
            Dim strsql As String = "delete  from COURSES_TAKEN where COURSE_ID =" + CType(e.Item.FindControl("Crs_id_lbl"), Label).Text
        End If
    End Sub

When I try to run this I get an error saying:>BC30519: Overload resolution failed because no accessible 'ToString' can be called without a narrowing conversion:

I have no clue what it means,
Please advise...
Also, if possible, please advise as to how the datagrid can be made better!!!

Thanks.
_Esam
0
_Esam
Asked:
_Esam
  • 10
  • 8
  • 2
7 Solutions
 
RamuncikasCommented:
Hey, Esam.

In cases like this i choose more simple way. For IDs I use simle databound columns and usually place them at first (0) possition. This way I get the ID in more simple way and I am sure it is there.

In your case Datagrid would look like this:

<asp:datagrid id="delete_crs_dg" runat="server" AutoGenerateColumns="False"
                  OnItemCommand="DeleteCourse"
     BorderColor="Tan" BorderWidth="1px" BackColor="LightGoldenrodYellow" CellPadding="2" GridLines="None"
     ForeColor="Black" Font-Names="Verdana" Font-Size="Smaller">
     <SelectedItemStyle ForeColor="GhostWhite" BackColor="DarkSlateBlue"></SelectedItemStyle>
                                   <AlternatingItemStyle BackColor="PaleGoldenrod"></AlternatingItemStyle>
                                   <HeaderStyle Font-Bold="True" BackColor="Tan"></HeaderStyle>
                                   <FooterStyle BackColor="Tan"></FooterStyle>
     <PagerStyle HorizontalAlign="Center" ForeColor="DarkSlateBlue" BackColor="PaleGoldenrod"></PagerStyle>
     

<Columns>
                        <asp:BoundColumn DataField="COURSE_ID" HeaderText="Course ID"></asp:BoundColumn>
                                     <asp:TemplateColumn HeaderText="Course Information">
                                             <ItemTemplate>
     <asp:Button CommandName="Delete" Text="Delete" Runat="server" ID="Button1"></asp:Button>
                 <asp:Label ID="UST_id_lbl" Runat=server text= '<%#Container.DataItem , ("UST_ID")%>'>
     </asp:Label>
     <asp:Label ID="Crs_id_lbl" Runat=server text= '<%#Container.DataItem , ("COURSE_ID")%>'>
     </asp:Label>
                                        <asp:Label ID="Semester_lbl" Runat=server text= '<%#Container.DataItem , ("SEMESTER")%>'>
                                   </asp:Label>
                                                  <asp:Label ID="Year_lbl" Runat=server text= '<%#Container.DataItem , ("YEAR")%>'>
                                                  </asp:Label>
                                             </ItemTemplate>
                                        </asp:TemplateColumn>
     </Columns>
</asp:datagrid>

The first column in Columns section. You can set it's visible property to false if you do not want it to be visible.

And finally your code:

Protected Sub DeleteCourse(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
        If e.CommandName = "Delete" Then
            Dim strsql As String = "delete  from COURSES_TAKEN where COURSE_ID =" & e.Item.Cells(0).Text
        End If
    End Sub


Hope this will help
Ramuncikas

0
 
_EsamAuthor Commented:
Hi,
Thanks for your explanation so far...
I need to clarify one more thing before I test this...

My COURSE_ID is text, and I also add a condition to the delete command as: AND UST_ID = ust_id.ToString() {this is a session integer session variable}

Could u pls let me know about the proper delete sql string?
In the mean time, i will be working on your suggestion.

Thanks.
_Esam
0
 
RamuncikasCommented:
Well, Im not very sure, but try this

Dim sUST_ID as String
sUST_ID=Me.Session("ust_id")

or even try
sUST_ID=CType(Me.Session("ust_id"), String)

The SQL statement line should look like this:
 Dim strsql As String = "delete  from COURSES_TAKEN where COURSE_ID =" & e.Item.Cells(0).Text & " AND UST_ID='" & sUST_ID & "'"

Hope this will do
Ramuncikas
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
_EsamAuthor Commented:
A little correction:
My COURSE_ID is text, and I also need to add a condition to the delete command as: AND UST_ID = ust_id {this is a session integer session variable}

Thanks.
_Esam
0
 
sriggummaCommented:
Hi,
Include the value ust_id.ToString()  inside single quotes.

Try changing
----->  AND UST_ID = ust_id.ToString()

as
-----> "..... AND UST_ID = '" & ust_id.ToString() & "'"

Hope this helps.
0
 
_EsamAuthor Commented:
I tried like this:

Dim strsql As String = "delete  from COURSES_TAKEN where COURSE_ID =" & e.Item.Cells(0).Text & " AND UST_ID='" & sUST_ID & "'"

I get the this error message again:
BC30519: Overload resolution failed because no accessible 'ToString' can be called without a narrowing conversion:

Well I hope I am not being too naive:

In the sub I am simply saying:
Dim strsql As String = "delete  from COURSES_TAKEN where COURSE_ID =" & e.Item.Cells(0).Text & " AND UST_ID='" & sUST_ID & "'"

But I did not create any specific connection before trying to delete?

Do I (/not)have to open another connection to the database table and then delete it???

Please let me know..
Thanks.
_Esam
0
 
RamuncikasCommented:
Did you try debuging you solution? Maybe error comes from somewhere else?

Ramuncikas
0
 
_EsamAuthor Commented:
I'm not too familiar with debugging yet but little suggestion would be great..
How I do that?

_Esam
0
 
_EsamAuthor Commented:
This is like that is showing the error message:


<asp:Label ID="UST_id_lbl" Runat=server text= '<%#Container.DataItem , ("UST_ID")%>'>

There is one obvious error that I detected: i deleted the comma : in between: Container.DataItem , ("UST_ID")%

Now the problem is when I delete, it doesn't delete, perhaps my datagrid is show as:

Course ID Course Information
CSIS 512  Delete 2318 CSIS 512 Fall 1998  
CSIS 510  Delete 2318 CSIS 510 Spring 1999  
CSIS 524  Delete 2318 CSIS 524 Spring 1999  

It looks terrible:

Please advise :
_Esam

_Esam
0
 
RamuncikasCommented:
I allways use databinding expressions like this:

DataBinder.Eval(Container, "DataItem.UST_ID")

Ramuncikas
0
 
_EsamAuthor Commented:
I changed the select query, made the boundcolumn not visible and it produced the result like:

Course Information
Delete 2318 CSIS 512 Fall 1998  
Delete 2318 CSIS 510 Spring 1999  
Delete 2318 CSIS 524 Spring 1999        this is so far ok.

But it is still not deleting the record????

Is is OK?

Dim strsql As String = "delete  from COURSES_TAKEN where COURSE_ID =" & e.Item.Cells(0).Text & " AND UST_ID='" & sUST_ID & "'"

_Esam
0
 
RamuncikasCommented:
I hope you're not just constructing sql statement, bu executing it too :)

If so, try to output you e.Item.Cells(0).Text  and sUST_ID values to the page with Response.Write() and see what are you trying to delete. Look at the database to see if you have a record maching this criteria

Ramuncikas
0
 
_EsamAuthor Commented:
Yes, I just tried to execute, and it worked, it did delete the course.

Now the last matter is how do i make the datagrid a little better.

How do i provide customized column names with customized width???

Please let me know..

Thanks..

_Esam
0
 
RamuncikasCommented:
You mean you want to let users name columns the way they want to?

0
 
_EsamAuthor Commented:
No, sorry.

I want to name them as a header.

Right now it is shown as:

Course Information
Delete 2318 CSIS 512 Fall 1998  
Delete 2318 CSIS 510 Spring 1999  
Delete 2318 CSIS 524 Spring 1999

I would like to show it as:

Course Informaiton

           Course #     Course Name        Term          Year

Delete   2318          CSIS 512              Fall            1998  
Delete   2318          CSIS 510              Spring        1999  
Delete   2318          CSIS 524              Spring        1999

Let me know how I can do that considering how I tried to do it with my code...

THanks.
_Esam
0
 
sriggummaCommented:
_Esam,

Below links may help you to learn more about usage of DataGrid :

Datagrid Events (Sort, Edit, Delete)  http://www.aspng.com/learn/gridevents.aspx
Deleting data from an ASP.NET DataGrid  http://builder.com.com/article.jhtml?id=u00220030210MCL01.htm

Hope this helps.
0
 
RamuncikasCommented:
You can do this in two ways:

1. Right click your datagrid in solution, click Propery builder. Property builder window opens. Make sure that in general tab "Show header" is checked. The go to Columns tab, select columns one by one and set the desider title for each in a Header text field.

2. You can do it in aspx source by adding a "HeaderText" attribute to each column. This solution is suitable when you dont have Visual Studio.NET and use some other tools to code your solution. If you use this method, your aspx source would look like this:
     <asp:datagrid id="dg" runat="server" AutoGenerateColumns="False">
                <Columns>
                    <asp:TemplateColumn HeaderText="Custommer No">
                        <ItemTemplate>
                            <asp:Label id="Label1" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.CustNo") %>'>
                            </asp:Label>
                        </ItemTemplate>
                </Columns>
            </asp:datagrid>

3. You also can do it at runtime which could be a pain at one body part and I'd suggest you using one of methods mentioned above

Ramuncikas
0
 
_EsamAuthor Commented:
Excellent Ramuncikas,

It finally worked the way I wanted it for now.
More sofisticated stuff to follow later.

I appreciate your patience.

Thanks a lot...

_Esam
0
 
_EsamAuthor Commented:
Thanks sriggumma..

_Esam
0
 
RamuncikasCommented:
Pleased to hear that I could help :)

Good Luck

Ramuncikas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 10
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now