none
Create table and give it name RRS feed

  • Question

  • can I create table on local database and give it name as the text in label text useing vb net
     Private Sub btnadduserdb_Click(sender As Object, e As EventArgs) Handles btnadduserdb.Click
            Dim tbname As String
            tbname = lbluser.Text
            Try
                Dim cn As SqlConnection = New SqlConnection(My.Settings.kylConnectionString)
    
                
                Dim sql As String 'sql query string
                sql = "CREATE TABLE @tbname (
        Datum        DATE           NOT NULL PRIMARY KEY,
        Starttid     NCHAR (10)     NULL, 
        Sluttid      NCHAR (10)     NULL, 
        Timmar       DECIMAL (3, 1) NULL,
        Ob1          DECIMAL (3, 1) NULL, 
        Ob2          DECIMAL (3, 1) NULL, 
        Ob3          DECIMAL (3, 1) NULL, 
        Ob4          DECIMAL (3, 1) NULL, 
        SOb1         DECIMAL (3, 1) NULL, 
        SOb2         DECIMAL (3, 1) NULL, 
        SOb3         DECIMAL (3, 1) NULL, 
        SOb4         DECIMAL (3, 1) NULL, 
        Sjukdelavdag DECIMAL (3, 1) NULL, 
        Sjukheldag   DECIMAL (3, 1) NULL, 
        Kompin       DECIMAL (3, 1) NULL, 
        Komput       DECIMAL (3, 1) NULL, 
        HKompin      DECIMAL (3, 1) NULL, 
        HKomput      DECIMAL (3, 1) NULL, 
        LKompin      DECIMAL (3, 1) NULL, 
        LKomput      DECIMAL (3, 1) NULL, 
        Semesterin   DECIMAL (3, 1) NULL, 
        Semesterut   DECIMAL (3, 1) NULL, 
        Tjänstledigt DECIMAL (3, 1) NULL, 
        Overtid100   DECIMAL (3, 1) NULL, 
        Overtid70    DECIMAL (3, 1) NULL, 
        Overtid50    DECIMAL (3, 1) NULL, 
        Helafton     DECIMAL (3, 1) NULL 
         )"
                Dim cmd As SqlCommand = New SqlCommand(sql, cn)
                cmd = New SqlCommand(sql, cn) ' create command with connection and query string 
                cmd.Connection.Open()
                cmd.Parameters.Add("@tbname", SqlDbType.NChar)
                cmd.Parameters("@tbname").Value = tbname
                cmd.ExecuteNonQuery()
                cmd.Connection.Close()
            Catch
    
            End Try
        End Sub



    Monday, April 2, 2018 5:13 PM

Answers

  • I think that this is the case when Parameterised Queries cannot be used. You have to perform a concatenation:

       sql = "CREATE TABLE " & tbname & "(

       . . .

    A “sanitisation” of tbname is required, with regards to some characters like --, [, ] (to avoid SQL Injection).



    • Edited by Viorel_MVP Monday, April 2, 2018 6:23 PM
    • Marked as answer by Majed Abbas Monday, April 2, 2018 6:26 PM
    Monday, April 2, 2018 6:20 PM

All replies

  • you can by doing an executenonquery and giving an alter command with that. 

    But it is not really clever. Why is difficult to explain, you will face that quick enough. 

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql


    Success
    Cor

    Monday, April 2, 2018 5:20 PM
  • What type of database? Access? SQL Server? Other? Below is an example that uses Microsoft Access:

            Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                                "Data Source=C:\Test Files\db1 XP.accdb"
    
            Dim ddlCreateTable As String = "CREATE TABLE TestAllTypes (" &
                                            "MyText       TEXT(50)," &
                                            "MyMemo       MEMO," &
                                            "MyByte       BYTE," &
                                            "MyInteger    INTEGER," &
                                            "MyLong       LONG," &
                                            "MyAutoNumber COUNTER," &
                                            "MySingle     SINGLE," &
                                            "MyDouble     DOUBLE," &
                                            "MyCurrency   CURRENCY," &
                                            "MyReplicaID  GUID," &
                                            "MyDateTime   DATETIME," &
                                            "MyYesNo      YESNO," &
                                            "MyOleObject  LONGBINARY)"
    
            Dim accessConnection As New System.Data.OleDb.OleDbConnection(connectionString)
            accessConnection.Open()
            Dim accessCommand As New System.Data.OleDb.OleDbCommand(ddlCreateTable, accessConnection)
            accessCommand.ExecuteNonQuery()
    
            accessConnection.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 2, 2018 5:27 PM
  • What type of database? Access? SQL Server? Other? Below is an example that uses Microsoft Access:

            Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                                "Data Source=C:\Test Files\db1 XP.accdb"
    
            Dim ddlCreateTable As String = "CREATE TABLE TestAllTypes (" &
                                            "MyText       TEXT(50)," &
                                            "MyMemo       MEMO," &
                                            "MyByte       BYTE," &
                                            "MyInteger    INTEGER," &
                                            "MyLong       LONG," &
                                            "MyAutoNumber COUNTER," &
                                            "MySingle     SINGLE," &
                                            "MyDouble     DOUBLE," &
                                            "MyCurrency   CURRENCY," &
                                            "MyReplicaID  GUID," &
                                            "MyDateTime   DATETIME," &
                                            "MyYesNo      YESNO," &
                                            "MyOleObject  LONGBINARY)"
    
            Dim accessConnection As New System.Data.OleDb.OleDbConnection(connectionString)
            accessConnection.Open()
            Dim accessCommand As New System.Data.OleDb.OleDbCommand(ddlCreateTable, accessConnection)
            accessCommand.ExecuteNonQuery()
    
            accessConnection.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    it is sql

    but how to get table name as the text in the lable


    Monday, April 2, 2018 5:36 PM
  • Visual Studio 2013 and higher you can use xml literal with embedded expression.

    Dim tableName As String = "Customers"
    Dim createTable As String =
        <SQL>
        CREATE TABLE [dbo].<%= tableName %>(
    	    [CustomerIdentifier] [int] IDENTITY(1,1) NOT NULL,
    	    [CompanyName] [nvarchar](40) NOT NULL,
    	    [ContactName] [nvarchar](30) NULL,
    	    [ContactIdentifier] [int] NULL,
    	    [ContactTypeIdentifier] [int] NULL,
    	    [Street] [nvarchar](60) NULL,
    	    [City] [nvarchar](15) NULL,
    	    [PostalCode] [nvarchar](10) NULL,
    	    [CountryIdentfier] [int] NULL,
    	    [Phone] [nvarchar](24) NULL,
    	    [ModifiedDate] [datetime2](7) NULL)
        </SQL>.Value


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Monday, April 2, 2018 6:16 PM
    Moderator
  • I think that this is the case when Parameterised Queries cannot be used. You have to perform a concatenation:

       sql = "CREATE TABLE " & tbname & "(

       . . .

    A “sanitisation” of tbname is required, with regards to some characters like --, [, ] (to avoid SQL Injection).



    • Edited by Viorel_MVP Monday, April 2, 2018 6:23 PM
    • Marked as answer by Majed Abbas Monday, April 2, 2018 6:26 PM
    Monday, April 2, 2018 6:20 PM
  • You can't use the table name as a parameter so you can either use Karen's method to build the SQL statement or take your example and simply insert the variable into the SQL string (remove the parameters):

    sql = "CREATE TABLE " & tbname & " (...
    


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 2, 2018 6:49 PM