Counting & printing to screen from 1-10000 with a C Loop in ASP.NET...

MrDarcy

Hi Folks, well I can do this manually and I know I'll end up batty by the end the time I'm finished or I can try knocking together some sort of a conditional statement to do it!

So here's my task... I need to build some SQL script that creates 10,000 entries in my MS SQL DB.

Basically the output (which I'm trying to generate on a webpage as HTML text and then copy and text the whole lot into my SQL script), has to look something like this...

Insert [User].[MyDB] ([MyProductSpecListID], [MyProductListID]) Values (1,1)

Insert [User].[MyDB] ([MyProductSpecListID], [MyProductListID]) Values (1,2)

Insert [User].[MyDB] ([MyProductSpecListID], [MyProductListID]) Values (1,3)


Insert [User].[MyDB] ([MyProductSpecListID], [MyProductListID]) Values (2,4)

Insert [User].[MyDB] ([MyProductSpecListID], [MyProductListID]) Values (2,5)

Insert [User].[MyDB] ([MyProductSpecListID], [MyProductListID]) Values (2,6)


So I'll need two loops, one to handle the variable that I'll use to generate the standard index number on the right (1,2,3,4,5,6......) from 1-30,000, and another one to increment the left value by one after 3 lines have been generated (111,222,333,444,555...), all the way up until the value on the right hits 10,000...

Any suggestions???

Also using ASP.NET it doesnt seem doable to be able to generate labels dynamically like this, or maybe some could tell me if it can be done???

Thanks again for any help with this...

Evil Phil

I would suggest outputting this to a file instead of a webpage but I won't. You better off writing this in T-SQL. You can implement your INSERT statements inside a loop and executing that directly in sql server.

MrDarcy

Evil Phil wrote: »

I would suggest outputting this to a file instead of a webpage but I won't. You better off writing this in T-SQL. You can implement your INSERT statements inside a loop and executing that directly in sql server.

Thanks for that Phil, I haven't used T-SQL before and can't really get into picking up some new language at the mo. Is it possible to create a few file and output the list to that using C#/ASP.NET???

NeverSayDie

What EvilPhil said. What you're trying to do here has nothing to do with ASP.NET or web tech in general as far as I can see, so forget about that.

Specifically re loops in T-SQL, see here;
http://www.databasejournal.com/features/mssql/article.php/3100621/T-SQL-Programming-Part-2---Building-a-T-SQL-Loop.htm

Re dynamic labels in ASP.NET, for future reference, yeah, you can do that. Set the Text property of a Label control, for instance, or dynamically add controls of that sort to the page if you want, etc etc.

NeverSayDie

MrDarcy wrote: »

Thanks for that Phil, I haven't used T-SQL before and can't really get into picking up some new language at the mo. Is it possible to create a few file and output the list to that using C#/ASP.NET???

T-SQL is just normal SQL with some extra stuff for SQL Server specific features.

Re files, yeah, it's pretty straightforward to write stuff out to text files in C# if you want - Google will turn up lots of tutorials on that. Having them downloadable through ASP.NET, if that's what you're trying to achieve, shouldn't be too much harder - again, Google will turn up the info.

MrDarcy

I can't use T-SQL to do this because I need to be able to see and edit each line outside of my needs above, (when I've achieved the above I still have to add in a different specific text description for every line).

MrDarcy

NeverSayDie wrote: »

What EvilPhil said. What you're trying to do here has nothing to do with ASP.NET or web tech in general as far as I can see, so forget about that.

Specifically re loops in T-SQL, see here;
http://www.databasejournal.com/features/mssql/article.php/3100621/T-SQL-Programming-Part-2---Building-a-T-SQL-Loop.htm

Re dynamic labels in ASP.NET, for future reference, yeah, you can do that. Set the Text property of a Label control, for instance, or dynamically add controls of that sort to the page if you want, etc etc.

I "think" I might be able to knock a result out of this tutorial, thanks for that! I'm obviously not the world's best programmer, thanks for the advice folks...

NeverSayDie

MrDarcy wrote: »

I can't use T-SQL to do this because I need to be able to see and edit each line outside of my needs above, (when I've achieved the above I still have to add in a different specific text description for every line).

For 10,000 entries? Jaysus. In that case, personally I'd store the data in a text file in CSV form, load that up in a C# app and insert it into the DB a line at a time. The incrementing numbers stuff could be done in Excel pretty easily, and then you can edit the exported CSV file manually to add your descriptions or other columns. That approach has the advantage that you can easily adapt it to run again and handle updates/duplicates, validate the data on the way in, and so on.

(That's assuming your descriptions or other data are all specific to the IDs - if this is just test data or something, you'll be a lot quicker writing a random description generator than you will entering that many by hand.)

amen

if you can't learn T-SQL should you be using a database at all ?
how are you going to do anything with the data if you don't know sql ?

MrDarcy

amen wrote: »

if you can't learn T-SQL should you be using a database at all ?
how are you going to do anything with the data if you don't know sql ?

I can learn it no problem but I'm under a particular time constraint here pardon da pun!

Thanks so much to everyone on thread who helped me with this today, got it fully resolved....

MrDarcy

Hi Folks,

While I have my solution sorted for this, I've come across something here in my (T-SQL), solution that is absolutely bugging the sh*t out of me!

Using the code that was available from the link that NeverSayDie posted up, I modified it a bit to build me the SQL code that I needed...


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

USE [**Removed**]
GO
drop table PART
GO
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(100)
declare @MyIndex int
create table PART ( MyIndex int IDENTITY(1,1), Part_Id int, Category_Id int, Description varchar(100))
set @Part_Id = 0
set @MyIndex = 0

set @Category_Id = 0

while @Part_Id < 10000

begin
set @Part_Id = @Part_Id + 1

while @Category_Id < 3

begin
set @MyIndex = @MyIndex + 1
set @Category_Id = @Category_Id + 1
set @Desc = 'INSERT [MySiteUser].[ProductTypeASpecProduct] ([ProductTypeASpecID], [ProductTypeAProductID]) VALUES ('+cast(@Part_Id as varchar)+','+cast(@MyIndex as varchar)+')'
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
select * from PART


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

This working as I expect it to, which will build my code, is entirely dependent upon the number of iterations I use. If I use 5000 I get success, if I use 10000 or some values over 5000 but lower than 10000, the @MyIndex variable completely f*cks up at record number 619 or some other number and jumps to a value of 22685 (or some other value), then on it goes but it's all out of kilter so every subsequent record is no use???

I do get the correct number of iterations, for example if I use 10000, I get 30,000 records as I'd expect and if I use 5000 I get 15000 records, etc???

MrDarcy

Just wondering can anyone see the bother with this??? It's wrecking my head!

ejvilla

Hi MrDarcy,

I'm no T-SQL expert but I don't think you should be using a field/column flagged as IDENTITY for tracking insertion order. I would imagine it's used to specify a primary key. A better option might be to manually increment a field, though this leaves you open to problems if you want to remove rows later on. Tracking insertion by date would be better.

I can't say why the values jumped, it could be a bug in the implementation of IDENTITY or something. Have a look at the documentation regarding IDENTITY here:

http://msdn.microsoft.com/en-us/library/ms186775.aspx

Hope it helps!

MrDarcy

ejvilla wrote: »

Hi MrDarcy,

I'm no T-SQL expert but I don't think you should be using a field/column flagged as IDENTITY for tracking insertion order. I would imagine it's used to specify a primary key. A better option might be to manually increment a field, though this leaves you open to problems if you want to remove rows later on. Tracking insertion by date would be better.

I can't say why the values jumped, it could be a bug in the implementation of IDENTITY or something. Have a look at the documentation regarding IDENTITY here:

http://msdn.microsoft.com/en-us/library/ms186775.aspx

Hope it helps!

That seems to have resolved the issue ejvilla, you're a legend!

amen

I ran this script myself and it works as expected.

Basically you have two loops, the Part_ID and the Category_ID.

For each Part_ID you insert three Category_Id.

So if Part_ID is 1 you get three rows, if its 100 you get 300 hundred rows etc.

To check you can use the following
[PHP]select * from PART where Category_Id = 3
and Part_Id * Category_Id != MyIndex
order by myindex[/PHP]

which returns all the rows where my_Index doesn't match the part_ID/category_id value.

I ran for 100,000 iterations and it works.

You should always have an order by clause on your select statement.
Without it there is no guarantee that if you run the same select statement multiple times you will get the data in the same order.

ejvilla

amen wrote: »

You should always have an order by clause on your select statement.
Without it there is no guarantee that if you run the same select statement multiple times you will get the data in the same order.

^^ That's important! I made the assumption that you had been doing that MrDarcy.