pass null values

I have a web form that allows a user to do a search, they can select all the
parameters on the web form or only select a few. If the user does not select
a value to search on, how can I pass that to stored procedure and get data
back?
example form:
the user can search on
first name
last name
hire date
termination date
if they only know the first and last name, how can i get data back if the
dates are not entered?see DBNull class
-- bruce (sqlwork.com)
"NuB" <me@.me.com> wrote in message
news:Od3vppMAGHA.2788@.TK2MSFTNGP14.phx.gbl...
>I have a web form that allows a user to do a search, they can select all
>the parameters on the web form or only select a few. If the user does not
>select a value to search on, how can I pass that to stored procedure and
>get data back?
> example form:
> the user can search on
> first name
> last name
> hire date
> termination date
> if they only know the first and last name, how can i get data back if the
> dates are not entered?
>
>

Pass Session Variable Value to Crystal Report?

I've got an aspx page that allows the user to select different options
whenever they want to run a report. On this page, I have added an
additional text box for the user to enter in comments about the report
they are running.

I would like to be able to have the comments that the user has entered
to appear on the report that is generated. I thought that I might be
able to pass this text to the report by using a session variable but I
have had no luck. It doesn't matter if I use a session variable to
accomplish this.

Can someone tell me how I can the user's comments from the web form
appear on the report?

Thanks!I figured out a way to accomplish this. I created a session variable
named UComments and stored the text that the user typed in it. On my
Crystal Report I created a blank formula field named UserComments I
next added the following code to my web form:

Dim oRpt As CrystalDecisions.CrystalReports.Engine.ReportDocum ent
= New CrystalDecisions.CrystalReports.Engine.ReportDocum ent()

'*** TELLING THE PROGRAM WHER THE REPORT IS LOCATED
'*** AND WHAT THE REPORT'S NAME IS. ***
oRpt.Load(Session("ReportLocation") & "Grant.rpt")

'*** CODE TO WRITE USER'S COMMENTS TO THE REPORT. ***
oRpt.DataDefinition.FormulaFields.Item("UserComments").Text = "'"
+ Trim(Session("UComments")) + "'"

CrystalReportViewer1.ReportSource = oRpt

crjunk@.earthlink.net (crjunk) wrote in message news:<e45e90aa.0308061021.788041e6@.posting.google.com>...
> I've got an aspx page that allows the user to select different options
> whenever they want to run a report. On this page, I have added an
> additional text box for the user to enter in comments about the report
> they are running.
> I would like to be able to have the comments that the user has entered
> to appear on the report that is generated. I thought that I might be
> able to pass this text to the report by using a session variable but I
> have had no luck. It doesn't matter if I use a session variable to
> accomplish this.
> Can someone tell me how I can the user's comments from the web form
> appear on the report?
> Thanks!

Pass Sql Select result to a simple Label.

I'm new and trying to learn. This is in VB.NET 2. I have a simple page
with one Textbox1, one Label1 and one Button1. The on_click Button one goes
and get's a simple data from the SQL db with the parameter from Textbox1.
What do I add to this code below to say that Label1 equals the result of my
Select statement? CroftUser being the result.
Thanks!
Newbie
----------

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim ds As New SqlDataSource()

ds.ConnectionString =
ConfigurationManager.ConnectionStrings("CroftDBConnectionString").ConnectionString

ds.SelectCommandType = SqlDataSourceCommandType.Text

ds.SelectCommand = "Select CroftUser From [User] Where (Password =
@dotnet.itags.org.Password)"

ds.SelectParameters.Add("Password", TextBox1.Text)

End SubPhilip,

If you are trying to populate a control directly via code, I think it is
easier fetch data from the Database using a System.Data.SqlClient.SqlCommand
object instead of the SqlDataSource.

Below is the code for a web page called "SimpleBinding".

Hope this helps,
Jason Vermillion

' alias to the SqlClient namespace
Imports System.Data.SqlClient

Partial Class SandBox_SimpleBindingVB
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As SqlConnection = New SqlConnection()
Dim cmd As SqlCommand = New SqlCommand()
Dim dr As SqlDataReader

Label1.Text = ""

cn.ConnectionString =
ConfigurationManager.ConnectionStrings("CroftDBConnectionString").ConnectionString
cmd.Connection = cn
cmd.CommandText = "Select CroftUser From [User] Where (Password =
@.Password)"
cmd.Parameters.AddWithValue("@.Password", Me.TextBox1.Text)

'Open the connection to the database
cn.Open()
' Execute the sql.
dr = cmd.ExecuteReader()
' Read all of the rows generated by the command (in this case only
one row).
Do While dr.Read()
Label1.Text = dr.Item("CroftUser").ToString()
Loop
' Close your connection to the DB.
dr.Close()
cn.Close()
End Sub
End Class
Jason, thanks SOOO much. That worked perfectly. Can you explain to me why
SqlClient is better in doing this than trying to create a SqlDataSource?

"Jason Vermillion" <JasonVermillion@.discussions.microsoft.comwrote in
message news:9E71D30B-AEF5-4F52-973E-C13501097DDA@.microsoft.com...

Quote:

Originally Posted by

Philip,
>
If you are trying to populate a control directly via code, I think it is
easier fetch data from the Database using a
System.Data.SqlClient.SqlCommand
object instead of the SqlDataSource.
>
Below is the code for a web page called "SimpleBinding".
>
Hope this helps,
Jason Vermillion
>
' alias to the SqlClient namespace
Imports System.Data.SqlClient
>
Partial Class SandBox_SimpleBindingVB
Inherits System.Web.UI.Page
>
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As SqlConnection = New SqlConnection()
Dim cmd As SqlCommand = New SqlCommand()
Dim dr As SqlDataReader
>
Label1.Text = ""
>
cn.ConnectionString =
ConfigurationManager.ConnectionStrings("CroftDBConnectionString").ConnectionString
cmd.Connection = cn
cmd.CommandText = "Select CroftUser From [User] Where (Password =
@.Password)"
cmd.Parameters.AddWithValue("@.Password", Me.TextBox1.Text)
>
'Open the connection to the database
cn.Open()
' Execute the sql.
dr = cmd.ExecuteReader()
' Read all of the rows generated by the command (in this case only
one row).
Do While dr.Read()
Label1.Text = dr.Item("CroftUser").ToString()
Loop
' Close your connection to the DB.
dr.Close()
cn.Close()
End Sub
End Class
>

Phillip,

Quote:

Originally Posted by

Jason, thanks SOOO much. That worked perfectly. Can you explain to me why
SqlClient is better in doing this than trying to create a SqlDataSource?
>

I would not say that using binding with the SqlDataSource is better or
worse, it just depends on what you are trying to do.

When you use a SqlDataSource and bound controls, you get a lot of database
functionality with only a few lines of code and without having to know much
about the way that the underlying Ado.Net works. This is especially true
when working with DataGrids, DataLists, and DataRepeaters. You get paging,
sorting, auto generating columns, and all kinds of other functionality that
would take a long time to code from scratch.

On the flip side, when you use the SqlDataSource (or any of the other data
source controls) you lose flexibility and a bit or performance that you get
when you work directly with the Data.SqlClient objects that are running under
the hood. When you start working with SqlDataSources, I think you'll find
that there are often some cases that come up when you find that the built-in
functionality is not adequate for you what you are trying to accomplish and
you might need to work directly with ado.net (SqlClient, etc.).

I would recommend at least knowing some of the basics of DataSets,
DataTables, DataViews, DataReaders, and DataAdapters.

Here are some links that might be helpful.
Live demos with source code showing hot to use SqlDataSources and Data bound
controls:
http://quickstarts.asp.net/QuickSta...ta/default.aspx
overview of data binding
http://msdn2.microsoft.com/en-us/li...359(VS.80).aspx
ado.net
http://msdn2.microsoft.com/en-us/library/e80y5yhx.aspx
Also, here is some code that just uses SqlDataSources and binding for your
original question. I think the trick was to wrap the User name Label in a
form view control.

Hope this helps,
Jason

Protected Sub cmdGetUserInfo_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles cmdGetUserInfo.Click
SqlDataSource1.Select(DataSourceSelectArguments.Em pty)
End Sub

<asp:TextBox ID="txtPwd" runat="server"></asp:TextBox>
<asp:Button ID="cmdGetUserInfo" runat="server" Text="Button" />
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
<asp:Label ID="lblUserName" runat="server"><%# Eval("CroftUser")
%></asp:Label>
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:CroftDBConnectionString %>"
ProviderName="<%$ ConnectionStrings:CroftDBConnectionString.Provider Name %>"
SelectCommand="Select CroftUser From [User] Where (Password = @.Password)">
<SelectParameters>
<asp:ControlParameter ControlID="txtPwd" Name="Password"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>

Pass Sql Select result to a simple Label.

I'm new and trying to learn. This is in VB.NET 2. I have a simple page
with one Textbox1, one Label1 and one Button1. The on_click Button one goes
and get's a simple data from the SQL db with the parameter from Textbox1.
What do I add to this code below to say that Label1 equals the result of my
Select statement? CroftUser being the result.
Thanks!
Newbie
--
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ds As New SqlDataSource()
ds.ConnectionString =
ConfigurationManager.ConnectionStrings("CroftDBConnectionString").Connection
String
ds.SelectCommandType = SqlDataSourceCommandType.Text
ds.SelectCommand = "Select CroftUser From [User] Where (Password =
@dotnet.itags.org.Password)"
ds.SelectParameters.Add("Password", TextBox1.Text)
End SubPhilip,
If you are trying to populate a control directly via code, I think it is
easier fetch data from the Database using a System.Data.SqlClient.SqlCommand
object instead of the SqlDataSource.
Below is the code for a web page called "SimpleBinding".
Hope this helps,
Jason Vermillion
' alias to the SqlClient namespace
Imports System.Data.SqlClient
Partial Class SandBox_SimpleBindingVB
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As SqlConnection = New SqlConnection()
Dim cmd As SqlCommand = New SqlCommand()
Dim dr As SqlDataReader
Label1.Text = ""
cn.ConnectionString =
ConfigurationManager.ConnectionStrings("CroftDBConnectionString").Connection
String
cmd.Connection = cn
cmd.CommandText = "Select CroftUser From [User] Where (Password =
@.Password)"
cmd.Parameters.AddWithValue("@.Password", Me.TextBox1.Text)
'Open the connection to the database
cn.Open()
' Execute the sql.
dr = cmd.ExecuteReader()
' Read all of the rows generated by the command (in this case only
one row).
Do While dr.Read()
Label1.Text = dr.Item("CroftUser").ToString()
Loop
' Close your connection to the DB.
dr.Close()
cn.Close()
End Sub
End Class
Jason, thanks SOOO much. That worked perfectly. Can you explain to me why
SqlClient is better in doing this than trying to create a SqlDataSource?
"Jason Vermillion" <JasonVermillion@.discussions.microsoft.com> wrote in
message news:9E71D30B-AEF5-4F52-973E-C13501097DDA@.microsoft.com...
> Philip,
> If you are trying to populate a control directly via code, I think it is
> easier fetch data from the Database using a
> System.Data.SqlClient.SqlCommand
> object instead of the SqlDataSource.
> Below is the code for a web page called "SimpleBinding".
> Hope this helps,
> Jason Vermillion
> ' alias to the SqlClient namespace
> Imports System.Data.SqlClient
> Partial Class SandBox_SimpleBindingVB
> Inherits System.Web.UI.Page
> Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> Dim cn As SqlConnection = New SqlConnection()
> Dim cmd As SqlCommand = New SqlCommand()
> Dim dr As SqlDataReader
> Label1.Text = ""
> cn.ConnectionString =
> ConfigurationManager.ConnectionStrings("CroftDBConnectionString").Connecti
onString
> cmd.Connection = cn
> cmd.CommandText = "Select CroftUser From [User] Where (Password =
> @.Password)"
> cmd.Parameters.AddWithValue("@.Password", Me.TextBox1.Text)
> 'Open the connection to the database
> cn.Open()
> ' Execute the sql.
> dr = cmd.ExecuteReader()
> ' Read all of the rows generated by the command (in this case only
> one row).
> Do While dr.Read()
> Label1.Text = dr.Item("CroftUser").ToString()
> Loop
> ' Close your connection to the DB.
> dr.Close()
> cn.Close()
> End Sub
> End Class
>
Phillip,

> Jason, thanks SOOO much. That worked perfectly. Can you explain to me wh
y
> SqlClient is better in doing this than trying to create a SqlDataSource?
>
I would not say that using binding with the SqlDataSource is better or
worse, it just depends on what you are trying to do.
When you use a SqlDataSource and bound controls, you get a lot of database
functionality with only a few lines of code and without having to know much
about the way that the underlying Ado.Net works. This is especially true
when working with DataGrids, DataLists, and DataRepeaters. You get paging,
sorting, auto generating columns, and all kinds of other functionality that
would take a long time to code from scratch.
On the flip side, when you use the SqlDataSource (or any of the other data
source controls) you lose flexibility and a bit or performance that you get
when you work directly with the Data.SqlClient objects that are running unde
r
the hood. When you start working with SqlDataSources, I think you'll find
that there are often some cases that come up when you find that the built-in
functionality is not adequate for you what you are trying to accomplish and
you might need to work directly with ado.net (SqlClient, etc.).
I would recommend at least knowing some of the basics of DataSets,
DataTables, DataViews, DataReaders, and DataAdapters.
Here are some links that might be helpful.
Live demos with source code showing hot to use SqlDataSources and Data bound
controls:
http://quickstarts.asp.net/QuickSta...ibrary/ms178359(VS.80).aspx
ado.net
http://msdn2.microsoft.com/en-us/library/e80y5yhx.aspx
Also, here is some code that just uses SqlDataSources and binding for your
original question. I think the trick was to wrap the User name Label in a
form view control.
Hope this helps,
Jason
Protected Sub cmdGetUserInfo_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles cmdGetUserInfo.Click
SqlDataSource1.Select(DataSourceSelectArguments.Empty)
End Sub
<asp:TextBox ID="txtPwd" runat="server"></asp:TextBox>
<asp:Button ID="cmdGetUserInfo" runat="server" Text="Button" />
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
<asp:Label ID="lblUserName" runat="server"><%# Eval("CroftUser")
%></asp:Label>
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:CroftDBConnectionStrin
g %>"
ProviderName="<%$ ConnectionStrings:CroftDBConnectionStrin
g.ProviderName %>"
SelectCommand="Select CroftUser From [User] Where (Password = @.Password)">
<SelectParameters>
<asp:ControlParameter ControlID="txtPwd" Name="Password"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>

pass through value of DropDownList ListItem into a string

Greetings.

I'm building an email form that uses a DropDownList to select the e-mail address via alias. Here is a portion of the control:

<asp:DropDownList id="ddlRecipient" CssClass="text-blue" AutoPostBack="true" runat="server">
<asp:ListItem Value="none" Selected="true">Please select from this list.</asp:ListItem>
<asp:ListItem Value="mornings">the morning show</asp:ListItem>
<asp:ListItem Value="news">News - newscasts, stories, comments</asp:ListItem>
</asp:DropDownList
Here is the code I am supposedly using to pass through the value:

Sub SendMe ()
Dim ddlRecipient As String
Dim ddlRecipientEmail As String = ddlRecipient & "@dotnet.itags.org.mydomain.com"
Response.Write(ddlRecipientEmail & " -- this is the email address you just sent")
End Sub

(The Response.Write line will be replaced by my email generation code, and I will use ddlRecipientEmail as the "from" field.)

However, all I get from the Response.Write is:
@dotnet.itags.org.mydomain.com -- this is the email address you just sent

So it's not passing through the value. How do I get it to do that?

Also, I'm going to do an If/Then/Else statement that, if "none" is the value, you get a popup and the program stops running. Anyone got a quick way to do that?

Appreciate the help.

ddlRecipient.SELECTEDVALUE

Curt_C:

ddlRecipient.SELECTEDVALUE

I actually just found that and it is working now.Thank you.

pass url parameter

Hi,

I want to pass a URL parameter from a hyperlinked column in my datagrid to a data list on another page.

How do I construct the select statement for the datalist page please?

Cheers,

JbDo you mean you want the hyperlink URL to be something like:

/SomeDir/SomePage.aspx?ID=TheValueFromSomeDataSourceField

If so, use a HyperLinkColumn. Set its DataNavigateUrlField to the
DataSource field from where you want the databound data to come from.
Then, set the DataNavigateUrlFormatString to:

"/SomeDir/SomePage.aspx?ID={0}"

Essentially, put {0} in the string wherever you want the actual
database value to appear.

(copy and paste from a scott mitchell usenet posting.. ;-).. )
henkm.

Thanks for the reply but i meant on the other page.

select * from tbltable where class_ID = ??

cheers,
JB
The post var can be retrieved with(c#):

So, you sql is going to be something like:

string sqlSelect = "select * from tbltable where class_ID = " + Request.QueryString["ID"];

is this wat you wanted to see, or do your mean the full page code to retrieve the data and show it?
Careful with that, it is wide open to Sql Injection attacks: what happens if I call youpageurl.aspx?ID=1;delete * from tbltable ?
Well, you lose all your data...
Please consider the use of the SqlParameter object to securely build your Sql queries.
bleroy,

Thanks for pointing this out.

Do you know of any good articles or examples of using the sql parameter object for this case?

Thanks all,
JB
Sure,http://samples.gotdotnet.com/quickstart/aspplus/doc/webdataaccess.aspx#param should be a good start.
What if you want to pass 2 parameter in the DataNavigateURLFormatString like:
/SomeDir/SomePage.aspx?id={0}&id2={1}

Where will you put the second field?

Pass Username and Pwd to database query in WebMartix?

I have an application that uses a login screen to pass UserName and Password to a details page. Then I use a SELECT query to generate a datagrid using UserName and Pwd as WHERE clause.

I have inserted a couple of label.texts to verify the variable make the second page.

How do I get the query to limit the return to only the records matching the UserName & Pwd combination?

Thanks,
ScottYou don't really want to pass the password around your site. And hopefully you're not allowing multiple duplicate usernames as long as the password is different.

If you're using asp.net authentication then it's somewhat safe to assume that the username is valid once they're logged in.

I.E. on your login page you set an authorization cookie.

On your details page you access their username via this cookie and use that as a parameter to your stored procedure/SQL Query.

Pass variable for use in Select Statement?

How do I pass a variable for use in my Select statement? My attempt below doesn't work.

Basically I want to pass the ID from the selected value of a dropdownlist for use as the variable to use in the select statement.

string passContractorID = Convert.ToString(ddl_JobTitles.SelectedValue);string query2 ="Select * FROM vw_bms_ActiveJobTitles WHERE contractorID = " + passContractorID +"";

SelectedValue is a string so you don't need to convert. You might need quotes around contractorID if that is a string.

string

query2 ="Select * FROM vw_bms_ActiveJobTitles WHERE contractorID ='" + DropDownList1.SelectedValue + "'";

pkellner thanks for your reply...

I was passing the inccorect ddl selected item and was the reason for the SQL error.

Is there a better way of writing the select statement above or a better practice perhaps?

Ok here is my current issue, I have a method that populates the databound with ana object datasource, the method for the select statement of the object datasource is in a seperate class, Data.cs .

How do I pass the selected value from a dropdownlist to this method?

Here is what I currently have...

 [DataObjectMethod(DataObjectMethodType.Select)]public static IEnumerable GetAllJobTitles() { DropDownList ddl_Contractors = (DropDownList)FormView_Descriptors.FindControl("ddl_Contractors");string sel ="Select * FROM vw_bms_JobTitles WHERE contractorID = " + ddl_Contractors.SelectedValue +""; SqlCommand cmd =new SqlCommand(sel,new SqlConnection(GetConnectionString())); cmd.Connection.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);return dr; }

Error is from this line..  DropDownList ddl_Contractors = (DropDownList)FormView_Descriptors.FindControl("ddl_Contractors");

ERROR: The name FormView_Descriptors does not exist in the current context.

The code works on the pag_load event in the code behind of the webform and I understand why it doesn't work, because you need to reference the page somehow but I don't know the code to do so?

if in VB and ID is alpha

try:

string query2 ="Select * FROM vw_bms_ActiveJobTitles WHERE contractorID = '" & passContractorID &"'";

if in VB and ID is alpha

try:

string query2 ="Select * FROM vw_bms_ActiveJobTitles WHERE contractorID = '" & passContractorID &"'";

Note: tick marks above (')" and "(')"

Jim,

In short you'll want to make a new method that excepts the contractorID parameter and secondly you'll need to "wire" that control into the select parameters of the objectdatasource that is bound to this method. This will effecively pass in the value of the dropdown as the parameter of the method call.

For example:

[DataObjectMethod(DataObjectMethodType.Select)]
public static IEnumerable GetAllJobTitles(int contractorID)
{
....
}

<asp:ObjectDataSourceID="ObjectDataSource2"runat="server"SelectMethod="GetAllJobTitles"TypeName="BLComponent"><SelectParameters><asp:ControlParameterControlID="ddl_Contractors"Name="contractorID"PropertyName="SelectedValue"Type="int"/></SelectParameters></asp:ObjectDataSource>

In addition I would highly suggest not "building" your SQL statements but instead using paramaterized queries. This is much safer and will prevent SQL injection attacks and also gets you out of the whole "quotes/double quotes/single quotes" issue.

For example:

[DataObjectMethod(DataObjectMethodType.Select)]
   public static IEnumerable GetAllJobTitles(int contractorID)
    {
        string sel ="Select * FROM vw_bms_JobTitles WHERE contractorID = @.ContractorID"
SqlCommand cmd =new SqlCommand(sel,new SqlConnection(GetConnectionString()));
cmd.Paramaters.AddWithValue("@.ContractorID", contractorID) cmd.Connection.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
    }

 Please note this is Pseudocode but pretty close. It may require some tweaking before you drop it into your code.

Hope this helps a bit. Let me know if I understood your problem correctly and if this answers your question.

Kind Regards,

Josh

I forgot to provide a link to some help found on the asp.net site related to this subject:

http://www.asp.net/learn/dataaccess/tutorial05cs.aspx?tabid=63

Also asp.net quickstarts have a nice little tutorial:

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/objectdatasource.aspx

Kind Regards,

Josh

Josh,

Thanks for your reply..

I did as you instructed, which makes sense.. but got this error.

Must declare the variable'@.contractorID'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the variable'@.contractorID'.

Source Error:

Line 41: SqlCommand cmd = new SqlCommand(sel, new SqlConnection(GetConnectionString()));
Line 42: cmd.Connection.Open();
Line 43: SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Line 44: return dr;
Line 45: }

Source File: c:\Visual Studio 2005\Websites\BMS_2_0\App_Code\BMSData.cs Line: 43

Here is my modified code you sugessted modifying.

<asp:ObjectDataSource ID="ods_PayPlans" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetAllJobTitles" TypeName="BMSData"> <SelectParameters> <asp:ControlParameter ControlID="ddl_Contractors" Name="contractorID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:ObjectDataSource>

[DataObjectMethod(DataObjectMethodType.Select)]
public static IEnumerable GetAllJobTitles(int contractorID)
{
string sel ="Select * FROM vw_bms_ActiveJobTitles WHERE contractorID = @.contractorID";
SqlCommand cmd =new SqlCommand(sel,new SqlConnection(GetConnectionString()));
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}

You forgot the add withparameter command mentioned above.

JimAmigo:

Here is my modified code you sugessted modifying.

<asp:ObjectDataSource ID="ods_PayPlans" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetAllJobTitles" TypeName="BMSData"> <SelectParameters> <asp:ControlParameter ControlID="ddl_Contractors" Name="contractorID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:ObjectDataSource>

[DataObjectMethod(DataObjectMethodType.Select)]
public static IEnumerable GetAllJobTitles(int contractorID)
{
string sel ="Select * FROM vw_bms_ActiveJobTitles WHERE contractorID = @.contractorID";
SqlCommand cmd =new SqlCommand(sel,new SqlConnection(GetConnectionString()));
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}

Looks like you forgot to add the new parameter (@.contractorid) to the command objects parameter collection: cmd.Paramaters.AddWithValue("@.ContractorID", contractorID). Your procedure should like something like the following:

[DataObjectMethod(DataObjectMethodType.Select)]
public static IEnumerable GetAllJobTitles(int contractorID)
{
string sel ="Select * FROM vw_bms_ActiveJobTitles WHERE contractorID = @.contractorID";
SqlCommand cmd =new SqlCommand(sel,new SqlConnection(GetConnectionString()));
cmd.Parameters.AddWithValue(Paramaters.AddWithValue("@.ContractorID", contractorID);
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}

Kind Regards,

Josh

Josh again thanks for the quick and great feedback.

It is working now.

But once I try to bind those two dropdownlists to display the data tables current selection.. I get this error..

ERROR: Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control.

Josh:

In case you haven't guessed, I'm trying to limit the selections of ddl_JobTitles based on the current contractorID selected in ddl_Contractors.

I would think this would be easier but I've have been beating my head against the wall for two days now.

Ideally, when the page first loads the selected values are selected based on the contractorID and jobtitleid already residing in the data table for a specific staff member.

If ddl_Contractors is selected, the page postsback and ddl_JobTitles only has items based on the contractorID that was selected from ddl_Contractors.

Of course I would want some type of error handling based on ddl_Contractors not having a corresponding contractorID in the ddl_JobTitles list but I must first crawl before I can walk.

I have found this example of how to perform this type of task...http://www.webswapp.com/CodeSamples/aspnet20/dependentlists/default.aspx

But for the life of me cannot understand the code because it is well above my current C# and asp.net knowledge at this point.

Any help on this isue would be greatly appreciated.

Jim,

Sorry for the delayed response, pre-christmas weekend craziness. We'll get this worked out for you. Couple quick questions before I start throwing up some sample code:

1) Are you in ASP.NET 1.X or 2.0?
2) Are these dropdowns being hosted inside of a control such as formview\detailview as the article link you provided does?

Generally speaking it sounds like what you're looking for is dependent dropdowns. If your using ASP.NET 2.0 and you are able to use a release candidate quality solution I would recommend using ASP.NET Ajax, Microsoft's AJAX toolkit currently in RC1. It has included with it a free toolkit of controls (called AJAXControlToolkit) that includes in a dependant dropdown control called CascadingDropdowns. It allows you to have as many dropdown controls chained together in a dependancy to load on demand. The nice aspect of this is it's all client side and no postbacks and IMO is very stable (Been using it in production since beta 2). Some examples and sample code can be found athttp://ajax.asp.net/ajaxtoolkit/CascadingDropDown/CascadingDropDown.aspx. All you need to do is download the ASP.NET Ajax libary and the toolkit (http://www.codeplex.com/Wiki/View.aspx?ProjectName=AtlasControlToolkit for the tookit andhttp://ajax.asp.net/ for the core library). Although this toolkit is RC1 I've found it to be very stable when it comes to CascadingDropDown functionality and as I mentioned I've been using in production since beta2 but as with all pre-release/beta products you do incure some risk.

If you happen to still be in the 1.1 world or beta/RC type soution is an option for you I'd be happy to provide you some sample code to get you going using normal dropdown binding and postback behaviors, if memory serves me right there can be a few gotcha's with it. Let me know.

Regards,

Josh

passing a value to an event handler from dropdownlist

I've got a command button to submit a value from a dropdown list that should
then filter a SELECT query. I'm simply appending a WHERE colx =
<variableSelectedFromDropdownList>. How do I pass this value into the event
handler?

-- MY EVENT HANDLER

Sub RunReport_OnClick(sender As Object, e As System.EventArgs)

_sqlStmt = _sqlStmt & " AND colx = '<variableSelectedFromDropdownList>'"
BindData()

End Sub

-- ON MY WEB FORM
<ASP:Button id="cmdRunReport" Text="Run Report" runat="server"
onclick="RunReport_OnClick" /
<ASP:dropdownlist id="Provinces" runat="server" Font-Size="8pt"
Width="100px"></ASP:dropdownlist
-- MY DATA ACCESS CODE

Sub BindData()
Dim conString As String = "server=server;database=db;uid=un;pwd=pwd;"
Dim myDataSet1 As New DataSet
Dim myDataAdapter1 As New SqlDataAdapter(_sqlStmt, conString)
myDataAdapter1.Fill(myDataSet1, "Communities")
DataGrid2.DataSource = myDataSet1.Tables("Communities")

Dim myDataSet2 As New DataSet
Dim myDataAdapter2 As New SqlDataAdapter(_sqlStmt2, conString)
myDataAdapter2.Fill(myDataSet2, "ProvincesT")
Provinces.Datasource = myDataSet2.Tables("ProvincesT")
Provinces.DataMember = "ProvincesT"
Provinces.DataTextField = "clnName"
Provinces.DataValueField = "clnGUID"

DataGrid2.DataBind()
Provinces.DataBind()

End Sub

_____
DC GChange the following
_sqlStmt = _sqlStmt & " AND colx = '<variableSelectedFromDropdownList>'
t
_sqlStmt = _sqlStmt & " AND colx = '" & mydropdownlist.SelectedItem.Value & "'
'your data access code will go her
BindData(

HTH
Suresh

p.s. Sorry i couldn't help you with "Datagrid won't sort" problem. If you still haven't figured it out please create another message for your problem on this NG

-- DC Gringo wrote: --

I've got a command button to submit a value from a dropdown list that shoul
then filter a SELECT query. I'm simply appending a WHERE colx
<variableSelectedFromDropdownList>. How do I pass this value into the even
handler

-- MY EVENT HANDLE

Sub RunReport_OnClick(sender As Object, e As System.EventArgs

_sqlStmt = _sqlStmt & " AND colx = '<variableSelectedFromDropdownList>'
BindData(

End Su

-- ON MY WEB FOR
<ASP:Button id="cmdRunReport" Text="Run Report" runat="server
onclick="RunReport_OnClick" /><ASP:dropdownlist id="Provinces" runat="server" Font-Size="8pt
Width="100px"></ASP:dropdownlist

-- MY DATA ACCESS COD

Sub BindData(
Dim conString As String = "server=server;database=db;uid=un;pwd=pwd;
Dim myDataSet1 As New DataSe
Dim myDataAdapter1 As New SqlDataAdapter(_sqlStmt, conString
myDataAdapter1.Fill(myDataSet1, "Communities"
DataGrid2.DataSource = myDataSet1.Tables("Communities"

Dim myDataSet2 As New DataSe
Dim myDataAdapter2 As New SqlDataAdapter(_sqlStmt2, conString
myDataAdapter2.Fill(myDataSet2, "ProvincesT"
Provinces.Datasource = myDataSet2.Tables("ProvincesT"
Provinces.DataMember = "ProvincesT
Provinces.DataTextField = "clnName
Provinces.DataValueField = "clnGUID

DataGrid2.DataBind(
Provinces.DataBind(

End Su

____
DC
Yes, that got rid of my error...but the results are only the first record in
the table everytime...and doesn't match the filter criteria...

_____
DC G

"Suresh" <anonymous@.discussions.microsoft.com> wrote in message
news:1284065D-1C70-46C7-B8E6-17C16AFB481C@.microsoft.com...
> Change the following
> _sqlStmt = _sqlStmt & " AND colx = '<variableSelectedFromDropdownList>'"
> to
> _sqlStmt = _sqlStmt & " AND colx = '" & mydropdownlist.SelectedItem.Value
& "'"
> 'your data access code will go here
> BindData()
> HTH,
> Suresh.
> p.s. Sorry i couldn't help you with "Datagrid won't sort" problem. If you
still haven't figured it out please create another message for your problem
on this NG.
>
> -- DC Gringo wrote: --
> I've got a command button to submit a value from a dropdown list that
should
> then filter a SELECT query. I'm simply appending a WHERE colx =
> <variableSelectedFromDropdownList>. How do I pass this value into
the event
> handler?
> -- MY EVENT HANDLER
> Sub RunReport_OnClick(sender As Object, e As System.EventArgs)
> _sqlStmt = _sqlStmt & " AND colx =
'<variableSelectedFromDropdownList>'"
> BindData()
>
> End Sub
> -- ON MY WEB FORM
> <ASP:Button id="cmdRunReport" Text="Run Report" runat="server"
> onclick="RunReport_OnClick" /><ASP:dropdownlist id="Provinces"
runat="server" Font-Size="8pt"
> Width="100px"></ASP:dropdownlist>
>
> -- MY DATA ACCESS CODE
> Sub BindData()
> Dim conString As String =
"server=server;database=db;uid=un;pwd=pwd;"
> Dim myDataSet1 As New DataSet
> Dim myDataAdapter1 As New SqlDataAdapter(_sqlStmt, conString)
> myDataAdapter1.Fill(myDataSet1, "Communities")
> DataGrid2.DataSource = myDataSet1.Tables("Communities")
> Dim myDataSet2 As New DataSet
> Dim myDataAdapter2 As New SqlDataAdapter(_sqlStmt2,
conString)
> myDataAdapter2.Fill(myDataSet2, "ProvincesT")
> Provinces.Datasource = myDataSet2.Tables("ProvincesT")
> Provinces.DataMember = "ProvincesT"
> Provinces.DataTextField = "clnName"
> Provinces.DataValueField = "clnGUID"
>
> DataGrid2.DataBind()
> Provinces.DataBind()
> End Sub
>
> _____
> DC G
What's your _sqlStmt

Can you also post your Data access code

Suresh

-- DC Gringo wrote: --

Yes, that got rid of my error...but the results are only the first record i
the table everytime...and doesn't match the filter criteria...

____
DC

"Suresh" <anonymous@.discussions.microsoft.com> wrote in messag
news:1284065D-1C70-46C7-B8E6-17C16AFB481C@.microsoft.com..
> Change the followin
> _sqlStmt = _sqlStmt & " AND colx = '<variableSelectedFromDropdownList>'
> t
> _sqlStmt = _sqlStmt & " AND colx = '" & mydropdownlist.SelectedItem.Valu
& "'
> 'your data access code will go her
> BindData(
>> HTH
> Suresh
>> p.s. Sorry i couldn't help you with "Datagrid won't sort" problem. If yo
still haven't figured it out please create another message for your proble
on this NG
>>> -- DC Gringo wrote: --
>> I've got a command button to submit a value from a dropdown list tha
shoul
> then filter a SELECT query. I'm simply appending a WHERE colx
><variableSelectedFromDropdownList>. How do I pass this value int
the even
> handler
>> -- MY EVENT HANDLE
>> Sub RunReport_OnClick(sender As Object, e As System.EventArgs
>> _sqlStmt = _sqlStmt & " AND colx
'<variableSelectedFromDropdownList>'
> BindData(
>>> End Su
>> -- ON MY WEB FOR
><ASP:Button id="cmdRunReport" Text="Run Report" runat="server
> onclick="RunReport_OnClick" /><ASP:dropdownlist id="Provinces
runat="server" Font-Size="8pt
> Width="100px"></ASP:dropdownlist>>>> -- MY DATA ACCESS COD
>> Sub BindData(
> Dim conString As String
"server=server;database=db;uid=un;pwd=pwd;
> Dim myDataSet1 As New DataSe
> Dim myDataAdapter1 As New SqlDataAdapter(_sqlStmt, conString
> myDataAdapter1.Fill(myDataSet1, "Communities"
> DataGrid2.DataSource = myDataSet1.Tables("Communities"
>> Dim myDataSet2 As New DataSe
> Dim myDataAdapter2 As New SqlDataAdapter(_sqlStmt2
conString
> myDataAdapter2.Fill(myDataSet2, "ProvincesT"
> Provinces.Datasource = myDataSet2.Tables("ProvincesT"
> Provinces.DataMember = "ProvincesT
> Provinces.DataTextField = "clnName
> Provinces.DataValueField = "clnGUID
>>> DataGrid2.DataBind(
> Provinces.DataBind(
>> End Su
>>> ____
> DC
>>>
Suresh, here's the whole thing:

<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Web.UI.WebControls" %>
<%@. Import Namespace="System.Web.UI.WebControls.DropDownList" %
<%@. Page Language="VB" Debug="true" %>
<script runat="server" language="VB"
Protected _sqlStmt As String = _
"SELECT c1.clnName as Community, s1.clnGUID, s1.clnPriorityCorrectedTC as
Impact, PopulationKeyInfo = ISNULL(s1.clnPopulationKeyInfo,0),
MinedAreaVictimCount = ISNULL(mavc1.MinedAreaVictimCount,0), nonRecentVictim
= ISNULL(s1.clnVictimOldKilled + s1.clnVictimOldInjured, 0), SHACount =
ISNULL(mavc1.SHACount,0), clnEconomicBaseTC =
ISNULL(s1.clnEconomicBaseTC,'None specified'), MinDistance =
ISNULL(sha1.MinDistance, 0), VictimAssist = ISNULL(mavc1.VictimAssist,
'No'), clnMADoneTC = ISNULL(s1.clnMADoneTC,'Unknown') FROM tblSurvey1 s1
INNER JOIN tblCity c1 ON s1.clnNearestCityGUID = c1.clnGUID INNER JOIN
vwMinDistancesToNearestSHA sha1 ON s1.clnGUID = sha1.clnSurveyGUID LEFT
OUTER JOIN vwMinedAreaVictimCount mavc1 ON s1.clnGUID = mavc1.clnGUID WHERE
s1.clnPriorityCorrectedTC <> 'None'"

Protected _sqlStmt5 As String = _
"SELECT c1.clnName, s1.clnGUID FROM tblSurvey1 s1 INNER JOIN tblCity c1 ON
s1.clnNearestCityGUID = c1.clnGUID ORDER BY c1.clnName"

'Protected WithEvents ddlCommunities As
System.Web.UI.WebControls.DropDownList

Sub Page_Load(Source As Object, E As EventArgs)
'If Not Page.IsPostBack Then
BindData()
'End If
End Sub

Sub BindData()
Dim conString As String = "server=server;database=db;uid=user;pwd=pwd;"
Dim myDataSet1 As New DataSet
Dim myDataAdapter1 As New SqlDataAdapter(_sqlStmt, conString)
myDataAdapter1.Fill(myDataSet1, "CommunitiesT1")
DataGrid2.DataSource = myDataSet1.Tables("CommunitiesT1")

Dim myDataSet5 As New DataSet
Dim myDataAdapter5 As New SqlDataAdapter(_sqlStmt5, conString)
myDataAdapter5.Fill(myDataSet5, "CommunitiesT2")
ddlCommunities.DataSource = myDataSet5.Tables("CommunitiesT2")
ddlCommunities.DataMember = "CommunitiesT2"
ddlCommunities.DataTextField = "clnName"
ddlCommunities.DataValueField = "clnGUID"

DataGrid2.DataBind()
ddlCommunities.DataBind()

End Sub

Sub SortCommand_OnClick(Source As Object, E As
DataGridSortCommandEventArgs)
_sqlStmt = _sqlStmt & " ORDER BY " & E.SortExpression
BindData()
End Sub

Sub PageIndexChanged_OnClick(Source As Object, E As
DataGridPageChangedEventArgs)
DataGrid2.CurrentPageIndex = E.NewPageIndex
BindData()
End Sub

Sub RunReport_OnClick(sender As Object, e As System.EventArgs)

_sqlStmt = _sqlStmt & " AND s1.clnGUID =
'"+ddlCommunities.SelectedItem.Value+"'"

BindData()

End Sub

</script
<html>
<head>
<title></title
<style>
.DataGrid {font:x-small Verdana, Arial, sans-serif}
</style>
<LINK rel="stylesheet" href="http://links.10026.com/?link=../Styles.css" type="text/css">
</head>
<body
<asp:dropdownlist Font-Size="8" id="ddlCommunities" runat="server"
Width="100"></asp:dropdownlist></td
<ASP:Button id="cmdRunReport" Text="Run Report" runat="server"
onclick="RunReport_OnClick" /
<asp:DataGrid
AllowCustomPaging="false"
AllowPaging="true"
AllowSorting="true"
AlternatingItemStyle-BackColor="#EFEFEF"
AutoGenerateColumns="false"
Border="0"
Cellpadding="4"
Cellspacing="0"
CssClass="DataGrid"
DataKeyField="clnGUID"
Enabled="true"
EnableViewState="true"
HeaderStyle-BackColor="Black"
HeaderStyle-Font-Bold="True"
HeaderStyle-ForeColor="White" id="DataGrid2" runat="server"
ShowFooter="false"
OnSortCommand="SortCommand_OnClick"
OnPageIndexChanged="PageIndexChanged_OnClick"
PageSize="50"
PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="Right"
ShowHeader="true"

<SelectedItemStyle Font-Bold="True" ForeColor="#663399"
BackColor="#FFCC66"></SelectedItemStyle>
<ItemStyle Font-Size="8pt" Font-Names="Verdana" ForeColor="#330099"
BackColor="White"></ItemStyle>
<HeaderStyle Font-Size="8pt" Font-Names="Verdana" Font-Bold="True"
HorizontalAlign="Center" ForeColor="#FFFFCC"
BackColor="#990000"></HeaderStyle>
<FooterStyle ForeColor="#330099" BackColor="#FFFFCC"></FooterStyle
<Columns>
<asp:HyperLinkColumn DataNavigateUrlField="Community"
DataNavigateUrlFormatString="clnGUID" DataTextField="Community"
Visible="true" NavigateUrl="http://{cgi.server_name}/c={clnGUID}"
SortExpression="Community"
HeaderText="Community" text="Community" runat="server">
<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
<ItemStyle HorizontalAlign="Left"></ItemStyle>
</asp:HyperLinkColumn>
<asp:BoundColumn DataField="Impact" SortExpression="Impact"
HeaderText="Impact"></asp:BoundColumn>
<asp:BoundColumn DataField="PopulationKeyInfo"
SortExpression="PopulationKeyInfo" HeaderText="Population<br>Key
Info"></asp:BoundColumn>
<asp:BoundColumn DataField="MinedAreaVictimCount"
SortExpression="MinedAreaVictimCount"
HeaderText="Recent<br>Victims"></asp:BoundColumn>
<asp:BoundColumn DataField="SHACount" SortExpression="SHACount"
HeaderText="No. of<br>SHA's"></asp:BoundColumn>
<asp:BoundColumn DataField="clnEconomicBaseTC"
SortExpression="clnEconomicBaseTC"
HeaderText="Economic<br>Base"></asp:BoundColumn>
<asp:BoundColumn DataField="clnMADoneTC" SortExpression="clnMADoneTC"
HeaderText="Mine Risk<br>Education"></asp:BoundColumn>
<asp:BoundColumn DataField="VictimAssist" SortExpression="VictimAssist"
HeaderText="Victim<br>Assist"></asp:BoundColumn>
<asp:BoundColumn DataField="MinDistance" SortExpression="MinDistance"
HeaderText="Min Distance<br>to SHA"></asp:BoundColumn>
</columns>
<PagerStyle HorizontalAlign="Center" ForeColor="#330099"
Position="TopAndBottom" BackColor="#FFFFCC"
Mode="NumericPages"></PagerStyle>
</asp:DataGrid
</form>
</body>
</html>
"Suresh" <anonymous@.discussions.microsoft.com> wrote in message
news:6EE07FEA-BD32-44E2-AB30-C0A814C040F2@.microsoft.com...
> What's your _sqlStmt?
> Can you also post your Data access code?
> Suresh.
> -- DC Gringo wrote: --
> Yes, that got rid of my error...but the results are only the first
record in
> the table everytime...and doesn't match the filter criteria...
> _____
> DC G
>
> "Suresh" <anonymous@.discussions.microsoft.com> wrote in message
> news:1284065D-1C70-46C7-B8E6-17C16AFB481C@.microsoft.com...
> > Change the following
> > _sqlStmt = _sqlStmt & " AND colx =
'<variableSelectedFromDropdownList>'"
> > to
> > _sqlStmt = _sqlStmt & " AND colx = '" &
mydropdownlist.SelectedItem.Value
> & "'"
> > 'your data access code will go here
> > BindData()
> >> HTH,
> > Suresh.
> >> p.s. Sorry i couldn't help you with "Datagrid won't sort" problem.
If you
> still haven't figured it out please create another message for your
problem
> on this NG.
> >>> -- DC Gringo wrote: --
> >> I've got a command button to submit a value from a dropdown
list that
> should
> > then filter a SELECT query. I'm simply appending a WHERE colx
=
> ><variableSelectedFromDropdownList>. How do I pass this value into
> the event
> > handler?
> >> -- MY EVENT HANDLER
> >> Sub RunReport_OnClick(sender As Object, e As
System.EventArgs)
> >> _sqlStmt = _sqlStmt & " AND colx =
> '<variableSelectedFromDropdownList>'"
> > BindData()
> >>> End Sub
> >> -- ON MY WEB FORM
> ><ASP:Button id="cmdRunReport" Text="Run Report" runat="server"
> > onclick="RunReport_OnClick" /><ASP:dropdownlist id="Provinces"
> runat="server" Font-Size="8pt"
> > Width="100px"></ASP:dropdownlist>>>> -- MY DATA ACCESS
CODE
> >> Sub BindData()
> > Dim conString As String =
> "server=server;database=db;uid=un;pwd=pwd;"
> > Dim myDataSet1 As New DataSet
> > Dim myDataAdapter1 As New SqlDataAdapter(_sqlStmt,
conString)
> > myDataAdapter1.Fill(myDataSet1, "Communities")
> > DataGrid2.DataSource = myDataSet1.Tables("Communities")
> >> Dim myDataSet2 As New DataSet
> > Dim myDataAdapter2 As New SqlDataAdapter(_sqlStmt2,
> conString)
> > myDataAdapter2.Fill(myDataSet2, "ProvincesT")
> > Provinces.Datasource = myDataSet2.Tables("ProvincesT")
> > Provinces.DataMember = "ProvincesT"
> > Provinces.DataTextField = "clnName"
> > Provinces.DataValueField = "clnGUID"
> >>> DataGrid2.DataBind()
> > Provinces.DataBind()
> >> End Sub
> >>> _____
> > DC G
> >>
Everything looks ok. The query maybe returning the wrong data

Put a break point on the following line
Dim myDataAdapter1 As New SqlDataAdapter(_sqlStmt, conString

After the report button click event retrieve what's in the _sqlStmt string variable. You should be able to get that text from Immediate Window. Copy it over to SQL query analyzer, execute it and see what you get

Suresh

-- DC Gringo wrote: --

Suresh, here's the whole thing

<%@. Import Namespace="System.Data" %><%@. Import Namespace="System.Data.SqlClient" %><%@. Import Namespace="System.Web.UI.WebControls" %><%@. Import Namespace="System.Web.UI.WebControls.DropDownList" %><%@. Page Language="VB" Debug="true" %><script runat="server" language="VB"

Protected _sqlStmt As String =
"SELECT c1.clnName as Community, s1.clnGUID, s1.clnPriorityCorrectedTC a
Impact, PopulationKeyInfo = ISNULL(s1.clnPopulationKeyInfo,0)
MinedAreaVictimCount = ISNULL(mavc1.MinedAreaVictimCount,0), nonRecentVicti
= ISNULL(s1.clnVictimOldKilled + s1.clnVictimOldInjured, 0), SHACount
ISNULL(mavc1.SHACount,0), clnEconomicBaseTC
ISNULL(s1.clnEconomicBaseTC,'None specified'), MinDistance
ISNULL(sha1.MinDistance, 0), VictimAssist = ISNULL(mavc1.VictimAssist
'No'), clnMADoneTC = ISNULL(s1.clnMADoneTC,'Unknown') FROM tblSurvey1 s
INNER JOIN tblCity c1 ON s1.clnNearestCityGUID = c1.clnGUID INNER JOI
vwMinDistancesToNearestSHA sha1 ON s1.clnGUID = sha1.clnSurveyGUID LEF
OUTER JOIN vwMinedAreaVictimCount mavc1 ON s1.clnGUID = mavc1.clnGUID WHER
s1.clnPriorityCorrectedTC <> 'None'

Protected _sqlStmt5 As String =
"SELECT c1.clnName, s1.clnGUID FROM tblSurvey1 s1 INNER JOIN tblCity c1 O
s1.clnNearestCityGUID = c1.clnGUID ORDER BY c1.clnName

'Protected WithEvents ddlCommunities A
System.Web.UI.WebControls.DropDownLis

Sub Page_Load(Source As Object, E As EventArgs
'If Not Page.IsPostBack The
BindData(
'End I
End Su

Sub BindData(
Dim conString As String = "server=server;database=db;uid=user;pwd=pwd;
Dim myDataSet1 As New DataSe
Dim myDataAdapter1 As New SqlDataAdapter(_sqlStmt, conString
myDataAdapter1.Fill(myDataSet1, "CommunitiesT1"
DataGrid2.DataSource = myDataSet1.Tables("CommunitiesT1"

Dim myDataSet5 As New DataSe
Dim myDataAdapter5 As New SqlDataAdapter(_sqlStmt5, conString
myDataAdapter5.Fill(myDataSet5, "CommunitiesT2"
ddlCommunities.DataSource = myDataSet5.Tables("CommunitiesT2"
ddlCommunities.DataMember = "CommunitiesT2
ddlCommunities.DataTextField = "clnName
ddlCommunities.DataValueField = "clnGUID

DataGrid2.DataBind(
ddlCommunities.DataBind(

End Su

Sub SortCommand_OnClick(Source As Object, E A
DataGridSortCommandEventArgs
_sqlStmt = _sqlStmt & " ORDER BY " & E.SortExpressio
BindData(
End Su

Sub PageIndexChanged_OnClick(Source As Object, E A
DataGridPageChangedEventArgs
DataGrid2.CurrentPageIndex = E.NewPageInde
BindData(
End Su

Sub RunReport_OnClick(sender As Object, e As System.EventArgs

_sqlStmt = _sqlStmt & " AND s1.clnGUID
'"+ddlCommunities.SelectedItem.Value+"'

BindData(

End Su

</script><html><head><title></title><style
.DataGrid {font:x-small Verdana, Arial, sans-serif
</style><LINK rel="stylesheet" href="http://links.10026.com/?link=../Styles.css" type="text/css"></head><body><asp:dropdownlist Font-Size="8" id="ddlCommunities" runat="server
Width="100"></asp:dropdownlist></td><ASP:Button id="cmdRunReport" Text="Run Report" runat="server
onclick="RunReport_OnClick" /><asp:DataGri
AllowCustomPaging="false
AllowPaging="true
AllowSorting="true
AlternatingItemStyle-BackColor="#EFEFEF
AutoGenerateColumns="false"
Border="0"
Cellpadding="4"
Cellspacing="0"
CssClass="DataGrid"
DataKeyField="clnGUID"
Enabled="true"
EnableViewState="true"
HeaderStyle-BackColor="Black"
HeaderStyle-Font-Bold="True"
HeaderStyle-ForeColor="White" id="DataGrid2" runat="server"
ShowFooter="false"
OnSortCommand="SortCommand_OnClick"
OnPageIndexChanged="PageIndexChanged_OnClick"
PageSize="50"
PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="Right"
ShowHeader="true"
><SelectedItemStyle Font-Bold="True" ForeColor="#663399"
BackColor="#FFCC66"></SelectedItemStyle><ItemStyle Font-Size="8pt" Font-Names="Verdana" ForeColor="#330099"
BackColor="White"></ItemStyle><HeaderStyle Font-Size="8pt" Font-Names="Verdana" Font-Bold="True"
HorizontalAlign="Center" ForeColor="#FFFFCC"
BackColor="#990000"></HeaderStyle><FooterStyle ForeColor="#330099" BackColor="#FFFFCC"></FooterStyle><Columns><asp:HyperLinkColumn DataNavigateUrlField="Community"
DataNavigateUrlFormatString="clnGUID" DataTextField="Community"
Visible="true" NavigateUrl="http://{cgi.server_name}/c={clnGUID}"
SortExpression="Community"
HeaderText="Community" text="Community" runat="server"><HeaderStyle HorizontalAlign="Left"></HeaderStyle><ItemStyle HorizontalAlign="Left"></ItemStyle></asp:HyperLinkColumn><asp:BoundColumn DataField="Impact" SortExpression="Impact"
HeaderText="Impact"></asp:BoundColumn><asp:BoundColumn DataField="PopulationKeyInfo"
SortExpression="PopulationKeyInfo" HeaderText="Population<br>Key
Info"></asp:BoundColumn><asp:BoundColumn DataField="MinedAreaVictimCount"
SortExpression="MinedAreaVictimCount"
HeaderText="Recent<br>Victims"></asp:BoundColumn><asp:BoundColumn DataField="SHACount" SortExpression="SHACount"
HeaderText="No. of<br>SHA's"></asp:BoundColumn><asp:BoundColumn DataField="clnEconomicBaseTC"
SortExpression="clnEconomicBaseTC"
HeaderText="Economic<br>Base"></asp:BoundColumn><asp:BoundColumn DataField="clnMADoneTC" SortExpression="clnMADoneTC"
HeaderText="Mine Risk<br>Education"></asp:BoundColumn><asp:BoundColumn DataField="VictimAssist" SortExpression="VictimAssist"
HeaderText="Victim<br>Assist"></asp:BoundColumn><asp:BoundColumn DataField="MinDistance" SortExpression="MinDistance"
HeaderText="Min Distance<br>to SHA"></asp:BoundColumn></columns><PagerStyle HorizontalAlign="Center" ForeColor="#330099"
Position="TopAndBottom" BackColor="#FFFFCC"
Mode="NumericPages"></PagerStyle></asp:DataGrid></form></body></html>

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.

Passing a variable in the onLoad event.

I have a data grid with multiple rows. Each row has a drop down box. I need
it to select the correct item for each row. What I am trying to figure out,
is can I pass a value to the onLoad event for the dropdownlist?

OnLoad="loadDefault(Container.DataItem("dropValue"))"Are you trying to put a selected index?

"Robin Bonin" <robin@.guavatools.com> wrote in message
news:Sr6dnWpfNfAlKIiiXTWJiA@.eatel.net...
> I have a data grid with multiple rows. Each row has a drop down box. I
need
> it to select the correct item for each row. What I am trying to figure
out,
> is can I pass a value to the onLoad event for the dropdownlist?
> OnLoad="loadDefault(Container.DataItem("dropValue"))"
I know the value of the selectedIndex, and that is what I'm trying to
select.

"Cristian Suazo" <crillus7@.hotmail.com> wrote in message
news:O$93Kq9SDHA.1724@.TK2MSFTNGP10.phx.gbl...
> Are you trying to put a selected index?
> "Robin Bonin" <robin@.guavatools.com> wrote in message
> news:Sr6dnWpfNfAlKIiiXTWJiA@.eatel.net...
> > I have a data grid with multiple rows. Each row has a drop down box. I
> need
> > it to select the correct item for each row. What I am trying to figure
> out,
> > is can I pass a value to the onLoad event for the dropdownlist?
> > OnLoad="loadDefault(Container.DataItem("dropValue"))"
Just set the selected index in the code behind: myDropDown.SelectedIndex =
intSelectedIndex

--
S. Justin Gengo, MCP
Web Developer / Programmer

Free Code Library At:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche

"Robin Bonin" <robin@.guavatools.com> wrote in message
news:foidna-hLOZ1JIiiXTWJjg@.eatel.net...
> I know the value of the selectedIndex, and that is what I'm trying to
> select.
>
> "Cristian Suazo" <crillus7@.hotmail.com> wrote in message
> news:O$93Kq9SDHA.1724@.TK2MSFTNGP10.phx.gbl...
> > Are you trying to put a selected index?
> > "Robin Bonin" <robin@.guavatools.com> wrote in message
> > news:Sr6dnWpfNfAlKIiiXTWJiA@.eatel.net...
> > > I have a data grid with multiple rows. Each row has a drop down box. I
> > need
> > > it to select the correct item for each row. What I am trying to figure
> > out,
> > > is can I pass a value to the onLoad event for the dropdownlist?
> > > > OnLoad="loadDefault(Container.DataItem("dropValue"))"
> >