Resolved 'SQL logic error near ")": syntax error'

rowlandsfc

Active member
Joined
Feb 3, 2019
Messages
43
Location
bridgend
Programming Experience
Beginner
i have a basic app in visual studio that is connected to a database, im trying to add a new antry into the account table but i get the error

'SQL logic error
near ")": syntax error'
for this line:
int recordsChanged = cmd.ExecuteNonQuery();

button click event:
        private void btnAdd_Click(object sender, EventArgs e)
        {

            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"INSERT INTO ccount (custid," + "prodid," + "balance," + "active,) VALUES (@setCustid, @setProdid, @setBalance, @setActive)";
                cmd.Parameters.AddWithValue("setCustid", cb_custID.Text);
                cmd.Parameters.AddWithValue("setProdid", Global.productID.ToString());
                cmd.Parameters.AddWithValue("setBalance", txtBalance.Text);
                cmd.Parameters.AddWithValue("setActive", Global.selectedActive.ToString());

                int recordsChanged = cmd.ExecuteNonQuery();
                MessageBox.Show("Account Added");
                conn.Close();

                Account account = new Account();
                account.Show();
                this.Hide();
            }
        }

i have the same code for a different table adding didfferent information and that works fine but this one doesnt so im not sure what ive done wrong


full form code:
namespace NewSQLiteCardiff
{
    public partial class Add_Account : Form
    {
        public Add_Account()
        {
            InitializeComponent();
        }

        SQLiteConnection conn;

        private void Add_Account_Load(object sender, EventArgs e)
        {
            // connects to the customers database
            try
            {
                conn = new SQLiteConnection();
                conn.ConnectionString = dbConnection.source;
                conn.Open();
            }
            catch (Exception ex)
            {
                conn.ConnectionString = dbConnection.source;
                conn.Close();

                MessageBox.Show(ex.Message);
            }

            custidCombo();
        }

        private void custidCombo()
        {
            try
            {
                conn = new SQLiteConnection(dbConnection.source);
                string sqlcommand = @"SELECT custid FROM customer";
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(sqlcommand, conn);
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    cb_custID.Items.Add(dr[0]);
                }
            }
            catch (Exception ex)
            {
                // write exception info to log or anything else
                MessageBox.Show("Error occured!");
            }
        }

        private void showCustomer()
        {
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM customer WHERE custid = '" + this.cb_custID.Text + "';";
                SQLiteDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    cb_title.Text = (read["title"].ToString());
                    txtFirst_name.Text = (read["firstname"].ToString());
                    txtSurname.Text = (read["lastname"].ToString());
                    dtp_DOB.Text = (read["dob"].ToString());
                    txtNI_Code.Text = (read["nicode"].ToString());
                    txtEmail.Text = (read["email"].ToString());
                    txtPassword.Text = (read["password"].ToString());
                    txtAllowance.Text = (read["allowance"].ToString());
                }
            }

        }

        private void cb_Accountid_SelectedIndexChanged(object sender, EventArgs e)
        {
            showCustomer();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            Account account = new Account();
            account.Show();
            this.Hide();
        }

        private void cb_Product_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(cb_Product.SelectedItem.ToString() == "Easy ISA Issue 1")
            {
                Global.interestRate = 0.03;
                Global.productID = 1;
                txtInterest.Text = Global.interestRate.ToString();

            }
            if (cb_Product.SelectedItem.ToString() == "Easy ISA Issue 2")
            {
                Global.interestRate = 0.07;
                Global.productID = 2;
                txtInterest.Text = Global.interestRate.ToString();
            }
            if (cb_Product.SelectedItem.ToString() == "Easy ISA Issue 3")
            {
                Global.interestRate = 0.03;
                Global.productID = 3;
                txtInterest.Text = Global.interestRate.ToString();
            }
            if (cb_Product.SelectedItem.ToString() == "Easy ISA Issue 4")
            {
                Global.interestRate = 0.05;
                Global.productID = 4;
                txtInterest.Text = Global.interestRate.ToString();
            }
            if (cb_Product.SelectedItem.ToString() == "Fool's Gold Account")
            {
                Global.interestRate = 0.05;
                Global.productID = 5;
                txtInterest.Text = Global.interestRate.ToString();
            }
            if (cb_Product.SelectedItem.ToString() == "Fleeced While You Watch Issue 1")
            {
                Global.interestRate = 0.005;
                Global.productID = 6;
                txtInterest.Text = Global.interestRate.ToString();
            }
            if (cb_Product.SelectedItem.ToString() == "Fleeced While You Watch Issue 2")
            {
                Global.interestRate = 0.005;
                Global.productID = 7;
                txtInterest.Text = Global.interestRate.ToString();
            }
            if (cb_Product.SelectedItem.ToString() == "Hardly Worth the Effort")
            {
                Global.interestRate = 0.02;
                Global.productID = 8;
                txtInterest.Text = Global.interestRate.ToString();
            }
            if (cb_Product.SelectedItem.ToString() == "Only for the bankers")
            {
                Global.interestRate = 0.08;
                Global.productID = 9;
                txtInterest.Text = Global.interestRate.ToString();
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {

            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"INSERT INTO ccount (custid," + "prodid," + "balance," + "active,) VALUES (@setCustid, @setProdid, @setBalance, @setActive)";
                cmd.Parameters.AddWithValue("setCustid", cb_custID.Text);
                cmd.Parameters.AddWithValue("setProdid", Global.productID.ToString());
                cmd.Parameters.AddWithValue("setBalance", txtBalance.Text);
                cmd.Parameters.AddWithValue("setActive", Global.selectedActive.ToString());

                int recordsChanged = cmd.ExecuteNonQuery();
                MessageBox.Show("Account Added");
                conn.Close();

                Account account = new Account();
                account.Show();
                this.Hide();
            }
        }
    }
}
 
You should get the same error on line 150 with that dangling to comma.
 
Solution
If you removed those extra concatenations, it would have been easier to spot:
C#:
"INSERT INTO ccount (custid,prodid,balance,active,) VALUES (@setCustid, @setProdid, @setBalance, @setActive)"
 
If you removed those extra concatenations, it would have been easier to spot:
C#:
"INSERT INTO ccount (custid,prodid,balance,active,) VALUES (@setCustid, @setProdid, @setBalance, @setActive)"
Thats how the college showed us to do it. But ill go through it and remove them tomorrow thanks
 
That's how you do it when you are laying out your code vertically:
C#:
cmd.CommandText =
    "INSERT INTO ccount (" +
         "custid," +
         "prodid," +
         "balance," +
         "active" +
    ") VALUES (" +
        "@setCustid," +
        "@setProdid," +
        "@setBalance," +
        "@setActive" +
    ")";

The intent there is that it makes it easier to insert extra columns and parameters while making the diff that is shown by the source control to be minimal. It's easier for a human to parse that a line was added or deleted, as opposed to trying to compare two lines of code with 100+ characters each trying to see what has changed.
 
Yeah, there's never a justification for concatenating two string literals unless you specifically want to break a string over two lines. Given that C# supports verbatim string literals, I'd tend to avoid any concatenation at all though:
C#:
cmd.CommandText = @"INSERT INTO ccount
                    (
                        custid,
                        prodid,
                        balance,
                        active
                    )
                    VALUES
                    (
                        @setCustid,
                        @setProdid,
                        @setBalance,
                        @setActive
                    )";
The only issue there is that it introduces some spurious whitespace in all but the first line when done as I did above. That's not an issue for SQL code, where whitespace is ignored, unless you will actually read it elsewhere. You can always start the literal on a new line to avoid that:
C#:
cmd.CommandText =
@"INSERT INTO ccount
(
    custid,
    prodid,
    balance,
    active
)
VALUES
(
    @setCustid,
    @setProdid,
    @setBalance,
    @setActive
)";
 
Back
Top Bottom