CODEWITHSUNDEEP
c#asp.netsqlsql-server
user3166530
user3166530

Reputation:

SQL Server Views with asp.net

I have created a view called view_SelectAll_Student in SQL Server which retrieves all columns from single table name

And I have a function that returns dataset or datatable using dataadapter somehow I get this error:

The request for procedure 'view_SellectAll_Student' failed because 'view_SellectAll_Student' is a view object.

Code:

public DataTable ViewStudentAll() 
{
      cons.Open();

      DataTable dt = new DataTable();

      cmd = new SqlCommand("view_SellectAll_Student", cons);
      cmd.Connection = cons;
      cmd.CommandType = CommandType.StoredProcedure;

      SqlDataAdapter adp = new SqlDataAdapter(cmd);

      adp.Fill(dt);
      cmd.Dispose();
      cons.Close();
      adp.Dispose();

      return dt; 
}

Upvotes: 1

Views: 1535

Answers (4)

Revan
Revan

Reputation: 1144

cons.Open();
DataTable dt = new DataTable();
cmd = new SqlCommand("select * view_SellectAll_Student",cons);
cmd.Connection = cons;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
cmd.Dispose();
cons.Close();
adp.Dispose();
return dt;

Upvotes: 0

smilu
smilu

Reputation: 899

Write it like this. If its a view you should SELECT it otherwise you wont get it.

cmd = new SqlCommand("SELECT * FROM view_SellectAll_Student",cons);
cmd.Connection = cons;
//cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
cmd.Dispose();
cons.Close();
adp.Dispose();
return dt;

TIP: While using a DataAdapter there is no need of con.Open() or con.Close() statement. DataAdapter itself will open and close it.

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

The SqlDataAdapter accepts as first argument an SqlCommand which can be a Select statement or stored procedure.

In this case you can replace "view_SellectAll_Student" with 

"Select * from view_SellectAll_Student"

Upvotes: 0

Simon Whitehead
Simon Whitehead

Reputation: 65059

Views still need to be queried. What you have here is just the view name..

So change this:

cmd = new SqlCommand("view_SellectAll_Student",cons);

to this:

cmd = new SqlCommand("SELECT put, columns, here FROM view_SellectAll_Student",cons);

Make sure you put the columns of the view there (or an asterisk.. if you're that way inclined).

Upvotes: 1

Related Questions