NemanjaT NemanjaT -4 years ago 100
SQL Question

Syntax error while using ADO.NET with ASP.NET MVC

I've been following this tutorial:
http://www.codeproject.com/Articles/522226/ADO-NET-cplustheplusrightplusway

to learn more about ADO.NET inside ASP.NET MVC. But if I put the following code inside an action

var connectionString = ConfigurationManager.ConnectionStrings["StoreCon"].ConnectionString;
var _connection = new SqlConnection(connectionString);
_connection.Open();

using (var command = _connection.CreateCommand())
{
command.CommandText = @"SELECT * FROM dbo.group";
using (var reader = command.ExecuteReader())
{
Console.WriteLine(reader[1]);
}
}


and I try to run it, I get a syntax error, as the system says, somewhere around "keyword
group
".

I'm new to MVC, so sorry if it's something trivial. I also added a connection string:-

<add name="StoreCon"
connectionString=
"Data Source=(LocalDb)\v11.0;
AttachDbFilename=|DataDirectory|\Items.mdf;Integrated Security=True"
providerName="System.Data.SqlClient" />


and added a database called Items.mdf with a table called dbo.group with some rows named 'Name' and sample data.

Answer Source

First of all, you need to provide escape group with square brackets as it is a reserved keyword,

SELECT * FROM [dbo].[group]

and from your comments then you need to use DataReader.Read to retrieve the data like this for "Invalid attempt to read when no data is present" error.

using (var reader = command.ExecuteReader())
{
  while (reader.Read()) 
  {
   //your code here
  }
}

It would be better if you write a separate question for second error.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download