locked
Select Data from DB Column based on a variable RRS feed

  • Question

  • User-601888530 posted

    So...

    I have a table with columns

    SKU, imgurl0, imgurl1, imgurl2, imgurl3, imgurl4, imgurl5, imgurl6, imgurl7, imgurl8, imgurl9

    These actually go all the way to 20..

    They contain an Image URL.

    I want to put the image into the page IF they exist.

    Here is my code.

     @{
                    var i = 0;
                    while (i < 20)
                    {
                var dbcolumnnamelooped = "imgurl" + i;
                var SQLSELECT = "SELECT * FROM products WHERE SKU=(@0)";
                var SQLDATA = db.Execute(SQLSELECT, SKU);
                var precelldata=SQLDATA + "." + dbcolumnnamelooped; 
                var celldata=precelldata;
                i += 1;
                if (celldata == null)
                {
                    <i>@i</i>
                }
                else
                {
                   <li><img src="/images/uploads/'@celldata" alt="@displayproduct.title" width="400px"></li>
                }
                }
                }

    It will be getting @displayproduct.title from further up in the project so ignore this.
    also db. is already declared. 

    The loop works well, loops 20 times as expected.

    When I try to retrieve data from specific column here

      var dbcolumnnamelooped = "imgurl" + i;
                var SQLSELECT = "SELECT * FROM products WHERE SKU=(@0)";
                var SQLDATA = db.Execute(SQLSELECT, SKU);
                var precelldata=SQLDATA + "." + dbcolumnnamelooped; 
                var celldata=precelldata;

    It types out that celldata as 'SQLDATA.imgurl0', 'SQLDATA.imgurl1', 'SQLDATA.imgurl2' etc

    Rather than it typing this it should be fethcing SQLDATA.imgurl0 and displaying that data?

    I think it has to do with data type? any help would be much appreciated!



    Friday, August 16, 2013 10:17 AM

Answers

  • User-1454326058 posted

    Hi happysack,

    Thanks for your post!

    According to your description, you want to retrieve single row data from DataBase.

    Based on your code, I don’t understand clearly.

    1.  You use the DataBase.Execute method, as far as I know it’s executes a non-query SQL statement.
    2.  The loop “while (i < 20)”, do you have 20 rows data instead of 20 columns?

    If you have 20 columns, I suggest that you can use the DataBase.Query method.

    @{
                var SQLSELECT = "SELECT * FROM products WHERE SKU=(@0)";
                var SQLDATA = db.Query(SQLSELECT, SKU);
           }
                <ul>
                @foreach(var item in SQLDATA)
                {
                    var i = 0;   
                    while(i<20)
                    {
                        if(item[i]==null)
                        {   
                            <li>@i</li>
                        }
                        else{
                            <li><img src="/images/uploads/@item[i]" alt="@displayproduct.title" width="400px"></li>
                        }
                        i++;
                    }
                             
                }  
               </ul>  
    

    Here is a reference below that you can get more information:

    Database Class

    http://msdn.microsoft.com/en-us/library/webmatrix.data.database(v=vs.111).aspx

    If I miss understand you, please let me know.

    Thanks

    Best Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 19, 2013 5:48 AM

All replies

  • User-376018714 posted

    Hi

    You are executing the query using Execute() method. But Execute() method does not return a resultset. Execute() method is used for executing  a non-query SQL statement. Here is the MSDN documentation for Execute() - http://msdn.microsoft.com/en-us/library/webmatrix.data.database.execute(v=vs.111).aspx

    What you need to do is to use the Query() method. Here is the documentation for Query() method - http://msdn.microsoft.com/en-us/library/webmatrix.data.database.query(v=vs.111).aspx

    For example on Data Access in web pages - go through the following blog post:

    http://www.mikesdotnetting.com/Article/172/Data-Access-Choices-For-WebMatrix

    regards
    Lohith 

    Monday, August 19, 2013 12:13 AM
  • User-601888530 posted

    Hi Kashyapa,

    Thanks for the reply.

    I have been able to receive data in this way in the past it just that when using a variable in it it doesnt work.

    How would i change this to get it working using your method all those 'string' seem quite confusing!

    many thanks!

    Monday, August 19, 2013 4:44 AM
  • User-1454326058 posted

    Hi happysack,

    Thanks for your post!

    According to your description, you want to retrieve single row data from DataBase.

    Based on your code, I don’t understand clearly.

    1.  You use the DataBase.Execute method, as far as I know it’s executes a non-query SQL statement.
    2.  The loop “while (i < 20)”, do you have 20 rows data instead of 20 columns?

    If you have 20 columns, I suggest that you can use the DataBase.Query method.

    @{
                var SQLSELECT = "SELECT * FROM products WHERE SKU=(@0)";
                var SQLDATA = db.Query(SQLSELECT, SKU);
           }
                <ul>
                @foreach(var item in SQLDATA)
                {
                    var i = 0;   
                    while(i<20)
                    {
                        if(item[i]==null)
                        {   
                            <li>@i</li>
                        }
                        else{
                            <li><img src="/images/uploads/@item[i]" alt="@displayproduct.title" width="400px"></li>
                        }
                        i++;
                    }
                             
                }  
               </ul>  
    

    Here is a reference below that you can get more information:

    Database Class

    http://msdn.microsoft.com/en-us/library/webmatrix.data.database(v=vs.111).aspx

    If I miss understand you, please let me know.

    Thanks

    Best Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 19, 2013 5:48 AM