I have a c# application with .NET 4.0 trying to create a SQLite database in code.
I am using System.Data.SQLite version 1.0.94.0
Here is my code.
public void CreateDatabaseFile()
{
ClassGlobalVars.MyLogger.LogStatus("Creating New Database");
SQLiteConnection.CreateFile(ClassGlobalVars.StrDBPath);
using (SQLiteConnection sqlConnect = new SQLiteConnection("Data Source=" + ClassGlobalVars.StrDBPath + ";Version=3;", true))
{
sqlConnect.Open();
using (SQLiteTransaction transaction = sqlConnect.BeginTransaction())
{
using (var sqlCommand = new SQLiteCommand("CREATE TABLE [tb_DataSync] ([tbds_ID] INTEGER PRIMARY KEY NOT NULL,[tbds_DataName] VARCHAR(50) NOT NULL, [tbds_DataKey] VARCHAR(255) NOT NULL, [tbds_User] VARCHAR(50) NULL);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create index
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UNIQUEDATASYNC] ON [tb_DataSync] ([tbds_User], [tbds_DataName]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//Insert First Values
using (var sqlCommand = new SQLiteCommand("INSERT INTO tb_DataSync([tbds_DataName],[tbds_DataKey],[tbds_User]) VALUES('dbversion','2.4','ALL')", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//Create Profiles
using (var sqlCommand = new SQLiteCommand("CREATE TABLE [tb_Profiles] ([tbp_ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [tbp_amUserName] VARCHAR(50), [tbp_FirstName] VARCHAR(50),[tbp_LastName] VARCHAR(50), [tbp_Gender] VARCHAR(10), [tbp_Login] VARCHAR(255), [tbp_Password] TEXT, [tbp_Email] VARCHAR(50), [tbp_Address1] VARCHAR(255), [tbp_Address2] VARCHAR(255), [tbp_City] VARCHAR(50), [tbp_State] VARCHAR(255), [tbp_Zip] VARCHAR(10), [tbp_Country] VARCHAR(50), [tbp_GEO] VARCHAR(255), [tbp_Telephone] VARCHAR(25), [tbp_SiteTitle] VARCHAR(50), [tbp_SiteUrl] VARCHAR(255), [tbp_ClickBank] VARCHAR(25), [tbp_Paypal] VARCHAR(255), [tbp_Payza] VARCHAR(50), [tbp_Twitter] VARCHAR(50), [tbp_FaceBook] VARCHAR(50), [tbp_Google] VARCHAR(50), [tbp_Skype] VARCHAR(50), [tbp_Yahoo] VARCHAR(50), [tbp_AIM] VARCHAR(50), [tbp_MSN] VARCHAR(50));", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//Index for profiles
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UNIQUEUSERNAME] ON [tb_Profiles] ([tbp_amUserName]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//Create Settings Table
using (var sqlCommand = new SQLiteCommand("CREATE TABLE [tb_Settings] ([tbs_setID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [tbs_setMemberUserName] VARCHAR(50), [tbs_setName] VARCHAR(50), [tbs_setValue1] TEXT, [tbs_setValue2] TEXT);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//Index for Settings
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UNIQUENAMESET] ON [tb_Settings] ([tbs_setMemberUserName], [tbs_setName]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create traffic exchange Data
using (var sqlCommand = new SQLiteCommand("CREATE TABLE [tb_teData] ([tbted_ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [tbted_Domain] VARCHAR(50) NOT NULL, [tbted_Title] VARCHAR(50) NOT NULL, [tbted_SignUpUrl] VARCHAR(255), [tbted_ReferralUrl] VARCHAR(255), [tbted_LoginUrl] VARCHAR(255), [tbted_MemberUrl] VARCHAR(255), [tbted_SurfUrl] VARCHAR(255), [tbted_IsSurfAjax] BOOLEAN NOT NULL DEFAULT 0, [tbted_AfterXDaysDeleted] INTEGER(3) NOT NULL DEFAULT 0, [tbted_AfterXDaysInActive] INTEGER(3) NOT NULL DEFAULT 0, [tbted_TabTimerSecDefault] INTEGER(3) NOT NULL DEFAULT 0, [tbted_IsEmail] BOOLEAN NOT NULL DEFAULT 0, [tbted_IsNumeric] BOOLEAN NOT NULL DEFAULT 0, [tbted_IsUserName] BOOLEAN NOT NULL DEFAULT 0, [tbted_LoginCode] TEXT, [tbted_SignupCode] TEXT, [tbted_PrizePages] TEXT, [tbted_SecurityPages] TEXT, [tbted_ServerDateRevision] INTEGER(12) NOT NULL DEFAULT 0, [tbted_IsActive] BOOLEAN NOT NULL DEFAULT 0, [tbted_IsCustomTE] BOOLEAN NOT NULL DEFAULT 0);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create Index 1 index for TE Data
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UNIQUEDOMAIN] ON [tb_teData] ([tbted_Domain]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create traffic exchange User Data
using (var sqlCommand = new SQLiteCommand("CREATE TABLE [tb_teDataUser] ([tbtedu_ID] INTEGER NOT NULL PRIMARY KEY, [tbtedu_amUserID] INT(10) NOT NULL,[tbtedu_amUserName] VARCHAR(50),[tbtedu_Domain] VARCHAR(50) NOT NULL, [tbtedu_ReferralID] VARCHAR(50), [tbtedu_ReferralIDDefault] VARCHAR(50),[tbtedu_Login] VARCHAR(50), [tbtedu_Password] TEXT, [tbtedu_TabTimerSec] INTEGER(3), [tbtedu_Notes] TEXT, [tbtedu_myRating] INTEGER(2) DEFAULT 0,[tbtedu_IsFavorite] BOOLEAN(1) NOT NULL DEFAULT 0, [tbtedu_IsUpgraded] BOOLEAN(1) NOT NULL DEFAULT 0, [tbtedu_IsLifeTime] BOOLEAN(1) NOT NULL DEFAULT 0, [tbtedu_ZoomLevel] DOUBLE(2) NOT NULL DEFAULT (0.00), [tbtedu_ServerDateRevision] INTEGER(12) NOT NULL DEFAULT 0, [tbtedu_IsModified] BOOLEAN(1) DEFAULT 0);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create Index 1 for TEUserData
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UNIQUEFORDATAUSERIDANDUSERNAMEANDUSERDOMAIN] ON [tb_teDataUser] ([tbtedu_amUserID], [tbtedu_amUserName], [tbtedu_Domain]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create Index 2 for TEUserData
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UNIQUETEFORDATAUSERID] ON [tb_teDataUser] ([tbtedu_amUserID], [tbtedu_Domain]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create Index 3 for TEUserData
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UNIQUETEFORDATAUSERNAME] ON [tb_teDataUser] ([tbtedu_amUserName], [tbtedu_Domain]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create groups
using (var sqlCommand = new SQLiteCommand("CREATE TABLE [tb_teGroups] ([tbteg_ID] INTEGER NOT NULL PRIMARY KEY, [tbteg_amUserID] INT(12) NOT NULL,[tbteg_amUserName] VARCHAR(50),[tbteg_GroupName] VARCHAR(50), [tbteg_GroupValue] TEXT, [tbteg_ServerDateRevision] INT(12) NOT NULL DEFAULT 1,[tbteg_IsModified] BOOLEAN(1) DEFAULT 0, [tbteg_IsActive] BOOLEAN(1) NOT NULL DEFAULT 1);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create Index 1 for GROUPS
using (var sqlCommand = new SQLiteCommand("CREATE INDEX [REVISIONCHECK] ON [tb_teGroups] ([tbteg_amUserID], [tbteg_ServerDateRevision]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create Index 2 for GROUPS
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UniqueGroupName] ON [tb_teGroups] ([tbteg_amUserID], [tbteg_amUserName], [tbteg_GroupName]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create TE Stats Table
using (var sqlCommand = new SQLiteCommand("CREATE TABLE [tb_teStatistics] ([tbtes_ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,[tbtes_amUserID] INT(10) NOT NULL,[tbtes_teDomain] VARCHAR(50) NOT NULL,[tbtes_isoDate] INTEGER(10) NOT NULL,[tbtes_dateTime] VARCHAR(20) NOT NULL,[tbtes_clickNum] INTEGER(4) DEFAULT 0,[tbtes_isReported] BOOLEAN DEFAULT 0);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create index 1 for TE STats Table
using (var sqlCommand = new SQLiteCommand("CREATE INDEX [INDEX_DATETIME] ON [tb_teStatistics] ([tbtes_dateTime]);", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
//create index 2 for TE STats Table
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UNIQUE_CLICK_STATS] ON [tb_teStatistics] ([tbtes_amUserID], [tbtes_isoDate], [tbtes_teDomain]) WHERE tbtes_isReported = 0;", sqlConnect))
{
sqlCommand.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
I am receiving an exception of System.Data.SQLite.Exception "SQL Logic error or missing database"
In this line of code.
//create index 2 for TE STats Table
using (var sqlCommand = new SQLiteCommand("CREATE UNIQUE INDEX [UNIQUE_CLICK_STATS] ON [tb_teStatistics] ([tbtes_amUserID], [tbtes_isoDate], [tbtes_teDomain]) WHERE tbtes_isReported = 0;", sqlConnect))
{
sqlCommand.ExecuteNonQuery(); ///ERROR EXCEPTION RIGHT HERE
}
I have looked over this code many times but I can't figure out the issue. if I remove that last code it works perfectly.
Can you see my my error?
Thanks in advance.
Aucun commentaire:
Enregistrer un commentaire