.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Need help with dynamic table generation

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :Sql Server
I am building a test script to help myself figure out dynamic SQL.  My goal is to generate column names from an existing table query.  To work in this direction I made the following test: declare @sql varchar(1000) declare @int int set @int = 1 set @sql = 'declare @test table (' set @sql = @sql + 'testfield varchar(10),' while @int < 5 begin  if @int < 4   Begin    set @sql = @sql + ' field' + CAST(@int as varchar(10)) + ' varchar(10),'   end  else if @int = 4   begin    set @sql = @sql + ' field' + CAST(@int as varchar(10)) + ' varchar(10))'   end  set @int = @int + 1  end select (@sql) exec (@sql) insert into @test (testfield, field1, field2, field3, field4) values (1,2,3,4,5) When I run this query I get the following from the select (@sql) query: declare @test table (testfield varchar(10), field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10)) This looks good.  When I run the exec statement, this returns 1 Row affected, so, that seems to work.  However, when I run the insert I get this error: Server: Msg 137, Level 15, State 2, Line 23 Must declare the variable '@test'. I know that GO statements will effectively create batch dividers between statement groups, but, I haven't yet learned

View Complete Post

More Related Resource Links

SSRS 2005 Dynamic Table Generation



I am attempting to build a report that allows the user to select the value of 4 different parameters. The final parameter allows the user to select multiple values. My question is as follows. Based on the users selections, is there a way to generate the required number of tables dynamically?

For instance, if the input for the final parameter is A, B, & C can the appriopriate number of tables be generated for the same dataset (Sales) without having to build a table for SalesA, SalesB, and SalesC? Or do I have to build the maximum number of tables and use an expression to determine visibility based on the parameters. Note: max number of possible tables is more than 100.





create and insert into a dynamic sql table at runtime

Below I have a bunch of SQL statements that creates a table at runtime and the "pic" column is a dynamic column and the code creates the number of "pic" columns depending on the count of the uploader control.  the problem comes when I try to insert into that table and I am trying to say for every "pic" declare variable @pic.... it works well for just one file in the uploader but for multiple files i get the following error The name "pic1" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Directory.CreateDirectory(MapPath(".") & "\port\" & clientname.Text & "\")        Dim objConn As New SqlConnection("Data Source=xxxxxxxxx.hostedresource.com; Initial Catalog=mrpoteat; User ID=xxxxxxx; Password=xxxxxxxxx;")        objConn.Open()        Dim strCommandText As String = ""        For index = 1 To Attachments1.Items.Count Step 1            If String.IsNullOrEmpty(strCommandText) Then                strCommandTex

Custom component Table name dynamic table name

Hi All How to i call the table dynamiclly in to C# class library. I can able to call the columns names.. but i unable to call the table name.. please suggest it Example:   IDTSOutputColumn100 column = output.OutputColumnCollection[x]; columnInfos[x] = new ColumnInfo(); columnInfos[x].columnName = column.Name; columnInfos[x].Column_reference = column.CustomPropertyCollection[ "Column_reference"].Value.ToString(); columnInfos[x].bufferColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID); columnInfos[x].lineageID = column.LineageID;   geting the columns names .. how to i get table name...?? Regards Ram

insert into dynamic SQL Table at runtim

the follow ing is supposed to create a table and insert the columns into the table at runtime.  It also has picture column and creates the amaount of picture columns based on the amount of files in the multiple file uploaer.. it works fine with just one file in the uploader.  The problem comes when there  are two or more files in the uploader and intead of adding the second image right after the first image is added, it instead adds the first image then goes back and trys to create the table again, and i get an error saying the table already exsits.I want it to add the second image right after the first image, not go back and create the table then add the second imageDirectory.CreateDirectory(MapPath(".") & "\port\" & clientname.Text & "\")              Dim objConn As New SqlConnection("Data Source=xxx")        objConn.Open()        Dim strCommandText As String = ""        For index = 1 To Attachments1.Items.Count Step 1            If String.IsNullOrEmpty(strCommandText) Then                strCommandText = "

Dynamic rows(fields) generation in crystal reports

Hi.....frnds,     I am working on Crystal reports.My requirement is that i need to  generate dynamic fields according to the count of Family_Members column in sql server database,for example if i have '4' in 'Family_Members' column i need to generate 4(Family members details)dynamically.

Using Sub reports and dynamic crystal report generation

Hi..........friends,         I am working on crystal reports to generate Ration card details.My form  consists of the fields like Address details of the ration card owner,existing ration card details and family members details.in these Family Details i have to generate dynamically according to the count of the family members.I am new to crystal reports.I am confusingabout whether i have to use sub reports or formula fields.................               Plz help me                                                                    Thanks in advance      

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Get value of dynamic textbox in dynamic table with masterpages

Public Sub createtable() Dim b as Integer = 1 For i As Integer = 1 To b Dim tbl As New Table() Dim tr0 As New TableRow() Dim tr1 As New TableRow() Dim tr2 As New TableRow() Dim trh As New TableRow() Dim th As New TableCell() Dim td As New TableCell() Dim td0 As New TableCell() Dim td1 As New TableCell() Dim td2 As New TableCell() Dim td3 As New TableCell() Dim td4 As New TableCell() Dim td5 As New TableCell() Dim td6 As New TableCell() Dim td7 As New TableCell() Dim td8 As New TableCell() Dim td9 As New TableCell() Dim td10 As New TableCell() Dim td11 As New TableCell() Dim title As New DropDownList() Dim fname As New TextBox() Dim mname As New TextBox() Dim lname As New TextBox() Dim address As New TextBox() title.ID = "txtTitle" title.Items.Add("Mr.") title.Items.Add("Ms.") title.Items.Add("Mrs.") fname.ID = "txtfname" mname.ID = "txtmname" lname.ID = "txtlname" address.ID = "txtaddress" th.

Dynamic SiteMap generation with MVC

Hi guys,Is there a way to create a dynamic site map with MVC? Thanks everyone

dynamic table name linq-to-sql query


I am writing a function in which i generate autocode in specific format i want to make this function generalize so that i send tablename and columnname  parameter and function return the next code. I am using linq to sql with MVC e.g. i want to make table name and column name dyanamic

from m in db.<tblnameparam> 
                       orderby m.<colnameparam> descending
                       select m.<colnameparam>

can dynamic sql be used against a table variable?


This Works

create table #tmp1(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into #tmp1(rowID) SELECT ' + @rowID)

This does not work

Declare @tmp1 table(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into @tmp1(rowID) SELECT ' + @rowID)

Error Msg:
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@tmp1'.

Is there a way to use Dynamic Sql against a Table var -- @tmp1?
How to do this?

The goal is to be able to pass in a string param like this to a proc

s1 = 'aa'',''bb'',''cc'

declare @s1 varchar(20)

Begin As

declare @tmp1(...)
exec('Insert Into ' + @tmp1 + ' (...) Where fldx In (''' + @s1 + ''')')


Add very dynamic controls to a asp:table


 Hi all. I want to create a TableRow with a TextBox. I want when click a Button a TableRow add to asp:table. in Button_Click event code I can add a row by a TableRow & TableCell & TextBox Objects and with help a HiddenField.
My problem is that: after add first row that has a textbox, next row doesn't add by click button. I think Controls delete after post back in server and first textbox delete and new textbox add same position. what is solution after every click, a row with textbox add to the table?

            TableRow tr = new TableRow();
            TableCell tc1 = new TableCell();
            TableCell tc2 = new TableCell();
            TableCell tc3 = new TableCell();
            TextBox txt1 = new TextBox();
            TextBox txt2 = new TextBox();
            DropDownList drp = new DropDownList(); drp.Width = Unit.Pixel(100);


Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. Write-Back table generation and Measure Expressions


    Hi Everyone,

    It would be interesting to see if anyone can tell me a couple of things about how SSAS works. If not, at least it would be very nice if anyone could hook me up with a SSAS product team member, who can shed some light…

    1. Write-Back Tables

    How does SSAS create a write-back table for a partition? More specifically, if you have a role-playing dimension (I’ll use an example with a Date), you will get two Date keys with _ suffixes. How do these suffixes get generated? Is there any logic which SSAS applies consistently? This would be very helpful when moving data from the write-table to the fact table.

    2. Measure Expressions

    There are two conflicting blog posts by Christian Wade and Teo Lachev about how Measure Expressions work. According to Teo Lachev, Measure Expressions get calculated at runtime, while Christian Wade writes that the calculation results are stored on disk (assuming during processing). It would be good to confirm this as it may matter in some specific cases (Currency Conversions with heaps of data).


    Boyan Penev --- http://www.bp-msbi.com

controls in dynamic html table



 I have a dynamic htmltable, in which I can add rows at runtime.


using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Web.Mail;
using System.Web.UI.WebControls.WebParts;
using System.Web.Security;
using System.Data.OleDb;
using System.IO;
using System.Text;

public partial class HR : System.Web.UI.Page

    //   private int numOfColumns = 4;

    private int ctr = 0;

    HtmlTable HtmlTable = null;
    protected void Page_Load(object sender, EventArgs e)

        if (!IsPostBack)

            HtmlTable = new HtmlTable();
            HtmlTable.ID = "tableBuild";

Dynamic table based on current viewers



Using MS Visual Web Developer 2010 Express, I'm trying to create a simple site where I can view how many people are currently viewing the page. So far I've added a Web Form called 'home.aspx' this will be the only page and it will contain the gridview that displays how many people are viewing (using the sql COUNT function). There's also the default web.config file which I havn't modified yet.

I've added a sqlexpress database called 'Users' and created a Table called 'viewers'. 'viewers' has collumns IP (varchar(50)) and Time (datetime).  The goal is for the table to grow (INSERT users) each time a viewer views the page, and get smaller (DELETE the user) when each person leaves.

I'm kinda stuck now:

1) I dont know what I have to put in my web.config file to create my connection string to my database (all the tutorials are written for people who know all the system.web mumbo jumo).

2) What do I write in my VB code behind file to INSERT users (I'm guessing I just need their IP and Time?) when they come to the site.

3) What do I write in my VB code behind to DELETE users if they leave. (I think this is the harder part. I dont know how to tell the database to constantly be checking to see if some IP address is still viewing, and then delete t

Dynamic Data. Multiple Custom Pages For The Same Table



I've just started out my first project using Dynamic Data and I've been running into a series of problems. Dynamic Data doesn't seem to be swimming in documentation. Anyway, my setup is:

  • Visual Studio 2010
  •  .net 4.0
  • Dynamic Data Domain Service Web Application Project Template (I have the RIA Services for ASP .net installed)
  • using an Entity Data Model
  • using a DomainService class
  • ASP .net Membership

There are a couple of things I am trying to achieve:

  1. I have my scaffolding turned on for all the tables. I am using the RequiresRole attribute in order to restric access to some tables. I have understood how you can create a Custom Page Template for a certain table, no problems there. I would like to be able to use the default PageTemplates for lets say the Administrator role and the Custom Page Templates for another role; the idea would be that the Administrator would be able to edit anything in the DB, but the restricted user would receive a Custom Page with only a few actions available. Long story short: is there a way to access both the standard and custom templates?
  2. Related to question number 1: Can I have multiple Custom Page Templates for the same table? I saw someone asking
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend