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!!!!!!!!!!!
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
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?
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 & ")"
ASKER
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 "?
" 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
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)
Try this instead of Left...
sharedsql = sharedsql.Substring(0, sharedsql.Length() - 1)
ASKER
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.
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
ASKER
Oops, sorry...the error occurs on this line: sharedcmd.ExecuteNonQuery( ) which is line 22 above
ASKER
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??
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
ASKER
Thanks for helping figure out how to create a table with open ended columns/rows
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.