How to Populate a DataTable from my Access query results?

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

Answer 1

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

Answer 2

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");
}

    }

Answer 3

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

Answer 4

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;
        }
    }