Checkedlistbox item text into SQL table as column

Question

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 27-Jan-17 2:25am

Member 11856456

Updated 27-Jan-17 3:48am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2017-01-27T03:48:00

Solution 1

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()

Posted 27-Jan-17 3:48am

Richard Deeming

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.