Loop to retrieve data from a table row after row

Hello,

how do I have to write the loop (SQL-code) if I want to retrieve the content of the first row from a table that consists of one column and many rows and then do some calculation, next get the content of the next row and do a calculation ... until the end of the column?

Thanks in advance.

Bendo

 

0
Bendo
12/30/2008 10:17:03 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

12 Replies
740 Views

Similar Articles

[PageSpeed] 56

I'm assuming you are using Visual Web Developer or Visual Studio 2008 (or something similar to the two) and you have  a SQL Server database.

You can do calculations using a ExecuteReader() and a reader.  I can help you further with this.  Would a demo help if it had one  column, containing number and

1) you have a variable total

2) at each row the program took the number from the row (the single column) and added it to the total and

3) at the end it displayed the total on the front page?

The point of doing it this way would be that you can then take the code and make changes to test it out different ways.

(bonus feature: If you are just starting and you like what ExecuteReader() does, you want to also look into ExecuteNonQuery() and ExecuteScalar() -- I don't have a number but I'm guessing more than 75% of the programs I write use one or more of these.)

-Larry

(written later)  Can you tell us a little more about where you are at?  For example: started a few weeks ago, have some prior experience in another language, etc.

Oh, and the question - for code behind pages do you have a preference for C# or VB? 

**************************

The example below uses a table named 'Numbers' and it contains a PRIMARY KEY field 'id' (autoincrements by 1, starts at 1) and also a field 'numbers' that is type int.

A simple SELECT command is used that will look at every row in the table (we can talk about more restrictive commands later).

For this example it does three things for each row in the table:

  1. It uses reader["number"] to get the value from the field 'number' for the current record and it puts it into a variable (also it has to tell the program that it is an integer too -- get ready to see Convert.ToInt32(), Convert.ToDouble(), Convert.ToString(), Convert.ToDateTime() when you start working with ExecuteReader).
  2. It prints a line that uses the variable in a sentence so you can verify that the program read each line.
  3. It adds the variable to the total.

*************************

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UseExecuteScalar.aspx.cs" Inherits="UseExecuteScalar" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">

    <div id="showresults" runat="server" />

    </form>
</body>
</html>
 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public partial class UseExecuteScalar : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        int total = 0; 
        int currentnumber;
        showresults.InnerHtml = "";

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT Number FROM Numbers";
        cmd.CommandType = CommandType.Text;
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            currentnumber = Convert.ToInt32(reader["Number"]);
            showresults.InnerHtml += "I am in the loop and I see " + currentnumber + "&lt;br />";
            total += currentnumber;          //comment, this is the same as total = total + currentnumber
        }
        reader.Close();
        conn.Close();

        showresults.InnerHtml += "&lt;br /><br />";
        showresults.InnerHtml += "I added each one, one at at time, and the sum of all numbers in the loop is " + total + ".";
    }
}

 

One last thing: there is a glitch in the forum and if you see &lt; above (example: &lt;br ) the &lt; is really a less than sign.


Larry Dechent - Sampson Coatings

www.wemakebetterpaint.com has 29 examples (C# & VB) to help beginners with ASP.NET.
0
ldechent
12/30/2008 11:21:32 PM

 Hi,Bendo

U can Use Newfeature that was introduced in Sql 2005 for that 

With common table expression and recursive queries with that 

U can ggogle it out to see examples 

http://www.google.co.in/search?hl=en&q=Using+cte+recursion&btnG=Search&meta=


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
12/31/2008 3:13:23 AM

HI,

  You can also try with the Cursor.

It will be great if you can explain about the complete requirement what you want to acheive so that you can get a better solution than looping all the records one by one which is a costly operation.
 


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
12/31/2008 7:46:07 AM

Hello,

thanks for your answers I have study them in detail.

Bendo

0
Bendo
12/31/2008 9:31:22 AM

As Sri points out, if you can tell us more about what you want to do, we can help you further.

Please, if you have a preference between C# or VB, could you tell us?  It is also OK to say that you haven't decided yet.

-Larry

I'm starting to really like this thread -- raghav_khunger mentioned something I haven't come across yet.  Bendo, you didn't know it but you're helping me.


Larry Dechent - Sampson Coatings

www.wemakebetterpaint.com has 29 examples (C# & VB) to help beginners with ASP.NET.
0
ldechent
12/31/2008 3:13:16 PM

Hello Larry,

this is the text I posted some time ago and may help to clarify my question:

 

... there is a table ("datatable") with data that should be copied in many other tables.

The table names are stored in one column in a table called "tablenames".

To insert data from one table into another table the following code can be used:

insert into [databasename].[dbo].[XXX]

select * FROM [databasename].[dbo].[datatable]

Now I need a loop that takes out one table name after another (goes from one row to the next) from the "tablenames"-table

as variable XXX which is used as tablename for the insert query.

Does somebody know how to do this?

...

 

The SQL-code is written in MS SQL Server Mgmt Studio I don't know if this has something to do with C# or VB. If yes, VB would be my preference.

Thanks a lot.

Bendo

0
Bendo
12/31/2008 4:55:30 PM

The suggestion by RAGHAV to use Common Table Expressions (CTE) seems to be a good idea but I need some time to figure out how this really works.

0
Bendo
12/31/2008 5:05:39 PM

Hi,Bendo

Ok I GotWhat U want

U have A table with a column contaong names of other columns

and u have other insert query which u want to use to use that columns data as th table names o that query

So try this example I have made the logic in Bold 

 

 

 

CReate table table_Containing_Names

(id int identity,[tablenames] varchar(20))

GO

insert into table_Containing_Names

Select 'table1'

union all

Select 'table2'

union all

Select 'table3'

union all

Select 'table4'

GO

Create table table1

(id int ,[name] varchar(20))

GO

Create table table2

(id int ,[name] varchar(20))

GO

 

Create table table3

(id int ,[name] varchar(20))

GO

Create table table4

(id int ,[name] varchar(20))

GO

 

Create table datatable

(id int ,[name] varchar(20))

GO

insert into datatable

Select 1,'Abc'

union all

Select 2,'Def'

union all

Select
3,'Ghi'

 

 

Go

declare @title varchar(80)

declare table_Names cursor for

Select [tablenames] from table_Containing_Names

open table_Names

fetch table_Names

into @title

while (@@fetch_status = 0)

begin

declare @sql as nvarchar(max)

Set @sql='insert into '+@title+' select * FROM [datatable] '

--print @sql

exec sp_executesql @sql

fetch table_Names into @title

end

close table_Names

deallocate table_Names

Go

Select * from table1

Select * from table2

Select * from table3

Select * from table4

 

GO

--Output

table1

id name

----------- --------------------

1 Abc

2 Def

3 Ghi

 

 

table2

id name

----------- --------------------

1 Abc

2 Def

3 Ghi

 

table3

 

id name

----------- --------------------

1 Abc

2 Def

3 Ghi

 

table4

 

id name

----------- --------------------

1 Abc

2 Def

3 Ghi


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
12/31/2008 5:37:23 PM

OK I try this one

 

0
Bendo
12/31/2008 5:56:08 PM

Hello Raghav,

great job!

This one works as I wanted.

Thanks a lot.

 

It seems that you are really good in this topic.

Maybe you can answer me another question that is related to this one.

 

I still have the table with all the tablenames and furthermore I have a table say "table1" with no content.

Now I want to copy the design of table1 and create/save so many new tables with the design of table1 as there are in the tablenames-table and saving them with the corresponding tablename by using a loop.

Your example above can probably used to a great extent to solve this problem.

Thanks in advance.

Bendo

 

0
Bendo
12/31/2008 6:21:29 PM

Hi,Bendo

Bendo:

Maybe you can answer me another question that is related to this one.

 

I still have the table with all the tablenames and furthermore I have a table say "table1" with no content.

Now I want to copy the design of table1 and create/save so many new tables with the design of table1 as there are in the tablenames-table and saving them with the corresponding tablename by using a loop.

 

Ok Try This

Example At the Last thae table with that tablenames in other table will be created at the last

 Here table1BaseTable is the table whose structure u wan t to copy

CReate table table_Containing_Names_New

(id int identity,[tablenames] varchar(20))

GO

insert into table_Containing_Names_New

Select 'table1new'

union all

Select 'table2new'

union all

Select 'table3new'

union all

Select 'table4new'

GO

Create table table1BaseTable

(id int ,[name] varchar(20))

GO

declare @title varchar(80)

declare table_Names cursor for

Select [tablenames] from table_Containing_Names_New

open table_Names

fetch table_Names

into @title

while (@@fetch_status = 0)

begin

declare @sql as nvarchar(max)

Set @sql='Select * into '+@title+' FROM table1BaseTable '

--print @sql

exec sp_executesql @sql

fetch table_Names into @title

end

close table_Names

deallocate table_Names

Go

Select * from table1new

Select * from table2new

Select * from table3new

Select * from table4new

GO

  

 


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
12/31/2008 6:43:30 PM
Hello Raghav, 

nice - this works too.

Thanks again and I wish you a great New Year.

Bendo

 

0
Bendo
12/31/2008 6:54:22 PM
Reply:

Similar Artilces:

ADO.net problem: i can not view image data for the rest of my table rows except the image for the first row but i can read all the other non image fields for all rows.
Hi i have a table with the following fields imgid............int imgdata........image imgtitle.........varchar(max) imgType........varchar(max) imgLength.....bigint incident_id.....int i have no problem with uploading the image but viewing the uploaded image(s) in a gridview has caused me great pain. The probelm is i have two pages. page1 grabs the image to display and page2 has a gridview that displays the grabed image. I wanted to have an sql statement like SELECT * FROM mytable WHERE incident_id=someValue. IF i have 5 records in this table and i view page2 ...

Data Controls insert rows into gridview then insert rows from gridview into SQL Table
Hello there, I'm still new at performing miracles with code . Currently I am having trouble with my latest coding adventure I have a web application which records jobs and labour assigned to jobs. The issue in particular I'm having is the way in which labour is to be assigned. I want to use standard controls (Textbox, Dropdownlist) to record the values into a gridview (ie,put multiple rows into the gridview using the controls). Then, when all the inserting is complete, the user clicks a submit button to insert all the rows from the gridview into a database table. The reason for...

SQL Data displayed row by row
Hi,  I was wondering if it was possible to get some help, what I am trying to do is create a ASP webpage that will display Data from an sql database row by row and if there is no data in one row then it will display a message saying "No Data in row"  Any help would be appreciated  Thanks, Michael   mstoten: I was wondering if it was possible to get some help, what I am trying to do is create a ASP webpage that will display Data from an sql database row by row and if there is no data in one row then it will display a message saying "No Data i...

How to move a row in a data table object to the top of the table? The data table is in a data set. The data is bound to a drop down list box.
I am maintaining some 2003 C#/asp.net code for a web application. In the application a table in a data set is usedto populate a drop down list box.(The drop down list box will display a list of names) Before the data set table is bound to the list box control, blank characters are inserted at the topof the data set table. The purpose of this, is to display a blank line in the drop down list box, when the list box isinitially displayed. This code works fine. (See lines 1-14 below). Now I have been asked to modify the source code so that instead of a blank line being inserted at to top of th...

How do I display data from an sql query row by row?
I have the following code (at bottom of msg) in a page I'm trying to write. It works ok with a datagrid but I don't want a datagrid on the page as they look ugly (by default). Questions ... 1. Can I use a datagrid to display data with a custom layout? 2. How can I access data obtained from an sql server stored procedure row by row? I'm told you do it with a Dataset but I don't know how. The site is to look like this ... http://www.numbersix.co.nz/temp/intranet.jpg ... that's the existing site in ASP but must be converted to ASP.NET to keep up with things. Can anyone hel...

retrieving from row # to row #.
I have over 750 000 rows to retrieve from a table using a datawindow, but my system can only handle approx 30 000 rows and I would like to start from the end of the table. Is it possible to set the row numbers to retrieve? For example, start the retrieve from row 750 000 and stop at row 720 000. There must be a way. thanks, Vern what backend are you using? <Vern> wrote in message news:6BA241541AAD7F2F0063357385256C40.0063358685256C40@webforums... > I have over 750 000 rows to retrieve from a table using a datawindow, but > my system can only handle approx 30 ...

Is having a trigger that inserts a row in Table 'A', when a row in same table is inserted by ADo.Net code?
I want to insert a row for a Global user  in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a s...

Looping a row in a table: how in .NET?
Hello I am used to Classic ASP (sort of) and I'm wishing to re-write this in .NET 2.0 VB. I have a table where the rows and columns are repeated a certain number of times depending on the value from the database. In each cell, a value is displayed from the recordset in that loop. How would I accomplish this in ASP.NET 2.0? (VWD 2005 Express) I would really appreciate it if someone could at least point me in the right direction, Im unsure how I would repeat the rows and columns and display the value from the code which will be in the code-behind page... I've removed some lines of code ...

Retrieve data row by row and multiple SELECT statements.
hai to all experts out there,can somebody help me please! I would like to do this sql select statement in vb.net in order to display the output in a gridview. I have a table name TBL_DOC consist of ID,DOC_NO,PIC_FLAG,MPIC1,MPIC2,MPIC3,MPIC4 ,MPIC5 columns. My intention is first i want to retrieve PIC_FLAG column data from all rows in TBL_DOC table  row by row like this: SELECT PIC_FLAG FROM TBL_DOC and then if PIC_FLAG = 'MPIC1' i want to do another select statement like this in the same table: SELECT ID,DOC_NO,PIC_FLAG,MPIC1 FROM TBL_DOC where PIC_FLAG = 'MPIC1...

I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row.
 Hi, Let say I've 4 rows in a table. I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row. Thanks in advance Asghar Ali Mohammed http://www.aliwebdev.com(Web Designer and Developer)Do not forget to "MARK AS ANSWER" on the post that helped you. If you can use Linq, you can use Skip. http://msdn.microsoft.com/en-us/vcsharp/aa336757.aspxThanks, EdMicrosoft MVP - ASP/ASP.NET Hii dear..U can fire the Query select Top(3) from Table1 order by id desc.. "A conclusion is whe...

Data row from a Data Table
Hi All, I have a method which returns all the rows to a DataTable.  So from that Data Table I want a particular Row whose values should be assigned to text boxes on the webpage. Please can any help me out on how to do this ? Thanks for your time..   // assuming that you want the first row DataRow r=dataTable1.Rows[0]; TextBox1.Text=r["Column1"]; TextBox2.Text=r["Column2"];  Regards,Anas Ghanem.Note:Please Don't hesitate to click "Report Abuse" link if you noticed something wrong on the forums (like duplicate ,Off-topic,offensiv...

Adding Rows from different Data table to a single Data Table
Hi, I have 3 data tables say dt1, dt2, dt3. None of the tables have any relation with each other. Now i my requirement is to merge all these 3 datatables into 1.  But, i dont want it into different rows. Means to say, record from 1st, 2nd and 3rd table shud be merged as a single row in resultant datatable. Below example explains better Table 1 : 123 abc 890 Table 2 : rew 234 Table 3: sdfsd 234 dfg 45645 Now Resultant table shud be :  123 abc 890 rew 234 sdfsd 234 dfg 45645 Any help is appreciated :) Thanks in advance   If you keep your feet firmly on ...

Update a rows in one table for each row in other table
Hello all, i am a newbie in SQL and i want to ask for your help in order to do the following update statement. I have a table tblUsers which holds all the users, one of the fields in their GroupId. In the UserGroup table i have a field "UsersCount" and in that field i want to keep the number of the users in that group. Basically i want everytime i am saving a user to recalculate the users in all usergroups. I am trying to have something like:SELECT User_GroupId, count(User_GroupId) as UsersCount FROM tblUsersWHERE DeletedFlag = 0 GROUP BY User_GroupId and then:"FOR EACH" User_GroupIdUPDAT...

Update row in Table A from row in Table B
Hello, I have 2 tables.... table A and table B. Ok, In a stored procedure, I pass in an int value @ID I want to get the row from Table B WHERE ID =@ID....and then UPDATE the Row in Table A that has the same ID...with the values from table B. Table A ID | Col1 | Col2 ----------------------- 2 a a Table B ID | Col1 | Col2 ----------------------- 2 b b I would like to update A.Col1 with B.Col1 and A.Col2 with B.Col2 Can someone help me out with this syntax? Thanks a lot. update tableA ...

Web resources about - Loop to retrieve data from a table row after row - asp.net.sql-datasource

Facebook Developers Can Retrieve Users’ Profile Pictures In Different Sizes
Facebook introduced a way for developers to retrieve users’ profile pictures for use within their applications in different sizes, rather than ...

Winston retrieves the news
... food and losing weight. Three months ago, we were told he had lymphosarcoma of the GI tract. On March 10, 2008, Winston was called to go retrieve ...

Gizzard uses a rope to retrieve a submerged gift, 10 second retrieval from noticing the rope - YouTube ...
This is the second attempt, and the second occurrence he has ever seen this contraption. Without the rope, he would probably drown to retrieve ...

Dutch backpacker paid $5 an hour to retrieve golf balls from lakes
A Dutch backpacker claims he was paid as little as $5 an hour to wade through murky ponds and lakes retrieving thousands of lost golf balls. ...

People Are Willing To Go To Extreme Lengths To Retrieve Their Stolen Smartphones
People are willing to pay a ton of money and potentially put themselves in danger to retrieve their stolen smartphones, a new survey has found. ...

Police officer shot at 'very large' shark to retrieve body of swimmer
A policeman who shot a shark in a bid to retrieve the body of a swimmer it had killed says his first bullets disintegrated as they hit the water ...

Divers retrieve body from NSW floodwaters
A woman's body has been retrieved from a submerged car in a creek in Maitland.

Tourist plunges to death from Potts Point rooftop park trying to retrieve football: police
A French tourist who fell to his death from a rooftop park in Potts Point in inner Sydney was attempting to retrieve a football that had gone ...

The art of war: Quest to retrieve what Nazis stole
Susan Fisher Sullam tells of learning her father was a wartime treasure hunter, tracking down what the Nazis had plundered from Holocaust victims. ...

Investigators retrieve more human remains at MH17 crash site in eastern Ukraine but wreckage cannot yet ...
Dutch forensic experts recover further human remains at the crash site of downed flight MH17.

Resources last updated: 1/17/2016 11:36:03 PM