How to insert numeric value in access table

Question

0.00/5 (No votes)

See more:

Hi all,

Last 20 days im trying to insert Numeric value into access database but i unable to insert it remaining two string value is inserted successfully but this numeric i unable to inserted all details im inserting using datagridview and code written on dataGridView1_RowLeave event

my table structure is
Med_id----> AutoNumber
Medicine_Name----> Memo
Dealer_name---->Text
Availability-----> Number

pls friends help me im totally tired now to do various RnD

What I have tried:

C#

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

           }


        }

Load_data()

C#

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();
          OleDbDataAdapter adapater = new OleDbDataAdapter(cmd);
          adapater.Fill(dt);
          dataGridView1.DataSource = dt;

      }

Posted 28-Jul-16 21:30pm

Atul Rokade

Updated 1-Aug-16 20:07pm

v2

Add a Solution

Comments

OriginalGriff 29-Jul-16 3:47am

And?
What happens when you use that code?

Atul Rokade 29-Jul-16 4:38am

its showing nothing i used degugger also but its showing me null value when debuger go at that code

OriginalGriff 29-Jul-16 4:42am

Where is it showing the null?

Atul Rokade 29-Jul-16 4:44am

@OriginalGriff : sir i improved the question

Atul Rokade 29-Jul-16 6:02am

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

Atul Rokade 29-Jul-16 5:50am

Sir can you help me how can i store integer ? any code or any suggestion ?

Michael_Davies 29-Jul-16 3:59am

You do not name the medicine_name or dealer_name parameters in your insert string, you use ? for both of those then name the availability.

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

Try

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

Even if it does not resolve the issue it makes it more readable.

Atul Rokade 29-Jul-16 4:44am

@Michael_Davies : sir i improved the question , but still same error

Michael_Davies 29-Jul-16 5:03am

Looks the same. Alter the code, try it, then update.

Atul Rokade 29-Jul-16 5:41am

not getting sir

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-07-28T23:58:00

Solution 1

"i reomve now try catch now its showing me exception Object cannot be cast from DBNull to other types."
And that's the problem: if your data doesn't contain a value, there isn't anything to insert into your table.
You can get round it by inserting a "default value":

C#

object o = dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value;
int availability = o == DBNull.Value ? -1 : (int) o;

But what you really need to do is look at your data source and find out why your DB contains a null in a field that you expect to contain an integer. Until you can sort that out this problem is going to recur.
We can't do that for you - we don't have access to your data!

Posted 28-Jul-16 23:58pm

OriginalGriff

Comments

Atul Rokade 29-Jul-16 6:06am

OrginalGriff : sir when i used your code
object o = dataGridView1.Rows[e.RowIndex].Cells["Availability"].Value;
int availability = o == DBNull.Value ? -1 : (int)o;
cmd.Parameters.AddWithValue("@Availability", o);

its giving me You must enter a value in the 'Medicine_Available_Detail.Availability' field. exception on int n = cmd.ExecuteNonQuery(); line

OriginalGriff 29-Jul-16 6:14am

:sigh:
You are supposed to think, not just blindly replace code...
Why do you think I converted it to an integer?

Atul Rokade 29-Jul-16 6:12am

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

OriginalGriff 29-Jul-16 6:16am

Your "new" DB table requires a non-null value in the Availabity column, yes?
Your old table contains null values. So either you need to fix your old table, or decide on a "sensible" thing to do with you get a null. I can't do that for you!

Atul Rokade 29-Jul-16 6:19am

ok @OriginalGriff sir actually i tired various type but still failed so thats why i frustrated

Atul Rokade · Answer 2 · 2016-08-01T20:07:00

Here i got a solution and finely inserting and updating numeric value

C#

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

}
}