Click here to Skip to main content
14,934,895 members
Please Sign up or sign in to vote.
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
Updated 6-Sep-16 20:12pm

1 solution

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[^]
   
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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900