none
unsure why my 'oleDbDA.Fill(dataTable)' instruction returns no result RRS feed

  • Question

  • hi
    I am trying to query an SqlBase Database using an OleDbAdaptor, OleDbCommand and DataTable. THe code is written in C#.Net, and I am using VS2008 to write it. I have written the code below, and sucessfully tested the SQL Query within SQLTalk, but somehow within this program the DataTable does not return with any data. Can someone please look at my code and tell me where I went wrong?

    Thanks

    using

     

    System;

    using

     

    System.Collections.Generic;

    using

     

    System.Linq;

    using

     

    System.Text;

    using

     

    System.Data;

    using

     

    System.Data.OleDb;

    using

     

    AmlibObjects;

    using

     

    AmlibdataBase;

    namespace

     

    DataTest

    {

     

    public class Program

    {

     

    protected static OleDbConnection conn;

     

    private static string sql = @"select bh.bor_bar_no, st.stk_item_no, st.stk_key1, st.stk_key4, st.stk_key2, st.stk_is_reserved," +

     

    " st.stk_times_reserved, st.unique_item_no, stk_edition, stk_description, stk_is_on_loan" +

     

    " from ad_stk_item st, bor_history bh where st.stk_item_no = bh.stk_item_no and bor_bar_no = ?";

     

     

    protected static DataTable ExecuteDataTable(OleDbCommand command)

    {

     

    DataTable dataTable = new DataTable();

     

    OleDbDataAdapter oleDbDA = new OleDbDataAdapter();

    oleDbDA.SelectCommand = command;

    //The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property.

    oleDbDA.Fill(dataTable);

     

    return dataTable;

    }

     

    static void Main(string[] args)

    {

     

    string myConnectionString = "Provider=SQLBASEOLEDB.1;Data Source=TELIB;User ID=SYSADM";

    conn =

    new OleDbConnection(myConnectionString);

    conn.Open();

     

    Lib_Member mem = new Lib_Member();

    mem.bor_num =

    "B303";

     

    object[] parameters = new object[] { mem.bor_num };

     

    OleDbParameter parameter = new OleDbParameter("bor_bar_no", parameters);

    parameter.SourceColumn =

    "bh.bor_bar_no";

     

    OleDbCommand command = new OleDbCommand(sql, conn);

    command.Parameters.Add(parameter);

     

    DataTable table = ExecuteDataTable(command);

     

    List<Lib_Item> entities = new List<Lib_Item>();

    • Moved by Gert Drapers (MSFT) Wednesday, August 19, 2009 6:48 PM This is an ADO.NET question, not VSDB (From:Visual Studio Team System - Database Edition)
    Monday, August 17, 2009 3:18 PM

Answers

  • Hi Sephora,

     

    I try to get the record as follows and it works fine on my computer:


       
    public class Program

        {

            protected static OleDbConnection conn;

            private static string sql = @"select * from stu where [id] = ?";

     

            protected static DataTable ExecuteDataTable(OleDbCommand command)

            {

                DataTable dataTable = new DataTable();

                OleDbDataAdapter oleDbDA = new OleDbDataAdapter();

                oleDbDA.SelectCommand = command;

                oleDbDA.Fill(dataTable);

                return dataTable;

            }

     

            static void Main(string[] args)

            {

                string myConnectionString = "Provider=SQLNCLI;Server=1-C5955279A7DB4\\sqlexpress;Database=Student;Uid=sa; Pwd=sa;";

                conn = new OleDbConnection(myConnectionString)

                conn.Open();

     

                OleDbCommand command = new OleDbCommand(sql, conn);

                OleDbParameter parameter = new OleDbParameter("p1", 2);

     

                command.Parameters.Add(parameter);

                DataTable table = ExecuteDataTable(command);

            }

        }


    There are several points:

    1)    Make sure you have the correct connection string.

    2)    Make sure the parameter is set correctly. It is a single value. And in your code, you set an array to it.

    You can output the command before fill method.

     

    If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

     

    Friday, August 21, 2009 4:53 AM
  • I am not sure what the error was, but after moving some lines of code around it worked. The code below is what I am using now.

     protected DataTable ExecuteDataTable(OleDbCommand command, string key)
            {
                DataTable dataTable = new DataTable();
                para = new OleDbParameter();
                para.Value = key;
                command.Parameters.Add(para);
                OleDbDataAdapter oleDbDA = new OleDbDataAdapter(command); //I added the command into the DataAdapter here as well
                //oleDbDA.SelectCommand = command; 
                oleDbDA.Fill(dataTable);
                return dataTable;
            }

            protected IList<T> GenericGetAll<T>(string sql, string key, ObjectGenerator<T> generator)
            {
              
                OleDbCommand command = new OleDbCommand(sql, conn);//, trans);
                //command.Parameters.Add(new OleDbParameter("bor_bar_no", key)); this was here before, moved it up into ExecuteDatateable method
                DataTable table = ExecuteDataTable(command,key);
                List<T> entities = new List<T>();
    • Marked as answer by Sephora Friday, August 21, 2009 8:05 PM
    Friday, August 21, 2009 8:05 PM

All replies

  • Hey just as a verification step, have you explicitly called and made sure that the select statement that you have being called by the adapter will really work. Can you just ensure this first? Perhaps just execute the select command and enumerate through the results to get data back. It could be in the copy and paste the query to code something may have been changed incorrectly.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, August 20, 2009 3:49 PM
  • Hi Sephora,

     

    I try to get the record as follows and it works fine on my computer:


       
    public class Program

        {

            protected static OleDbConnection conn;

            private static string sql = @"select * from stu where [id] = ?";

     

            protected static DataTable ExecuteDataTable(OleDbCommand command)

            {

                DataTable dataTable = new DataTable();

                OleDbDataAdapter oleDbDA = new OleDbDataAdapter();

                oleDbDA.SelectCommand = command;

                oleDbDA.Fill(dataTable);

                return dataTable;

            }

     

            static void Main(string[] args)

            {

                string myConnectionString = "Provider=SQLNCLI;Server=1-C5955279A7DB4\\sqlexpress;Database=Student;Uid=sa; Pwd=sa;";

                conn = new OleDbConnection(myConnectionString)

                conn.Open();

     

                OleDbCommand command = new OleDbCommand(sql, conn);

                OleDbParameter parameter = new OleDbParameter("p1", 2);

     

                command.Parameters.Add(parameter);

                DataTable table = ExecuteDataTable(command);

            }

        }


    There are several points:

    1)    Make sure you have the correct connection string.

    2)    Make sure the parameter is set correctly. It is a single value. And in your code, you set an array to it.

    You can output the command before fill method.

     

    If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

     

    Friday, August 21, 2009 4:53 AM
  • I am not sure what the error was, but after moving some lines of code around it worked. The code below is what I am using now.

     protected DataTable ExecuteDataTable(OleDbCommand command, string key)
            {
                DataTable dataTable = new DataTable();
                para = new OleDbParameter();
                para.Value = key;
                command.Parameters.Add(para);
                OleDbDataAdapter oleDbDA = new OleDbDataAdapter(command); //I added the command into the DataAdapter here as well
                //oleDbDA.SelectCommand = command; 
                oleDbDA.Fill(dataTable);
                return dataTable;
            }

            protected IList<T> GenericGetAll<T>(string sql, string key, ObjectGenerator<T> generator)
            {
              
                OleDbCommand command = new OleDbCommand(sql, conn);//, trans);
                //command.Parameters.Add(new OleDbParameter("bor_bar_no", key)); this was here before, moved it up into ExecuteDatateable method
                DataTable table = ExecuteDataTable(command,key);
                List<T> entities = new List<T>();
    • Marked as answer by Sephora Friday, August 21, 2009 8:05 PM
    Friday, August 21, 2009 8:05 PM