create sql table from listbox selected item/'Incorrect syntax near '3142'.'

[tshukela.george]

Hi all

I'm creating a warehouse management system, then i want when new orders are created, a customer's name be selected from a list box and that customer's selected name from a list box be the table name that will hold records of the table's order with a random number upto 200.

And for the orders table names created from customer names listed in a listbox, I want the created table to have a randomly generated number(upto 200) in order for each created order-per customer to be unique.

So from my codes I get an error that reads 'Incorrect syntax near '3142'.'

Can anyone tell me whats wrong with my codes.

Dim inte As Integer = Int((200 - 1 + 1) * Rnd() + 1)

        Dim con As New SqlClient.SqlConnection("data source=ADMIN-PC\SQLEXPRESS;initial catalog=Warehouse;Integrated Security=True")
        Dim cmd As New SqlCommand()
        cmd.Connection = con
        con.Open()
        cmd.CommandText = "CREATE TABLE  '" & ListBox1.SelectedIndex & inte & "'(ID int ,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int, PRIMARY KEY (ID))"

        cmd.CommandType = CommandType.Text
        cmd.ExecuteNonQuery()

[rproffitt]

Since you are using Visual Studio, pause at line 9 to get the command string you made in line 8. You can then test that command with your choice of sqlcommand ( https://technet.microsoft.com/en-us/library/ms165702(v=sql.105).aspx ) or tool of your choice.

[xrjf]

I guess the table names are being repeated, to avoid this problem you may set the 200 numbers, firstly ordered in vinteand then reorder randomly:

        Dim n As Int32 = 200
        Dim vinte(n - 1) As Int32
        For i As Int32 = 0 To n - 1
            vinte(i) = i + 1
        Next
        Dim vByte(n - 1) As Byte
        rnd.NextBytes(vByte)
        ' vByte() contains random entries and vinte() is ordered.'
        ' sort vByte (keys) and reorder vinte entries in the same'
        ' way. Suppose vByte=25,4,7 and vinte=1,2,3 after Array.Sort'
        Array.Sort(vByte, vinte)
        ' vByte=4,7,25 and vinte=2,3,1'

[tshukela.george]

xrj , please clarify this, how do i associate your suggestion to my codes?

I tried to copy ang paste your suggestion to my project and the error " 'NextBytes' is not a member of 'Single'."

The main reason of me including my codes, is that I need association between my codes and your suggestion

[xrjf]

Sorry, rnd should be an instance of Random class:

dim rnd as new Random()

[xrjf]

I don't know exactly what is your code, but perhaps you could call createTable() passing the selected index as argument:

Sub createTable(selectedIndex As Int32)
    Dim n As Int32 = 200
    Static vinte(-1) As Int32
    Try
        If vinte.Length = 0 Then
            ' initialize vinte() just in the first call: '
             ReDim vinte(n - 1)
           Dim rnd As New Random
            For i = 0 To n - 1
                vinte(i) = i + 1
            Next
            Dim vByte(n - 1) As Byte
            rnd.NextBytes(vByte)
            Array.Sort(vByte, vinte)
        End If

        Dim con As New SqlClient.SqlConnection("data source=ADMIN-PC\SQLEXPRESS;initial catalog=Warehouse;Integrated Security=True")
        Dim cmd As New SqlCommand()
        cmd.Connection = con
        con.Open()
        cmd.CommandText = "CREATE TABLE  'table" & selectedIndex & vinte(selectedIndex) & "'(ID int ,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int, PRIMARY KEY (ID))"
        cmd.CommandType = CommandType.Text
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        Throw ex
    End Try
End Sub

[tshukela.george]

this solved my problem, thank you all

 Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ListBox1.SelectedIndexChanged
        Dim inte As Integer = Int((200 - 1 + 1) * Rnd() + 1)
        TextBox1.Text = ListBox1.SelectedValue & inte
    End Sub

[xrjf]

That's a good new. Anyway, running several times the application seems that easily there may be duplicated table names, but I imagine you already have consider it.