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