我有一个网页不允许我对下拉列表进行排序。我有一个SQLDataSource连接，配置为在表tblCodesWorkNotRec中显示Description行。当我转到Order By并选择sort By Description，然后测试查询时，我得到了一个错误-
text、ntext和image数据类型不能进行比较或排序，除非使用IS NULL或LIKE运算符。
为什么？
如果你需要查看代码，什么代码是aspx或vb？
这是vb代码：
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Mvc
Partial Class TimeOffAddNoRequest
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Once data is edited on this webpage, do not go back to first loaded data (data from gridview)
If Page.IsPostBack = True Then
Return
End If
Dim windowsLoginName As System.String = HttpContext.Current.User.Identity.Name 'System.Security.Principal.WindowsIdentity.GetCurrent().Name
Dim split As String() = Nothing
Dim vname As String
Dim sqlConnection As New SqlConnection("Data Source=janetdev;Initial Catalog=TimeSQL;Persist Security Info=True;User ID=sa;Password=password")
Dim cmd, cmd1, cmd2 As New SqlCommand
Dim returnValue, returnValue1, returnValue2 As Object
Dim dt As Date
dt = Today
MsgBox(Today)
'Get network login name (name only)
split = windowsLoginName.Split("".ToCharArray)
vname = split(1)
'Get firstname from tblEmployees that matches login name
cmd.CommandText = "SELECT FirstName FROM tblEmployees where login = '" & vname & "'"
cmd.CommandType = CommandType.Text
cmd.Connection = sqlConnection
'Get lastname from tblEmployees that matches login name
cmd1.CommandText = "SELECT LastName FROM tblEmployees where login = '" & vname & "'"
cmd1.CommandType = CommandType.Text
cmd1.Connection = sqlConnection
'Get employeeid from tblEmployees that matches login name
cmd2.CommandText = "SELECT EmployeeID FROM tblEmployees where login = '" & vname & "'"
cmd2.CommandType = CommandType.Text
cmd2.Connection = sqlConnection
sqlConnection.Open()
'firstname
returnValue = cmd.ExecuteScalar()
'lastname
returnValue1 = cmd1.ExecuteScalar()
'employeeid
returnValue2 = cmd2.ExecuteScalar()
sqlConnection.Close()
'display firstname and lastname on screen
TextBox3.Text = returnValue & " " & returnValue1
TextBox4.Text = returnValue2
'display today's date
DateRequested.Text = dt
'Get value of other fields
If Not [String].IsNullOrEmpty(Request.QueryString("BeginDateOff").ToString()) Then
'Retrieving the BeginDateOff Value
BeginDate.Text = Request.QueryString("BeginDateOff").ToString()
End If
If Not [String].IsNullOrEmpty(Request.QueryString("EndDateOff").ToString()) Then
'Retrieving the EndDateOff Value
EndDate.Text = Request.QueryString("EndDateOff").ToString()
End If
If Not [String].IsNullOrEmpty(Request.QueryString("BeginTimeOff").ToString()) Then
'Retrieving the BeginTimeOff Value
BeginTimeDD.SelectedValue = Request.QueryString("BeginTimeOff").ToString
End If
If Not [String].IsNullOrEmpty(Request.QueryString("EndTimeOff").ToString()) Then
'Retrieving the EndTimeOff Value
EndTimeDD.Text = Request.QueryString("EndTimeOff").ToString()
End If
'Retrieving the All Day Value
AllDay.Checked = Boolean.Parse(Request.QueryString("AllDay_YesNo"))
'AllDay.Checked = Request.QueryString("AllDay_YesNo")
'Retrieving the WorkHoursNotRecordID
wkid.Text = Request.QueryString("WorkHoursNotRecordID").ToString()
If Request.QueryString("description").ToString = "Unknown" Then
Label2.Text = "You must change the reason from Unknown."
DropDownList2.Focus()
Exit Sub
Else
'Retrieving the Description Value
DropDownList2.Text = Request.QueryString("Description").ToString()
End If
BeginDate.Focus()
End Sub
这是aspx代码：
<%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="~/Site.Master" CodeBehind="TimeOffAddNoRequest.aspx.vb" Inherits="timework.TimeOffAddNoRequest" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<asp:ToolkitScriptManager ID="toolkitScriptManager" runat="server" />
<asp:Label ID="Label2" runat="server"
style="color: #990033; font-weight: 700"></asp:Label>
<asp:Label ID="Label1" runat="server"
style="color: #000099; font-weight: 700"></asp:Label>
<br />
<label>Employee Name:</label><asp:TextBox ID="TextBox3" runat="server" BorderStyle="None"
style="margin-left: 7px; font-size: medium; font-weight: 700;"
Width="150px" Height="22px" AutoPostBack="True"></asp:TextBox>
<asp:TextBox ID="TextBox4" runat="server" Width="129px"
AutoPostBack="True" Visible="False"></asp:TextBox>
<br />
<hr align="left" style="width: 791px; margin-left: 0px" />
<label>Date Requested:<asp:TextBox ID="DateRequested" runat="server" style="margin-left: 9px; font-size: medium; font-weight: 500;"
Width="150px" Height="22px" AutoPostBack="True" BorderStyle="None"></asp:TextBox>
</label>
<br />
<br />
<label>Begin Date Off:</label>
<asp:TextBox ID="BeginDate" runat="server" style="margin-left: 10px; font-size: medium; font-weight: 500;"
Width="150px" Height="22px" AutoPostBack="True" BorderStyle="Solid"
BorderWidth="1px"></asp:TextBox>
<label> End Date Off:</label>
<asp:TextBox ID="EndDate" runat="server" style="margin-left: 15px; font-size: medium; font-weight: 500;"
Width="150px" Height="22px" AutoPostBack="True" BorderStyle="Solid"
BorderWidth="1px"></asp:TextBox>
<br />
<br />
<label> All Day?</label>
<asp:CheckBox ID="AllDay" runat="server" AutoPostBack="True" />
<label> Begin Time Off:</label> <label>
<asp:DropDownList ID="BeginTimeDD" runat="server"
DataSourceID="SqlDataSource1" DataTextField="Time" DataValueField="Time"
Height="26px" Width="100px">
</asp:DropDownList>
End Time Off:</label> <asp:DropDownList ID="EndTimeDD"
runat="server" DataSourceID="SqlDataSource1" DataTextField="Time"
DataValueField="Time" Height="26px" Width="100px">
</asp:DropDownList>
<br />
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:TimeSQLConnectionString2 %>"
SelectCommand="SELECT [Time] FROM [tblCodesTime] ORDER BY [Time]">
</asp:SqlDataSource>
<label> Reason:</label>
<asp:DropDownList
ID="DropDownList2" runat="server" AutoPostBack="True"
DataSourceID="SqlDataSource2" DataTextField="Description"
DataValueField="Description" Height="26px" Width="149px">
</asp:DropDownList>
<br />
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:TimeSQLConnectionString1 %>"
SelectCommand="SELECT [Description] FROM [tblCodesWorkNotRec] ORDER BY [Description]">
</asp:SqlDataSource>
<asp:TextBox ID="wkid" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button2" runat="server" Text="Save" />
<br />
<br />
</asp:Content>
我相信您的错误在这行的aspx中
SELECT [Description] FROM [tblCodesWorkNotRec] ORDER BY [Description]
order by description会给你这个错误。您可以通过转换为varchar:来修复此问题
SELECT [Description]
FROM [tblCodesWorkNotRec]
ORDER BY Convert(varchar(max), [Description])
我不知道你为什么要按描述字段订购。也许最好的办法就是去掉order by。错误也可能发生在order by [time]线上。但如果这是一个DateTime字段，那就好了。该字段可能应该重命名为非关键字，如CodeTime
关于代码的其他注意事项，重构这是个好主意。
作为一个建议，我会创建一个单个类来与您的数据库对话。不要把sql连接放在代码后面，更不用说在aspx中了。（如果要这样做，请选择一个位置，而不是同时选择两个位置）这个类将拥有您所有的sql，并只返回您想要的值。
整合您的sql。没有理由对同一个表进行3次调用以从每次调用中获得一个值。打一个电话就能得到三样东西。我说的是名字，姓，员工的电话。
不要将业务逻辑放在事件处理程序中。请注意您的Page_Load方法。这是一个事件处理程序。你放在这里的任何代码都不能从其他地方调用，所以你最终会一遍又一遍地复制代码。只需将调用移动到它们自己的方法，然后从Page_Load调用该方法。（GoF图案-立面）
将UI元素放在它们自己的div标记中，并将它们与CSS对齐去掉（或者至少仔细考虑一下）
<br/>和 标记，并使用css中的margin之类的东西来移动元素。同时将所有内联样式标记移动到css。