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 CSV file into APSX page and returning query from SQL server using CSV data?

Is this possible basically?

I have a CSV file that contains a list of barcodes, I would like to create a page that will upload this into a temp table of some kind and query a SQL using the CSV file to join the data to the table.

This would then output the result to a list on the page.

Any ideas? As yet I haven't managed to start anything on ASP.net yet!

Of course it's possible. Almost anything is possible. When you get to your coding you should ask the specific questions that you need answered.

Here is one way on how to do this

1. Read your file using FileStream.
2. Create a datatable and its columns.
3. Loop through your file (using FileStream) and populate your datatable.
4. Create a dataview and set to the datatable.
5. Use rowfilter to filter out data you don't want to show.
6. Bind the data to your DataGrid or DataList to display the data onto the page.

Many thanks for your reply, more the kinda reply that I was hoping for.

The method that you have said, trying to understand this:

Does part 3 put the data into the database?

Where do I execute a query/sp to get the correct additional data back? (As more relevant data will be coming back based on a join to the new fields in the table)?

Thanks

passing a value to another page in C#?

I have a master page with a label on it and i would like to set the text of that label from my other pages programatically. Basically I have tried to access the control directly, and also created a public method in the master page that takes in a string value and sets it to the control. However from say the default page, I cannot access that public method. How can I pass the value to the master page so that it sets the aspnet control text?

I have also tried making a public string variable, and while in the pageload having it set the text for the label. From the default page, in the pageload I tried to set the value, but I cannot. Any suggestions? Thanks.

Have you tried to add a Public Property in your MasterPages that returns the Label?

This way you can access the Label through any content page!

HTH,

regards

Hello,
If you are talking about the Master and Content pages, then i have a small information for you. You can work upon it and find out the details of it.

The Content page can access the Master Page through the Keyword "Master". So u can search the control from the master using Master.FindControl() and set its value.

Hope this works for you.

Az.
can you show me an example? I am not sure of what you are saying. Thanks.
what does that findcontrol method return?

If you have a label in your master page called Label1 and a textbox on your default page called Textbox1, then you could set the label with the contents of the textbox with this code:

protected void Button1_Click(object sender, EventArgs e) { ((Label)Master.FindControl("Label1")).Text = TextBox1.Text; }

The Master object is a property in the page object that stores the master page for the content page. FindControl returns a control from the page with an ID that matches the string passed as a parameter. The control that is returned from FindControl must be cast to Label to get the Text property.

This code allows you to set the value of the label in the master page, but that value will not persist to other content pages that use the same master page.

It is better to use a property, than the casting one!

Suppose you have changed the name of the Label in the master page, then you will have to go back to all your content pages and change the label name, but in the Master Page using a property, you could change the control name, and keep the property name the same!

HTH,

Regards

Well the main reason for not setting the label.text property in the master page is that it needs to be dynamic. The text of that label will be set on the page_load of the page using the master template. If i set it in the master template, I cannot change it dynamically from the aspx page. That findcontrol method casted worked great however. Thank you!

Please check my post again. I didn't say casting doesn't work!

I said watch out to a drawback when using Cast, which happens if you change the Control Name in the master Page!

HTH,

Regards

Hi ,

Can you show me - or point me to any ariticle(s) on, how to pass values from content pages to master pages after the properties are set in the master pages?

Example:

Say I have a text box id="txt1"

In the master page code behid we would have something like

Private _txt as string

Public Property thisText As String
Get
Return _txt
End Get
Set
_txt = value
End Set
End property

Me.txt1.text = thisText

I hope I am right so far...

The question is: How would I pass values for 'thisText' from different content pages.

Thanks.

samirayes:

*snipped*

I hope I am right so far...

The question is: How would I pass values for 'thisText' from different content pages.

Master.thisText = "foo"