Asp.net SQL exception

I am making an ASP.net web application, and I am trying to fill a table with information from a database. However, I am getting the error:

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword ‘FROM’.

Incorrect syntax near the keyword ‘AS’.

Here is the code

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();

And the matching grid view.

<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>

Thanks.

Answer

Check your string concatenation

..SELECT” +
“ion.formula

will result in

SELECTion.formula

not

SELECT ion.formula

But beyond the immediate problem, two recommendations.

When trying to track down an issue like this, put in a debug breakpoint and copy the value from command.CommandText and paste it in SSMS and try to execute it. That should give you a headstart on where your problem is.
Use the @ string prefix to make multiline SQL queries. As in

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);

Advertisement

Answer