Asp.net SQL exception

Question

我正在制作一个ASP.net web应用程序，并试图用数据库中的信息填充一个表。然而，我得到了错误：

异常详细信息：System.Data.SqlClient.SqlException：关键字"FROM"附近的语法不正确。

关键字"AS"附近的语法不正确。

这是代码

conn.Open();
// Make for new table with covalent
SqlCommand command = new SqlCommand("SELECT [compound_name], cc.[nonmetal1_quantity] AS [nonMetal1_quantity], (SELECT" + 
"ion.formula FROM NonMetal AS ion WHERE ion.[nonmetal_id] = cc.[nonmetal1_id]) AS[nonMetal1]," +
"cc.[nonmetal2_quantity] as [nonMetal2_quantity], (SELECT ion.formula FROM NonMetal AS ion WHERE ion.[nonmetal_id] = cc.[nonmetal2_id]) AS[nonMetal2]," + 
"c.compound_id AS [compound_id] FROM Compound as c, Covalent AS cc, NonMetal AS n WHERE c.[compound_id] = cc.[compound_id] ORDER BY c.[compound_name] ASC", conn);
DataTable table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(table); //Line where the error is thrown
CompoundTable.DataSource = table;
CompoundTable.DataBind();

以及匹配的网格视图。

<asp:GridView ID="CompoundTable" runat="server" AllowSorting="True" AllowPaging="True" AutoGenerateColumns="False" PageSize="20" OnSorting="CompoundTable_Sorting" OnPageIndexChanging="CompoundTable_PageIndexChanging" OnRowCancelingEdit="CompoundTable_CancelEdit" OnRowEditing="CompoundTable_Edit" OnRowUpdating="CompoundTable_Update" onrowdatabound="CompoundTable_DataBound" CellPadding="4">
<Columns>
<asp:TemplateField Visible="false" HeaderText="ID"> 
<ItemTemplate>
<asp:Label ID="compound_id" runat="server" Text='<%#Eval("compound_id") %>'/>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Compound"> 
<ItemTemplate>
<asp:Label ID="compound_name" runat="server" Text='<%#Eval("compound_name") %>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID ="name_txt" runat="server" Text='<%#Eval("compound_name") %>'>
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Non-Metal 1"> 
<ItemTemplate>
<asp:Label ID="nonMetal1" runat="server" Text='<%#Eval("nonMetal1") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID ="nonMetal1_txt" runat="server" >
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Quantity"> 
<ItemTemplate>
<asp:Label ID="nonMetal1_quantity" runat="server" Text='<%#Eval("nonMetal1_quantity") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID ="nonMetal1_quantity_txt" runat="server" Text='<%#Eval("nonMetal1_quantity") %>'>
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Non-Metal 2"> 
<ItemTemplate>
<asp:Label ID="nonMetal2" runat="server" Text='<%#Eval("nonMetal2") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID ="nonMetal2_txt" runat="server" >
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Quantity"> 
<ItemTemplate>
<asp:Label ID="nonMetal2_quantity" runat="server" Text='<%#Eval("nonMetal2_quantity") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID ="nonMetal2_quantity_txt" runat="server" Text='<%#Eval("nonMetal2_quantity") %>'>
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:HyperLinkField DataNavigateUrlFields="compound_id" DataNavigateUrlFormatString="DeleteCompound.aspx?compound={0}" Text="Delete Compound" />
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="LBEdit" runat="server"  CommandName="Edit" >Edit</asp:LinkButton>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="LBCancel" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
<asp:LinkButton ID="LBUpdate" runat="server" CommandName="Update">Update</asp:LinkButton>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

谢谢。

Answer 1

检查字符串连接

。。选择"+
"。公式

将导致

SELECTion.公式

不是

选择.公式

但除了眼前的问题，还有两个建议。

1. 当试图跟踪这样的问题时，放入调试断点并从命令中复制值。CommandText并将其粘贴到SSMS中，然后尝试执行它。这应该会让你知道问题所在

2. 使用@string前缀进行多行SQL查询。如

   SqlCommand命令=新的SqlCommand(@"SELECT[compound_name]，cc.[nonMetal_quantity]AS[nonMetal_quality]，(SELECTion.formula FROM非金属AS离子WHERE离子。[nnonmetal_id]＝cc.[nonmetal_id](AS[nonmetal1]，cc.[nonmetal2_quantity]as[nonmetal2_quantiy]，(SELECT ion.formula FROM NonMetal as ion WHERE ion.[NonMetal_id]=cc.[nonmetal2_id](as[nonmetal2]，c.compound_id AS[compound_id]FROM化合物为c，共价AS为cc，非金属AS为n，其中c.[compund_id]=cc。[compound_I]ORDER BY c.[compound_name]ASC"，conn(；