Help: Please check my code for dynamic table names with respect toschema

[Loogie]

I have some code that loops through a database with tables names all
beginning with the prefix 'projSupp' I then add to it an integer counter
that is supposed to cycle through and check to see if the table exists.

If it does not then create it.

The tables look like this

projSupp1
projSupp2
projSupp3

etc

Now that is how it is supposed to work but unfortunately it does not and
I do not know why. Could someone please look it over to see where the
problem is.

Thanks

:L


Dim ssceconnSS As New SqlCeConnection("Data Source = \Program
Files\data\projects\" & Me.Text & "\" & Me.Text & ".sdf")

ssceconnSS.Open()

Dim strTable As String = "projSupp" & XSS.ToString 'note XSS is a
counter I have running. It is at 1 at this stage - the first loop through.

Dim sqlSeekSS As New SqlCeCommand("SELECT * FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = " & strTable & " AND
TABLE_TYPE = 'TABLE'", ssceconnSS)

Dim sqlCreateTableSS As SqlCeCommand = ssceconnSS.CreateCommand()

Dim readerSS As SqlCeDataReader = sqlSeekSS.ExecuteReader() ' the
following error is triggered here:

The column name is not valid. [ Node name (if any) = ,Column name =
projSupp1 ]

 

[Jin Chang]

First of all, you'll need the quotes around your strTable value.
Second, you may need to avoid declaring the second command (one for
the sqlCreateTableSS) before excuting the reader.

 

[Loogie]

First of all, you'll need the quotes around your strTable value.
Second, you may need to avoid declaring the second command (one for
the sqlCreateTableSS) before excuting the reader.

I changed the select line to:

Dim sqlSeekSS As New SqlCeCommand("SELECT * FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" & strTable & "' AND
TABLE_TYPE = 'TABLE'", ssceconnSS)

It now works fine.

Thanks

:L