Please help me fix my code to check for existence of table

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

Loogie

I am using vb.net 2005 compact framework. Having a problem checking to
see if a table exists. I want to create the table if it does not exist.
If it does then no action.

In my code below the word exists throws an error as it is not a member
of System.Data.SQLServerCe.SQLCeCommand...

So how do I fix this snippet to make it work? Please go easy on me as I
am a novice when it comes to using this stuff.

Thanks

:L

Dim sqlSeek As New SqlCeCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'products' AND TABLE_TYPE = 'TABLE'", ssceconn)

Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()

If Not sqlSeek.exists Then
' Create product table
sqlCreateTable.CommandText = "CREATE TABLE product(p_item int
IDENTITY(1,1) PRIMARY KEY NOT NULL, p_code NVARCHAR(1) NOT NULL, p_name
NVARCHAR(255) NOT NULL, p_min numeric(2) NOT NULL, p_top numeric(2) NOT
NULL, p_len numeric(2) NOT NULL, p_lcl NUMERIC(2) NULL, p_ucl NUMERIC(2)
NULL, p_units NVARCHAR(7) NULL, p_speclist NVARCHAR(100) NOT NULL,
p_notes NVARCHAR(255) NULL, p_verify NVARCHAR(3) NULL)"

sqlCreateTable.ExecuteNonQuery()
End If
 
Hi

I was able to solve it with some help from another source:

Dim sqlSeek As New SqlCeCommand("SELECT * FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'products' AND TABLE_TYPE =
'TABLE'", ssceconn)
Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()
Dim reader As SqlCeDataReader = sqlSeek.ExecuteReader()

If reader.HasRows = False Then
' Create product table
sqlCreateTable.CommandText = "CREATE TABLE product(p_item
int IDENTITY(1,1) PRIMARY KEY NOT NULL, p_code NVARCHAR(1) NOT NULL,
p_name NVARCHAR(255) NOT NULL, p_min numeric(2) NOT NULL, p_top
numeric(2) NOT NULL, p_len numeric(2) NOT NULL, p_lcl NUMERIC(2) NULL,
p_ucl NUMERIC(2) NULL, p_units NVARCHAR(7) NULL, p_speclist
NVARCHAR(100) NOT NULL, p_notes NVARCHAR(255) NULL, p_verify NVARCHAR(3)
NULL)"
sqlCreateTable.ExecuteNonQuery()
End If


:L
 
Back
Top