Link to home
Start Free TrialLog in
Avatar of adamchicago
adamchicago

asked on

VB6 Create SQL Table - columns based on data

Please help me with the VB6 SQL create table command that would create a table where the column names would be variable, to be named later based on user input.  So for now I'm just interested in getting the create table command correct and later I'll work on the coding that asks the user to name the columns.

The number of columns and rows, the primary key and type of data (numeric, alpha) will change each time the user the creates a table which may be a few columns and rows on one instance and maybe hundreds of columns or tens of thousands of rows in another instance.

Basically the user will import the data into an open ended table, then look at the table in a datagridview, and then begin naming the columns based on what they see in the datagridview columns.

Thank you very much for any help!!!!!!!!!!!
Private Sub importmodulesBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles importmodulesBtn.Click
 
 
        ' IMPORTS DATA INTO SQL DB WHERE DATA LOCATION IS BASED ON BROWSE BUTTON'S CODE
        ' THAT POPULATES TEXTBOX MODULENAMETB WITH PATH TO DATA TO BE IMPORTED
 
        'CREATE NEW TABLE CODE
 
        ' Open the connection
        If mySharedConn.State = ConnectionState.Open Then
            mySharedConn.Close()
        End If
        mySharedConn.Open()
        sharedsql = "CREATE TABLE " & Me.modulenmTB.Text & "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," & "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)"
        sharedcmd = New SqlCommand(sharedsql, mySharedConn)
        Try
            sharedcmd.ExecuteNonQuery()
 
        Finally
            If mySharedConn.State = ConnectionState.Open Then
                mySharedConn.Close()
            End If
        End Try
 
        MsgBox("TABLE CREATED")
 
    End Sub
End Class

Open in new window

Avatar of HooKooDooKu
HooKooDooKu

There should be no difference in building a table from a hard coded string and a soft coded string.  Howver, a common mistake is to forget something (perhaps quotation marks, spaces, etc) as they try to build a soft coded string from variables.

So assuming you already know how to build a table with hard coded strings, I would suggest that you add some debug code (Debug.Print or just stepping through code in the IDE) to test what the value of sharedsql before you use it.

Even better, first rewrite the code to do everything hard coded.  Once you have the hard coded syntax working, then morph the logic to using soft coded variables.  That way, you'll know what the end result of your soft coded commands should look like to work, then use the debug suggestion to make sure you didn't make a mistake building the string.
Avatar of adamchicago

ASKER

Thanks for the response...this is my first attempt at the create table coding.  How do you write the command line if the table has a variable number of columns/rows and either no column names or at least only column names based on the top row of data (headers)?

For example the will import maybe 5 columns of data, then name the columns and then import a new table with 200 columns and then names those columns...how do you handle that variability in column number and column naming?

Assuming you've got your field names in an array ... and you always want your fields to be 255-character text.

sSQL = "Create Table MyTableName ("
for i = 1 to arFieldNames.count
  sSQL = sSQL & arFieldnames(i) & " varchar(255),"
next i
 
'Now trim off the trailing comma
sSQL = left(sSQL, Len(sSQL)-1)
 
'Close the parens
sSQL = sSQL & ")"

Open in new window

Thanks for you help...I inserted your code and am getting this error for the LEFT function:

" Public Property LEFT() As Integer has no parameters and its return type is cannot be indexed "

Do you know how to overcome this?

FYI does it have something to do with the " Dim arFieldNames As String "?
' OPEN ENDED TABLE COLUMNS EXPERIMENT
        Dim arFieldNames As String
        sharedsql = "Create Table MyTableName ("
        For i = 1 To arFieldNames.count
            sharedsql = sharedsql & arFieldnames(i) & " varchar(255),"
        Next i
 
        'Now trim off the trailing comma
        sharedsql = Left(sharedsql, Len(sharedsql) - 1)
 
        'Close the parens
        sharedsql = sharedsql & ")"
 
        Try
            sharedcmd.ExecuteNonQuery()
 
        Finally
            If mySharedConn.State = ConnectionState.Open Then
                mySharedConn.Close()
            End If
        End Try

Open in new window

Left( start, length) is the good old substring function that has been around since Basic.  For some reason, Microsoft saw fit to drop it going into .Net, and now you have to use functions of the string object.

Try this instead of Left...

sharedsql = sharedsql.Substring(0, sharedsql.Length() - 1)
Thanks with the LEFT question...that's weird that MS would nix the LEFT command??

Do you know why I would now be getting this error

"NullReferenceException was unhandled
Object reference not set to an instance of an object.  
   -Troubleshooting tips:
        - Use the New keyword to create an object instance
        - Check to determine if the object is null before calling method"
       
Keep in mind it's opening a newly created blank database and creating a new empty table with unlimited columns and rows to start.
' OPEN THE CONNECTION
        If mySharedConn.State = ConnectionState.Open Then
            mySharedConn.Close()
        End If
        mySharedConn.Open()
 
        ' OPEN ENDED TABLE COLUMNS EXPERIMENT
        Dim arFieldNames As String = 0
        sharedsql = "Create Table " & Me.modulenmTB.Text & " ("
        'For i = 1 To arFieldNames.count
        For i = 1 To 1000
            sharedsql = sharedsql & i & " varchar(255),"
        Next i
 
        'Now trim off the trailing comma
        sharedsql = sharedsql.Substring(0, sharedsql.Length() - 1)
 
        'Close the parens
        sharedsql = sharedsql & ")"
 
        Try
            sharedcmd.ExecuteNonQuery()
 
        Finally
            If mySharedConn.State = ConnectionState.Open Then
                mySharedConn.Close()
            End If
        End Try

Open in new window

Oops, sorry...the error occurs on this line: sharedcmd.ExecuteNonQuery() which is line 22 above
Actually I just took out lines 21 - 24 & 28 and it seems to get through it error free.

However after I run the code and go to SQL Server Management Studio and find the database and look in the tables there's nothing there but "Server Tables"...I think this code isn't really working??
ASKER CERTIFIED SOLUTION
Avatar of HooKooDooKu
HooKooDooKu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I still can't get this thing to work.  I added the "sharedcmd = New SqlCommand(sharedsql, mySharedConn) " line in as  you stated previously but I lost the Dim statement for the sharedcmd variable and I can't remember what it was dim'd as and diming at as a string doesn't work and outside of that the code isn't creating a table but it is running without errors...please, any help on this would be awesome!

Thanks again.
' OPEN ENDED TABLE COLUMNS EXPERIMENT
        Dim arFieldNames As String = 0
        Dim sharedsql As String
 
        sharedsql = "Create Table " & Me.modulenmTB.Text & " ("
        sharedcmd = New SqlCommand(sharedsql, mySharedConn)
        'For i = 1 To arFieldNames.count
        For i = 1 To 1000
            sharedsql = sharedsql & i & " varchar(255),"
        Next i
 
        'Now trim off the trailing comma
        sharedsql = sharedsql.Substring(0, sharedsql.Length() - 1)
 
        'Close the parens
        sharedsql = sharedsql & ")"
 
        'mySharedConn.execute(sharedsql)
 
        'Try
        sharedcmd.ExecuteNonQuery()
 
        'Finally
        If mySharedConn.State = ConnectionState.Open Then
            mySharedConn.Close()
        End If
        'End Try

Open in new window

Thanks for helping figure out how to create a table with open ended columns/rows