ExecuteNonQuery showing error

Question

I've updated my code and have parameters set for the statements. Everything works fine the way it should except for the update statement, no error is given and it completes the else statement but it doesn't update the database. Is the update code wrong or something else in the 'else' statement? thanks

MainWindow.xaml.cs:

   using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.OleDb;
using System.Data;

namespace WpfApplication12
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {

        OleDbConnection con;
        DataTable dt;


        public MainWindow()
        {
            InitializeComponent();
            con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.Ace.Oledb.12.0; Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\MONDIAL.accdb";
            BindGrid();
        }

        private void BindGrid()
        {
            OleDbCommand cmd = new OleDbCommand();
            if (con.State != ConnectionState.Open)
                con.Open();
            cmd.Connection = con;
            cmd.CommandText = "select * from Country";
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            gvData.ItemsSource = dt.AsDataView();

            if (dt.Rows.Count > 0)
            {
                gvData.Visibility = System.Windows.Visibility.Visible;

            }
            else
            {
                gvData.Visibility = System.Windows.Visibility.Hidden;
            }
        }

        private void btnAdd_Click(object sender, RoutedEventArgs e)
        {
            OleDbCommand cmd = new OleDbCommand();
            if (con.State != ConnectionState.Open)
                con.Open();
            cmd.Connection = con;

            if (txtCode.Text != "")
            {
                if (txtCode.IsEnabled == true)
                {
                    if (txtCountryName.Text != "")
                    {
                        cmd.CommandText = "INSERT INTO Country" + "([CountryName],[Code],[Capital],[Province],[Area],[Population])" +
                            "VALUES (@CountryName, @Code, @Capital, @Province, @Area, @Population)";
                        cmd.Parameters.AddRange(new OleDbParameter[]
                        {
                            new OleDbParameter("@CountryName", txtCountryName.Text),
                            new OleDbParameter("@Code", txtCode.Text),
                            new OleDbParameter("@Capital", txtCapital.Text),
                            new OleDbParameter("@Province", txtProvince.Text),
                            new OleDbParameter("@Area", txtArea.Text),
                            new OleDbParameter("@Population", txtPopulation.Text),
                        });
                            cmd.ExecuteNonQuery();
                            BindGrid();
                            MessageBox.Show("Country Added Successfully..");
                            ClearAll();
                    } 
                    else
                    {
                        MessageBox.Show("Please add a code");
                    }
                }
                else
                {
                    DataRowView row = (DataRowView)gvData.SelectedItems[0];
                    cmd.CommandText = "UPDATE [Country] SET [CountryName] = @CountryName, [Capital ]= @Capital, [Province] = @Province, [Area] = @Area, [Population] = @Population WHERE [Code] = @Code";
                        cmd.Parameters.AddRange(new OleDbParameter[]
                        {
                            new OleDbParameter("@Code",  txtCode.Text),
                            new OleDbParameter("@CountryName", txtCountryName.Text),   
                            new OleDbParameter("@Capital", txtCapital.Text),
                            new OleDbParameter("@Province", txtProvince.Text),
                            new OleDbParameter("@Area", txtArea.Text),
                            new OleDbParameter("@Population", txtPopulation.Text),
                        });
                            cmd.ExecuteNonQuery();
                            BindGrid();
                            MessageBox.Show("Country Updated Successfully..");
                            ClearAll();
                    } 
            }
            else
            {
                MessageBox.Show("add country name");
            }
        }

             private void btnCancel_Click(object sender, RoutedEventArgs e)
        {
            ClearAll();
        }

            private void ClearAll()
             {
                 txtCountryName.Text = "";
                 txtCode.Text = "";
                 txtCapital.Text = "";
                 txtProvince.Text = "";
                 txtArea.Text = "";
                 txtPopulation.Text = "";
                 btnAdd.Content = "Add";
                 txtCode.IsEnabled = true;

             }

            private void btnEdit_Click(object sender, RoutedEventArgs e)
            {
                if (gvData.SelectedItems.Count > 0)
                {
                    DataRowView row = (DataRowView)gvData.SelectedItems[0];
                    txtCountryName.Text = row["CountryName"].ToString();
                    txtCode.Text = row["Code"].ToString();
                    txtCapital.Text = row["Capital"].ToString();
                    txtProvince.Text = row["Province"].ToString();
                    txtArea.Text = row["Area"].ToString();
                    txtPopulation.Text = row["Population"].ToString();
                    btnAdd.Content = "Update";
                    txtCode.IsEnabled = false;
                }
                else
                {
                    MessageBox.Show("please select a country from the list");
                }
            }

private void btnDelete_Click(object sender, RoutedEventArgs e)
        {
            if (gvData.SelectedItems.Count > 0)
            {



                DataRowView row = (DataRowView)gvData.SelectedItems[0];

                OleDbCommand cmd = new OleDbCommand();
                if (con.State != ConnectionState.Open)
                    con.Open();
                cmd.Connection = con;
                cmd.CommandText = "DELETE FROM [Country] WHERE [Code]= @CodeRow";
                cmd.Parameters.AddRange(new OleDbParameter[]
                    {
                        new OleDbParameter("@CodeRow", row["Code"].ToString())
                    });
                cmd.ExecuteNonQuery();
                BindGrid();
                MessageBox.Show("country Deleted Successfully...");
                ClearAll();
            }
            else
            {
                MessageBox.Show("Please Select Any country From List...");
            }
        }

        //Exit
        private void btnExit_Click(object sender, RoutedEventArgs e)
        {
            Application.Current.Shutdown();
        }
    }
}

MainWindow.xaml:

<Window x:Class="WpfApplication12.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="MainWindow" Height="1000" Width="1000">
<Window.Resources>
    <Style TargetType="TextBlock">
        <Setter Property="FontSize" Value="20"/>
        <Setter Property="Width" Value="160"/>
        <Setter Property="Margin" Value="10"/>
    </Style>
    <Style TargetType="TextBox">
        <Setter Property="FontSize" Value="20"/>
        <Setter Property="Width" Value="250"/>
        <Setter Property="Margin" Value="10"/>
    </Style>
</Window.Resources>
<DockPanel Name="dockMain" VerticalAlignment="Top" HorizontalAlignment="Center" LastChildFill="False">
    <StackPanel>
        <WrapPanel>
            <TextBlock Text="Country Name"/>
            <TextBox Name="txtCountryName"/>
        </WrapPanel>
        <WrapPanel>
            <TextBlock Text="Code"/>
            <TextBox Name="txtCode"/>
        </WrapPanel>
        <WrapPanel>
            <TextBlock Text="Capital"/>
            <TextBox Name="txtCapital"/>
        </WrapPanel>
        <WrapPanel>
            <TextBlock Text="Province"/>
            <TextBox Name="txtProvince"/>
        </WrapPanel>
        <WrapPanel>
            <TextBlock Text="Area"/>
            <TextBox Name="txtArea"/>
        </WrapPanel>
        <WrapPanel>
            <TextBlock Text="Population"/>
            <TextBox Name="txtPopulation"/>
        </WrapPanel>
        <WrapPanel Margin="0" HorizontalAlignment="Center" Height="59">
            <Button Name="btnAdd" Content="Add" FontSize="25" Width="120" Margin="5" Click="btnAdd_Click" />
            <Button Name="btnEdit" Content="Edit" FontSize="25" Width="120" Margin="5" Click="btnEdit_Click" />
            <Button Name="btnDelete" Content="Delete" FontSize="25" Width="120" Margin="5" Click="btnDelete_Click" />
            <Button Name="btnCancel" Content="Cancel" FontSize="25" Width="120" Margin="5" Click="btnCancel_Click" />
            <Button Name="btnExit" Content="Exit" FontSize="25" Width="120" Margin="5" Background="#400000" Foreground="Bisque" Click="btnExit_Click" />
        </WrapPanel>
        <ScrollViewer ScrollViewer.VerticalScrollBarVisibility="Auto">
            <WrapPanel Margin="20" HorizontalAlignment="Center">
                <DataGrid AutoGenerateColumns="True" Name="gvData" IsReadOnly="False" SelectionMode="Single" FontSize="15" Padding="5" Background="Black" Height="525" Width="775" />
            </WrapPanel>
        </ScrollViewer>
    </StackPanel>
</DockPanel>

On which line exactly? And you should **always** use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. — Soner Gönül, Dec 18 '15 at 11:33
Did you _read_ the exception? Anyway you're missing quotes around area, amongst others. Basic debugging would've shown this. Set breakpoints, inspect your variables. — CodeCaster, Dec 18 '15 at 11:36
@SonerGönül when i try to insert new data it creates the error on cmd.ExecuteNonQuery under the 'insert into country' statement and the same for update it appears on the nonquery under that statement also the same for the delete statement — abr, Dec 18 '15 at 11:37
@CodeCaster just added the quotes and it still gives the same exception — abr, Dec 18 '15 at 11:40
Yeah because that's not the only place where you made that mistake. I'm not going to point them all out for you. Set a breakpoint, inspect your query string. And **use parameterized queries instead**. — CodeCaster, Dec 18 '15 at 11:41
@abr If you were used parameterized statements, you wouldn't get this exception _at all_. — Soner Gönül, Dec 18 '15 at 11:43
@SonerGönül not sure what they are. i'll have a read up now. Do you have any sources that are good for wpf novices to look at? — abr, Dec 18 '15 at 11:46
@abr see [Using parameters in SQL statements](http://stackoverflow.com/questions/7505808/using-parameters-in-sql-statements). :) — CodeCaster, Dec 18 '15 at 12:00
@CodeCaster after some reading i have updated the coding and it works fine with the parameters in place apart from the update query. which it completes but doesn't change any records in the database. Any ideas? — abr, Dec 18 '15 at 15:49
@abr are you sure that insert and delete work? Depending on the provider (yeah, now it's relevant) you may or may not have support for named parameters. You may need to use `?` instead of `@paramname` and add the parameters in the proper order. Anyway that's a new question. — CodeCaster, Dec 18 '15 at 15:53
@CodeCaster all sorted... had the code parameter in the wrong place. cheers for your help — abr, Dec 18 '15 at 16:09

Leonardo Spina · Accepted Answer · 2015-12-18T11:52:14.503

You miss some delimiters in your fields values when concatenating the SQL statement, like

VALUES(" + txtCountryName.Text + ",

should be

VALUES('" + txtCountryName.Text + "',

Check the other ones accordingly to their types in the database, also the "update" query seems to suffer the same issue.

BTW, you should NEVER concatenate values in a SQL statement like you're doing, this exposes your application to a common vulnerability known as SQL Injection, as well as poor performance. Use parameters instead. Take a look at here:

http://www.csharp-station.com/Tutorial/AdoDotNet/Lesson06

ExecuteNonQuery showing error

1 Answers1