How to overwrite a sqlite database?

Question

0.00/5 (No votes)

See more:

I'm new to coding and trying to make a little game along the lines of Football Manager series that uses a database, I'm using WPF C# and SQLite.

So what I'm trying to do is set up a SQLite database at the beginning of my game, and I want it so that you can choose to overwrite an existing database (ie with the same name).

It was working at some point, but then I changed some things in the way I was getting the savepath to open the database connection later. Now it throws an exception when trying to overwrite an existing database. But it still creates a database with a new name fine.

This is the save dialog and getting the filepath and filename of the database file it's creating:

C#

public void SaveName()
      {
          Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog();
          sfd.FileName = "NewDatabase"; // Default file name
          sfd.DefaultExt = ".db3"; // Default file extension
          sfd.Filter = "SQLite Databases (.db3)|*.db3"; // Filter files by extension
          sfd.ShowDialog();
          Variables.dbPath = sfd.FileName;
          Variables.dbName = System.IO.Path.GetFileNameWithoutExtension(Variables.dbPath);
      }

This is my "new game" button:

C#

private void button_Click(object sender, RoutedEventArgs e)
    {
        dbt.SaveName();
        DatabaseTasks.CreateDatabase(Variables.dbName);
        dbt.SetConnection(Variables.dbPath);
        dbt.CreateTables();
        dbt.PopulatePeopleTable();
    }

This is creating the tables:

C#

  public void CreateTables()
        {
            m_dbConn.Open();

            string ctPeople = "CREATE TABLE people (FirstName VARCHAR(20), LastName VARCHAR(20), Age INT, Strength INT, Agility INT, Dexterity INT, CA INT)";
            SQLiteCommand cmdCTP = new SQLiteCommand(ctPeople, m_dbConn);
            cmdCTP.ExecuteNonQuery();

            string ctShips = "CREATE TABLE ships (ShipName VARCHAR(20), Hull INT)";
            SQLiteCommand cmdCTS = new SQLiteCommand(ctShips, m_dbConn);
            cmdCTS.ExecuteNonQuery();

            string ctPorts = "CREATE TABLE ports (PortName VARCHAR(20), Population INT)";
            SQLiteCommand cmdCTPt = new SQLiteCommand(ctPorts, m_dbConn);
            cmdCTPt.ExecuteNonQuery();

            m_dbConn.Close();
}

It throws the exception on line 7 at cmdCTP.ExecuteNonQuery();

This is the exception details:

C#

System.Data.SQLite.SQLiteException was unhandled
  ErrorCode=1
  HResult=-2147467259
  Message=SQL logic error or missing database
table people already exists
  Source=System.Data.SQLite
  StackTrace:
       at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
       at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
       at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
       at System.Data.SQLite.SQLiteDataReader.NextResult()
       at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
       at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
       at ShipsWPF.DatabaseTasks.CreateTables() in C:\Users\Keith\Documents\Visual Studio 2015\Projects\ShipsGame\WpfApplication2\DatabaseTasks.cs:line 67
       at ShipsWPF.Page1.button_Click(Object sender, RoutedEventArgs e) in C:\Users\Keith\Documents\Visual Studio 2015\Projects\ShipsGame\WpfApplication2\Page1.xaml.cs:line 36
       at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
       at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
       at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
       at System.Windows.UIElement.RaiseEvent(RoutedEventArgs e)
       at System.Windows.Controls.Primitives.ButtonBase.OnClick()
       at System.Windows.Controls.Button.OnClick()
       at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
       at System.Windows.UIElement.OnMouseLeftButtonUpThunk(Object sender, MouseButtonEventArgs e)
       at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
       at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
       at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
       at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
       at System.Windows.UIElement.ReRaiseEventAs(DependencyObject sender, RoutedEventArgs args, RoutedEvent newEvent)
       at System.Windows.UIElement.OnMouseUpThunk(Object sender, MouseButtonEventArgs e)
       at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
       at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
       at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
       at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
       at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
       at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args)
       at System.Windows.UIElement.RaiseEvent(RoutedEventArgs args, Boolean trusted)
       at System.Windows.Input.InputManager.ProcessStagingArea()
       at System.Windows.Input.InputManager.ProcessInput(InputEventArgs input)
       at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport)
       at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel)
       at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
       at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
       at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
       at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
       at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
       at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
       at System.Windows.Application.RunDispatcher(Object ignore)
       at System.Windows.Application.RunInternal(Window window)
       at System.Windows.Application.Run(Window window)
       at System.Windows.Application.Run()
       at ShipsWPF.App.Main() in C:\Users\Keith\Documents\Visual Studio 2015\Projects\ShipsGame\WpfApplication2\obj\Debug\App.g.cs:line 0
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

What I have tried:

I don't know what to try, it worked before and now it doesn't and as a noob I haven't got the old version of the code to compare it to. I didn't change CreateTables()

The main thing I changed was using a class Variables to store the dbPath and dbName so I could use get/set.

Also any general observations about how I could make the code better would be appreciated.

Posted 6-Sep-16 18:12pm

Keith Lewis

Updated 6-Sep-16 20:12pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-09-06T20:12:00

Solution 1

A error message is quite clear: table people already exists.
Before you start creating tables, you have to check if they already exist.
1. way:

SQL

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

2. way:

SQL

create table if not exists TableName (col1 typ1, ..., colN typN)

Another way is to drop table before creating new one:

SQL

drop table if exists TableName

Source: How do I check in SQLite whether a table exists? - Stack Overflow[^]

Posted 6-Sep-16 20:12pm

Maciej Los

Comments

CPallini 7-Sep-16 2:41am

5.

Keith Lewis 7-Sep-16 7:36am

I know that is the error but what I don't understand is why it is finding a table when it's supposed to be replacing the file? I don't want it to work with the existing file at all, it should be creating a brand new database before it builds the tables.

Maciej Los 7-Sep-16 10:29am

You have to check if file exists, then you'll be able to delete existing file and create new database.

Keith Lewis 7-Sep-16 23:49pm

The savedialog already checks this though, it says "This file already exists, do you want to replace it?" Problem is, it obviously isn't replacing it now. But I know that it should work because I had it working before and I don't know what I've changed that has made it not work. I guess there must be something preventing the file from being deleted.

Maciej Los 8-Sep-16 2:02am

As i mentioned it earlier, you have to check if file exists. This mean that SaveName method does not contain:

if(IO.File.Exists(filename))
{
   //delete file
}

File.Exists Method (String)

Maciej Los 7-Sep-16 10:27am

Thank you, Carlo.