locked
SqlDataAdapter fails to return any data, but Reader.read() returns data? RRS feed

  • Question

  • User-468333257 posted

    Using SQL Server Management studio 2012 with VS 2012

    Function is as follows:

    public DataTable GetInvoice(){
    string connstr=@"Data Source=........" //this connection string there is nothing wrong
    string sql="SELECT * FROM Invoice";
    Datatable dt= new DataTable();
    using(SqlConnection con=new SqlConnection(connstr)){
    try{
    con.Open();
    }
    catch{
    
    return dt;
    }
    using(SqlCommand cmd =new SqlCommand(sql,con)){
    using(SqlDataAdapter adp=new SqlDataAdapter(cmd)){
    
    adp.Fill(dt);
    }
    }
    
    
    
    return dt;
    
    }
    
    
    
    
    
    }

    This does not work, however if I use

    SqlDataReader reader=cmd.ExecuteReader();

    reader.Read();

    int invoiceid=(int)reader["InvoiceID"];

    It is able to read 

    What does this mean?

    Tuesday, June 18, 2013 11:59 AM

All replies

  • User-1629691846 posted

    In dataadapter you have to pass datatable or dataset, so u just use the fill method as :

    adp.Fill(dt);

    //I think you have updated the question, right?

    Tuesday, June 18, 2013 12:06 PM
  • User-468333257 posted

    yes I did that, yet there is no rows in the dataTable

    I wonder why?

    Confirmed that there are already data in the Invoice table 

    Tuesday, June 18, 2013 12:10 PM
  • User-468333257 posted

    one of the columns in the table is decimal(8,4)

    wonder if that might cause a problem in 

    adp.Fill();

    Tuesday, June 18, 2013 12:24 PM
  • User1938476581 posted

    Hi,

    try this

    using (SqlConnection c = new SqlConnection(
    		Properties.Settings.Default.DataConnectionString))
    	    {
    		c.Open();
    		// 2
    		// Create new DataAdapter
    		using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM ..", c))
    		{
    		    // 3
    		    // Use DataAdapter to fill DataTable
    		    DataTable t = new DataTable();
    		    a.Fill(t);
    
    		    // 4
    		    // Render data onto the screen
    		    // dataGridView1.DataSource = t; // <-- From your designer
    		}
    	    }
    

    And refer here

    http://www.dotnetperls.com/sqldataadapter

    Hope it can help you

    Wednesday, June 19, 2013 3:43 AM
  • User-468333257 posted

    how does using Properties.Settings.Default.DataConnectionString make any difference?

    Used the above method in VS 2010 no problems at all, only when in VS 2012 I can't get it to work

    I do not wish to use reader as I have to read every column for every row

    Is there problems in getting SqlDataAdapter to work in VS2012?

    I heard there are also problems with getting gridview to work in VS 2012 too

    Wednesday, June 19, 2013 6:29 AM
  • User-468333257 posted

    would like to know if there are any support for this for VS 2012

    Friday, June 21, 2013 11:28 PM