Numeric value insert as 0 in accessdatabase

Atul Rokade

Iam creating one application where data inserted into access table using datagridview but issue is two value(s) are inserted correctly but when im trying to insert numeric value its inserted as 0

private void dataGridView1_RowLeave(object sender, DataGridViewCellEventArgs e)

{

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],[total_available])values(?,?,?)";

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);

int Availability=0;

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

if (total_availablehasvalue)

{

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

}

else

{

cmd.Parameters.AddWithValue("@total_available", DBNull.Value);

}

13 Replies

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

Atul Rokade10y ago
Dear Pravin
Its a C#.net its a windows application friend
0
Praveen Sreeram10y ago
Hi Atul,
Please provide the .ASPX code as well.
Thanks,
Prawin
0
Atul Rokade10y ago
hi pravin
here is a code
Method which is bind datagridview
public void Load_data()
{

OleDbCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Medicine_Name,Dealer_name,Availability from Medicine_Available_Detail";

DataTable dt = new DataTable();
con.Open();
OleDbDataAdapter adapater = new OleDbDataAdapter(cmd);
adapater.Fill(dt);
cmd.ExecuteNonQuery();
con.Close();

dataGridView1.DataSource = dt;

}
here is a insert code :
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(?,?,?)";
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);
//int Availability = 0;
//Availability = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells["total_available"].Value.ToString());
//if (Availability > -1)
//{
// cmd.Parameters.AddWithValue("@total_available", Availability);
//}
//else
//{
// cmd.Parameters.AddWithValue("@total_available", DBNull.Value);
//}
int sum = 0;
for (int x = 0; x < dataGridView1.Rows.Count; x++)
{
sum += Convert.ToInt32(dataGridView1.Rows[x].Cells["Availability"].Value);
cmd.Parameters.AddWithValue("@Availability", sum);
}

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();
}
}
table is created in ms access , table structure is
Med_id (pk) , autonumber
Medicine_Name,Text
Dealer_name, Text
Availability, Number
0
Praveen Sreeram10y ago
Hi Atul,
The problem is dataGridView1.Rows[x].Cells["Availability"].Value doesn't have any value. rather it's returing null and so you are facing problems. Is it possible to share the code so that we can try to help you out?
0
Atul Rokade10y ago
Hi vinay ,
Following code im used
int sum = 0;
for (int x = 0; x < dataGridView1.Rows.Count; x++)
{
sum += Convert.ToInt32(dataGridView1.Rows[x].Cells["Availability"].Value);
cmd.Parameters.AddWithValue("@Availability", sum);
}
but its giving me Object cannot be cast from DBNull to other types. onsum += Convert.ToInt32(dataGridView1.Rows[x].Cells["Availability"].Value); line
0
Vinay Singh10y ago
Hi
Try the below code
intsum=0;
for(intx=0;x
{
sum+=Convert.ToInt32(dataGridView1.Rows[x].Cells["Column"].Value);
}
0
Praveen Sreeram10y ago
Please share the code so that I can try on my machine.
0
Atul Rokade10y ago
brother i change code also
int Availability;
bool Availability1 = Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value.ToString(), out Availability);
if (Availability1)
{
cmd.Parameters.AddWithValue("@Availability", Availability);
}
but still same issue
0
Praveen Sreeram10y ago
That means, you are not getting the value that have entered. It's returning a space and so you are getting an exception..
Please double check if you are referring the same column?? If you are sure..then as an alternate.. please create a new column in the grid and use that in your code.. and see whats happening..
The reason I am suggesting this is because..the other two columns are working fine and the problem is only with the total_available column.. So, There seems to be a problem with thetotal_available column..
Thanks,
Prawin
0
Atul Rokade10y ago
dear pravin
its showing me Input string was not in a correct format exception
0
Praveen Sreeram10y ago
Okay. Replace the following code
intAvailability=0;
booltotal_availablehasvalue=int.TryParse(dataGridView1.Rows[e.RowIndex].Cells["total_available"].Value.ToString(),outAvailability);
if(total_availablehasvalue)
{
cmd.Parameters.AddWithValue("@total_available",Availability);
}
else
{
cmd.Parameters.AddWithValue("@total_available",DBNull.Value);
}
with the following
intAvailability=0;
Availability=Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells["total_available"].Value.ToString());
if(Availability>-1)
{
cmd.Parameters.AddWithValue("@total_available",Availability);
}
else
{
cmd.Parameters.AddWithValue("@total_available",DBNull.Value);
}
and debug what value you are getting in the Availability variable and let me know if you still getting 0 or any error.
The only problem with parseInt is it will just return 0 if there is any exception.
Thanks,
Prawin
0
Atul Rokade10y ago
its showing me 0 only freind
0
Praveen Sreeram10y ago
Hi Atul,
It looks like the conversion of the value from textbox to integer usingTryParse is getting failed. Please debug and see what value you are getting in the dataGridView1.Rows[e.RowIndex].Cells["total_available"].Value.ToString()
Hope it helped,
Thanks,
Prawin
0