Click here to Skip to main content
15,851,387 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
OK this code was rewritten from a VB.Net application
I am now trying to adapt it to C#
First and most confusing NO Exception is thrown
I am using VB Helper and can see the table and columns
I will include the code that creates the table which is working based on VB Helper
The issue is with the INSERT code

OK I am now seeing this ERROR with Griff's Code
code = Unknown (-1), message = System.Data.SQLite.SQLiteException (0x80004005): unknown error
Insufficient parameters supplied to the command
   at System.Data.SQLite.SQLiteStatement.BindParameter(Int32 index, SQLiteParameter param)
   at System.Data.SQLite.SQLiteStatement.BindParameters()
   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 Contacts.frmADE.btnSave_Click(Object sender, EventArgs e) in C:\Users\Dwight\source\repos\Contacts\Contacts\frmADE.cs:line 71


What I have tried:

This creates the table
public void makeFriendsData()
{
    // create table FriendsData String for cmd
    string create_table = string.Empty;
    create_table = @"CREATE TABLE IF NOT EXISTS FriendsData(
                FID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                fxFirstName TEXT,
                fxLastName TEXT,
                fxAddress TEXT,
                fxCity TEXT,
                fxState TEXT,
                fxZip TEXT,
                fxCellPhone TEXT,
                fxEmail TEXT,
                fxInfo TEXT)";// This is a Rich Text Box ?

    string dbTable = "FriendsData";

    if (!File.Exists(dbTable))
        {
        try
        {
            SQLiteCommand cmd = new SQLiteCommand(create_table, sqlite_con);
            cmd.ExecuteNonQuery();
            tbMessage.Text = "Database & Table Created";
        }
        catch (Exception ex)
        {
            //tbMessage.Text = ex.ToString();
            MessageBox.Show(ex.ToString());
            sqlite_con.Close();
        }
    }
}

Here is the INSERT code
I declard this top level
public string dbName = "Contacts.db";

private void btnSave_Click(object sender, EventArgs e)
{
    //if (! ValidateUpdateInput()){
        //return;
    //}
    {
        using (var conn = new SQLiteConnection($"Data Source = '{dbName}';Version=3;"))
        {
            conn.Open();

            using (var cmd = new SQLiteCommand())
            {
                cmd.Connection = conn;
                try
                {
                    cmd.CommandText = $"INSERT INTO FriendsData (fxFirstName,fxLastName,fxAddress,fxCity,fxState,fxZip,fxCellPhone,fxEmail,fxInfo) VALUES (@fxFirstName,@fxLastName,@fxAddress,@fxCity,@fxState,@fxZip,@fxCellPhone,@fxEmail,@tfxInfo)";
                    cmd.Parameters.Add("@fxFirstName",DbType.String).Value = tbFirstName.Text.Trim();
                    cmd.Parameters.Add("@fxLastName", DbType.String).Value = tbLastName.Text.Trim();
                    cmd.Parameters.Add("@fxAddress", DbType.String).Value = tbAddress.Text.Trim();
                    cmd.Parameters.Add("@fxCity", DbType.String).Value = tbCity.Text.Trim();
                    cmd.Parameters.Add("@fxState", DbType.String).Value = tbState.Text.Trim();
                    cmd.Parameters.Add("@fxZip", DbType.String).Value = tbZip.Text.Trim();
                    cmd.Parameters.Add("@fxCellPhone", DbType.String).Value = mtbCellPhone.Text.Trim();
                    cmd.Parameters.Add("@fxEmail", DbType.String).Value = tbEmail.Text.Trim();
                    cmd.Parameters.Add("@fxInfo", DbType.String).Value = rtbInfo.Text.Trim();

                    cmd.ExecuteNonQuery();
                   // conn.Close();
                }

                catch (Exception ex)
                {

                    tbMessage.Text = ex.ToString();

                } // "Label Table Failed"
                conn.Close();
            }
        }

        tbMessage.Text = "Data Saved";
        btnSave.Enabled = false;
        //SetRead();
        //btnBack.Select();
    }
}
Posted
Updated 21-Jan-23 7:02am
v3
Comments
Graeme_Grant 20-Jan-23 22:52pm    
You're overriding your error message
Jibesh 21-Jan-23 0:10am    
Adding more clarity to Grants answer.

You might be still getting an exception.

 
         catch (Exception ex)
                {

                    tbMessage.Text = ex.ToString();

                } // "Label Table Failed"
                conn.Close();
            }
        }

        tbMessage.Text = "Data Saved"; /// 


But message got overwritten by the statement
tbMessage.Text = "Data Saved"

if you still want to report "Data Saved" message

remove it from here and add as last statement inside the try {} block
eg: after
cmd.ExecuteNonQuery();
conn.Close();
tbMessage.Text = "Data Saved"; 
}
catch (Exception ex)
                {

                    tbMessage.Text = ex.ToString();

                } // "Label Table Failed"
                conn.Close();
            }
        }
     
        btnSave.Enabled = false;
Graeme_Grant 21-Jan-23 0:24am    
Use:
try
{ 

}
catch
{
    tbMessage.Text = "Data Saved";
    return;
}
finally
{
    conn.Close();
}

1 solution

Graeme_Grant's suggestion of the finally block is good, but unnecessary - since you have a using block around each element, the object created in it is Disposed when you leave the block by any means, which means it is automatically Closed as well.
C#
using (type variableName = new type())
   {
   ... variableName exists here ...
   }
... variableName is out of scope here, and Dispose (and Close) have already been called on it ...


But he is right that you overwrite your error message. I'd do it like this:
C#
private void MyButton_Click(object sender, EventArgs e)
    {
    using (var conn = new SQLiteConnection($"Data Source = '{dbName}';Version=3;"))
        {
        conn.Open();
        string sql = "INSERT INTO FriendsData " +
                     "   (fxFirstName, fxLastName, fxAddress, fxCity, fxState, fxZip, fxCellPhone, fxEmail, fxInfo) " +
                     "   VALUES " +
                     "   (@fxFirstName, @fxLastName, @fxAddress, @fxCity, @fxState, @fxZip, @fxCellPhone, @fxEmail, @tfxInfo)";
        string result = "Data Saved";
        using (var cmd = new SQLiteCommand(sql, conn))
            {
            try
                {
                cmd.Parameters.AddWithValue("@fxFirstName", tbFirstName.Text.Trim());
                cmd.Parameters.AddWithValue("@fxLastName", tbLastName.Text.Trim());
                cmd.Parameters.AddWithValue("@fxAddress", tbAddress.Text.Trim());
                cmd.Parameters.AddWithValue("@fxCity", tbCity.Text.Trim());
                cmd.Parameters.AddWithValue("@fxState", tbState.Text.Trim());
                cmd.Parameters.AddWithValue("@fxZip", tbZip.Text.Trim());
                cmd.Parameters.AddWithValue("@fxCellPhone", mtbCellPhone.Text.Trim());
                cmd.Parameters.AddWithValue("@fxEmail", tbEmail.Text.Trim());
                cmd.Parameters.AddWithValue("@fxInfo", rtbInfo.Text.Trim());
                cmd.ExecuteNonQuery();
                }
            catch (Exception ex)
                {
                result = ex.ToString();
                }
            }
        }
    tbMessage.Text = result;
    btnSave.Enabled = false;
    }
 
Share this answer
 
Comments
Graeme_Grant 21-Jan-23 2:09am    
finally will work with the using.

I wrote a Sqlite wrapper lib[^] that does all of this and more already. Not sure why he wants to reinvent the wheel.... Also suggested EF Core ... wrote a sample for him to show how easy it was...
OriginalGriff 21-Jan-23 2:22am    
Yes, it will work - but it isn't needed because the nested using blocks Dispose each item in the right order, and that automatically calls Close on each item when needed.
Graeme_Grant 21-Jan-23 2:28am    
Years ago there was a memory leak, so had to manually. But now it should be fixed. Old habit.
Choroid 21-Jan-23 11:39am    
Updated the question as I am now seeing the ERROR
Will try your code and get back
If you have time please look at the ERROR
The DB is in the BIN folder where it should be and the table is in the DB
OriginalGriff 21-Jan-23 12:10pm    
How do you expect us to look at an error we cannot see, on a system we cannot access, in code we can;t run?

Start by looking at the actual error message and work from that.

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