Sql Serverce

[suganzeni]

Hi to all
I am working with Sql server ce for mobile application with vb.net.
I want to crete a table before that i have to check weather the table exists or not.
Also want to know about Object id in sql server and how to create it.
Thanks and Regards
Sugan

[Nick Evan]

You could do that with sql:

CREATE TABLE IF NOT EXISTS foo (bar int)

This creates a table foo, only if it hasn't been created before.

[suganzeni]

Hi nick
Thanks for your help.
Following is the code i used.
But when i run the appliation it showing the follwoing error

There was an error parsing the query.[Token line number,Token line offset, Token in error,,]

Dim cn1 As SqlCeConnection = Nothing
cn1 = New SqlCeConnection("Data Source=\My Documents\myinsurance.sdf; " + "Password=")
Try
If cn1.State = ConnectionState.Open Then
cn1.Close()
End If
cn1.Open()

Dim sqlCreateTable As SqlCeCommand = cn1.CreateCommand()
sqlCreateTable.CommandText = "CREATE TABLE IF NOT EXISTS worklist(wcode ntext, wid int, description ntext, startdate datetime, enddate datetime, starttime ntext, endtime ntext,status ntext)"
sqlCreateTable.ExecuteNonQuery()

cn1.Close()
Catch sce As SqlCeException
MessageBox.Show(sce.Message)
End Try

sugan

[Nick Evan]

replace the 'ntext' with 'text' in your SQL-query.
and I think you meant 'starttime datetime' instead of 'starttime ntext'

[suganzeni]

Hi Nick
I am working with SQL SERVER CE.
I found the data types supported for SQL SERVER CE in the following link
http://msdn2.microsoft.com/en-us/library/aa275635(SQL.80).aspx
But there is no text data type only ntext avilable.

No time stamp also available
so i used datetime datatype for date and for time i used ntext and i type casted it to time in VB.NET application.

If i am running the following code
sqlCreateTable.CommandText = "CREATE TABLE meeting(mid int,type ntext, name ntext, startdate datetime, starttime datetime, endtime datetime, address ntext,status ntext)"
sqlCreateTable.ExecuteNonQuery()

The table is creted and i can also insert values to it.
But before creating this i want to check weather it exists or not.
Thanks

[Nick Evan]

aha.. I haven't worked with SQL on CE (yet).
So the following command doesn't work?

CREATE TABLE IF NOT EXISTS meeting(mid int,type ntext, name ntext, startdate datetime, starttime datetime, endtime datetime, address ntext,status ntext)

Another (but ugly) option would be to try a SELECT query on the table (SELECT *) and if you get a MySQL-error, the table doesn't exist. But this is not fail-proof...

[edit] link

[suganzeni]

Yes Nick
CREATE TABLE IF NOT EXISTS meeting(mid int,type ntext, name ntext, startdate datetime, starttime datetime, endtime datetime, address ntext,status ntext)
This is not working.
Also i tried with SELECT * command but it does not give me a good solution

Thanks

[AaronASterling]

Hi to all
I am working with Sql server ce for mobile application with vb.net.
I want to crete a table before that i have to check weather the table exists or not.
Also want to know about Object id in sql server and how to create it.
Thanks and Regards
Sugan

I am not so familiar with ce but i would say try

IF OBJECT_ID(tablename) IS NULL
CREATE table
(
...
)

if by object id you mean the OBJECT_ID system function then it is just a system function that returns the system ID of any object

Hope that helps,
Aaron

[vinu_32123]

i used following code it worked for me

string query1 = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'RelationDocs' AND TABLE_TYPE = 'TABLE'";                
                sql.Connection = myConnection;
                sql.CommandText = query1;
                SqlCeDataReader reader = sql.ExecuteReader();
                if (reader.Read() == false)
                {

                    string query = "CREATE TABLE RelationDocs (Relation NVARCHAR(100) , Path NVARCHAR(100))";
                    cmd.Connection = myConnection;
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }