SQL Server的列名无效

本文关键字：无效 Server SQL | 更新日期: 2023-09-27 18:27:19

每当我提交表单时，它都会给我一个错误代码：

所有eventspecialist、phone、PHONE2等和@SPECIALIST、@CUST_phone、@CUST_PHONE2等的"无效列名"

下面是我的代码，也许我没有正确地编写SQL语句或参数？带@infront的是我数据库中的内容，其他小写的是我的文本框。我对这种类型的编码比较陌生。

编辑： 将我的INSERT INTO语句更改为建议的内容。错误仍然存在，但仅最小化到

"无效列名eventspecial、无效列名phone、无效列名phone2等。"

private void execution(string eventspecialist, string phone, string phone2, string firstname, string lastname, string besttime, string companyname, string nonprofit, string requesteddate, string requestedtime, string attendance, string eventtype, string other, string leadsource, string notes, string catering, string bar, string damagedeposit, string dancefloor, string griddate, string gridnotes, string comments)
{
    SqlConnection conn = new SqlConnection(GetConnectionString());
    string sql = "INSERT INTO tblcontacts (@SPECIALIST, @CUST_PHONE1, @CUST_PHONE2, @CUST_FNAME, @CUST_LNAME, @BEST_TIME, @COMPANY_NAME, @NONPROFIT, @REQ_DATE, @REQ_TIME, @ATTENDANCE, @EVENT_TYPE, @OTHER_DESC, @LEAD_SOURCE, @NOTES, @CATERING, @BAR, @DAMAGE_DEPOSIT, @DANCE_FLOOR) VALUES (eventspecialist, phone, phone2, firstname, lastname, besttime, companyname, nonprofit, requesteddate, requestedtime, attendance, eventtype, other, leadsource, notes, catering, bar, damagedeposit, dancefloor)";
    string sql2 = "INSERT INTO tblnotes (@NOTEDATE, @NOTEBY, @COMMENTS) VALUES (griddate, gridnotes, comments)";
    try
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@SPECIALIST", SqlDbType.NVarChar, 50).Value = eventspecialist;
        cmd.Parameters.Add("@CUST_PHONE1", SqlDbType.NVarChar, 50).Value = phone;
        cmd.Parameters.Add("@CUST_PHONE2", SqlDbType.NVarChar, 50).Value = phone2;
        cmd.Parameters.Add("@CUST_FNAME", SqlDbType.NVarChar, 50).Value = firstname;
        cmd.Parameters.Add("@CUST_LNAME", SqlDbType.NVarChar, 50).Value = lastname;
        cmd.Parameters.Add("@BEST_TIME", SqlDbType.NVarChar, 50).Value = besttime;
        cmd.Parameters.Add("@COMPANY_NAME", SqlDbType.NVarChar, 225).Value = companyname;
        cmd.Parameters.Add("@NONPROFIT", SqlDbType.NVarChar, 10).Value = nonprofit;
        cmd.Parameters.Add("@REQ_DATE", SqlDbType.Date, 20).Value = requesteddate;
        cmd.Parameters.Add("@REQ_TIME", SqlDbType.Time, 20).Value = requestedtime;
        cmd.Parameters.Add("@ATTENDANCE", SqlDbType.Int, 50).Value = attendance;
        cmd.Parameters.Add("@EVENT_TYPE", SqlDbType.NVarChar, 50).Value = eventtype;
        cmd.Parameters.Add("@OTHER_DESC", SqlDbType.NVarChar, 225).Value = other;
        cmd.Parameters.Add("@LEAD_SOURCE", SqlDbType.NVarChar, 50).Value = leadsource;
        cmd.Parameters.Add("@NOTES", SqlDbType.NVarChar, 225).Value = notes;
        cmd.Parameters.Add("@CATERING", SqlDbType.NVarChar, 1).Value = catering;
        cmd.Parameters.Add("@BAR", SqlDbType.NVarChar, 1).Value = bar;
        cmd.Parameters.Add("@DAMAGE_DEPOSIT", SqlDbType.NVarChar, 19).Value = damagedeposit;
        cmd.Parameters.Add("@DANCE_FLOOR", SqlDbType.Money).Value = Decimal.Parse(dancefloor);

        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
    }
    catch (System.Data.SqlClient.SqlException ex_msg)
    {
        string msg = "Error occured while inserting";
        msg += ex_msg.Message;
        throw new Exception(msg);
    }
    finally
    {
        conn.Close();
    }
    try
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql2, conn);
        cmd.Parameters.Add("@NOTEDATE", SqlDbType.Date, 50).Value = griddate;
        cmd.Parameters.Add("@NOTEBY", SqlDbType.NVarChar, 50).Value = gridnotes;
        cmd.Parameters.Add("@COMMENTS", SqlDbType.NVarChar, 50).Value = comments;
        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
    }
    catch (System.Data.SqlClient.SqlException ex_msg)
    {
        string msg = "Error occured while inserting";
        msg += ex_msg.Message;
        throw new Exception(msg);
    }
    finally
    {
        conn.Close();
    }
}
protected void Page_Load(object sender, EventArgs e)
{
    griddate.Text = DateTime.Now.ToString("yyyy/MM/dd");
}
protected void submit_Click(object sender, EventArgs e)
{
    if (requesteddate.Text == "")
    {
        Finish.Text = "Please complete the form!";
    }
    else if (requestedtime.Text == "")
    {
        Finish.Text = "Please complete the form!";
    }
    else if (attendance.Text == "")
    {
        Finish.Text = "Please complete the form!";
    }
    else
    {
        execution(eventspecialist.Text, phone.Text, phone2.Text, firstname.Text, lastname.Text, besttime.SelectedItem.Text, companyname.Text, nonprofit.Text, requesteddate.Text, requestedtime.Text, attendance.Text, eventtype.SelectedItem.Text, other.Text, leadsource.SelectedItem.Text, notes.Text, catering.Text, bar.Text, damagedeposit.Text, dancefloor.SelectedItem.Text, griddate.Text, gridnotes.SelectedItem.Text, comments.Text);
        Finish.Visible = false;
        conform.Visible = true;
    }
}

SQL Server的列名无效

首先是列名，然后是变量

string sql = "INSERT INTO tblcontacts (Col1,Col2,...) VALUES (@Var1,@Var2,...)"

我假设您的列是

SPECIALIST, CUST_PHONE1, CUST_PHONE2, CUST_FNAME, CUST_LNAME, BEST_TIME, 
COMPANY_NAME, NONPROFIT, REQ_DATE, REQ_TIME, ATTENDANCE, EVENT_TYPE, OTHER_DESC, 
LEAD_SOURCE, NOTES, CATERING, BAR, DAMAGE_DEPOSIT, DANCE_FLOOR

如果是这样的话，你应该以这种方式更改你的INSERT INTO

INSERT INTO tblcontacts 
(SPECIALIST, CUST_PHONE1, CUST_PHONE2, CUST_FNAME, CUST_LNAME, BEST_TIME, 
COMPANY_NAME, NONPROFIT, REQ_DATE, REQ_TIME, ATTENDANCE, EVENT_TYPE, OTHER_DESC, LEAD_SOURCE, 
NOTES, CATERING, BAR, DAMAGE_DEPOSIT, DANCE_FLOOR)
VALUES
(@SPECIALIST, @CUST_PHONE1, @CUST_PHONE2, @CUST_FNAME, @CUST_LNAME, @BEST_TIME, @COMPANY_NAME, 
 @NONPROFIT, @REQ_DATE, @REQ_TIME, @ATTENDANCE, @EVENT_TYPE, @OTHER_DESC, @LEAD_SOURCE, @NOTES, 
 @CATERING, @BAR, @DAMAGE_DEPOSIT, @DANCE_FLOOR)

在execution方法中，您正在创建参数，但是，在insert语句中
首先放入实际的列名，然后在值部分放入参数名称
（带@前缀）

顺便说一句，下次，请尝试更好地格式化代码。

您的列和值是错误的：

string sql = "INSERT INTO tblcontacts (eventspecialist, phone, phone2, firstname, lastname, besttime, companyname, nonprofit, requesteddate, requestedtime, attendance, eventtype, other, leadsource, notes, catering, bar, damagedeposit, dancefloor) VALUES (@SPECIALIST, @CUST_PHONE1, @CUST_PHONE2, @CUST_FNAME, @CUST_LNAME, @BEST_TIME, @COMPANY_NAME, @NONPROFIT, @REQ_DATE, @REQ_TIME, @ATTENDANCE, @EVENT_TYPE, @OTHER_DESC, @LEAD_SOURCE, @NOTES, @CATERING, @BAR, @DAMAGE_DEPOSIT, @DANCE_FLOOR)";

您有向后的顺序：广义的，它是：

insert into tableName (column1, ..., column20) values (value1, ..., value20)

所以；只需交换列和值：

string sql = "INSERT INTO tblcontacts (eventspecialist, phone, phone2, firstname, lastname, besttime, companyname, nonprofit, requesteddate, requestedtime, attendance, eventtype, other, leadsource, notes, catering, bar, damagedeposit, dancefloor) VALUES (@SPECIALIST, @CUST_PHONE1, @CUST_PHONE2, @CUST_FNAME, @CUST_LNAME, @BEST_TIME, @COMPANY_NAME, @NONPROFIT, @REQ_DATE, @REQ_TIME, @ATTENDANCE, @EVENT_TYPE, @OTHER_DESC, @LEAD_SOURCE, @NOTES, @CATERING, @BAR, @DAMAGE_DEPOSIT, @DANCE_FLOOR)";
string sql2 = "INSERT INTO tblnotes (griddate, gridnotes, comments) VALUES (@NOTEDATE, @NOTEBY, @COMMENTS)";

您的INSERT语句都错了。。。。。

取而代之的是：

INSERT INTO tblcontacts (@SPECIALIST, @CUST_PHONE1, .....)
VALUES (eventspecialist, phone, .....);

你需要使用相反的方法：

INSERT INTO tblcontacts (eventspecialist, phone, .....)
VALUES (@SPECIALIST, @CUST_PHONE1, .....);

您需要在列表中的表名后面有表的列名，在VALUES

后面有参数的（带前导@）

您已经将参数输入为列，将列输入为参数。试试另一种方法。

用以下替换您的代码

"INSERT INTO tblcontacts (SPECIALIST, CUST_PHONE1, CUST_PHONE2, CUST_FNAME, CUST_LNAME, BEST_TIME, COMPANY_NAME, NONPROFIT, REQ_DATE, REQ_TIME, ATTENDANCE, EVENT_TYPE, OTHER_DESC, LEAD_SOURCE, NOTES, CATERING, BAR, DAMAGE_DEPOSIT, DANCE_FLOOR) VALUES (@eventspecialist, @phone, @phone2, @firstname, @lastname, @besttime, @companyname, @nonprofit, @requesteddate, @requestedtime, @attendance, @eventtype, @other, @leadsource, @notes, @catering, @bar, @damagedeposit, @dancefloor)";

这里是

    cmd.Parameters.Add("@eventspecialist", SqlDbType.NVarChar, 50).Value = eventspecialist;
    cmd.Parameters.Add("@phone", SqlDbType.NVarChar, 50).Value = phone;
    cmd.Parameters.Add("@phone2", SqlDbType.NVarChar, 50).Value = phone2;
    cmd.Parameters.Add("@firstname", SqlDbType.NVarChar, 50).Value = firstname;
    cmd.Parameters.Add("@lastname", SqlDbType.NVarChar, 50).Value = lastname;
    cmd.Parameters.Add("@besttime", SqlDbType.NVarChar, 50).Value = besttime;
    cmd.Parameters.Add("@companyname", SqlDbType.NVarChar, 225).Value = companyname;
    cmd.Parameters.Add("@nonprofit", SqlDbType.NVarChar, 10).Value = nonprofit;
    cmd.Parameters.Add("@requesteddate", SqlDbType.Date, 20).Value = requesteddate;
    cmd.Parameters.Add("@requestedtime", SqlDbType.Time, 20).Value = requestedtime;
    cmd.Parameters.Add("@attendance", SqlDbType.Int, 50).Value = attendance;
    cmd.Parameters.Add("@eventtype", SqlDbType.NVarChar, 50).Value = eventtype;
    cmd.Parameters.Add("@other", SqlDbType.NVarChar, 225).Value = other;
    cmd.Parameters.Add("@leadsource", SqlDbType.NVarChar, 50).Value = leadsource;
    cmd.Parameters.Add("@notes", SqlDbType.NVarChar, 225).Value = notes;
    cmd.Parameters.Add("@catering", SqlDbType.NVarChar, 1).Value = catering;
    cmd.Parameters.Add("@bar", SqlDbType.NVarChar, 1).Value = bar;
    cmd.Parameters.Add("@damagedeposit", SqlDbType.NVarChar, 19).Value = damagedeposit;
    cmd.Parameters.Add("@dancefloor", SqlDbType.Money).Value = Decimal.Parse(dancefloor);
相关文章：