logo标签列表

在C#中向Microsoft Access数据库插入记录

c#ms-access
5

我正在使用C#向Access 2000-2003文件格式的数据库插入数据。当我的数据库只有两个字段时，查询是正常工作的，但是当有更多字段时，它就不起作用了。

我对两者都采用了相同的代码，但是我无法找到问题所在。

using System.Data.OleDb;    // By using this namespace I can connect to the Access Database.

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private OleDbConnection myconn;
        public Form1()
        {
            InitializeComponent();
            myconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\leelakrishnan\Desktop\NewManageContacts.mdb");
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'newManageContactsDataSet.Contacts' table. You can move, or remove it, as needed.
           // this.contactsTableAdapter.Fill(this.newManageContactsDataSet.Contacts);
            // TODO: This line of code loads data into the 'newManageContactsDataSet.Contacts' table. You can move, or remove it, as needed.
            this.contactsTableAdapter.Fill(this.newManageContactsDataSet.Contacts);

        }


        private void button1_Click(object sender, EventArgs e)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.Text;
           // string query = "insert into Contacts (fname,lname,llnum,mobnum,e-mail,street,city,country) values ('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "')";
            cmd.CommandText = @"insert into Contacts (fname,lname,llnum,mobnum,e-mail,street,city,country) values ('" + textBox1.Text + "','" + textBox2.Text +  "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "')";
            cmd.Connection = myconn;
            myconn.Open();
            cmd.ExecuteNonQuery();
            System.Windows.Forms.MessageBox.Show("User Account Succefully Created", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
            myconn.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox6.Text = "";
            textBox7.Text = "";
            textBox8.Text = "";

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

    }
}

这是只有两个字段的表格的代码

public partial class Form1 : Form
{
    private OleDbConnection myCon;
    public Form1()
    {
        InitializeComponent();
        myCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\leelakrishnan\Desktop\Database1.mdb");
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'database1DataSet.Table1' table. You can move, or remove it, as needed.
        this.table1TableAdapter.Fill(this.database1DataSet.Table1);

    }

    private void button1_Click(object sender, EventArgs e)
    {
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into Table1 (name,fname) values ('" + textBox1.Text + "','" + textBox2.Text + "')";
        cmd.Connection = myCon;
        myCon.Open();
        cmd.ExecuteNonQuery();
        System.Windows.Forms.MessageBox.Show("User Account Succefully Created", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
        myCon.Close();

    }

    private void button2_Click(object sender, EventArgs e)
    {
        textBox1.Text = "";
        textBox2.Text = "";
    }
}
1
SQL注入漏洞严重，应使用参数化查询代替直接从用户输入拼接SQL字符串。 - Thomas
@thomas 你能给个例子吗？为什么只有两个字段的表没有问题？ - Beginner
你能发一下你得到的错误吗？另外，你想插入到联系人中的值包含单引号（即'）吗？ - Sem Vanmeenen
“它不工作”是什么意思？你是否遇到了异常？如果是，请提供异常详细信息。将来，您应该尝试发布有关您的问题的具体问题，而不是大量的代码。 - Justin
@user561730 - 正如其他人所提到的，我们需要更多信息来回答有关错误的问题。至于SQL注入，您的查询应该像这样：insert into Table1 (name,fname) values (@name, @fname)，然后您调用 cmd.AddParameterWithValue("@name", textbox1.Text); 以及类似的内容为 fname。永远不要直接将用户输入的值连接到SQL语句中。 - Thomas
我在插入语句中遇到了语法错误。 - Beginner
4个回答
5
您正在尝试插入的额外字段可能具有无法轻松连接成有效SQL语句的值。例如：
string field1 = "meh";
string field2 = "whatever";
string field3 = "'Ahoy!' bellowed the sailor.";
var cmd = new SqlCommand(
    "INSERT INTO blah (x, y, z) VALUES ('" + field1 + "', '" + field2 + "', '" + field3 + '")");

想象一下，根据上述输入，连接后的SQL语句会是什么样子。

更糟糕的是，想象一下如果有人在您的表单中输入了这个内容，您将要执行的SQL语句将会是什么：

field3 = "Bobby'); DROP TABLE Users; -- ";

使用参数化查询，通过 cmd.Parameters.Add 或 AddRange（这里有描述 here）。以上示例可以修改如下：

var cmd = new SqlCommand("INSERT INTO blah (x, y, z) VALUES (@x, @y, @z)");
cmd.Parameters.AddRange(new[] {
    new SqlParameter("@x", field1),
    new SqlParameter("@y", field2),
    new SqlParameter("@z", field2)
    });
OleDbParameter myParm = cmd.Parameters.Add("@fname", OleDbType.VarChar, 50); myParm.Value = textBox1.Text; myParm = cmd.Parameters.Add("@lname", OleDbType.VarChar, 50); myParm.Value = textBox2.Text; 这样对我有解决作用吗？ - Beginner
1
这段代码是公开的：
OleDbConnection con = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Users\Mohammadhoseyn_mehri\Documents\Data.mdb");

这是注册按钮的代码：

 try
 {
     createaccount();
     else
     {
         MessageBox.Show("Please re-enter your password");
     }
 }
 catch(Exception ex)
 {
     MessageBox.Show(ex.Message);
 }
 finally
 {
     MessageBox.Show("Data saved successfully...!");
     con.Close();
}

这是创建账户方法的代码：

This is the code for create account method:

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * from Login", con);
con.Open();

String ticketno = textBox2.Text.ToString();
String Purchaseprice = textBox1.Text.ToString();
String my_query = $"INSERT INTO Login (username, pass) VALUES ('{ticketno}', '{Purchaseprice}')";

OleDbCommand cmd = new OleDbCommand(my_query, con);

cmd.ExecuteNonQuery();
0
如果您正在使用数据库，则大多数情况下需要使用try-catch块语句来帮助和指导您的代码。这里我将向您展示如何通过单击按钮事件将一些值插入数据库。
 private void button2_Click(object sender, EventArgs e)
    {
        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
    @"Data source= C:\Users\pir fahim shah\Documents\TravelAgency.accdb";

     try
       {
           conn.Open();
           String ticketno=textBox1.Text.ToString();                 
           String Purchaseprice=textBox2.Text.ToString();
           String sellprice=textBox3.Text.ToString();
           String my_query = "INSERT INTO Table1(TicketNo,Sellprice,Purchaseprice)VALUES('"+ticketno+"','"+sellprice+"','"+Purchaseprice+"')";
         
            OleDbCommand cmd = new OleDbCommand(my_query, conn);
            cmd.ExecuteNonQuery();

            MessageBox.Show("Data saved successfuly...!");
          }
         catch (Exception ex)
         {
             MessageBox.Show("Failed due to"+ex.Message);
         }
         finally
         {
             conn.Close();
         }
SQL注入警报 - 您不应将SQL语句串联在一起 - 而是使用参数化查询来避免SQL注入 - 查看“小Bobby Tables”。 - marc_s
-2
private void btnSave_Click(object sender, EventArgs e)**
{
   OleDbCommand cmd = new OleDbCommand();
   cmd.CommandType = CommandType.Text;
   cmd.CommandText = @"insert into Personal (P_name, P_add,P_Phone)VALUES('" + txtName.Text + "','" +txtAddress.Text + "','" + txtPhone.Text + "')";
   cmd.Connection = con;
   con.Open();
   cmd.ExecuteNonQuery();
   System.Windows.Forms.MessageBox.Show("Recrod Succefully Created");
   con.Close();
   txtName.Text = "";
   txtAddress.Text = "";
   txtPhone.Text = "";
}
2
SQL注入，使用参数化查询，如果有意义的话始终如此，如果是用户输入，则毫无例外。 - Tony Hopkinson
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接