DataTable in Coldfusion (in cfscript)

[Shraddha_Prajapati]

Hi i want to add DataTable in Coldfusion script...Like c# code...C# code is following:

                   DataTable productPropertyFilterTable = new DataTable();

                    productPropertyFilterTable.Columns.Add("RowID", typeof(int));

                    productPropertyFilterTable.Columns.Add("PropertyId", typeof(long));

                    productPropertyFilterTable.Columns.Add("PropertyWeight", typeof(string));

                    productPropertyFilterTable.Columns.Add("LowerRange", typeof(float));

                    productPropertyFilterTable.Columns.Add("UpperRange", typeof(float));

[duncancumming · Accepted solution]

You want to use QueryNew and QueryAddColumn.

productPropertyFilterTable  = queryNew('');

queryAddColumn(productPropertyFilterTable, 'RowID', 'Integer');

queryAddColumn(productPropertyFilterTable, 'PropertyWeight', 'VarChar');

Alternatively you can just do the same with QueryNew on its own.

productPropertyFilterTable  = queryNew('RowID,PropertyWeight', 'Integer,VarChar');

[Shraddha_Prajapati]

How to add data in datatabel like C# Code:

productPropertyFilterTable.Rows.Add(i, wordsStrings[0], wordsStrings[3], Limit[0], Limit[1]);

[duncancumming]

Now you need to use QueryAddRow and QuerySetCell

[Shraddha_Prajapati]

I try to use

QueryAddRow and QuerySetCell

queryAddRow(productPropertyFilterTable,1);

querySetCell(productPropertyFilterTable,RowID,i,1);

but its not work....

[duncancumming]

That second line you need to quote the column name:

querySetCell(productPropertyFilterTable, "RowID", i, 1);

[Shraddha_Prajapati]

Thanks...Its work....

[Shraddha_Prajapati]

Hi i have another issue...How can i write this c# code in coldfusion(Script).....

var warnings = new SqlParameter("ProductPropertyFilter", SqlDbType.Structured);

                        warnings.Value = productPropertyFilterTable;

                        warnings.TypeName = @"[dbo].[ProductPropertyFilter]";

                        using (var ctx = new ProductBookEntities())

                        {

                            var productFilter = ctx.Database.SqlQuery<ProductFilter>("dbo.FindScore @ProductPropertyFilter",

                               warnings);

                            dataProducts = productFilter.ToList();

                            if (dataProducts.Count() > range)

                            {

                                dataProducts = dataProducts.OrderByDescending(x => x.Score).Take(range).ToList();

                            }

                            ctx.Dispose();

                        }

[duncancumming]

You probably want to start by reading up on some CF documentation of how to do queries.  Start here:

• http://www.learncfinaweek.com/week1/Databases/
• https://wikidocs.adobe.com/wiki/display/coldfusionen/Accessing+and+Using+Data

[Shraddha_Prajapati]

remote function Retrieve(string dataLists)

          {

myQry = new Query();

myQry.setSQL("SELECT * FROM Product");

myQuery = myQry.execute();

dataProducts=myQuery.getResult();

dataProducts1=serializeJSON(dataProducts);

return dataProducts;

}

I call this method by using following code::

read: {

          type:"POST",

         url: "Controllers/Home.cfc",

         dataType: "json",

         data: {

                 method: "Retrieve",

                 dataLists: JSON.stringify(request)

                  }

        }

I want to return dataProducts or dataProducts1 from function , i want to know that which type of data they return string or list or json??

And also we get (dataProducts1=null), why this happen???In dataProducts i get the value...when i convert it json i get null value.....why????