How to insert data in single column(cell) of new row in c#?

Question

I have an .accdb file with four tables in it

Product_Particulars
Cust_Details
Variable_fields
Permanant_fields

The number of column in the 'Variable_fields' table is not fixed (changed using 'ALTER TABLE' OleDb nonQuery). But it has two fixed columns 'Tranx_ID', 'Tranx_time'.

I want to accomplish something that will enable me to add data in the 'Tranx_ID' Column in a new row from a textBox without caring about other columns in the table (i.e. other cells in that row, in which the 'textBox.Text' is attempted to insert) and save the row with data in only one cell.

N.B.: I am actually using OleDb & I will use the 'Tranx_ID' for Updating that particular row using an OleDbCommand like,

"UPDATE Variable_fields " +
"SET [This column]='" +thistxtBx.Text +
     "',[That column]='" +thattxtBx.Text +
 "'WHERE ([Tranx_ID]='" +textBox.Text+ "')";

Though I have googled to get solution of my problem, it may be I have asked something 'stupid', So do not hesitate to downvote my question, but please, please help me to get out of this peoblem. Any kind of help, code snippet, link all are welcome. :( — Rajib Majumdar, Jul 28 '16 at 18:01
And what doesn't work in your proposed solution? Did you try it? — Steve, Jul 28 '16 at 18:02
Hi steve, the problem is that, whenever I try to insert textBox.Text in Tranx_ID using oledbcommand like "INSERT INTO Variable_fields VALUES ("+textBox.Text+")"; — Rajib Majumdar, Jul 28 '16 at 18:26
Hi steve, the problem is that, whenever I try to insert textBox.Text in Tranx_ID using oledbcommand like "INSERT INTO Variable_fields VALUES ("+textBox.Text+")"; an exception fires up saying "No value given for one or more required parameters". I know I am not providing any data about what to do with other cells, but I have not found any other way to so. — Rajib Majumdar, Jul 28 '16 at 18:35
This exception comes if any of the 'forgotten' columns has been defined to NOT ALLOW null values. In this scenario you are out of luck. You need to provide a value for that column or change the definition of the column to allow null values. — Steve, Jul 28 '16 at 19:34
Ok, Steve if I allow every other fields to allow NULL VALUE, then what would be the proper oledbcommand(SYNTAX) to insert textBox.Text in Tranx_ID column of Variable_fields table without saying anything about what to do with other columns? Is that possible at all? — Rajib Majumdar, Jul 29 '16 at 00:38
If it is possible then please post the OleDbCommand syntax for execution of successful NonQueary, as answer, I would like to MARK it as ANSWER, as it would solve my problem. :) — Rajib Majumdar, Jul 29 '16 at 00:49

score 0 · Accepted Answer · edited May 23 '17 at 12:22

The exception is caused by the fact that one or more of the columns that you don't insert cannot have NULL as value. If you can remove this flag and allow a null value then your INSERT could work or not for other reasons.

Indeed you use a string concatenation to build your query and this is a well known source of bugs or a vector for an hacking tecnique called Sql Injection (I really suggest you to document yourself about this nasty problem)

So your code could be the following

string query = @"UPDATE Variable_fields 
                 SET [This column]= @this,
                     [That column]=@that
                 WHERE ([Tranx_ID]=@trans";
using(OleDbConnection con = new OleDbConnection(....constringhere....))
using(OleDbCommand cmd = new OleDbCommand(query, con))
{
   con.Open();
   cmd.Parameters.Add("@this", OleDbType.VarWChar).Value = thisTextBox.Text;
   cmd.Parameters.Add("@that", OleDbType.VarWChar).Value = thatTextBox.Text;
   cmd.Parameters.Add("@trans", OleDbType.VarWChar).Value = transTextBox.Text;
   int rowsInserted = cmd.ExecuteNonQuery();
   if(rowsInserted > 0)
       MessageBox.Show("Record added");
   else
       MessageBox.Show("Record NOT added");
}

Helpful links:

Sql Injection explained
Give me parameterized query or give me death
Using statement

Very sorry! Steve, but probably I could not make my problem clear enough, I said I WILL use tranx_ID to update my database, but before I do that, I need to insert Tranx_ID value in a new row & save it. :) & I was failing to do that (INSERTing value in a single column i.e. Tranx_ID Column, in a new row). However, I must thank you for the problems that you mentioned which may arise in the next step, and also provided it's Solution. :) — Rajib Majumdar, Jul 29 '16 at 13:35
To insert you just need to change the query string to _INSERT INTO variable_fieds (trans_is, this_column, that_column) VALUES (@trans, @this, @that)_ all the code is same as above — Steve, Jul 29 '16 at 17:01

How to insert data in single column(cell) of new row in c#?

1 Answers1