CODEWITHSUNDEEP
c#asp.netsql-server
Sweg
Sweg

Reputation: 99

Update Each Exisiting Row of SQL Server Table Using Loop

I'm trying to update a certain column for each row in my SQL Server table, using ASP.NET, C#.

When this code is executed, I want the OrderNo of the first row in the column to be = 1. From then on, I want the OrderNo to increment by 1 for the other existing rows in the table.

At the moment, I can update the OrderNo of one row, however I can't get a loop to properly read through all the rows & increment as I would like.

Here is the structure of my table

Here

And below is my C#:

 con.Open();
 SqlCommand cmdUpdateOrderNo;
 cmdUpdateOrderNo = new SqlCommand("UPDATE tblImages SET [OrderNo]=@O WHERE [OrderNo] = 2;", con);
 cmdUpdateOrderNo.Parameters.AddWithValue("@O", 4);
 cmdUpdateOrderNo.ExecuteNonQuery();

Here is the reason I need to update the OrderNo:

  protected void Timer1_Tick(object sender, EventArgs e)
{
    int i = (int)ViewState["ImageDisplayed"];
    i = i + 1;
    ViewState["ImageDisplayed"] = i;
    DataRow imageDataRow = ((DataSet)ViewState["ImageData"]).Tables["image"].Select().FirstOrDefault(x => x["order"].ToString() == i.ToString());
    if (imageDataRow != null)
    {
        Image1.ImageUrl = "~/MyImages/" + imageDataRow["name"].ToString();
        lblImageName.Text = imageDataRow["name"].ToString();
        lblImageOrder.Text = imageDataRow["order"].ToString();
        lblImageDesc.Text = imageDataRow["Desc"].ToString();
    }
    else
    {
        SetImageUrl();
    }
}

private void SetImageUrl()
{
    DataSet ds = new DataSet();//Creating a dataset
    SqlDataAdapter da = new SqlDataAdapter("SELECT Name, [Order], [Desc] FROM tblImages", con);
    da.Fill(ds, "image");

    ViewState["ImageData"] = ds;//storing the dataset in a ViewState variable
    ViewState["ImageDisplayed"] = 1;//storing order number of the image currently displayed

    DataRow imageDataRow = ds.Tables["image"].Select().FirstOrDefault(x => x["order"].ToString() == "1");
    Image1.ImageUrl = "~/MyImages/" + imageDataRow["name"].ToString();
    lblImageName.Text = imageDataRow["name"].ToString();
    lblImageOrder.Text = imageDataRow["order"].ToString();
    lblImageDesc.Text = imageDataRow["Desc"].ToString();
}

Upvotes: 1

Views: 985

Answers (2)

S3S
S3S

Reputation: 25112

I agree with @Jamiec on this that it seems like a bad idea, but you don't need a loop if it's really what you want to do. Here's an example using a CTE and a Window Function.

Example

WITH T AS
    (   SELECT  
            *,
            ROW_NUMBER() OVER (ORDER BY ID) as RN
        FROM    YourTable
    )

/* this shows you the results of the CTE, 
   specifically the ROW_NUMBER() 
   which will replace the OrderNo in the code below */

SELECT * FROM T

Runing This WIll Update Your Table

WITH T AS
(   SELECT  
        *,
        ROW_NUMBER() OVER (ORDER BY ID) as RN
    FROM    YourTable
)
UPDATE T
SET OrderNo = RN

Upvotes: 3

James Casey
James Casey

Reputation: 2507

When dealing with SQL you almost never want to be writing loops.

Try changing your command to this 

; with cte as
(
    select *, row_number() over (order by ID) as rn 
    from tblImages
) 
update tblImages
set OrderNo = rn
from cte

Upvotes: -1

Related Questions