Click here to Skip to main content
15,918,041 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi,
I am trying to load a page in asp.net which displays the detail of the selected Tournament. The exception is thrown during the loading page, I have debuged it, but in no line of Page_Load was an error.
The Error: Incorrect syntax near Player_Reg
I have SQL Statement with Player_Reg only in button click method. This SQL statement which selects players of a team to register to tournament is correct when I run it in SQL Managenent Studio, but when I try to run page from Visual Studio it throws this error, does anybody know why???

Codes:
C#
public class Client
    {
        private String ConnectionString = "Data Source=PETA3NEC_NTBK;Initial Catalog=Beach_Volleyball;Integrated Security=True";
        public SqlConnection Connection { get; set; }
        public SqlCommand Command { get; set; }
        public SqlDataAdapter Adapter { get; set; }
        public SqlDataReader Reader {get; set;}
        //
        public Client()
        {
            Connection = new SqlConnection(this.ConnectionString);
        }
...
}


Page Load and button click:
C#
protected void Page_Load(object sender, EventArgs e)
    {
        Client client = new Client();
        try
        {
            
            client.Connect();
            client.Command = client.Connection.CreateCommand();
            client.Command.CommandText = "select Nazev, Zacatek_Turnaje from Tournament where ID = @id";
            client.Command.Parameters.Add("@id", System.Data.SqlDbType.Int, 10, "ID").Value = Session["Tournament"];
            client.Reader = client.Command.ExecuteReader();
            DateTime date = new DateTime();
            while (client.Reader.Read())
            {
                lblTournamentName.Text = client.Reader.GetString(0);
                date = client.Reader.GetDateTime(1);
            }
            if (Session["Nickname"] == null || DateTime.Now > date)
            {
                Panel3.Visible = false;
            }
            else
            {
                Panel3.Visible = true;
                lblRegister.Visible = false;
            }
            client.Disconnect();
        }
        catch (Exception)
        {
            
           throw;
        }
        DB_Registration_View tmp = new DB_Registration_View();
        GridView3.DataSource = tmp.Select("A");//in this select is no Player_Reg
        GridView3.DataBind();
        GridView4.DataSource = tmp.Select("B");
        GridView4.DataBind();
        GridView5.DataSource = tmp.Select("C");
        GridView5.DataBind();
        GridView6.DataSource = tmp.Select("D");
        GridView6.DataBind();
        GridView7.DataSource = tmp.Select("E");
        GridView7.DataBind();
        GridView8.DataSource = tmp.Select("F");
        GridView8.DataBind();
        if (GridView5.Rows.Count == 0)
        {
            Panel1.Visible = false;
            Panel2.Visible = false;
        }
        else if (GridView7.Rows.Count == 0)
        {
            Panel1.Visible = true;
            Panel2.Visible = false;
        }
        else
        {
            Panel1.Visible = true;
            Panel2.Visible = true;
        }
        

    }
protected void btnRegister_Click(object sender, EventArgs e)
    {
        Client client1 = new Client();
        Client client2 = new Client();
        List<DB_Player> players = new List<DB_Player>();
        try
        {
            client1.Connect();
            client1.Command = client1.Connection.CreateCommand();
            client1.Command.CommandText = "select pla.Nickname from Player pla, Player_Reg plareg where pla.Nickname = plareg.Player_ID and plareg.Team_ID = @id and (plareg.Pozice = 'C' or plareg.Pozice = 'Player') order by plareg.Pozice";
            client1.Command.Parameters.Add("@id", SqlDbType.Int).Value = GridView9.SelectedRow.Cells[1];
            client1.Reader = client1.Command.ExecuteReader();
            while (client1.Reader.Read())
            {
                DB_Player tmp = new DB_Player();
                tmp.Nickname = client1.Reader.GetString(0);
                players.Add(tmp);

            }
            client1.Disconnect();
            client2.Connect();
            client2.Command = client2.Connection.CreateCommand();
            client2.Command.CommandText = "Tournament_Registration";
            client2.Command.CommandType = System.Data.CommandType.StoredProcedure;
            client2.Command.Parameters.Add("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            client2.Command.Parameters.Add("@Team_ID", SqlDbType.Int).Value = GridView9.SelectedRow.Cells[1];
            client2.Command.Parameters.Add("@Tournament_ID", SqlDbType.Int).Value = Session["Tournament"];
            client2.Command.Parameters.Add("@Paid", SqlDbType.Int).Value = 0;
            client2.Command.Parameters.Add("@Player1_ID", SqlDbType.VarChar).Value = players[0].Nickname;
            client2.Command.Parameters.Add("@Player2_ID", SqlDbType.VarChar).Value = players[1].Nickname;
            client2.Command.ExecuteNonQuery();
            client2.Disconnect();
            int output = (int)client2.Command.Parameters[0].Value;
            if (output == 0)
            {
                lblRegister.Visible = true;
                lblRegister.Text = "Nepodařilo se registrovat do turnaje! Pravděpodobně jeden z hráčů již hraje v turnaji!";
            }
            else
            {
                lblRegister.Visible = true;
                lblRegister.Text = "Úspěšná registrace!";
            }
        }
        catch (Exception)
        {
            
            throw;
        }
        
    }


Thank you for replies!

-Pepin z Hane
Posted

The error was in the sql statement of one SQL Source for one of the Grid Views... I have solved it! There was missing a comma.
 
Share this answer
 
v3
You can't use multiple table names in a FROM clause:
C#
client1.Command.CommandText = "select pla.Nickname from Player pla, Player_Reg plareg where pla.Nickname = plareg.Player_ID and plareg.Team_ID = @id and (plareg.Pozice = 'C' or plareg.Pozice = 'Player') order by plareg.Pozice";
Probably, you need to join the two tables, or remove the second table name.
 
Share this answer
 
Comments
MT_ 19-Oct-12 4:27am    
or use dataset?
Pepin z Hane 19-Oct-12 4:46am    
In 99% cases it didn't threw me an error joining tables like this in where clause, it doesn't matter if you use inner join or something, or you write the equality between PK and FK to where clause.
OriginalGriff 19-Oct-12 4:48am    
It's not the WHERE clause that's the problem: it's the FROM clause. Only one table name is expected there.
Inside btnRegister_Click is the following code....

"
SQL
select pla.Nickname from Player pla, Player_Reg plareg where pla.Nickname = plareg.Player_ID and plareg.Team_ID = @id and (plareg.Pozice = 'C' or plareg.Pozice = 'Player') order by plareg.Pozice


Your Sql statement is completely wrong. As far as I can see you're from is in the wrong place. As well as if Player_Reg
is another Table you need to join you need to join it with a left join or something along those lines. Here is a joining tables.

http://msdn.microsoft.com/en-us/library/ms191517(v=sql.105).aspx - Joining Tables
 
Share this answer
 
v2
Comments
Pepin z Hane 19-Oct-12 4:45am    
In 99% cases it didn't threw me an error joining tables like this in where clause, it doesn't matter if you use inner join or something, or you write the equality between PK and FK to where clause.
select pla.Nickname from Player pla, Player_Reg plareg where pla.Nickname = plareg.Player_ID and plareg.Team_ID = @id and (plareg.Pozice = 'C' or plareg.Pozice = 'Player') order by plareg.Pozice


I don't see anything syntactically wrong with that statement, but it does seem odd because you are joining the tables by pla.Nickname=plareg.Player_Id and then just selecting pla.Nickname. If they really are the same, why not ignore the pla table altogether and just select plareg.PlayerId?
 
Share this answer
 
Comments
Pepin z Hane 19-Oct-12 6:57am    
It's a good idea, i have changed the, no table join is there now, but it's still the same error
Zasky 19-Oct-12 7:06am    
Check this out:
client1.Command.Parameters.Add("@id", SqlDbType.Int).Value = GridView9.SelectedRow.Cells[1];
Does that give the value you expect?
Maybe try "... = Int32.Parse(GridView9.SelectedRow.Cells[1].Text);"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900