updating datagrid in c' using oledb

Question

Hi all

im trying to update a datagrid table and below is the code i have been using but every time i run it i get an error:

Error:

System.NullReferenceException was unhandled

HResult=-2147467261

Message=Object reference not set to an instance of an object.

Source=WindowsFormsApplication6

private void button6_Click(object sender, EventArgs e)

{

string sql = "Insert into [Sheet1$](first,last) Values(`" + textBox1.Text + "`, `" + textBox2.Text + "`)";

connect.Open();

OleDbDataAdapter adap = new OleDbDataAdapter(sql, connect);

adap.SelectCommand.ExecuteNonQuery();

adap.Dispose();

connect.Close();

connect.Dispose();

MessageBox.Show("Saved");

}

Imtiyaz Ansari · Answer

HI george alabi behind update button :- for (int i = 0; i { obj.OpenConnection(); String str2 = update Damage_Product set Bill_Date1=' + Convert.ToDateTime(dtpdate.Text) + 'Product_Group=' + dataGridView2.Rows[i].Cells[1].Value + 'Product_Type=' + dataGridView2.Rows[i].Cells[2].Value + 'Size1=' + dataGridView2.Rows[i].Cells[3].Value + 'Prize1=' + dataGridView2.Rows[i].Cells[4].Value + 'Qty1= + Convert.ToDouble(dataGridView2.Rows[i].Cells[5].Value) + Amount1= + Convert.ToDouble(dataGridView2.Rows[i].Cells[6].Value) + Firm=' + dataGridView2.Rows[i].Cells[7].Value + 'Total_Amount= + Convert.ToDouble(dataGridView2.Rows[i].Cells[8].Value) + Gorss_Amount1= + Convert.ToDouble(txtgrossamount.Text) + Net_Amount1= + Convert.ToDouble(txtnetamount.Text) + Pay_Amount1= + Convert.ToDouble(txtpayamount.Text) + Balance_Amount1= + Convert.ToDouble(txtbalance.Text) +  where Bill_No1=' + txtbillno.Text + '; Cmd = new OleDbCommand(str2 obj.Con); Cmd.ExecuteNonQuery(); and use where condition for grid id that is better ......

george alabi · Answer

Hi again i tried to emulate the update code you posted i did everything correctly not really sure why it is not working; private void button6_Click_3(object sender EventArgs e) { connect = new OleDbConnection(connectionstring); connect.Open(); txt = UPDATE sheet1 SET id=@idfirst=@firstLast=@Lastgrade=@gradeemail=@email Where id=@id; command = new OleDbCommand(txt connect); command.Parameters.AddWithValue(@id textBox5.Text); command.Parameters.AddWithValue(@first textBox1.Text); command.Parameters.AddWithValue(@Last textBox2.Text); command.Parameters.AddWithValue(@grade textBox3.Text); command.Parameters.AddWithValue(@email textBox4.Text); command.ExecuteNonQuery(); //command.CommandType = CommandType.StoredProcedure; MessageBox.Show(done); connect.Close(); loaddata(); i keep getting error: Syntax error in UPDATE statement.

george alabi · Answer

i realised that i declared two 'connects' one in the main body and the other in the button6 method. will correct it now and hope it works. thanks for the reply

george alabi · Answer

Thanks for the reply i will have a look at code...

Vulpes · Answer

The only thing I can think of which may be null is your OleDbConnection object connect which is defined outside the button6_Click method. So are you sure that 'connect' has already been initialized when you click the button?

Satyapriya Nayak · Answer

Form1.cs using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace Insert_Update_Delete_DataGridView { public partial class Form1 : Form { string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings[dsn]; OleDbCommand com; OleDbDataAdapter oledbda; string str; DataSet ds; public Form1() { InitializeComponent(); } private void Form1_Load(object sender EventArgs e) { bindgrid(); } private void bindgrid() { OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = select * from employee; com = new OleDbCommand(str con); oledbda = new OleDbDataAdapter(com); ds = new DataSet(); oledbda.Fill(ds employee); dataGridView1.DataMember=employee; dataGridView1.DataSource = ds; con.Close(); } private void Clear() { txt_sno.Text = ; txt_name.Text = ; txt_address.Text = ; txt_age.Text = ; } private void btn_edit_Click(object sender EventArgs e) { int i; i = dataGridView1.SelectedCells[0].RowIndex; txt_sno.Text = dataGridView1.Rows[i].Cells[1].Value.ToString(); txt_name.Text = dataGridView1.Rows[i].Cells[2].Value.ToString(); txt_address.Text = dataGridView1.Rows[i].Cells[3].Value.ToString(); txt_age.Text = dataGridView1.Rows[i].Cells[4].Value.ToString(); } private void btn_update_Click(object sender EventArgs e) { OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = Update employee set sno=@snoname=@nameaddress=@addressage=@age Where sno=@sno; com = new OleDbCommand(str con); com.Parameters.AddWithValue(@sno txt_sno.Text); com.Parameters.AddWithValue(@name txt_name.Text); com.Parameters.AddWithValue(@address txt_address.Text); com.Parameters.AddWithValue(@age txt_age.Text); com.ExecuteNonQuery(); MessageBox.Show(Record Successfully Updated); con.Close(); bindgrid(); Clear(); } private void btn_delete_Click(object sender EventArgs e) { OleDbConnection con = new OleDbConnection(ConnectionString); com = new OleDbCommand(); if (dataGridView1.Rows.Count > 1 && dataGridView1.SelectedRows[0].Index != dataGridView1.Rows.Count - 1) { com.CommandText = DELETE FROM employee WHERE ID= + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + ; con.Open(); com.Connection = con; com.ExecuteNonQuery(); con.Close(); dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index); MessageBox.Show(Record Successfully Deleted); } bindgrid(); } private void btn_insert_Click(object sender EventArgs e) { OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = Insert into employee(snonameaddressage) Values (@sno@name@address@age); com = new OleDbCommand(str con); com.Parameters.AddWithValue(@sno txt_sno.Text); com.Parameters.AddWithValue(@name txt_name.Text); com.Parameters.AddWithValue(@address txt_address.Text); com.Parameters.AddWithValue(@age txt_age.Text); com.ExecuteNonQuery(); con.Close(); MessageBox.Show(Record Successfully Inserted); bindgrid(); Clear(); } } }