Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-05-09
20
Medium Priority
?
475 Views
Last Modified: 2010-04-07
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
Comment
Question by:_Esam
  • 10
  • 8
  • 2
20 Comments
 
LVL 14

Accepted Solution

by:
Ramuncikas earned 1840 total points
ID: 13965940
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
 

Author Comment

by:_Esam
ID: 13965984
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
 
LVL 14

Assisted Solution

by:Ramuncikas
Ramuncikas earned 1840 total points
ID: 13966013
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:_Esam
ID: 13966015
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
 
LVL 8

Assisted Solution

by:sriggumma
sriggumma earned 160 total points
ID: 13966016
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
 

Author Comment

by:_Esam
ID: 13966075
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
 
LVL 14

Expert Comment

by:Ramuncikas
ID: 13966088
Did you try debuging you solution? Maybe error comes from somewhere else?

Ramuncikas
0
 

Author Comment

by:_Esam
ID: 13966110
I'm not too familiar with debugging yet but little suggestion would be great..
How I do that?

_Esam
0
 

Author Comment

by:_Esam
ID: 13966205
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
 
LVL 14

Assisted Solution

by:Ramuncikas
Ramuncikas earned 1840 total points
ID: 13966255
I allways use databinding expressions like this:

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

Ramuncikas
0
 

Author Comment

by:_Esam
ID: 13966269
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
 
LVL 14

Assisted Solution

by:Ramuncikas
Ramuncikas earned 1840 total points
ID: 13966337
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
 

Author Comment

by:_Esam
ID: 13966628
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
 
LVL 14

Expert Comment

by:Ramuncikas
ID: 13966669
You mean you want to let users name columns the way they want to?

0
 

Author Comment

by:_Esam
ID: 13966708
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
 
LVL 8

Assisted Solution

by:sriggumma
sriggumma earned 160 total points
ID: 13966789
_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
 
LVL 14

Assisted Solution

by:Ramuncikas
Ramuncikas earned 1840 total points
ID: 13966796
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
 

Author Comment

by:_Esam
ID: 13966929
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
 

Author Comment

by:_Esam
ID: 13966965
Thanks sriggumma..

_Esam
0
 
LVL 14

Expert Comment

by:Ramuncikas
ID: 13967006
Pleased to hear that I could help :)

Good Luck

Ramuncikas
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month11 days, 8 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question