SQLCommand语法错误

本文关键字：错误 语法 SQLCommand | 更新日期: 2023-09-27 18:05:34

我写信是为了请求一些关于我的c#语法的帮助。我总是看到"sda.Fill(dt);"的语法，我不知道为什么。

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System.Data;
  using System.Data.SqlClient;
  using System.Configuration;
  public partial class Default2 : System.Web.UI.Page
 {
    protected void Page_Load(object sender, EventArgs e)
 {
    if (!IsPostBack)
    {
        string query = "select distinct Price_type from Price";
        DataTable dt = GetData(query);
        ddlPrice.DataSource = dt;
        ddlPrice.DataTextField = "Price_type";
        ddlPrice.DataValueField = "Price_type";
        ddlPrice.DataBind();
        ddlPrice2.DataSource = dt;
        ddlPrice2.DataTextField = "Price_type";
        ddlPrice2.DataValueField = "Price_type";
        ddlPrice2.DataBind();
        ddlPrice2.Items[1].Selected = true;
    }
}
     protected void Compare(object sender, EventArgs e)
    {
      string query = string.Format("select price, date from Price where Price_type = '{0}' order by date)", ddlPrice.SelectedItem.Value);
       DataTable dt = GetData(query);
    string[] x = new string[dt.Rows.Count];
    decimal[] y = new decimal[dt.Rows.Count];
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        x[i] = dt.Rows[i][0].ToString();
        y[i] = Convert.ToInt32(dt.Rows[i][1]);
    }
        LineChart1.Series.Add(new AjaxControlToolkit.LineChartSeries { Name = ddlPrice.SelectedItem.Value, Data = y });
        query = string.Format("select price, date from Price where Price_type = '{0}' order by date)", ddlPrice2.SelectedItem.Value);
    dt = GetData(query);
     y = new decimal[dt.Rows.Count];
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        x[i] = dt.Rows[i][0].ToString();
        y[i] = Convert.ToInt32(dt.Rows[i][1]);
    }
         LineChart1.Series.Add(new AjaxControlToolkit.LineChartSeries { Name = ddlPrice2.SelectedItem.Value, Data = y });
    LineChart1.CategoriesAxis = string.Join(",", x);
        LineChart1.ChartTitle = string.Format("{0} and {1} Order Distribution", ddlPrice.SelectedItem.Value, ddlPrice2.SelectedItem.Value);
    LineChart1.Visible = true;
}
private static DataTable GetData(string query)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["bwic testConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }
        }
        return dt;
    }
}

SQLCommand语法错误

date可能是保留关键字，这取决于您使用的版本。改为写[date]。这

"select price, date from Price where Price_type = '{0}' order by date)"
应该

"select price, date from Price where Price_type = '{0}' order by date"

你可以试试下面的代码

using (var con = new SqlConnection(constr))
{
    using (var cmd = new SqlCommand(query))
    {
        using (var sda = new SqlDataAdapter())
        {
            con.Open();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            sda.Fill(dt);
        }
    }
    return dt;
}

你的GetData()方法没有语法错误，正如你原来的帖子所示。您可以尝试注释掉方法的整个主体，然后开始一次一个地取消注释，以隔离实际问题。

但它显然似乎你没有语法错误:你有无效的SQL。你要执行的SQL是:

select price, date from Price where Price_type = '{0}' order by date)

它有两个错误:

  • 结尾的右括号是你的语法错误，
  • 参数不能用引号括起来
你的SQL应该看起来像 
select price, date from Price where Price_type = {0} order by date

你的方法应该看起来像这样:

private static DataTable GetData1( string query , string p0 )
{
  DataTable dt = new DataTable();
  string constr = ConfigurationManager.ConnectionStrings["bwic testConnectionString"].ConnectionString;
  using ( SqlConnection con = new SqlConnection( constr ) )
  using ( SqlCommand cmd = con.CreateCommand() )
  using ( SqlDataAdapter sda = new SqlDataAdapter(cmd) )
  {
    cmd.CommandText = query;
    cmd.CommandType = CommandType.Text;
    SqlParameter p = new SqlParameter() ;
    p.Value = p0Value ;
    cmd.Parameters.Add( p ) ;
    con.Open();
    sda.Fill( dt );
    con.Close();
  }
  return dt;
}

不要忘记在SELECT子句和ORDER BY子句中使用括号两次。

在你的代码块

using (SqlConnection con = new SqlConnection(constr))
{
    using (SqlCommand cmd = new SqlCommand(query))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            sda.Fill(dt);
        }
    }
    return dt;
}

改为

using (SqlConnection con = new SqlConnection(constr))
{
    using (SqlCommand cmd = new SqlCommand(query))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            con.Open();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            sda.Fill(dt);
        }
    }
    return dt;
}

这里的问题是您在填充之前没有打开任何连接

您没有在任何地方打开连接。您必须先打开连接才能使用它

如果您的整个类代码确实是这样的，那么您就缺少了在

文件末尾的关闭}。
 public partial class Default2 : System.Web.UI.Page
 {

声明。

相关文章：