Ran into something little that I know I’m going to forget if I don’t write down. It appears that when using a TABLE variable in tsql ( SQL Server 2005 ), you must DECLARE that variable on it’s own line, as opposed to inline with your other @variables.
Typically in my sprocs or sql scripts I do my best to have a main DECLARE block and seperate my @variables with a comma like this.
If you're using a TABLE variable, put it on it's own DECLARE line
After some mucking around, it turns out moving the TABLE @variable to it’s own DECLARE line fixes this issue.
DECLARE TABLE @variables on their own line
I haven’t found this info in SQL BOL, so I hope this helps somebody else.
Thanks Eric, I can now stop beating my head against the wall wondering why it doesn't like my table variable. Does Microsoft do this sort of thing just to annoy us?
Man I'm glad this helped somebody else, I remember how frustrating this one was. My money is on yes, Microsoft does this on purpose.
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(@"Data Source=.sqlexpress;Initial Catalog=rahuldb;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fillgrid();
}
}
public void fillgrid()
{
con.Open();
SqlCommand cmd = new SqlCommand("Select * from table", con);
SqlDataReader dr = cmd.ExecuteReader(); <<—–ERROR
GridView1.DataSource = dr;
GridView1.DataBind();
dr.Close();
con.Close();
}
}
I am gettin an ERROR"Incorrect syntax near the keyword 'table'." can u plz help me out to get rid off dis error…
The code is correct, but the word “table” is a reserved keyword in SQL. I never name my database tables “table” so I've never run into this one before.Do you have any other tables in your database that you can run this code against?-EF