How to check if table exist?

S

Sylpheed

Joined
Jul 19, 2005
Messages
9
Reaction score
0
I have a SQL code to drop a table, but i would need to check if the table exist before i drop it.

For normal SQL i use this code

IF OBJECT_ID('ItemTable') IS NOT NULL DROP TABLE ItemTable

it works fine.. but when i run the same code on my sQL CE on my palm device, it gives me an error

Native Error : 25501
Description : There was an error parsing the query. [Token Line number, Token line offset,, Token in error,,]

anyone have any ideas?
 
Found a solution.

Code: 
  Private Function DoesTableExist(ByRef table As String) As Boolean

        Dim sqlConn As SqlCeConnection = Nothing
        Try
            sqlConn = New SqlCeConnection("data source=\My Documents\[db].sdf")
            sqlConn.Open()
            Dim sqlCmd As SqlCeCommand = sqlConn.CreateCommand()
            Dim tableName As SqlCeParameter = New SqlCeParameter

            sqlCmd.CommandText = "SELECT COUNT(*) FROM MSysObjects WHERE Name = ?"

            tableName.ParameterName = "@TableName"
            tableName.Size = 128
            tableName.Value = table

            sqlCmd.Parameters.Add(tableName)

            Dim rVal As Boolean = sqlCmd.ExecuteScalar().Equals(1)
            sqlConn.Close()
            Return rVal

        Catch ex As SqlCeException
            MsgBox(ex.Message)
            Return False
        Catch ex As Exception
            MsgBox(ex.Message)
            Return False
        Finally
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
        End Try

    End Function
 
Back
Top