how to create a new SQL Server table from datatable created dynamically from .net page

lets assume you're interactively letting the users create table on the fly.
your code would have to be something as  follows
dim dc as DataColumn
dim st as string=""

for each dc in MyDataTable1.columns
 st=st+dc.ColumnName + " "+dc.DataType + ","
next


st="Create table " + MyDataTable1.tableName + "(" + st + ")"

'--------- the general code for executing a sql statement .. below

Dim mySC As SqlCommand
mySC = New SqlCommand(MY_SQL_STATEMENT, myDataBase)
Dim mySDA As SqlDataAdapter = New SqlDataAdapter(mySC)
Dim myDS As DataSet = New DataSet()
mySDA.Fill(myDS)

ok, i'm sorry but i'm having difficulty understanding. my table will always have 4 columns, it is just that the rows will be different.

so i have .NET DataTableC with 4 columns, my SQL statement would be......

strSQL = "Create Table MyTable DataColumn.Col1 DataColumn.SystemString DataColumn.Col2 DataColumn.SystemString... etc

is this right?

would someone mind spelling this out like i was a 6 year old?

i have .NET DataTableC that has 4 columns that i need to add to a SQL Server table. I can create the table first in SQL if that will be easier, then just do a delete all from the table each time i "upload" DataTableC

do you already have the tables setup or are u creating the tables during runtime?

if easier, i can create a blank table first in my SQL server database since i know how many columns there will be.

By the way, what exacly are u trying to achieve? (I'm assuming you've two tables as your inputs) and you're trying to comeup with the 3rd table using the previous two tables.
What are the exact field names of the first two tables. What is the logic behind the calculation and what are you trying to output to table # 3. I'll give you the required code if you can provide me the requested info.

thanks

malhar

I think I can see what is going on here.

I think it would satisfy your requirements if you had a blank table (that you had prepared beforehand) with 4 columns and I helped you by showing you how to INSERT new rows into that table from the DataTable object that you have built in your sample code.

This is not called making a new table.  This is called inserting rows.  So all the advice regarding the CREATE TABLE command doesn't apply.  Instead, you need to add an Insert command to your data adapter and then call the update method on the dataset.  I can give you more details about this if you need them...

However, I suspect that adding rows to a third table is not really a good idea.  The data you would store there can always be generated from the first two tables at will.  This can be done on the fly whenever the third table would have been used.  I think you should try to learn more about a SQL clause called "INNER JOIN".  If you look at that it may provide you with much better way of doing this.

Ads B

adsB,

thanks for the feedback, but if you will see my code on how the third table was created -posted above, you will see that the third table cannot be created from an inner join.

trevoray,

I have read your code as requested and I agree that a simple INNER JOIN won't do it.

However, the rest of my previous answer does apply to you.  Here it is again...

> I think it would satisfy your requirements if you had a blank table (that you had prepared beforehand)
> with 4 columns and I helped you by showing you how to INSERT new rows into that table from the DataTable
> object that you have built in your sample code.

> This is not called making a new table.  This is called inserting rows.  So all the advice regarding the CREATE TABLE
> command doesn't apply.  Instead, you need to add an Insert command to your data adapter and then call the
> update method on the dataset.  I can give you more details about this if you need them...

Ads B

AdsB,

Thanks for your input on this and another question. I realize what you are saying and plan to take this TableC, add it to my database (with the table already defined) and then just do queries off of it instead of making new tables like i was wanting to do in my other question. With all that, i would really appreciate some help in structuring the INSERT command that i would use under the SqlAdapter commands. I understand that the SqlAdapter has various commands you can attribute, that will be called upon when you do the SqlAdapter1.Update(MyDataSet,MyDataTable) function.

My insert command will look like this:

INSERT INTO mbrcomm_assign (appno,R1,R2,R3) VALUES (@appno,@R1,@R2,@R3)

my question is how do i add the parameters? since i don't know how many new rows there will be i am guessing i will have to do something like

Dim dr ans DataRow
For Each dr in DataTableC.Rows
SqlCommand1.Parameters("appno").Value = DataTableC.Columns("applicant").Value
SqlCommand1.Parameters("R1").Value = DataTableC.Columns("R1").Value
SqlCommand1.Parameters("R2").Value = DataTableC.Columns("R2").Value
SqlCommand1.Parameters("R3").Value = DataTableC.Columns("R3").Value
Next

Can you show me the proper way to do what i am trying to do?

My complete code for the page the successfully generates DataTableC is below.....

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

Public Class mbr_committee
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.NewMembers1 = New mbr_app.NewMembers
        Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
        CType(Me.NewMembers1, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = CType(configurationAppSettings.GetValue("DBConnect", GetType(System.String)), String)
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.InsertCommand = Me.SqlCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT autonumber, first_name FROM newmemberperson WHERE complete = 'yes' AND rev" & _
        "iewmonth = 'september' ORDER BY mtype; SELECT membership_committee.memid, tblsot" & _
        "person.nm_first, tblsotperson.nm_last FROM membership_committee, tblsotperson WH" & _
        "ERE membership_committee.nmmst_id = tblsotperson.nmmst_id ORDER BY tblsotperson." & _
        "nm_last"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'NewMembers1
        '
        Me.NewMembers1.DataSetName = "NewMembers"
        Me.NewMembers1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'SqlCommand1
        '
        Me.SqlCommand1.CommandText = "INSERT INTO Mbrcomm_assign (appno, R1, R2, R3) VALUES (@appno, @R1, @R2, @R3)"
        Me.SqlCommand1.Connection = Me.SqlConnection1
        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@appno", System.Data.SqlDbType.NVarChar, 50, "appno"))
        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@R1", System.Data.SqlDbType.NVarChar, 50, "R1"))
        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@R2", System.Data.SqlDbType.NVarChar, 50, "R2"))
        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@R3", System.Data.SqlDbType.NVarChar, 50, "R3"))
        CType(Me.NewMembers1, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub
    Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents DataGrid2 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents DataGrid3 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents NewMembers1 As mbr_app.NewMembers
    Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        SqlDataAdapter1.Fill(Newmembers1)

        Dim DataTableA As DataTable = Newmembers1.Tables(0)
        Dim DataTableB As DataTable = Newmembers1.Tables(1)
       
        Dim Reviewers() As Integer
        Dim DataTableC As New DataTable
        DataTableC.Columns.Add("applicant", Type.GetType("System.String"))
        DataTableC.Columns.Add("R1", Type.GetType("System.String"))
        DataTableC.Columns.Add("R2", Type.GetType("System.String"))
        DataTableC.Columns.Add("R3", Type.GetType("System.String"))

        Dim dr, dr2 As DataRow
        Dim i, current_reviewer As Integer

        ' build an array of reviewers
        ReDim Reviewers(DataTableB.Rows.Count - 1)
        i = 0
        For Each dr In DataTableB.Rows
            Reviewers(i) = dr(0)
            i += 1
        Next

        ' read TableA, build the result table
        current_reviewer = 0
        For Each dr In DataTableA.Rows
            dr2 = DataTableC.NewRow
            dr2(0) = dr(0)
            For i = 1 To 3
                dr2(i) = Reviewers(current_reviewer)
                ' cycle thru all of the reviewers
                current_reviewer += 1
                If current_reviewer > UBound(Reviewers) Then
                    current_reviewer = 0
                End If
            Next
            DataTableC.Rows.Add(dr2)
        Next



        DataGrid1.DataSource = DataTableA
        DataGrid1.DataBind()

        DataGrid2.DataSource = DataTableB
        DataGrid2.DataBind()

        DataGrid3.DataSource = DataTableC
        DataGrid3.DataBind()


    End Sub

End Class