如何在访问表中插入数值 [英] How to insert numeric value in access table

问题描述

大家好，



过去20天我试图将数值插入访问数据库但我无法插入剩余的两个字符串值已成功插入但此数字我无法插入使用datagridview插入的所有细节和在dataGridView1_RowLeave事件上编写的代码



我的表结构是

Med_id ----> ;自动编号

Medicine_Name ---->备注

Dealer_name ---->文字

可用性----->号码



请各位朋友帮我吧我现在已经累了做各种RnD



我是什么尝试过：

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:

 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（）

Load_data()

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;

      }

推荐答案

我现在尝试抓住它现在让我看到它exception无法将对象从DBNull强制转换为其他类型。

这就是问题所在：如果您的数据不包含值，则表中没有任何内容可以插入。

您可以通过插入默认值来绕过它：

"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":

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

但你真正需要做的是查看你的数据源并找出为什么您的数据库在您希望包含整数的字段中包含null。直到你能解决这个问题才会重复出现。

我们不能为你做到这一点 - 我们无权访问你的数据！

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!

这里我得到了一个解决方案并精确插入和更新数值

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

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

}
}

这篇关于如何在访问表中插入数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！