Create Tables Dynamically

Hi! I am using VS 2003 (v 1.1)

I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.

How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.

Give me the complete code with can example (if possible)

Thanks & Regrads

Jai Shankar

Here's anexample of how to create dynamic controls and add events. You can apply this to any type of control you need (in you case a Table).

Create table using data from another table

Hi,
How could I create a new table dynamically where columns names are data
from another table?
Example:
I have a table "Table1" with one column "T1Col"
The column contains following data:
"Row1"
"Row2"
"Row3"
Now I would like to "read" data from Table1 and create a new table
Table2 which will contain columns "Row1", "Row2" and
"Row3".
Any help will be appreciated.
Thank you in advance.Google for "transpose" or "cross-tab" or search this newsgroup.
ML
http://milambda.blogspot.com/sql

create subtotals dynamically

I am trying to dynamically create subtotals base on values in a column. I also have to group by year. I have gotten the group by year. I used the (previous function) to check for the year and col1 not being equal. I am getting the first subtotal back, don't know how to proceed in my function to return the remaining subtotals

i.e.

col1 col2 col3

aaa 111 2005

bbb 222 2006

aaa 333 2006

ccc 444 2005

ccc 555 2006

bbb 666 2006

bbb 777 2006

ddd 888 2007

2005

aaa subtotal: 1

ccc subtotal: 1

total: 2

2006

aaa subtotal: 1

bbb subtotal: 3

ccc subtotal: 1

total: 5

2007

ddd subtotal: 1

total: 1

grandtotal:

aaa 2

bbb 3

ccc 2

ddd 1

totalnumber 8

I found the solution.

Thanks

Create stored procs in new DB from within a stored proc?

In our application, we dynamically create new databases using a stored
procedure. Each new database must have a few required stored procedures
created in it. Since SQL Server does not allow the specification of a
different database context for creation of procedures or functions, the
current workaround is to define a system stored procedure in Master that
creates the stored procs. We call this in the context of the new DB after it
is created. This works fine, but an approach that does not require the use o
f
any system databases would be preferred.
Is there a better way to accomplish this without Master or Model (in
pseudocode):
create procedure usp_NewDB
@.DBName
as
begin
create database @.DBName
create procedure @.DBName.dbo.SP1 as ...
create procedure @.DBName.dbo.SP2 as ...
endYou cannot do this with a variable in that way...
http://www.sommarskog.se/dynamic_sql.html
"ScottL" <ScottL@.community.nospam> wrote in message
news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
> it
> is created. This works fine, but an approach that does not require the use
> of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Yes, I know. That's why I said it was pseudocode. The issue is not one of
dynamic SQL, it is of creating a stored procedure in a different database
context. Regardless of dynamic SQL, the syntax CREATE PROCEDURE
<DBName>.dbo.<SPName> is not valid, since you cannot specify the database
name with CREATE PROCEDURE. Let me rephrase it for you more simply:
How can I create a stored procedure in Database_B from a stored procedure
running in Database_A?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You cannot do this with a variable in that way...
> http://www.sommarskog.se/dynamic_sql.html
>
> "ScottL" <ScottL@.community.nospam> wrote in message
> news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
>
>|||> Let me rephrase it for you more simply:
> How can I create a stored procedure in Database_B from a stored procedure
> running in Database_A?
Let me answer for you "more simply":
EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');|||Aaron Bertrand [SQL Server MVP] wrote:
> Let me answer for you "more simply":
>
Aaron, you're not getting annoyed are you :)
Remember people should EXPECT to get their questions answered promptly
and in a way they feel is appropriate, we have to make an effort
keeping them happy ;)
/impslayer, aka Birger Johansson|||Strange response, but thanks anyway. This syntax will not work and actually
executing it would result in:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Let me answer for you "more simply":
> EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');
>
>|||> Aaron, you're not getting annoyed are you :)
The implication I got was, here idiot, since the original question was too
complex for you, let me dumb it down.|||Yes, if you can dynamic sql.
Context switch not change in stored procedure.
You can refer below my example
-- S2K SP3
DECLARE @.I_DB_NAME NVARCHAR(200)
SET @.I_DB_NAME='Demo'
DECLARE @.proc NVARCHAR(4000)
SELECT @.proc =QUOTENAME(@.I_DB_NAME) + '.dbo.sp_execresultset'
EXEC @.proc 'CREATE VIEW t2 AS SELECT GETDATE() D'
"ScottL"?? ??? ??:

> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
it
> is created. This works fine, but an approach that does not require the use
of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Here's a way to bypass the parser.
DECLARE @.sql VARCHAR(255);
SET @.sql = 'USE tempdb; EXEC(''CREATE PROCEDURE dbo.foo AS SELECT bar =
1'');';
EXEC(@.sql);
GO
EXEC tempdb.dbo.foo;
GO
USE tempdb;
GO
DROP PROCEDURE dbo.foo;
GO|||Aaron Bertrand [SQL Server MVP] skrev:

> The implication I got was, here idiot, since the original question was too
> complex for you, let me dumb it down.
Yeah, I interpreted it the same was as you, and my reply was intended
to support you, in a somewhat humorously way. Not sure I succeeded
though :)
/impslayer, aka Birger Johanssonsql

Create Report Parameters Dynamically

Hi there,
I was wandering if it is possible to create report parameters on the
fly.
for example. table returns one moment 3 values, other moment 6 values.
depending how many values i would like to create report parameters...
Could anyone help?
Greetz,
Hippo from [NL]
--
remove xxx from mailIf you want to have a parameter that is a combo box filled from a query
that is not problem. If you want to vary the number of parameters then that
is fixed. In layout tab, click on a part of the report (empty part), go to
the report menu, parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"hippo" <nijlpaard.xxx@.gmail.com> wrote in message
news:00ma515285q2fmmgouguksq2t4l538mlhh@.4ax.com...
> Hi there,
> I was wandering if it is possible to create report parameters on the
> fly.
> for example. table returns one moment 3 values, other moment 6 values.
> depending how many values i would like to create report parameters...
> Could anyone help?
>
> Greetz,
> Hippo from [NL]
> --
> remove xxx from mail|||Hello, i got the same problem, only the following:
Depending on returned values i would like to create a combobox.
For example, my dataset or stored procedure returned [x] names, and each
name has 5 possible values. Depending on [x] i would like to create
dynamically parameters. Is this possible?
"Bruce L-C [MVP]" wrote:
> If you want to have a parameter that is a combo box filled from a query
> that is not problem. If you want to vary the number of parameters then that
> is fixed. In layout tab, click on a part of the report (empty part), go to
> the report menu, parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "hippo" <nijlpaard.xxx@.gmail.com> wrote in message
> news:00ma515285q2fmmgouguksq2t4l538mlhh@.4ax.com...
> > Hi there,
> >
> > I was wandering if it is possible to create report parameters on the
> > fly.
> >
> > for example. table returns one moment 3 values, other moment 6 values.
> > depending how many values i would like to create report parameters...
> >
> > Could anyone help?
> >
> >
> > Greetz,
> > Hippo from [NL]
> > --
> > remove xxx from mail
>
>|||Search Books On-Line on the phrase cascading parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RamR0m" <RamR0m@.discussions.microsoft.com> wrote in message
news:D54159D9-4F2D-4BE9-8873-862332EABBCD@.microsoft.com...
> Hello, i got the same problem, only the following:
> Depending on returned values i would like to create a combobox.
> For example, my dataset or stored procedure returned [x] names, and each
> name has 5 possible values. Depending on [x] i would like to create
> dynamically parameters. Is this possible?
> "Bruce L-C [MVP]" wrote:
> > If you want to have a parameter that is a combo box filled from a query
> > that is not problem. If you want to vary the number of parameters then
that
> > is fixed. In layout tab, click on a part of the report (empty part), go
to
> > the report menu, parameters.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "hippo" <nijlpaard.xxx@.gmail.com> wrote in message
> > news:00ma515285q2fmmgouguksq2t4l538mlhh@.4ax.com...
> > > Hi there,
> > >
> > > I was wandering if it is possible to create report parameters on the
> > > fly.
> > >
> > > for example. table returns one moment 3 values, other moment 6 values.
> > > depending how many values i would like to create report parameters...
> > >
> > > Could anyone help?
> > >
> > >
> > > Greetz,
> > > Hippo from [NL]
> > > --
> > > remove xxx from mail
> >
> >
> >|||Thank you Bruce for you reply, but this isn't what i wish.
What i understand of Cascading Parameters is that you have to predefine your
parameters before execution.
What i would like is to have is, depending on values returned by the
dataset, vary the amount of visual parameters.
Greetz, RamR0m
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
"Bruce L-C [MVP]" wrote:
> Search Books On-Line on the phrase cascading parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RamR0m" <RamR0m@.discussions.microsoft.com> wrote in message
> news:D54159D9-4F2D-4BE9-8873-862332EABBCD@.microsoft.com...
> > Hello, i got the same problem, only the following:
> >
> > Depending on returned values i would like to create a combobox.
> > For example, my dataset or stored procedure returned [x] names, and each
> > name has 5 possible values. Depending on [x] i would like to create
> > dynamically parameters. Is this possible?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > If you want to have a parameter that is a combo box filled from a query
> > > that is not problem. If you want to vary the number of parameters then
> that
> > > is fixed. In layout tab, click on a part of the report (empty part), go
> to
> > > the report menu, parameters.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "hippo" <nijlpaard.xxx@.gmail.com> wrote in message
> > > news:00ma515285q2fmmgouguksq2t4l538mlhh@.4ax.com...
> > > > Hi there,
> > > >
> > > > I was wandering if it is possible to create report parameters on the
> > > > fly.
> > > >
> > > > for example. table returns one moment 3 values, other moment 6 values.
> > > > depending how many values i would like to create report parameters...
> > > >
> > > > Could anyone help?
> > > >
> > > >
> > > > Greetz,
> > > > Hippo from [NL]
> > > > --
> > > > remove xxx from mail
> > >
> > >
> > >
>
>|||>From everything I've seen, the parameters are hard coded into the RDL,
and there is no way to dynamically add or remove parameters at report
run time. You may want to see if someone from Microsoft knows if this
is a wishlist item for future versions.
If you are letting Reporting Services prompt the user for parameters,
the best thing I can think of that you can do is define the maximum
number of parameters, then use cascading parameters in a way that the
unnecessary parameters only have a single choice called "Not
Applicable" or "Skip Me". If you have an application that is calling
the report, you could prompt the user only for the parameters that you
really care about, and then behind the scenes fill in the remaining
parameters with dummy values that aren't actually needed to generate
your report data.
Ted

Create report items dynamically - how?

Hi, All,
I have a report with a parameter (a combo box with two names), that only
show the CompanyName on the report. When I choose Name No1, I would like to
show 4 textboxes, and choosing the name No2, I would like to show another
one, and hide the pevious four.
I'm going to make these textboxes dynamically.
Can I do it? Or is there another solution?
Thanks,
SzabtiYou can write an Expression in the "Hidden" part of the "Visibility" property
of the fields involved. Let this expression evaluate your parameter and have
it return a boolean value. That's all!
D.P.
"szabti" wrote:
> Hi, All,
> I have a report with a parameter (a combo box with two names), that only
> show the CompanyName on the report. When I choose Name No1, I would like to
> show 4 textboxes, and choosing the name No2, I would like to show another
> one, and hide the pevious four.
> I'm going to make these textboxes dynamically.
> Can I do it? Or is there another solution?
> Thanks,
> Szabti
>
>|||Yes, I've done it at first. But here was a great problem with rendering:
after hiding the first 4 elements and showing the only one, remaining part
of my report (these are textboxes with summary data) was fall apart: some
textboxes moved to top of another and the value became unreadable.
So it is why I've thought to draw these elements dynamically...
szabti
"D.P." <DP@.discussions.microsoft.com> az alábbiakat írta a következõ
hírüzenetben: 896D6245-F152-49CD-9B38-7AFDF961C7C5@.microsoft.com...
> You can write an Expression in the "Hidden" part of the "Visibility"
> property
> of the fields involved. Let this expression evaluate your parameter and
> have
> it return a boolean value. That's all!
> D.P.
> "szabti" wrote:
>> Hi, All,
>> I have a report with a parameter (a combo box with two names), that only
>> show the CompanyName on the report. When I choose Name No1, I would like
>> to
>> show 4 textboxes, and choosing the name No2, I would like to show another
>> one, and hide the pevious four.
>> I'm going to make these textboxes dynamically.
>> Can I do it? Or is there another solution?
>> Thanks,
>> Szabti
>>
>>

Create Destination Table Dynamically in a program

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan

Hi Pavan,

If you have the schema and the name, can't you execute a CREATE TABLE T-Sql statement?

Andy

|||

Pavan Kurimilla wrote:

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan

The most I've done is supply a name dynamically and keep the mappings constant. I dont think you can do mappings dynamically.

|||

Pavan Kurimilla wrote:

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan

You can usually do this with a "select ... into destination_table from source_table..." statement. That SQL statement will create the table using the results of the select statement.

http://msdn2.microsoft.com/en-us/library/ms189499.aspx

Create Cursor with Dynamic SQL

I am attempting to create a store procedure that contains a cursor. I would like to Dynamically create the cursor and I am having difficulty.

Code:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

CURSOR cur_gl_bucket
IS
SELECT <column name >
FROM ledger_stat
WHERE <statement>

BEGIN

Select <column name> from table;

OPEN Cursor cur_gl_bucket with retrieved column name.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

The <column name> is selected from a table based on the parameters passed into the procedure. I would then like to OPEN the cursor with the retrieved column name dynamically imbedded in the Select statment for the cursor.

I successfully use DBMS_SQL.PARSE and EXECUTE later in the procedure but have been unable to get it to work when I open the cursor. I guess the question is can this be done, and if so what is the syntax for doing this.....

Regards
Patrick McCarthy
mailto: patrick_mccarthy@.canmail.comLooks like you are on Oracle?

create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

TYPE refcur IS REF CURSOR;
cur_gl_bucket refcur;

v_column_name VARCHAR2(30);

BEGIN

Select <column name> INTO v_column_name from table;

OPEN cur_gl_bucket FOR 'SELECT '||v_column_name||' FROM ledger_stat WHERE <statement>';