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

  • Thread starter Thread starter Loogie
  • Start date Start date
L

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 ]
 
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.
 
Jin said:
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.
Click to expand...

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
 
Back
Top