Friday, March 16, 2012

Passing a value to a where clause

I need the where statement of my select, to change randomly and I can't make it work.

Everything works fine as long as the Where clause is hard coded. This is the code that I'm playing with. I'm using Access. I'm not sure it makes a difference..

The randomprofileid variable is what I would like to have in place of the DefaultValue="3"

=====================================

<%
Dim randomprofileid
RANDOMIZE
randomprofileid =INT(((7)*RND())+1)
%
<FORM ID="form1" RUNAT="server">
<asp:AccessDataSource
runat="server"
id="AccessDataSource1"
DataFile="gorally.mdb"
SelectCommand="SELECT Profile_Name, Profile_Notes, Profile_Image_Small, Profiles_id FROM Profiles where (profiles_id = @dotnet.itags.org.profiles_id) "
<SelectParameters>
<asp:Parameter Name="profiles_id" DefaultValue="3" />
</SelectParameters
</asp:AccessDataSource>
=====================================

hey, this page might help you understand how it all works.

http://www.pluralsight.com/blogs/fritz/archive/2006/01/16/18054.aspx


My ASP.Net knowledge is so rudimentary that I'm all confused...

I need to pass a variable, which in this case is a random number, to the where clause.

From the code below you'll see what I need: instead of the hard coded DefaultValue="3", I would like the contents of the "randomprofileid" variable.
Off course this is in the hope that when the sql statement executes, it will use in its where clause a random profileid value

Thanks a million for your time.

=====================================
<%
Dim randomprofileid
RANDOMIZE
randomprofileid =INT(((7)*RND())+1)
%
<FORM ID="form1" RUNAT="server">
<asp:AccessDataSource
runat="server"
id="AccessDataSource1"
DataFile="gorally.mdb"
SelectCommand="SELECT Profile_Name, Profile_Notes, Profile_Image_Small, Profiles_id FROM Profiles where (profiles_id = @.profiles_id) "
<SelectParameters>
<asp:Parameter Name="profiles_id" DefaultValue="3" />
</SelectParameters
</asp:AccessDataSource>
=====================================


Anyone? Is it that hard?


I think you need to assign your random number to the select parameter in code. Try:

Dim randomprofileid
Randomize()
randomprofileid = Int(((7) * Rnd()) + 1)

AccessDataSource1.SelectParameters("profiles_id").DefaultValue = randomprofileid


Is this the layout of the code? Please see below...

If it is, it didn't work. It gives me an error:NullReferenceException: Object reference not set to an instance of an object.

==================================

<%
'randomize the record to retrieve
Dim randomprofileid
Randomize()
randomprofileid = Int(((7) * Rnd()) + 1)
AccessDataSource1.SelectParameters("profiles_id").DefaultValue = randomprofileid
%>

<FORM ID="form1" RUNAT="server">

<asp:AccessDataSource
runat="server"
id="AccessDataSource1"
DataFile="gorally.mdb"
SelectCommand="SELECT Profile_Name, Profile_Notes, Profile_Image_Small, Profiles_id FROM Profiles where (profiles_id = @.profiles_id) ">

<SelectParameters>
<asp:Parameter Name="profiles_id" />
</SelectParameters>

</asp:AccessDataSource>
....
....
</FORM>

==================================


You are referring to the datasource before it has been initiated. I use code-behind files, so I'm not sure where you need to put the inline code to make it work. But you could put your code in the 'Init' event of your datasource in the code-behind file:

Protected Sub AccessDataSource1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles AccessDataSource1.Init
Dim randomprofileid
Randomize()
randomprofileid = Int(((7) * Rnd()) + 1)
AccessDataSource1.SelectParameters("profiles_id").DefaultValue = randomprofileid
End Sub


franklopes:

I need the where statement of my select, to change randomly and I can't make it work.

Everything works fine as long as the Where clause is hard coded. This is the code that I'm playing with. I'm using Access. I'm not sure it makes a difference..

The randomprofileid variable is what I would like to have in place of the DefaultValue="3"

=====================================

<%
Dim randomprofileid
RANDOMIZE
randomprofileid =INT(((7)*RND())+1)
%>

<FORM ID="form1" RUNAT="server">
<asp:AccessDataSource
runat="server"
id="AccessDataSource1"
DataFile="gorally.mdb"
SelectCommand="SELECT Profile_Name, Profile_Notes, Profile_Image_Small, Profiles_id FROM Profiles where (profiles_id = @.profiles_id) "
<SelectParameters>
<asp:Parameter Name="profiles_id" DefaultValue="3" />
</SelectParameters
</asp:AccessDataSource>
=====================================

Hi Franklopes,

You can alter the Parameter value in the AccessDataSource1_Selecting method, the following is the code:

Protected Sub AccessDataSource1_Selecting(ByVal senderAs Object,ByVal eAs SqlDataSourceSelectingEventArgs)Dim intValueAs Integer = 2e.Command.Parameters("@.profiles_id").Value = intValueEnd Sub
I hope this helps.

0 comments:

Post a Comment