[RESOLVED] [2005] Variable inside an SQL Query? Help please.

[hostcord]

This is how I thought it was done but everytime I try this I get an error.

For Each FoundMatch As System.Text.RegularExpressions.Match In Mymatches
            Dim table8 As String = FoundMatch.Value
            MsgBox(table8)
            conn.Execute("CREATE TABLE " & table8 & "(file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, file_name VARCHAR(64) NOT NULL, file_size MEDIUMINT UNSIGNED NOT NULL, file MEDIUMBLOB NOT NULL)")
 
 
        Next

The error is...

System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2147217900
  Message="[MySQL][ODBC 3.51 Driver][mysqld-5.0.18-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30(file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, file_name VARC' at line 1"
  Source="Microsoft OLE DB Provider for ODBC Drivers"

[hostcord]

One more thing...everything works fine when I'm not trying to place a variable inside the code.

[penagate]

Use angle quotes (`) around field, table, and database names. Single quotes (') around string literals (be careful to escape any single quotes that might go into those strings with backslashes \'). No quotes around numbers.

[hostcord]

conn.Execute("CREATE TABLE table8(file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, file_name VARCHAR(64) NOT NULL, file_size MEDIUMINT UNSIGNED NOT NULL, file MEDIUMBLOB NOT NULL)")

I'm sorry...that's a little too advanced and unclear for me. Could you please point me in the right direction with my current code above ^ table8 is the variable I wanna use.

[penagate]

conn.Execute( _
  "CREATE TABLE `" + table8 + "` (" + _
  "  `file_id` SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, " + _
  "  `file_name` VARCHAR(64) NOT NULL, " + _
  "  `file_size` MEDIUMINT UNSIGNED NOT NULL, " + _
  "  `file` MEDIUMBLOB NOT NULL)" _
)

I tested that exact query on a MySQL client with no problems.

[hostcord]

Thank You!