将 DataAdapter.InsertCommand 传递到方法中会导致 OleDbCommand 为空值

Question

我正在尝试从“程序员天堂：C# 学校”学习 ADO.NET

这是我根据该电子书创建的代码，但运行它会导致：

System.NullReferenceException：未将对象引用设置为对象的实例。

尝试将参数 (cmd.Parameters.Add("@" + col, OleDbType.Char, 0, col);) 添加到命令时。

有人可以指出我出了什么问题吗？

编辑：

添加问题描述

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

namespace AdoNET
{
    public partial class MainForm : Form
    {
    private OleDbConnection conn;
    private OleDbDataAdapter dataAdapter;

    private DataTable dataTable;
    private DataSet ds;

    private int currRec = 0;
    private int totalRec = 0;
    private bool insertSelected;

    public MainForm()
    {
        InitializeComponent();
    }

    void BtnLoadTableClick(object sender, EventArgs e)
    {
        this.Cursor = Cursors.WaitCursor;

        //create connection string
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db\\ProgrammersHeaven.mdb;";

        //create connection
        conn = new OleDbConnection(connectionString);

        //define command
        string commandString = "SELECT * FROM Article, Author WHERE Author.authorId = Article.authorId";

        //create Data Adapter for communication with DB
        dataAdapter = new OleDbDataAdapter(commandString, conn);

        //create Data Set to store data offline
        ds = new DataSet ();

        //fill dataset (table prog) with data from dataAdapter
        dataAdapter.Fill(ds, "prog");

        //create and fill table dataTable
        dataTable = ds.Tables["prog"];
        currRec = 0;
        totalRec = dataTable.Rows.Count;

        FillControls();
        InitializeCommands();
        ToggleControls(true);

        btnNext.Enabled = true;
        btnPrevious.Enabled = true;

        this.Cursor = Cursors.Default;
    }

    private void FillControls()
    {
        txtArticleID.Text = dataTable.Rows[currRec]["artId"].ToString();
        txtArticleTitle.Text = dataTable.Rows[currRec]["title"].ToString();
        txtArticleTopic.Text = dataTable.Rows[currRec]["topic"].ToString();
        txtAuthorId.Text = dataTable.Rows[currRec]["Author.authorId"].ToString();
        txtAuthorName.Text = dataTable.Rows[currRec]["name"].ToString();
        txtNumOfLines.Text = dataTable.Rows[currRec]["lines"].ToString();
        txtDateOfPublishing.Text = dataTable.Rows[currRec]["dateOfPublishing"].ToString();
    }

    void BtnNextClick(object sender, EventArgs e)
    {
        currRec++;
        if (currRec>=totalRec)
        {
            currRec=0;
        }
        FillControls();
    }

    void BtnPreviousClick(object sender, EventArgs e)
    {
        currRec--;
        if (currRec<0)
        {
            currRec=totalRec-1;
        }
        FillControls();
    }

    private void InitializeCommands()
    {
        //preparing INSERT command
        dataAdapter.InsertCommand = conn.CreateCommand();
        dataAdapter.InsertCommand.CommandText =
            "INSERT INTO article " +
            "(artId, title, topic, authorId, lines, dateOfPublishing) " +
            "VALUES (@artId, @title, @topic, @authorId, @lines, @dateOfPublishing)";
        AddParams(dataAdapter.InsertCommand, "artId", "title", "topic", "lines", "dateOfPublishing");

        //preparing UPDATE command
        dataAdapter.InsertCommand = conn.CreateCommand();
        dataAdapter.InsertCommand.CommandText =
            "UPDATE article SET" +
            "title = @title, topic = @topic, lines = @lines, dateOfPublishing = @dateOfPublishing" +
            "WHERE artId = @artId";
        AddParams(dataAdapter.UpdateCommand, "artId", "title", "topic", "authorId", "lines", "dateOfPublishing");

        //prepare DELETE command
        dataAdapter.InsertCommand = conn.CreateCommand();
        dataAdapter.InsertCommand.CommandText =
            "DELETE FROM article WHERE artId = @artId";
        AddParams(dataAdapter.DeleteCommand, "artId");
    }

    private void AddParams(OleDbCommand cmd, params string[] cols)
    {
        //adding hectic (?) parameters
        foreach (string col in cols)
        {
            cmd.Parameters.Add("@" + col, OleDbType.Char, 0, col);
        }
    }

    private void ToggleControls(bool val)
    {
        txtArticleTitle.ReadOnly = val;
        txtArticleTopic.ReadOnly = val;
        txtAuthorId.ReadOnly = val;
        txtNumOfLines.ReadOnly = val;
        txtDateOfPublishing.ReadOnly = val;

        btnLoadTable.Enabled = val;
        btnNext.Enabled = val;
        btnPrevious.Enabled = val;
        btnEditRecord.Enabled = val;
        btnInsertRecord.Enabled = val;
        btnDeleteRecord.Enabled = val;

        btnSave.Enabled = !val;
        btnCancel.Enabled = !val;
    }

    void BtnEditRecordClick(object sender, EventArgs e)
    {
        ToggleControls(false);
    }

    void BtnSaveClick(object sender, EventArgs e)
    {
        lblStatus.Text = "Saving Changes...";
        this.Cursor = Cursors.WaitCursor;
        DataRow row = dataTable.Rows[currRec];
        row.BeginEdit();
        row["title"] = txtArticleTitle.Text;
        row["topic"] = txtArticleTopic.Text;
        row["Article.authorId"] = txtAuthorId.Text;
        row["lines"] = txtNumOfLines.Text;
        row["dateOfPublishing"] = txtDateOfPublishing.Text;
        row.EndEdit();
        dataAdapter.Update(ds, "prog");
        ds.AcceptChanges();

        ToggleControls(true);
        insertSelected = false;
        this.Cursor = Cursors.Default;
        lblStatus.Text = "Changes Saved";
    }


    void BtnCancelClick(object sender, EventArgs e)
    {
        if (insertSelected)
        {
            BtnDeleteRecordClick(null,null);
            insertSelected=false;
        }

        FillControls();
        ToggleControls(true);
    }

    void BtnDeleteRecordClick(object sender, EventArgs e)
    {
        DialogResult res = MessageBox.Show("Are you sure you want to delete the current record?",
                                           "Confirm Record Deletion", MessageBoxButtons.YesNo);
        if (res == DialogResult.Yes)
        {
            DataRow row = dataTable.Rows[currRec];
            row.Delete();
            dataAdapter.Update(ds, "prog");
            ds.AcceptChanges();
            lblStatus.Text = "Record Deleted";
            totalRec--;
            currRec = totalRec - 1;
            FillControls();
        }
    }

    void BtnInsertRecordClick(object sender, EventArgs e)
    {
        insertSelected = true;
        //table must be loaded to create new row
        DataRow row = dataTable.NewRow();
        dataTable.Rows.Add(row);
        totalRec = dataTable.Rows.Count;
        currRec = totalRec-1;
        row["artId"] = totalRec;

        txtArticleID.Text = totalRec.ToString();
        txtArticleTitle.Text = "";
        txtArticleTopic.Text = "";
        txtAuthorId.Text = "";
        txtNumOfLines.Text = "";
        txtDateOfPublishing.Text = DateTime.Now.Date.ToString();

        ToggleControls(false);
    }
}

}

Answer 1

在InitializeCommands 方法中，无需创建发送到AddParams 方法的Update 和Delete 命令。

在相应位置将 dataAdapter.InsertCommand 更改为 dataAdapter.UpdateCommand 和 dataAdapter.DeleteCommand 。

旁注：如果您阅读错误消息，很明显您正在尝试使用某个引用，但该引用未引用堆中的任何对象。在这种情况下，请运行您的代码以找到没有定义的人。

Answer 2

我意识到这已经晚了几年，但在查看这段代码后我想添加一些内容：

private void AddParams(OleDbCommand cmd, params string[] cols)
{
    //adding hectic (?) parameters
    foreach (string col in cols)
    {
        cmd.Parameters.Add("@" + col, OleDbType.Char, 0, col);
    }
}

• 这是一个 OleDb 数据连接。我不是 100%，但是当我使用 @ 语法作为我的参数名称时，我从来没有让参数正常工作，就像我使用 Microsoft SQL 一样。你可能想看看。如果我没记错的话，OleDb 使用 ? 符号并忽略您分配的名称。您必须（至少据我所知）按照在 SQL 语句中声明的顺序添加参数，因为 OleDb 不会尝试匹配它们up - 再次，据我所知。

• 此外，您还指定此 OleDbTypeChar 参数的大小为零 (0)。如果您尝试为参数赋值，这也可能会导致问题。

由于这个答案仍然会出现在搜索中，因此我想为其他像我一样想阅读您的代码的人发布一些内容。