How to insert integer value in access database?

Atul Rokade

I am trying to insert Integer value into MS-Access database by using datagridview, but other string values are entered successfully but integer is not. I use debugger also but when debugger come to that line its showing me null value. In my database I use Memo as datatype for Availability, here is my code and possible snapshot

private void dataGridView1_RowLeave(object sender, DataGridViewCellEventArgs e)

{

try

{

if (dataGridView1.IsCurrentRowDirty)

{

string connectionString = null;

connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

con.ConnectionString = connectionString;

string cmd1 = "insert into Medicine_Available_Detail(Medicine_Name,Dealer_name,Availability) values(?,?,@Availability)";

OleDbCommand cmd = new OleDbCommand(cmd1, con);

cmd.CommandType = CommandType.Text;

string Medicine_Name = dataGridView1.Rows[e.RowIndex].Cells["Medicine_Name"].Value.ToString();

cmd.Parameters.AddWithValue("@Medicine_Name", Medicine_Name);

string Dealer_name = dataGridView1.Rows[e.RowIndex].Cells["Dealer_name"].Value.ToString();

cmd.Parameters.AddWithValue("@Dealer_name", Dealer_name);

string Availability = dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value.ToString();

cmd.Parameters.AddWithValue("@Availability", Availability);

con.Open();

int n = cmd.ExecuteNonQuery();

con.Close();

if (n > 0)

{

MessageBox.Show("Data Inserted Successfully", "Data Inserted ", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

catch (Exception ex)

{

Load_data();

dataGridView1.Refresh();

}

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vinay Singh10y ago
Hi
Try by passing an interger value. if it's saved success fully then check where the value is becoming null.
0
Atul Rokade10y ago
Hi all,
Finally got a solution on this i change my codedataGridView1_RowLeave todataGridView1_CellValueChanged and its working fine
private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
med_id = dataGridView1.Rows[e.RowIndex].Cells["Med_id"].Value.ToString();
if (med_id == "")
{
med_id1 = 0;
}
else
{
med_id1 = Convert.ToInt32( dataGridView1.Rows[e.RowIndex].Cells["Med_id"].Value.ToString());
}
if (med_id1 == 0)
{
try
{
string Medicine_Name = dataGridView1.Rows[e.RowIndex].Cells["Medicine_Name"].Value.ToString();
string Dealer_name = dataGridView1.Rows[e.RowIndex].Cells["Dealer_name"].Value.ToString();
int Availability = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value.ToString());
string cmd1 = "insert into Medicine_Available_Detail(Medicine_Name,Dealer_name,Availability) values(@Medicine_Name,@Dealer_name,@Availability)";
OleDbCommand cmd = new OleDbCommand(cmd1, con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Medicine_Name", Medicine_Name);
cmd.Parameters.AddWithValue("@Dealer_name", Dealer_name);
cmd.Parameters.AddWithValue("@Availability", Availability);
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("Data Inserted Successfully", "Data Inserted ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
Load_data();
dataGridView1.Refresh();
}
catch (Exception ex)
{
}
}
else
{
string Medicine_Name = dataGridView1.Rows[e.RowIndex].Cells["Medicine_Name"].Value.ToString();
string Dealer_name = dataGridView1.Rows[e.RowIndex].Cells["Dealer_name"].Value.ToString();
int Availability = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value.ToString());

cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd = con.CreateCommand();
cmd.CommandText = "update Medicine_Available_Detail set Medicine_Name='" + dataGridView1.Rows[e.RowIndex].Cells["Medicine_Name"].Value.ToString() + "',Dealer_name='" + dataGridView1.Rows[e.RowIndex].Cells["Dealer_name"].Value.ToString() + "',Availability='" + Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value.ToString())+ "'where Med_id=" + med_id1 + "";
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("Data Updated Successfully", "Data Inserted ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
Load_data();
dataGridView1.Refresh();
}
}
0
Atul Rokade10y ago
whenever i run program now its giving me
Operation is not valid because it results in a reentrant call to the SetCurrentCellAddressCore function. exception
public void Load_data()
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
string connectionString = null;
connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
con.ConnectionString = connectionString;
using (con = new OleDbConnection(connectionString))
{
using (cmd = new OleDbCommand("select Medicine_Name,Dealer_name,Availability from Medicine_Available_Detail", con))
{
cmd.CommandType = CommandType.Text;
using (OleDbDataAdapter oda = new OleDbDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
oda.Fill(dt);
dataGridView1.DataSource = dt;// this line
}
}
}
}

}
and @manas that code also not work yaar
0
Manas Mohapatra10y ago
Try to use like below, do not mix up ? and parameterized:

string cmd1 = "insert into Medicine_Available_Detail(Medicine_Name,Dealer_name,Availability) values(@Medicine_Name,@Dealer_name,@Availability)";

cmd.Parameters.AddRange(new OleDbParameter[]
{
   new OleDbParameter("@Medicine_Name", Medicine_Name),
   new OleDbParameter("@Dealer_name", Dealer_name),
   new OleDbParameter("@Availability", Availability)
});
Make sure that you are getting correct value in Availability variable.
http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database
0
Atul Rokade10y ago
int Availability;

bool accountHasValue = int.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value.ToString(), out Availability);

if (accountHasValue)
{
cmd.Parameters.AddWithValue("@Availability", Availability);
}
else
{
cmd.Parameters.AddWithValue("@Availability", DBNull.Value);
}
this code i used another application for datagridview its working fine but same code i pasted to this application is go cmd.Parameters.AddWithValue("@Availability", DBNull.Value);
0
Atul Rokade10y ago
hi manas
i change datatype integer in database and code also but still i unable to store integer value
updated code:
try
{

if (dataGridView1.IsCurrentRowDirty)
{
string connectionString = null;
connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
con.ConnectionString = connectionString;

string cmd1 = "insert into Medicine_Available_Detail(Medicine_Name,Dealer_name,Availability) values(?,?,@Availability)";
OleDbCommand cmd = new OleDbCommand(cmd1, con);

cmd.CommandType = CommandType.Text;

string Medicine_Name = dataGridView1.Rows[e.RowIndex].Cells["Medicine_Name"].Value.ToString();
cmd.Parameters.AddWithValue("@Medicine_Name", Medicine_Name);

string Dealer_name = dataGridView1.Rows[e.RowIndex].Cells["Dealer_name"].Value.ToString();
cmd.Parameters.AddWithValue("@Dealer_name", Dealer_name);
//i reomve now try catch now its showing me exception Object cannot be cast from DBNull to other types. on below line
int Availability =Convert.ToInt32 (dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value);//this line
cmd.Parameters.AddWithValue("@Availability", Availability);
// i event try below code also but debugger always go else
//int Availability = 0;
//bool total_availablehasvalue = int.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value.ToString(), out Availability);
//if (total_availablehasvalue)
//{
// cmd.Parameters.AddWithValue("@Availability", Availability);
//}
//else
//{
// cmd.Parameters.AddWithValue("@Availability", DBNull.Value);
//}
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{

MessageBox.Show("Data Inserted Successfully", "Data Inserted ", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

}
}
catch (Exception ex)
{

Load_data();
dataGridView1.Refresh();

}

}
0
Manas Mohapatra10y ago
A Memo field can be useful for storing large amounts alphanumeric information. Some typical uses for this data type would be a note, comments, description, or address field.

However, you are storing integer which is not going to work. Use Tinyint or Smallint instead.
0