Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 4 > BOOK: Beginning ASP.NET 4 : in C# and VB
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning ASP.NET 4 : in C# and VB
This is the forum to discuss the Wrox book Beginning ASP.NET 4: in C# and VB by Imar Spaanjaars; ISBN: 9780470502211
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP.NET 4 : in C# and VB section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 18th, 2013, 09:04 PM
Registered User
Points: 35, Level: 1
Points: 35, Level: 1 Points: 35, Level: 1 Points: 35, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2013
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Unhappy How to access database values in behind code files?

Hello folks,

I am creating my first test website based on the WroxWorld sample website that comes in the book. Exciting times for me!

I have a listview that I use to upload new images and also delete them. The sample code in the book only shows how to delete the database records but not the actual physical image file. That's what I am trying to figure out.

My website is using C# and instead of using the entity framework to access the DB, I am using a SqlDataSource control to source my listview.

I selected the "Generate INSERT, UPDATE and DELETE statements" so I can have all the functions I need in the listview to work with the DB.

These are my questions:

1) Where is exactly the code located to do the deletion? The SqlDataSource control has the following command to delete in the aspx:

DeleteCommand="DELETE FROM [Pictures] WHERE [PictureID] = @PictureID"

However, I don't understand how this is called. When I click the delete button, the record is deleted successfully but I don't know how exactly how this gets done.

My DeleteButton is located on the ItemTemplate as follows:

<asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" CausesValidation="false" />

My guess is that the CommandName "Delete" would execute the datasource DeleteCommand? Am I correct here or missing something?

2) If I am correct in the previous question, and if this is something done in the aspx and *not* in the code behind file, then how can I implement some logic to delete the image file from the disk?

I was trying to use the ListView1_ItemDeleted event, would this work? If this is feasible way to do it, them I am failing because I don't know how to access the DB value of the filename.

Inside the ListView1_ItemDeleted event I have tried the following to try to grab the value of the file to delete:

1) Option one (does not work since the value of this string is always null, so the value from the DB is no assigned to the string)

string fileName = e.Values["PictureID"].ToString();

2) Option two (using findcontrol to find the value of the imageurl, same problem, does not work since no value is ever assigned, so I edn up with an empty string)

Image ImageURL = (Image)ListView1.FindControl("ImageUrl");
fileName = ImageURL.ImageUrl.ToString();


I just need to grab the value of PictureID in the code behind, without this I am stuck. I already know how to code the file deletion from the disk.

The books has good examples on how to use DB data using LINQ, but can't find how to do it with normal SQL data sources.

As always, very grateful for your help and time.

Thanks!
Reply With Quote
  #2 (permalink)  
Old September 19th, 2013, 06:29 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,960
Thanks: 79
Thanked 1,559 Times in 1,536 Posts
Default

Hi there,

Quote:
Exciting times for me!
Indeed! ;-)

Quote:
The sample code in the book only shows how to delete the database records but not the actual physical image file.
Exercise 4 of chapter 14 (page 551) shows you how to do this.

Quote:
My guess is that the CommandName "Delete" would execute the datasource DeleteCommand? Am I correct here or missing something?
Almost. When you click the button, the ListView intercepts the postback, looks at the CommandName and then calls the Delete method on its associated data source control. Page 534 has this:

Quote:
Note the CommandName of the Button control in the ItemTemplate. Itís set to Delete, which turns this button into a true Delete button. When you click it, the ListView figures out what picture you clicked the button for and then instructs the EntityDataSource control to delete the associated picture from the database.
The same is true for the SqlDataSource control.

Quote:
how can I implement some logic to delete the image file from the disk?
Instead of looking at e.Values, look into e.Keys which gives you the primary key of the picture (called Id in the PlanetWrox database). Here's an example:

Code:
protected void ListView1_ItemDeleted(object sender, ListViewDeletedEventArgs e)
{
  // Grab the ID of the deleted picture and do something with it
  int id = Convert.ToInt32(e.Keys["Id"]);
}
You would then need to execute your own SQL statement (using EF, or a SqlConnection and SqlCommand object) to retrieve the ImageUrl from the database.

However, there's an easier approach. You can make the ImageUrl a data key as well, so it shows up in e.Keys too. To make this work, first add ImageUrl to the DataKeyNames property of the ListView:

Code:
<asp:ListView ID="ListView1" runat="server" DataKeyNames="Id, ImageUrl" DataSourceID="SqlDataSource1" ...>
Then in Code Behind, access this key and delete the image:

Code:
protected void ListView1_ItemDeleted(object sender, ListViewDeletedEventArgs e)
{
  // Grab the ImageUrl and delete directly. Requires DataKeyNames to be set
  string fileName = e.Keys["ImageUrl"].ToString();
  string fileOnDisk = Server.MapPath(fileName);
  if (File.Exists(fileOnDisk))
  {
    File.Delete(fileOnDisk);
  }
}
Below you find the full source code for a file called DeleteWithSql. You can drop it in the PlanetWrox project and it will delete the pictures for you.

Hope this helps,

Imar

Markup

Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DeleteWithSql.aspx.cs" Inherits="DeleteWithSql" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
    <div>
      <asp:ListView ID="ListView1" runat="server" DataKeyNames="Id, ImageUrl" DataSourceID="SqlDataSource1" OnItemDeleted="ListView1_ItemDeleted">
        <ItemTemplate>
          <tr style="">
            <td>
              <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" />
            </td>
            <td>
              <asp:Label ID="IdLabel" runat="server" Text='<%# Eval("Id") %>' />
            </td>
            <td>
              <asp:Image ID="ImageUrl" runat="server" ImageUrl='<%# Eval("ImageUrl") %>' Width="150px" />
            </td>
          </tr>
        </ItemTemplate>
        <LayoutTemplate>
          <table runat="server">
            <tr runat="server">
              <td runat="server">
                <table id="itemPlaceholderContainer" runat="server" border="0" style="">
                  <tr runat="server" style="">
                    <th runat="server"></th>
                    <th runat="server">Id</th>
                    <th runat="server">ImageUrl</th>
                  </tr>
                  <tr id="itemPlaceholder" runat="server">
                  </tr>
                </table>
              </td>
            </tr>
            <tr runat="server">
              <td runat="server" style=""></td>
            </tr>
          </table>
        </LayoutTemplate>
      </asp:ListView>
      <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PlanetWroxConnectionString1 %>" DeleteCommand="DELETE FROM [Picture] WHERE [Id] = @Id" InsertCommand="INSERT INTO [Picture] ([Description], [ToolTip], [ImageUrl], [PhotoAlbumId]) VALUES (@Description, @ToolTip, @ImageUrl, @PhotoAlbumId)" SelectCommand="SELECT * FROM [Picture]" UpdateCommand="UPDATE [Picture] SET [Description] = @Description, [ToolTip] = @ToolTip, [ImageUrl] = @ImageUrl, [PhotoAlbumId] = @PhotoAlbumId WHERE [Id] = @Id">
        <DeleteParameters>
          <asp:Parameter Name="Id" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
          <asp:Parameter Name="Description" Type="String" />
          <asp:Parameter Name="ToolTip" Type="String" />
          <asp:Parameter Name="ImageUrl" Type="String" />
          <asp:Parameter Name="PhotoAlbumId" Type="Int32" />
        </InsertParameters>
        <UpdateParameters>
          <asp:Parameter Name="Description" Type="String" />
          <asp:Parameter Name="ToolTip" Type="String" />
          <asp:Parameter Name="ImageUrl" Type="String" />
          <asp:Parameter Name="PhotoAlbumId" Type="Int32" />
          <asp:Parameter Name="Id" Type="Int32" />
        </UpdateParameters>
      </asp:SqlDataSource>
    </div>
  </form>
</body>
</html>
Code Behind

Code:
using System;
using System.IO;
using System.Web.UI.WebControls;

public partial class DeleteWithSql : System.Web.UI.Page
{
  protected void ListView1_ItemDeleted(object sender, ListViewDeletedEventArgs e)
  {
    // Grab the ID of the deleted picture and do something with it
    int id = Convert.ToInt32(e.Keys["Id"]);

    // Alternatively, grab the ImageUrl and delete directly. Requires DataKeyNames to be set
    string fileName = e.Keys["ImageUrl"].ToString();
    string fileOnDisk = Server.MapPath(fileName);
    if (File.Exists(fileOnDisk))
    {
      File.Delete(fileOnDisk);
    }
  }
}
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #3 (permalink)  
Old October 2nd, 2013, 01:16 PM
Registered User
Points: 35, Level: 1
Points: 35, Level: 1 Points: 35, Level: 1 Points: 35, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2013
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks a lot for your help, this worked out perfectly!

One more issue I am facing.

I am using a detailsview control to insert new rows in my DB. Right after I insert it, I want to grab the new row's value of the primary key to do something with it. This is an identity PK so I am not providing the value in the web page.

For this, I am using the SqlDataSource inserted event as follows:

protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{

string NewID = e.Command.Parameters["@ReviewID"].Value;

}

In order for this to work, I modified my SqlDataSource as follows:

To the insert query I appended this: ;SELECT @NewReviewID= @@Identity

So my query looks like this:

INSERT INTO [WineReviews] ([Winery], [Wine], [TypeID], [A], [BottleID], [SugarResidual], [Price], [Website], [Review], [Rating], [Notes], [TypeID2]) VALUES (@Winery, @Wine, @TypeID, @A, @BottleID, @SugarResidual, @Price, @Website, @Review, @Rating, @Notes, @TypeID2) ;SELECT @NewReviewID= @@Identity

Then I made sure that the ReviewID direction is "Output" and that the ReviewID is setup as a boundfield in the detailsview. However, I am sure I have something wrong.

When I try to insert, now I get this compilation error:

-----------------
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS1955: Non-invocable member 'System.Data.Common.DbCommand.Parameters' cannot be used like a method.
-----------------

Thank you for helping me out. I am learning something new everyday creating this small test system
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using book code with Access 2007 .accdb files ostrandernw BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 0 November 25th, 2010 11:56 PM
How to open different access database from code TomW Access VBA 2 May 6th, 2008 12:49 PM
Access Database & Null Values jroxit Classic ASP Databases 4 January 9th, 2007 09:34 PM
Write data from access database to text files hayley Classic ASP Basics 1 February 1st, 2005 08:54 AM



All times are GMT -4. The time now is 10:50 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.