Using object properties in an AccessDataSource parameter

[StevenB]

Hi folks,

I imagine I'll get the same folks answering me here, but I figured it's best to post separate questions as separate threads, so that people who search might find this helpful.

So, quick summary: I have a very simple .net app connected to an Access database. Users visit a site and are presented with a suggestion about a software program they use. They vote on the suggestion using a series of radio buttons. When they vote, their vote is written to the database, and a new suggestion is displayed.

When I write the vote data to the database, I include the user's IP address, which I grab using Request.UserHostAddress.

I've used an AccessDataSource for this select:

        <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="xxx"
            SelectCommand="SELECT SuggestionID, CategoryName, Suggestion, Suggester, URL FROM SuggestionSelector WHERE (SuggestionID NOT IN (SELECT SuggestionID FROM Votes WHERE (IP = ?)))">
            <SelectParameters>
                <asp:Parameter DefaultValue="help me!" Name="IP" />
            </SelectParameters>
        </asp:AccessDataSource>

I want to use that WHERE clause to exclude suggestions that the current user has already voted on, based on their IP address.

As you can see, I've created a parameter for the IP address, and I want that parameter to use the Request.UserHostAddress. But, if I put that in the parameter like this, it doesn't work:

<asp:Parameter DefaultValue="Request.UserHostAddress" Name="IP" />

If I hard-code an IP address into the DefaultValue, it works exactly as I'd like it to.

So, my question is this: how can I get the IP parameter to use the Request.UserHostAddress, so that the SELECT statement will dynamically exclude certain suggestions based on the IP of the user?

(FWIW, I'm also aware that this solution isn't particularly graceful. Obviously, a person could vote multiple times from different computers and so forth. But, this application is very low-key and I'm not that concerned about it. A mild deterrent will serve fine!)

Thanks very much!

Steve

 

[Rhys666]

I've not used the AccessDataSource but if using the ObjectDataSource you can handle the 'Selecting' event which occurs prior to the data retrieval operation and amend the input parameters.

Looking at MSDN, the AccessDataSource.Selecting event is inherited from the SqlDataSource.Selecting event which states;

By adding an event handler delegate to handle the Selecting event, you can perform any additional preprocessing required or cancel the database query entirely. Because the SqlDataSourceSelectingEventArgs class is derived from the SqlDataSourceCommandEventArgs class, you can cancel a pending SqlDataSource database query by setting the Cancel property to true. You can examine and manipulate the CommandText, Parameters collection, and other database query properties prior to running the query by accessing the DbCommand object exposed by the Command property. You can also examine the DataSourceSelectArguments object that is passed to the Select method by accessing the Arguments property.

The SqlDataSourceSelectingEventArgs class is used in the OnSelecting method to provide access to a SqlDataSource database query before it is run.

NB: Note the bold text

I would probably look at handling this event and setting the value of the IP Parameter in the Code-Behind at run-time.


Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[ca8msm]

I don't think you can set the DefaultValue with a value that won't be constant. If you are going to use the AccessDataSource control, you are probably best off using the Selecting event as Ryhs666 has described e.g.

    Protected Sub AccessDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles AccessDataSource1.Selecting
        e.Command.Parameters("IP").Value = Request.UserHostAddress
    End Sub

However, are you sure this should be based on IP Address? What happens if the client's IP changes?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[StevenB]

Thanks very much, this is great information. Admittedly, I've done a decent amount of surfing the MSDN stuff, but since I'm already in over my head I unfortunately feel it's often over my head.

Mark, I'm basing this on IP for a few reasons:

a. It's not terribly complicated
b. This doesn't have to perfect, and validating by IP will at least prevent rampant repeat voting in the database. I'm not too concerned if an IP change or a multi-PC user here and there sneaks an extra vote or two in.
c. It's absolutely not worth building a security (user/pw) model to ensure uniqueness

That said, if there's another value I can capture somewhere without the user's input, that has more constancy than IP, I'd certainly consider it!

Thanks!

Steve

 

[ca8msm]

In that case, there's probably nothing else that can uniquely identify the user apart from the IP Address and if you're not too concerned if that is bypassed, then it's probably the easiest and best method to go with.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.