locked
How to Populate a DataTable from my Access query results? RRS feed

  • Question

  • User737013469 posted

    Hi,

    I'm writing a web service that needs to query an Access 2003 database (.mdb).  I've found the following code in doing some research on OLEDB connections and queries:

     

    public class completedb : System.Web.Services.WebService
        {
            [WebMethod]
            public DataSet GetProductDetails();
    
                string connStr = ConfigurationManager.ConnectionStrings["CRADBCon"].ConnectionString;
                string sqlString = "SELECT * FROM ProductDetailsAll";
                OleDbDataAdapter da = new OleDbDataAdapter(sqlString,connStr);
                DataSet dsProds = new DataSet();
               DataTable dt = new DataTable();
                da.Fill(dsProds,"ProductDetailsAll");
    
        }

    Errors are as follows:

    Error 3 A field initializer cannot reference the non-static field, method, or property 'CompleteRentalls.completedb.sqlString' 

    Error 5 'CompleteRentalls.completedb.da' is a 'field' but is used like a 'type' 

    Can anyone help me rectify the above code such that the query results are returned to my datatable?

    Thanks,

    Sid

    Monday, February 21, 2011 2:47 AM

Answers

  • User-693248168 posted

    Hi, Sorry I didnt see that. here is the complete version.

     

    You have to return the dataset that you are preparing.

    public class completedb : System.Web.Services.WebService
        {
            [WebMethod]
            public DataSet GetProductDetails()
            {
                string connStr = ConfigurationManager.ConnectionStrings["CRADBCon"].ConnectionString;
                string sqlString = "SELECT * FROM ProductDetailsAll";
                OleDbDataAdapter da = new OleDbDataAdapter(sqlString, connStr);
                DataSet dsProds = new DataSet();
                DataTable dt = new DataTable();
                da.Fill(dsProds, "ProductDetailsAll");
                return dsProds;
            }
        }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 22, 2011 12:39 AM

All replies

  • User2019981500 posted

    Hi,

    Try below code

     

    public class completedb : System.Web.Services.WebService
        {
    [WebMethod]
    public DataSet GetProductDetails();
    {string query = "Select * from ProductDetailsAll string cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=databasename.mdb;"; DataTable dt = new DataTable("ProductDetails"); using (var cnn = new OleDbConnection(cnnString)) { cnn.Open(); using (var da = new OleDbDataAdapter()) { da.SelectCommand = new OleDbCommand( query, cnn); // Populate the DataTable da.Fill(dt); } }}}

     

     

    Regards

    shabir

    Monday, February 21, 2011 3:43 AM
  • User-693248168 posted

    Hi,

     

    Try this code.

    public class completedb : System.Web.Services.WebService
        {
            [WebMethod]
            public DataSet GetProductDetails()
    {
    
                string connStr = ConfigurationManager.ConnectionStrings["CRADBCon"].ConnectionString;
                string sqlString = "SELECT * FROM ProductDetailsAll";
                OleDbDataAdapter da = new OleDbDataAdapter(sqlString,connStr);
                DataSet dsProds = new DataSet();
               DataTable dt = new DataTable();
                da.Fill(dsProds,"ProductDetailsAll");
    }
    
        }

    Monday, February 21, 2011 3:44 AM
  • User737013469 posted

    Thanks for the suggestion.  I now have the following code:

     

     public class completedb : System.Web.Services.WebService
        {
            [WebMethod]
            public DataSet GetProductDetails()
            {
                string connStr = ConfigurationManager.ConnectionStrings["CRADBCon"].ConnectionString;
                string sqlString = "SELECT * FROM ProductDetailsAll";
                OleDbDataAdapter da = new OleDbDataAdapter(sqlString, connStr);
                DataSet dsProds = new DataSet();
                DataTable dt = new DataTable();
                da.Fill(dsProds, "ProductDetailsAll");
            }
        }

    However, I still receive an error stating that not all code paths return a value.  How do I rectify this error?

    Thanks,

    Sid

    Monday, February 21, 2011 9:19 PM
  • User-693248168 posted

    Hi, Sorry I didnt see that. here is the complete version.

     

    You have to return the dataset that you are preparing.

    public class completedb : System.Web.Services.WebService
        {
            [WebMethod]
            public DataSet GetProductDetails()
            {
                string connStr = ConfigurationManager.ConnectionStrings["CRADBCon"].ConnectionString;
                string sqlString = "SELECT * FROM ProductDetailsAll";
                OleDbDataAdapter da = new OleDbDataAdapter(sqlString, connStr);
                DataSet dsProds = new DataSet();
                DataTable dt = new DataTable();
                da.Fill(dsProds, "ProductDetailsAll");
                return dsProds;
            }
        }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 22, 2011 12:39 AM