Hi;

I am relatively new to this forum and to C#.net. I am trying to retrieve data from an SQLBase database using C#.net, System.Data.OleDb namespace on visual studios 2008. i have executed the SQL statement given within the code (copy and pasting it into SQLTalk) and it worked fine. when I run it within the code however, the DataTable returns with zero elements. What am I doing incorrectly?

This is what I have coded.

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;  
            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";
           OleDbParameter parameter = new  OleDbParameter("bor_bar_no", mem.bor_num);
           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>();

Hello.
Your trouble may lay here:

OleDbDataAdapter oleDbDA = new OleDbDataAdapter();

try to create your Data Adapter based on your connection:

OleDbDataAdapter oleDbDA = 
        new OleDbDataAdapter(selectCommand, connection);

unfortunately it still returns with an empty table when i use the
1.
OleDbDataAdapter oleDbDA = new OleDbDataAdapter(selectCommand, connection) synthax

I also tried manually updating the value of the parameter, like what is given below

command.Parameters.Add("@bh.bor_bar_no", OleDbType.VarChar, 25).Value = bor_bar_no;
            DataTable table = ExecuteDataTable(command);
            List<Lib_Item> entities = new List<Lib_Item>();

but still nothing. Any one else has any other ideas..please?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.