System.Data.Sqlclient.Sqlexception: Line1 incorrect syntax at ']'.

[chombe]

Hi there
I'm a newbie to asp.net and trying to learning it doing small forum application. In the application I've a page which lists the available threads in a given topic and I used a gridview. In the gridview the thread's title is hyperlinked and when clicked it will redirect to another page that lists the thread's original message and any other replays to it.

My problem is it won't work as intended. When I click the thread's title it displays the page but with error message. Here is a portion of the browser's output

Server Error in '/' Application.

Line 1: Incorrect syntax near ']'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ']'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Line 1: Incorrect syntax near ']'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188

here is the code for handling the click event of the selected index in the gridview

protected void GridView1_SelectedIndexChanged( object sender,   ventArgs e)
{
string threadID = GridView1.SelectedValue.ToString();
string topicID = Request.QueryString[“topic”];
Response.Redirect(“Messages.aspx?topic=” + topicID
+ “&thread=” + threadID);
}

<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" 
          AutoEventWireup="true" CodeFile="Messages.aspx.cs" Inherits="Messages" Title="Messages" %>
<asp:Content ID="Content1" Runat="Server" ContentPlaceHolderID="ContentPlaceHolder1" >
    <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1">
        <ItemTemplate>
            <h3>Topic:<asp:Label ID="topicNameLabel" runat="server" Text='<%# Bind("name") %>' /></h3>
        </ItemTemplate>
    </asp:FormView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                        ConnectionString ="<%$ ConnectionStrings:ForumConnectionString%>"
                        SelectCommand="SELECT [name], [description] FROM [Topics] WHERE ([topicid] = @topicid)">
                        <SelectParameters>
                            <asp:QueryStringParameter Name="topicid" QueryStringField="topic" Type="Int32" />
                        </SelectParameters>
    </asp:SqlDataSource>
    <asp:FormView ID="FormView2" runat="server" DataSourceID="SqlDataSource2">
        <ItemTemplate>
            <h3>Thread:<asp:Label ID="threadNameLabel" runat="server" Text='<%# Bind("subject") %>' /></h3>
        </ItemTemplate>
    </asp:FormView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ForumConnectionString%>"
                        SelectCommand="SELECT [subject]FROM [Threads]WHERE ([threadid] = @threadid)">
                        <SelectParameters>
                            <asp:QueryStringParameter Name="threadid" QueryStringField="thread" Type="Int32" />
                        </SelectParameters>
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
                    DataSourceID="SqlDataSource3" ShowHeader="False" AllowPaging="True" >
                    <Columns>
                        <asp:TemplateField> 
                            <ItemTemplate>
                                At<asp:Label ID="Label1" runat="server" Text='<% #Eval("date") %>' />
                                  <asp:Label ID="Label2" runat="server" Text='<% #Eval("author") %>' />
                                wrote:
                                <br /><br />
                                <table border=0>
                                    <tr><td width=300>
                                        <asp:Label ID="Label3" runat="server" Text='<% #Eval("message") %>' />
                                    </td></tr>
                                </table>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ForumConnectionString%>"
                        SelectCommand="SELECT [author], [date], [message]FROM [Messages]WHERE ([threadid] = @threadid)ORDER BY [date]">
                        <SelectParameters>
                            <asp:QueryStringParameter Name="threadid" QueryStringField="thread" Type="Int32" />
                        </SelectParameters>
    </asp:SqlDataSource>
    <br />
    <asp:Button ID="btnAdd" runat="server" Text="Add a Reply to this Thread" OnClick="btnAdd_Click" />
    <br /><br />
    <asp:LinkButton ID="btnReturn" runat="server" Text="Return to Threads page" OnClick="btnReturn_Click" />
</asp:Content>

I know it is very tedious to read the above text but i don't have any choice

please help me

thank you very much

[sedgey]

you are right it is tedious to read through it!

my suggestions, the sql error appears to be a problem with the syntax of the inline queries you have specified for your SqlDataSources, more specifically something near a ']' you don't actually need the [] around column names unless the column names you have chosen match some SQL keyword or have spaces so get rid of them.

Next you don't need the () around the items in the where clause so remove them.

There appears to be spaces missing after the ] in some of the queries you should always have spaces after each element of a query.

2 approcahes to debugging this, one would be to copy the queries into Sql Management studio and try querying the database with them (replace the @parameter with a value which should work)
This will check that your queries actually work (this is the danger of inline sql no checking before you run it)

2nd approach remove all the gridviews and sqldatasources but one and check to see if the page works, do this for each one to find the problem query.

I think fixing the syntax as suggested above should fix the problems

[chombe]

Thank you very much for helping me.
I spent two days trying to fix it.

keep on helping

God bless you!