无法在SQL Server 2008中使用where子句提取nvarchar类型数据

本文关键字：子句 where 提取 nvarchar 数据 类型 SQL Server 2008 | 更新日期: 2023-09-27 17:59:03

string constr = Properties.Settings.Default.Subject_1ConnectionString;
SqlConnection conn = new SqlConnection(constr);
SqlCommand com = new SqlCommand("SELECT * from Subject_Title WHERE Date BETWEEN @hello and @hello1 ", conn);
// com.Parameters.Add("@hello", SqlDbType.NVarChar).Value = textBox1.Text;
// com.Parameters.Add("@hello1", SqlDbType.NVarChar).Value = textBox2.Text;
com.Parameters.Add("@hello", SqlDbType.NVarChar);
com.Parameters["@hello"].Value = textBox1.Text;
com.Parameters.Add("@hello1", SqlDbType.NVarChar);
com.Parameters["@hello1"].Value = textBox2.Text;
// com.Parameters.AddWithValue("@hello", textBox1.Text);
// com.Parameters.AddWithValue("@hello1", textBox2.Text);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds, "Subject_title");
for (int i = 0; i < 8; i++)
{
    this.labeltext = this.labeltext + " " + ds.Tables["Subject_Title"].Rows[i]["Date"].ToString();
    this.labeltext = this.labeltext + " " + ds.Tables["Subject_Title"].Rows[i]["Subject"].ToString();
    this.labeltext = this.labeltext + " ";
}
this.label1.Text = this.labeltext;

这里我没有从数据库中获得任何数据

Date是我的nvarchar类型的列名，而Subject是text类型的另一列。

请任何人解决我的问题

无法在SQL Server 2008中使用where子句提取nvarchar类型数据

我想您应该使用：Con.Open((；Con.Close((；

但如果我是你，我会写这样的代码：

    string constr = Properties.Settings.Default.Subject_1ConnectionString;
    SqlConnection conn = new SqlConnection(constr);
    SqlCommand com = new SqlCommand("SELECT * from Subject_Title WHERE Date BETWEEN '"01-03-14'" and '"01-04-14'" ", conn);
    conn.Open();       
    SqlDataReader reader =com.ExecuteReader();
    while(reader.read()){
    this.labeltext += " " + reader.GetString(0);       //Use column ordinal for Date
    this.labeltext += " " + reader.GetString(1)+" ";   //Use column ordinal for Subject
    }
    conn.Close()
    this.label1.Text = this.labeltext;

我试图为您提供一个更好的代码库。

您需要：

  • 使用更有意义的名称！像hello和hello1这样的参数对阅读代码的人来说不是很有用。。。。另外：不要用Date之类的保留关键字来命名列-再次：使用对上下文更有意义的

  • 如果要使用与日期相关的方法，则必须使用DATE或DATETIME2(N)数据类型。如果您已将数据存储为nvarchar，则必须首先将其转换为DATE

  • 请始终将您的SqlConnection和SqlCommand放入using(...) { .. }块中，以确保正确快速地处理

  • 如果你只需要一个DataTable——只需实例化一个DataTable并填充它——不要使用DataSet不必要的额外开销——这只是浪费资源。。。

代码：

string constr = Properties.Settings.Default.Subject_1ConnectionString;
// if you only need one single data table - use a DataTable - not a DataSet !
DataTable dt = new DataTable();
// *ALWAYS* put your SqlConnection and SqlCommand into using() blocks!
// also - if you want to use BETWEEN, you *MUST* use DATE!
// also: don't call your column "date" - that's a SQL Server reserved keyword! Use a more meaningful name 
// like "DateCreated" or "DateLastUpdated" or something
// and please also use more meaningful parameter names - "hello" and "hello1" is very confusing and not clear!!
using (SqlConnection conn = new SqlConnection(constr))
using (SqlCommand com = new SqlCommand("SELECT * FROM dbo.Subject_Title WHERE CAST(DateCreated AS DATE) BETWEEN @start and @end ", conn))
{
   // add parameters as DATE type!
   com.Parameters.Add("@start", SqlDbType.Date);
   com.Parameters["@start"].Value = DateTime.Parse(textBox1.Text).Date;
   com.Parameters.Add("@end", SqlDbType.Date);
   com.Parameters["@end"].Value = DateTime.Parse(textBox2.Text).Date;
   SqlDataAdapter da = new SqlDataAdapter(com);
   da.Fill(dt);
}
for (int i = 0; i < 8; i++)
{
    this.labeltext = this.labeltext + " " + dt.Rows[i]["Date"].ToString();
    this.labeltext = this.labeltext + " " + ds.Rows[i]["Subject"].ToString();
    this.labeltext = this.labeltext + " ";
}
this.label1.Text = this.labeltext;
相关文章：