Click here to Skip to main content
15,905,323 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
So what I am trying to do is have my program allow users to pick from a checked listbox what columns they would like to have in their database. so far I have changed the code to where it formats the checkedlistbox into a string, problem is I can not separate the text into individual instances instead it take the text from all the checked boxes and meshes them into one long string and makes only one column.

Dim cb As New System.Text.StringBuilder

          For Each item In CheckedListBox1.CheckedItems
              cb.Append(item).Append("")
              cb.ToString.Split()

          Next
          sqlcon.Open()

              Dim cmd As SqlClient.SqlCommand
              Dim sql As String

          sql = "CREATE Table " + InputBox("Name of Table ") + "(Id INTEGER Not Null PRIMARY KEY CLUSTERED ([Id] ASC)," + cb.ToString + " NVARCHAR (MAX) NULL)"


What I have tried:

I have tried to change the split function and have tried to change the append string format into something I could use the split function with, but to no avail, it still produces one long string or I receive an error.
Posted
Updated 27-Jan-17 3:48am
Comments
[no name] 27-Jan-17 8:42am    
"produces one long string", mostly because that is exactly what you told it to do, "cb.ToString". You would need to separate cb into individual strings and create a valid SQL statement from them.
Member 11856456 27-Jan-17 8:52am    
how can I go about that using the for each statement?

1 solution

Try something like this:
VB.NET
Dim cb As New System.Text.StringBuilder("CREATE TABLE");
Dim tableName As String = InputBox("Name of table ")
cb.AppendFormat(" [{0}] ", tableName.Replace("]", "]]"))
cb.Append("(Id INTEGER Not Null PRIMARY KEY CLUSTERED ([Id] ASC)")

For Each item In CheckedListBox1.CheckedItems
    Dim columnName As String = Convert.ToString(item)
    cb.AppendFormat(", [{0}] nvarchar(max) NULL", columnName.Replace("]", "]]"))
Next

cb.Append(")")

Dim sql As String = cb.ToString()
 
Share this answer
 
Comments
Member 11856456 27-Jan-17 9:58am    
Richard, you are a genius thank you. I am still learning a lot about vb.net, would you mind possibly explaining the [{0}] in the code?
Richard Deeming 27-Jan-17 10:06am    
In MS SQL Server, surrounding a name with [...] allows you to use "special" characters. It's not recommended, but if your user is entering the name, you need to account for the possibility that they'll enter something odd like "My super-secret table name".

If the user enters a name that contains the ] character, that would normally break this scheme. To prevent that, you need to "escape" the character by replacing it with ]].

(Normally, you'd want to pass user-supplied values as parameters, to avoid SQL Injection[^]. But unfortunately, you can't pass table or column names as parameters.)

The AppendFormat method accepts a format string, using the same syntax as the String.Format method[^]. {0} is a placeholder, which will be replaced with the first format argument. So, for example, AppendFormat("[{0}]", "Table1") would append the string "[Table1]".
Member 11856456 27-Jan-17 10:53am    
Thank you for the explanation.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900