I wish to make a CREATE table string

[tracmonali]

Hello:
I am trying to create a web form where there are around 30 textboxes. The user inputs only few of those textboxes. My click button should be able to read the textbox.text values store it in two dimension (rectangular) array. The textboxes which have Null values should not be stored in array.

These textbox values are actually the "column names" for a create table command. The data type for the columns of table is nvarchar by default.

After storing the textbox values, I wish to make a CREATE table string where using the loop (for,while Do) raed all the values in the two dimensional array.

I am bale to create a table with table name and PK constraint but no fields. Any help would be appreciated. I have highlighted code that is not working.

Here is my code:

SQLConnectionName.Open()


        Dim tablename As String
        tablename = Dimtbltxtbox.Text

        Dim pk As String
        pk = tablename & "_key"

        Dim rectangularArray(50, 2) As String

[COLOR="Red"]Try
                    For i = 1 To 1000
                    rectangularArray(i, 0) = CType(Controls("textbox" + CStr(i)), TextBox).Text
                rectangularArray(i, 1) = "nvarchar"
                    Next i
                Catch
                End Try[/COLOR]
'Dim i As Integer

        CreateCmdString = "CREATE TABLE " + tablename + "("
        CreateCmdString += pk + " Integer PRIMARY KEY,"
             [COLOR="Red"]   Try
            For i = 0 To 50
                CreateCmdString += rectangularArray(i, 0) + " " + rectangularArray(i, 1)
            Next i
        Catch
        End Try[/COLOR]
        CreateCmdString += ")"

        CommandCreate = New SqlCommand(CreateCmdString, SQLConnectionName)
        CommandCreate.ExecuteNonQuery()
        SQLConnectionName.Close()

   End Sub

 

[MattP]

Instead of creating an array and then casting every control on the page as a TextBox try something like this:

        For Each oControl As Control In Me.Controls
            If oControl.GetType.ToString() = "TextBox" AndAlso Not DirectCast(oControl, TextBox).Text = String.Empty Then
                CreateCmdString += ", " & DirectCast(oControl, TextBox).Text & " nvarchar"
            End If
        Next

Haven't tried this out but it should be syntactically close enough where you get the idea.

This way you're checking if the control is a TextBox and that it's not empty. Also rather than adding this information to an array just append it to your CreateCmdString.