Custom Query Issue

For general questions and discussions specific to the AbleCommerce 7.0 Asp.Net product.
Post Reply
User avatar
laramp
Lieutenant, Jr. Grade (LT JG)
Lieutenant, Jr. Grade (LT JG)
Posts: 36
Joined: Tue Jul 25, 2006 1:01 pm

Custom Query Issue

Post by laramp » Sat Jun 08, 2013 1:29 pm

Can anyone please guide me in the right direction on this query. I have been trying to get it to work for a few days and for some reason the AddInParameters is not being used by the query???
I am trying to run this query for a DropDownList based upon the selected value of another DropDownList. I am getting the value from the first dropdown.... it just isn't working in the query (@InputValue)
this is the line that is not working(database.AddInParameter(selectCommand, "@InputVal", System.Data.DbType.String, inputvalue);)
If anyone has a better way to write this ...I am all ears... I am pretty much a hack at C#.
private void FillOWCollections(string inputvalue)
{
//Response.Write(inputvalue);
CommerceBuilder.Data.Database database = Token.Instance.Database;
string sql = ("SELECT DISTINCT InputValue AS InputValue2, InputFieldId FROM ac_ProductTemplateFields WHERE (InputFieldId = 696) AND (ProductId IN (SELECT ProductId FROM ac_ProductTemplateFields AS ac_ProductTemplateFields_1 WHERE (InputValue = '@InputVal'))) ");
DbCommand selectCommand = database.GetSqlStringCommand(sql);
database.AddInParameter(selectCommand, "@InputVal", System.Data.DbType.String, inputvalue);
using (IDataReader reader = database.ExecuteReader(selectCommand))
if (((System.Data.Common.DbDataReader)reader).HasRows)
{
//database.AddInParameter(selectCommand, "@InputVal", System.Data.DbType.String, inputvalue);
//ddlOWCollections.DataSource = reader;
ddlOWCollections.DataTextField = "InputValue2";
ddlOWCollections.DataValueField = "InputValue2";
ddlOWCollections.DataBind();
ddlOWCollections.Items.Insert(0, "--Select Collection--");

}
else
{
lblMsg.Text = "No Media Types found";
}
}

User avatar
laramp
Lieutenant, Jr. Grade (LT JG)
Lieutenant, Jr. Grade (LT JG)
Posts: 36
Joined: Tue Jul 25, 2006 1:01 pm

Re: Custom Query Issue

Post by laramp » Mon Jun 10, 2013 12:19 pm

I have figured this out... I needed to use a Stored Procedure. Everything works fine now.

string sql = "dbo.mystoredproc";
DbCommand selectCommand = database.GetStoredProcCommand(sql);

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Custom Query Issue

Post by AbleMods » Wed Jun 12, 2013 2:22 pm

I can probably help you find an easier way to do it with the Able classes if you want to give me some specifics.

Looks like you're trying to pull in product template input choices for a static InputFieldId of 696? And then populate those choices into a dropdown list control through data binding?
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
laramp
Lieutenant, Jr. Grade (LT JG)
Lieutenant, Jr. Grade (LT JG)
Posts: 36
Joined: Tue Jul 25, 2006 1:01 pm

Re: Custom Query Issue

Post by laramp » Wed Jun 12, 2013 3:15 pm

Hi Joe,
Yes, that is what I am doing. I would prefer to use jquery to do this. I am building cascading dropdowns and using a product template with 3 options to populate the dropdowns. I have the dropdowns working at this point but still need to reload the content page with the results of each selected index from the dropdown.

Post Reply