Login-screen crashes with SQLite

Question

I am trying to create a simple login window in MonoDevelop but it crashes as soon as I click the button.

Using the following command: SqliteConnection.CreateFile("*.sqlite"); I can identify that it runs until line 26 but not 28.

Here is my code:

 using System;
    using Gtk;
    using Mono.Data.Sqlite;
    using System.Data;

    namespace BB
    {
        public partial class BBLogin : Gtk.Window
        {
            public BBLogin () :
                base (Gtk.WindowType.Toplevel)
            {
                this.Build ();
            }

            private void btnLoginOnClick (object sender, EventArgs e)
            {
                SqliteConnection conn = new SqliteConnection();
                conn.ConnectionString = "Data Source=BBUser.sqlite;Version=3;";
                SqliteCommand command = new SqliteCommand();
                command.CommandText = ("SELECT UserName FROM T_test WHERE UserName=@UserName AND Password=@Password");
                command.Parameters.AddWithValue ("@UserName", txtUserName.Text);
                command.Parameters.AddWithValue ("@Password", txtPassword.Text);

                conn.Open ();
/*line 26*/     SqliteConnection.CreateFile("failsafe0.sqlite");
                object result = command.ExecuteScalar();
                SqliteConnection.CreateFile("failsafe1.sqlite");
                conn.Close ();
                SqliteConnection.CreateFile("failsafe2.sqlite");
                string userNameLogin = Convert.ToString(result);
                SqliteConnection.CreateFile("failsafe3.sqlite");

                if (userNameLogin != "") 
                {
                    SqliteConnection.CreateFile("success.sqlite");
                    /*MessageDialog md = new MessageDialog ("Username was correct!");
                    md.Run ();
                    md.Destroy();*/
                } 
                else 
                {
                    SqliteConnection.CreateFile("failed.sqlite");
                    /*MessageDialog md = new MessageDialog ("Username or password is incorrect!");
                    md.Run ();
                    md.Destroy();*/
                }
            }

        }
    }

And here is the full exception detail:

Exception in Gtk# callback delegate Note: Applications can use GLib.ExceptionManager.UnhandledException to handle the exception. System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: No connection associated with this command at Mono.Data.Sqlite.SqliteCommand.InitializeForReader () [0x00000] in :0 at Mono.Data.Sqlite.SqliteCommand.ExecuteReader (CommandBehavior behavior) [0x00000] in :0 at Mono.Data.Sqlite.SqliteCommand.ExecuteScalar () [0x00000] in :0 at BB.BBLogin.btnLoginOnClick (System.Object sender, System.EventArgs e) [0x0006c] in /home/christian/BB/BB/BBLogin.cs:27 at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&) at System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000] in :0 --- End of inner exception stack trace --- at System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000] in :0 at System.Reflection.MethodBase.Invoke (System.Object obj, System.Object[] parameters) [0x00000] in :0 at System.Delegate.DynamicInvokeImpl (System.Object[] args) [0x00000] in :0 at System.MulticastDelegate.DynamicInvokeImpl (System.Object[] args) [0x00000] in :0 at System.Delegate.DynamicInvoke (System.Object[] args) [0x00000] in :0 at GLib.Signal.ClosureInvokedCB (System.Object o, GLib.ClosureInvokedArgs args) [0x00000] in :0 at GLib.SignalClosure.Invoke (GLib.ClosureInvokedArgs args) [0x00000] in :0 at GLib.SignalClosure.MarshalCallback (IntPtr raw_closure, IntPtr return_val, UInt32 n_param_vals, IntPtr param_values, IntPtr invocation_hint, IntPtr marshal_data) [0x00000] in :0 at GLib.ExceptionManager.RaiseUnhandledException(System.Exception e, Boolean is_terminal) at GLib.SignalClosure.MarshalCallback(IntPtr raw_closure, IntPtr return_val, UInt32 n_param_vals, IntPtr param_values, IntPtr invocation_hint, IntPtr marshal_data) at Gtk.Application.gtk_main() at Gtk.Application.Run() at BB.MainClass.Main(System.String[] args) in /home/christian/BB/BB /Program.cs:line 15

Best regards,

Chris

Answer 1

A command to be executed needs a command text and a connection to use. You provide the command text but not the binding to the connection

Just add this line before executing the command

command.Connection = conn;

Also I suggest you to use this code when dealing with disposable objects

string userNameLogin = string.Empty;
using(SqliteConnection conn = new SqliteConnection("Data Source=BBUser.sqlite;Version=3;"))
using(SqliteCommand command = new SqliteCommand(@"SELECT UserName 
        FROM T_test WHERE UserName=@UserName AND Password=@Password", conn))
{
        command.Parameters.AddWithValue ("@UserName", txtUserName.Text);
        command.Parameters.AddWithValue ("@Password", txtPassword.Text);
        conn.Open ();
        result = command.ExecuteScalar();
}
if(string.IsNullOrEmpty(result))
{
    // Failure
}
else
{
    // Success
}

This approach is better because you are guaranteed that the connection will be closed and disposed when the code exits from the using block also in case of exceptions. Note also that using the SqliteCommand overload that takes two parameters (the command text and the connection) you don't have to remember to set them afterwards.