Click here to Skip to main content
15,899,679 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace SRL
{
    public partial class DAILY_REPORT : Form
    {
        OleDbConnection daily_rpt_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Chirag\documents\visual studio 2012\Projects\SRL\SRL\SRL.accdb;Persist Security Info=True");
        public DAILY_REPORT()
        {
            InitializeComponent();
        }
        private void btn_submit_Click(object sender, EventArgs e)
        {
           

            OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM daily where bill_no='" +txt_bill_no.Text+ "'", daily_rpt_con);
            DataTable dt = new DataTable();
            da.Fill(dt);

            
            try
            {
                OleDbCommand cmd1 = new OleDbCommand("select * from daily where bill_no='" + txt_bill_no.Text + "'", daily_rpt_con);
                OleDbDataReader dr1;
                daily_rpt_con.Open();
                dr1 = cmd1.ExecuteReader();


                if (dr1.Read())
                {
                    MessageBox.Show("Duplicate Entry");
                    dataGridView1.DataSource = dt;
                    dataGridView1.Visible = true;
                   
                }

                    //    else
                //    {
                //        string reg_date = dateTimePicker1.Value.ToString();
                //        string dob = dateTimePicker2.Value.ToString();
                //        OleDbCommand inst = new OleDbCommand();
                //        inst.CommandText = "insert into vehicle(registration_no,date_of_registration,chassis_no,pan_no,engine_no,date_of_birth,owner_name,owners_father,address,colony,city)values('" + textBox1.Text + "','" + reg_date + "','" + textBox2.Text + "','" + textBox9.Text + "','" + textBox3.Text + "','" + dob + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "')";
                //                     //        OleDbDataAdapter DA1 = new OleDbDataAdapter("select registration_no,pan_no,owner_name,address,colony,city from vehicle where registration_no ='" + textBox1.Text + "'", insert);
                //        DataTable Table1 = new DataTable();
                //        DA1.Fill(Table1);
                //        dataGridView1.DataSource = Table1;
                //        dataGridView1.Visible = true;
                //    }

                else
                {
                    
                    string current_date = dtp_current_date.Value.ToString();
                    string date = dtp_date.Value.ToString();
                    OleDbCommand daily_rpt_cmd = new OleDbCommand();

                    //daily_rpt_cmd.CommandText = "insert into daily(bill_no,current_date,frm_daily,todaily,date,vechile_no,destination,sip_d_no,del_no,gr_no,qty,rate,amount)values('" + txt_bill_no.Text + "','" + current_date + "','" + txt_from.Text + "','" + txt_to.Text + "','" + date + "','" + txt_vehicle_no.Text + "','" + txt_destination.Text + "','" + txt_sip_no.Text + "','" + txt_del_no.Text + "','" + txt_g_r_no.Text + "','" + txt_quantity.Text + "','" + txt_rate.Text + "','" + txt_amount.Text + "')";
                    daily_rpt_cmd.CommandText = "insert into daily(bill_no,current_date,frm_daily,todaily,date,vechile_no,destination,sip_d_no,del_no,gr_no,qty,rate,amount)values(@bill_no,@current_date,@frm_daily,@todaily,@date,@vechile_no,@destination,@sip_d_no,@del_no,@gr_no,@qty,@rate,@amount)";
                    daily_rpt_cmd.Parameters.AddWithValue("@bill_no", txt_bill_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@current_date",current_date);
                    daily_rpt_cmd.Parameters.AddWithValue("@frm_daily",txt_from.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@todaily",txt_to.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@date",date);
                    daily_rpt_cmd.Parameters.AddWithValue("@vechile_no",txt_vehicle_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@destination",txt_destination.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@sip_d_no",txt_sip_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@del_no",txt_del_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@gr_no",txt_g_r_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@qty",txt_quantity.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@rate",txt_rate.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@amount",txt_amount.Text);

                    daily_rpt_cmd.Connection = daily_rpt_con;
                    daily_rpt_cmd.ExecuteNonQuery();
                    
                    MessageBox.Show("hello");
                    MessageBox.Show("Record Submitted Successfully!");


                    OleDbDataAdapter da1 = new OleDbDataAdapter("select * from daily where bill_no='" + txt_bill_no.Text + "'", daily_rpt_con);
                    DataTable dt1 = new DataTable();
                    da1.Fill(dt1);
                    dataGridView1.DataSource = dt1;
                    dataGridView1.Visible = true;
                }

            }
            catch
            {
            }
            daily_rpt_con.Close();

           
        }

       
    }
}


What I have tried:

C#
//daily_rpt_cmd.CommandText = "insert into daily(bill_no,current_date,frm_daily,todaily,date,vechile_no,destination,sip_d_no,del_no,gr_no,qty,rate,amount)values('" + txt_bill_no.Text + "','" + current_date + "','" + txt_from.Text + "','" + txt_to.Text + "','" + date + "','" + txt_vehicle_no.Text + "','" + txt_destination.Text + "','" + txt_sip_no.Text + "','" + txt_del_no.Text + "','" + txt_g_r_no.Text + "','" + txt_quantity.Text + "','" + txt_rate.Text + "','" + txt_amount.Text + "')";
                    daily_rpt_cmd.CommandText = "insert into daily(bill_no,current_date,frm_daily,todaily,date,vechile_no,destination,sip_d_no,del_no,gr_no,qty,rate,amount)values(@bill_no,@current_date,@frm_daily,@todaily,@date,@vechile_no,@destination,@sip_d_no,@del_no,@gr_no,@qty,@rate,@amount)";
                    daily_rpt_cmd.Parameters.AddWithValue("@bill_no", txt_bill_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@current_date",current_date);
                    daily_rpt_cmd.Parameters.AddWithValue("@frm_daily",txt_from.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@todaily",txt_to.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@date",date);
                    daily_rpt_cmd.Parameters.AddWithValue("@vechile_no",txt_vehicle_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@destination",txt_destination.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@sip_d_no",txt_sip_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@del_no",txt_del_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@gr_no",txt_g_r_no.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@qty",txt_quantity.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@rate",txt_rate.Text);
                    daily_rpt_cmd.Parameters.AddWithValue("@amount",txt_amount.Text);
Posted
Updated 1-Mar-16 9:11am
v3
Comments
Herman<T>.Instance 1-Mar-16 4:15am    
What does the syntax error say?
Richard MacCutchan 1-Mar-16 4:18am    
vechile_no
Should that not be vehicle_no?
Maciej Los 1-Mar-16 15:13pm    
My virtual 5!
Bandaru Laxman 1-Mar-16 4:28am    
Can you give us the error?

1 solution

Given that you clearly know about parameterised queries - a big chunk of that code is using them - that you have switched back to string concatenation is a surprise.
Not only does it cause teh error you are seeing, but it also makes your code vulnerable to SQL Injection which means a user can damage or destroy your database just by typing in your textboxes.
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
Your problem will disappear at the same time...
 
Share this answer
 

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