CODEWITHSUNDEEP
mysqlvb.net
njm
njm

Reputation: 51

Creating Table using vb.net, mysql

My friend wants to create a new table in the database (his using vb.net, mysql). The tricky thing is that he wants to name the new table from the input being encoded into a textbox.

Is that possible? To create and name a new table from the input in the textbox.

Upvotes: 0

Views: 11206

Answers (3)

Vikram
Vikram

Reputation: 309

This code should work....

Add one button to the form for testing purpose and place the following code to its click event 

Imports MySql.Data.MySqlClient

'place this in the class............. 

Dim myConnectionString = My.Settings.MySQL_DBConnectionString  
Dim con As New MySqlConnection(myConnectionString)

'Code on the button...........

Dim createSql As String
Try
    con = New MySqlConnection(myConnectionString)
    con.Open()
    Dim tblname = "anyInputName"
    createSql = "CREATE TABLE " & tblname & " (id INT(6) NOT NULL AUTO_INCREMENT,otherField TEXT NOT NULL,PRIMARY KEY (id));"
    Dim cmd As New MySqlCommand(createSql, con)
    cmd.ExecuteNonQuery()
    cmd.Dispose()
Catch ex As Exception
    MsgBox(ex.Message)
Finally
    con.Close()
End Try

Now go experimenting.......

Upvotes: 1

Chirag Lukhi
Chirag Lukhi

Reputation: 1546

@njm you can create one string variable having query of "Create table <name>(field1,field2....)" In above query must be replace with your input text box.

And pass this string to mysql command variable having its own connection ... And simply execute this query...

Upvotes: 2

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

Certainly it can be done.

Basically you build a string witha Create Table statement in it and then execute it.

e.g. someSql "Create Table " + EditBox1.Text + "(.....)"

BUT

Building sql from user input opens you to sql injection attacks.

Legal identifiers, certain characters can't be used, some must be escaped, the name may already be in use...

Once you have the table, how is the code going to use it, to know that there is a table called "MyTable" in the database, why it's there...

You wouldn't normally just hand out create table permissions.

What about dependencies (relations, stored procs, contraints etc)

There are several ways of dealing with this, one is to do table creation in another app, build up rules and store meta information somehow that the code can use to use the table.

Another for simpler apps, is to give the table some unique name in the db and then use the the name entered by the user as an alias for it, so Show("MyTable") gets mapped to Show("UserTable1876")

Another possibility is if the table has definite common structure and it's just user Fred's copy of it is to add another column to the table and then simply add Fred's UserID when any CRUD functions are used.

So yes it can be done, should it? Depends....

Upvotes: 0

Related Questions