ADODO Issue

Question

sundog1 0 Posting Whiz in Training

12 Years Ago

Hi again guys...

Right, Having issue with a ADODB connection as I have never used one before. Currently trying to read files out of a folder using the ODBC connection that comes with the Software I want to extract data from.

I have managed to get the connection to the database and currently upto the part where I want to pass in SQL Statemnet

However, what should I use instead of the 'SqlDataAdapter', and the the 'Fill' option I Have done in the past with SQL Databases.

My code is below:

   {

        //**SAGE LINE 50 CONNECTION**\\
        private ADODB.Connection adoConn = new ADODB.Connection();
        private ADODB.Recordset adoRS = new ADODB.Recordset();


        public Form1()
        {
            InitializeComponent();


        }

        //**SQL EXPRESS CONNECTION INFORMATION**\\
        System.Data.SqlClient.SqlConnection con;
        System.Data.SqlClient.SqlDataAdapter da;
        DataSet ds1;
        int inc = 0;

        private void Form1_Load(object sender, EventArgs e)
        {

            //**ADODB CONNECTION INFORMATION**\\
            adoConn = new ADODB.Connection();
            adoRS = new ADODB.Recordset();

            adoConn.Open("SageLine50v17", "Manager", "", 0);

            string Sage50SQL = "SELECT SALES_LEDGER.ACCOUNT_REF,SALES_LEDGER.ADDRESS_1 FROM SALES_LEDGER";



            //**SQL EXPRESS CONNECTION INFORMATION**\\
            con = new System.Data.SqlClient.SqlConnection();
            ds1 = new DataSet();

            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            string sql = "SELECT * FROM tblWorkers";
            da = new System.Data.SqlClient.SqlDataAdapter(sql, con);

            con.Open();

            da.Fill(ds1, "Workers");
            NavigationRecords();
            con.Close();
        }

        private void NavigationRecords()
        {
            DataTable dtable = ds1.Tables["Workers"];

            for (int i = 0; i < dtable.Rows.Count; i++)
            {
                DataRow dRow = ds1.Tables["Workers"].Rows[i];



                ListViewItem item = new ListViewItem(dRow["first_Name"].ToString());
                item.SubItems.Add(dRow["last_Name"].ToString());
                item.SubItems.Add(dRow["job_Title"].ToString());

                listView1.Items.Add(item);

            }


        }

Bascially I need the ADODB Connection to list all the data in a listview as before but the difference with this data set is it would be live and need refreshing each time the form is loaded.

I dont have much knowledge using ADO so any help would be great!

Kind Regards
Mark B.

3 Contributors
19 Replies
267 Views
4 Days Discussion Span
Latest Post 12 Years Ago Latest Post by lolafuertes

lolafuertes 145 Master Poster

12 Years Ago

The message you receive means that on a OleDb command you must use and OleDb connection, not an AdoDb one.

Hope this helps.

lolafuertes 145 Master Poster

12 Years Ago

Sorry. Too fast reading. Anyway, naming is relevant to find errors.

Edited 12 Years Ago by lolafuertes

Reply to this topic

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.

wen_cai -3 Junior Poster in Training · Answer 1 · 2012-05-18T12:36:27+00:00

You can use OleDbDataAdapter instead of SqlDataAdapter

Example: OleDbDataAdapter daTitles=new OleDbDataAdapter();

Then you create your dataset and use the OleDbDataAdapter to fill your dataset

sundog1 0 Posting Whiz in Training · Answer 2 · 2012-05-18T13:01:44+00:00

Hi Wen.

I've tried adding the OleDnDataAdapter as I have in the way I connect and fill the SQL dataset but the 'OleDbDataAdapter' line has a red sqwiggle underneath and states

"Error 1 The best overloaded method match for 'System.Data.OleDb.OleDbDataAdapter.OleDbDataAdapter(string, string)' has some invalid arguments C:\Users\mbayliss\Documents\Visual Studio 2010\Projects\AccessDB_and_load_selected Item into Text Boxes\AccessDB_and_load_selected Item into Text Boxes\Form1.cs 49 19 AccessDB_and_load_selected Item into Text Boxes"

Also when I try to call my 'Sage50Connection' method it states it cannot convert the section 'S50ds' from a ADODB Connection string.

   public partial class Form1 : Form
    {

        //**SAGE LINE 50 CONNECTION**\\
        private ADODB.Connection adoConn = new ADODB.Connection();
        private ADODB.Recordset adoRS = new ADODB.Recordset();



        public Form1()
        {
            InitializeComponent();


        }

        //**SQL EXPRESS CONNECTION INFORMATION**\\
        System.Data.SqlClient.SqlConnection con;
        System.Data.SqlClient.SqlDataAdapter da;
        DataSet ds1;
        int inc = 0;
        OleDbDataAdapter dba;

        private void Form1_Load(object sender, EventArgs e)
        {

            //**ADODB CONNECTION INFORMATION**\\
            adoConn = new ADODB.Connection();
            adoRS = new ADODB.Recordset();
            DataSet S50ds;


            adoConn.Open("SageLine50v17", "Manager", "", 0);

            string Sage50SQL = "SELECT SALES_LEDGER.ACCOUNT_REF,SALES_LEDGER.ADDRESS_1 FROM SALES_LEDGER";

            dba = new System.Data.OleDb.OleDbDataAdapter(Sage50SQL,adoConn);

            dba.Fill(S50ds, "Sage50");
            Sage50Connection();
            adoConn.Close();

            //////////////////////////////////////////////////////////////////////////////

            //**SQL EXPRESS CONNECTION INFORMATION**\\
            con = new System.Data.SqlClient.SqlConnection();
            ds1 = new DataSet();

            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            string sql = "SELECT * FROM tblWorkers";
            da = new System.Data.SqlClient.SqlDataAdapter(sql, con);

            con.Open();

            da.Fill(ds1, "Workers");
            NavigationRecords();
            con.Close();
        }

        private void NavigationRecords()
        {
            DataTable dtable = ds1.Tables["Workers"];

            for (int i = 0; i < dtable.Rows.Count; i++)
            {
                DataRow dRow = ds1.Tables["Workers"].Rows[i];



                ListViewItem item = new ListViewItem(dRow["first_Name"].ToString());
                item.SubItems.Add(dRow["last_Name"].ToString());
                item.SubItems.Add(dRow["job_Title"].ToString());

                listView1.Items.Add(item);

            }


        }

        private void LvSelect2TextBox(Object sender, EventArgs e)
        {
            txtFirstName.Text = listView1.SelectedItems[0].SubItems[0].Text;
            txtLastName.Text = listView1.SelectedItems[0].SubItems[1].Text;
            txtJobTitle.Text = listView1.SelectedItems[0].SubItems[2].Text;
        }

        private void txtFirstName_TextChanged(object sender, EventArgs e)
        {

        }

        private void Sage50Connection()
        {
            DataTable dtable1 = S50ds.Tables["Sage50"];

            for (int i = 0; i < dtable1.Rows.Count; i++)
            {
                DataRow dRow = S50ds.Tables["Sage50"].Rows[i];



                ListViewItem item = new ListViewItem(dRow["ACCOUNT_REF"].ToString());
                item.SubItems.Add(dRow["ADDRESS_1"].ToString());


                listView2.Items.Add(item);
            }
        }

    }
}

I feel like i'm nearly there and once I've managed to get this far I can add-on or take away the parts I need.

Kind Regards
Mark

wen_cai -3 Junior Poster in Training · Answer 3 · 2012-05-18T13:25:36+00:00

Nono.... create a new OleDbDataAdapter using the OleDbDataAdapter() constructor. From there you set your sql command. Once that is done, you just fill your dataset. It is very similar to SqlDataAdapter.

Look at the example on http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter(v=vs.71).aspx

Change Line 37 and I think you are good to go.

sundog1 0 Posting Whiz in Training · Answer 4 · 2012-05-18T14:17:26+00:00

Hi Wen,

I'm still a little stick with this Constructor..

So close to complete this mile stone but still keep falling over.

I have added the OleDbDataAdapter and created the SelectCommand as per the example of the MSN Website but it does not like passing in my SQL Query from a String vairable. Am I missing something here?

        private void Form1_Load(object sender, EventArgs e)
        {

            //**ADODB CONNECTION INFORMATION**\\
            adoConn = new ADODB.Connection();
            adoRS = new ADODB.Recordset();
            DataSet S50ds;


            adoConn.Open("SageLine50v17", "Manager", "", 0);

            string Sage50SQL = "SELECT SALES_LEDGER.ACCOUNT_REF,SALES_LEDGER.ADDRESS_1 FROM SALES_LEDGER";

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = new OleDbCommand(Sage50SQL, adoConn);
            adapter.Fill(S50ds, "Sage50");

            Sage50Connection();
            adoConn.Close();

            //////////////////////////////////////////////////////////////////////////////

            //**SQL EXPRESS CONNECTION INFORMATION**\\
            con = new System.Data.SqlClient.SqlConnection();
            ds1 = new DataSet();

            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            string sql = "SELECT * FROM tblWorkers";
            da = new System.Data.SqlClient.SqlDataAdapter(sql, con);

            con.Open();

            da.Fill(ds1, "Workers");
            NavigationRecords();
            con.Close();
        }

        private void NavigationRecords()
        {
            DataTable dtable = ds1.Tables["Workers"];

            for (int i = 0; i < dtable.Rows.Count; i++)
            {
                DataRow dRow = ds1.Tables["Workers"].Rows[i];



                ListViewItem item = new ListViewItem(dRow["first_Name"].ToString());
                item.SubItems.Add(dRow["last_Name"].ToString());
                item.SubItems.Add(dRow["job_Title"].ToString());

                listView1.Items.Add(item);

            }


        }

        private void LvSelect2TextBox(Object sender, EventArgs e)
        {
            txtFirstName.Text = listView1.SelectedItems[0].SubItems[0].Text;
            txtLastName.Text = listView1.SelectedItems[0].SubItems[1].Text;
            txtJobTitle.Text = listView1.SelectedItems[0].SubItems[2].Text;
        }

        private void txtFirstName_TextChanged(object sender, EventArgs e)
        {

        }

        private void Sage50Connection()
        {
            DataTable dtable1 = S50ds.Tables["Sage50"];

            for (int i = 0; i < dtable1.Rows.Count; i++)
            {
                DataRow dRow = S50ds.Tables["Sage50"].Rows[i];



                ListViewItem item = new ListViewItem(dRow["ACCOUNT_REF"].ToString());
                item.SubItems.Add(dRow["ADDRESS_1"].ToString());


                listView2.Items.Add(item);
            }
        }

I would love to get this little issue fixed today if possible so any help would be gratfully recieved!

Kind regards
Mark :)

wen_cai -3 Junior Poster in Training · Answer 5 · 2012-05-18T21:27:47+00:00

What is the error you receive? I am rather slow in a Saturday morning so I would appreciate if you could just post your error here without me having to scan through your code above. Thanks.

sundog1 0 Posting Whiz in Training · Answer 6 · 2012-05-21T08:09:40+00:00

good morning Wen,

Many thanks for your continued support in helping me crack this.

**1st error states the following: **
"The best overloaded method match for 'System.Data.OleDbCommand.OleDbCommand(string,System.Data.OleDbConnection)' has some invalid Arugements'.

--This errror seems to refer to the the OleDd adapater.SelectCommand line.--

Second Error states:

'Argument 2: cannot convert from ADODB.Connection' to 'System.Data.OleDb.OleDbConnection'

3 and 4 errors are:

The name 'S50ds' does not exist in the current context.

wen_cai -3 Junior Poster in Training · Answer 7 · 2012-05-21T12:21:07+00:00

No problem buddy. Can you replace your code with the code below? It is another way to connect to OleDb.

OleDbConnection adoConn = new OleDbConnection(strAccessConn);
StrAccessConn is your string connection. Similar to your con.ConnectionString

OleDbCommand myAccessCommand = new OleDbCommand(Sage50SQL,adoConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

Replace the line below with the line above. This will create a new SQL command instead of adding it to the adapter using the .SelectCommand. Normally the .SelectCommand should work but for your case I am not sure why it does not. So lets try to switch alternative solution and see if it works.

adapter.SelectCommand = new OleDbCommand(Sage50SQL, adoConn);

adoConn.Open();
myDataAdapter.Fill(S50ds, "Sage50");

Then we open the connection and fill the dataset.

Good luck. = )

sundog1 0 Posting Whiz in Training · Answer 8 · 2012-05-21T12:51:31+00:00

Hi wen,

Many thanks for your reply. I'm obvioulsy going somewhere seriously wrong here. Feel like an idiot.

I think its Monday morning blues.. heh.. any help to point where I'm going wrong would be great.. I think I need a coffee.

            //**ADODB CONNECTION INFORMATION**\\
            //adoConn = new ADODB.Connection();
            OleDbConnection adoConn = new OleDbConnection(strAccessConn);

            adoRS = new ADODB.Recordset();
            DataSet S50ds;


            strAccessConn.Open("SageLine50v17", "Manager", "", 0);

            string Sage50SQL = "SELECT SALES_LEDGER.ACCOUNT_REF,SALES_LEDGER.ADDRESS_1 FROM SALES_LEDGER";


            OleDbCommand myAccessCommand = new OleDbCommand(Sage50SQL, adoConn);
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
            //OleDbDataAdapter adapter = new OleDbDataAdapter();
            //adapter.SelectCommand = new OleDbCommand(Sage50SQL, adoConn);
            //adoConn.Open();
            //adapter.Fill(S50ds, "Sage50");

            Sage50Connection();
            adoConn.Close();

Kind regards
Mark

wen_cai -3 Junior Poster in Training · Answer 9 · 2012-05-21T12:59:59+00:00

Line 9: strAccessConn suppose to be a string where your connection string is.

Example: string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BugTypes.MDB";

That said, it does not have an Open method.

strAccessConn.Open("SageLine50v17", "Manager", "", 0);

What you are looking for is adoConn.Open().

If you encounter any problem, feel free to post it here with the error message. Thanks.

Regards,
Wen

sundog1 0 Posting Whiz in Training · Answer 10 · 2012-05-21T13:25:26+00:00

Hi Wen,

It now talks about ..

"Error 1 Cannot use local variable 'strAccessConn' before it is declared"

I feel like we are getting closer.

           //**ADODB CONNECTION INFORMATION**\\
            //adoConn = new ADODB.Connection();
            OleDbConnection adoConn = new OleDbConnection(strAccessConn);

            adoRS = new ADODB.Recordset();
            DataSet S50ds;


            string strAccessConn= "SageLine50v17"; //"Manager","",0";

            string Sage50SQL = "SELECT SALES_LEDGER.ACCOUNT_REF,SALES_LEDGER.ADDRESS_1 FROM SALES_LEDGER";


            OleDbCommand myAccessCommand = new OleDbCommand(Sage50SQL, adoConn);
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
            //OleDbDataAdapter adapter = new OleDbDataAdapter();
            //adapter.SelectCommand = new OleDbCommand(Sage50SQL, adoConn);
            //adoConn.Open();
            //adapter.Fill(S50ds, "Sage50");

            Sage50Connection();
            adoConn.Close();

wen_cai -3 Junior Poster in Training · Answer 11 · 2012-05-21T14:05:00+00:00

Your strAccessConn is similar to your con.ConnectionString. Your con.connectionString is tied to SQLExpress. However, you want to connect to your oledb so get your connection string that is tied to oledb and insert that value in.

sundog1 0 Posting Whiz in Training · Answer 12 · 2012-05-21T14:17:46+00:00

Hi Wen,

I think i know what you mean but According to the information given by Sage 50 on connecting to the database, all you need to do is point the connection to the ODBC driver. Its a read-only access and the file types of data a .DTA files which ODBC does not understand. :S

wen_cai -3 Junior Poster in Training · Answer 13 · 2012-05-21T21:52:32+00:00

Morning mark,

Hmmm..... ODBC does not work? I done some research in the dta file and most people says you have to open it using ODBC. You are using SAGE ODBC driver right? I am not sure if the oledb can read the file as I have not try it on that type of extension file but theoretically it should be able to just as long as you find the right property string to it.

Regards,
Wen

sundog1 0 Posting Whiz in Training · Answer 14 · 2012-05-22T07:32:22+00:00

Hi guys.

the information from Sage says its must be conntected to via ADO.using the ODBC driver that is installed to the computer when you install their Line 50 application.

sundog1 0 Posting Whiz in Training · Answer 15 · 2012-05-22T09:58:33+00:00

Hi guys,

this is the example code given by sage on how to connect to their Data on a Read-Only basis.

I have copied it word-by-word just to test it works but get some errors when doing so. I have noted them below the code Snippet.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Line50
{
    public partial class Form1 : Form
    {
        private ADODB.Connection adoConn = new ADODB.Connection();
        private ADODB.Recordset adoRS = new ADODB.Recordset();


        public Form1()
        {
            InitializeComponent();

            adoConn.Open("SageLine50v17", "Manager", "", 0);


            adoRS =
        adoConn.OpenSchema(ADODB.SchemaEnum.adSchemaTables, null,
        System.Reflection.Missing.Value);


            while (!(adoRS.EOF))
            {
                comboBox1.Items.Add(adoRS.Fields["TABLE_NAME"].Value.ToString());

                adoRS.MoveNext();
            }

            adoRS.Close();


        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Declare variables
            string strSelect = null;
            ListViewItem LvItem = default(ListViewItem);

            const int adCmdText = 1;

            //Build SQL Statement and open
            strSelect = "SELECT * FROM " + comboBox1.Text;
            adoRS.Open(strSelect, adoConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockPessimistic, adCmdText);

            if (adoRS.EOF == false)


                adoRS.MoveFirst();


                //Clear any existing data
                LvMain.Clear();
                LvMain.View = View.Details;



                //Create Columns

                for (int i = 0; i <= adoRS.Fields.Count - 1; i++)
                {
                    LvMain.Columns.Add(adoRS.Fields[i].Name, 100);
                }

                LvMain.BeginUpdate();

                do
                {
                    LvItem = new ListViewItem();
                    LvItem.Text = 
                    Convert.ToString(adoRS.Fields[0].Value);




                    for (int i = 1; i <= adoRS.Fields.Count - 1; i++)
                    {



                        try
                        {
                            LvItem.SubItems.Add(Convert.ToString(adoRS.Fields[i].Value));
                        }
                        catch
                        {

                            LvItem.SubItems.Add(" ");
                        }

                        LvMain.Items.Add(LvItem);
                        adoRS.MoveNext();

                    } while (!(adoRS.EOF == true));


                    LvMain.EndUpdate();

                    adoRS.Close();

            }  
            else
            {
                LvMain.Clear();
                adoRS.Close();
            }

            LvMain.Focus();

        }

    }

} expected on line 125 col.6
Syntax errror '(' expected on line 114 col. 14
Syntax error, 'while' expected on line 114 col.14
Invalid expression term 'else' on line 115 col.17
*; expected on line 115 col. 17
) expected on Line 115 col. 17

sundog1 0 Posting Whiz in Training · Answer 16 · 2012-05-22T10:05:52+00:00

If I force a Run-Debug on this code I get the following error message also.

Capture48

regards
Mark.

wen_cai -3 Junior Poster in Training · Answer 17 · 2012-05-22T10:59:35+00:00

For your first error is because you miss an open {. Line 59 seems to be missing an open bracket. As for your second error is because you did not handle the exception. Add a try catch at line 57 and you good to go.