11/21/2024 1:50:14 PM
slxdeveloper.com
Now Live!
|
|
| |
| How to Connect to External Data Using ADO |
|
| Description: |
Welcome to the wonderful world of ADO. With SalesLogix v6, ADO is the new standard for accessing data. Whether you're accessing data from SalesLogix, or from another data source, ADO is the technology you'll use inside of a SalesLogix VBScript. This article will focus on the aspect of accessing external data using ADO. We'll look at three different examples using Access, Excel, and CSV files.
|
| Category: |
Architect How To Articles
|
| Author: |
Ryan Farley
|
| Submitted: |
11/27/2002
|
| |
|
| Stats: |
Article has been read 33497 times
|
Rating:
  - 4.9 out of 5 by 13 users |
|
|
|
fiogf49gjkf0d
Welcome to the wonderful world of ADO. With SalesLogix v6, ADO is the new standard for accessing data. Whether you're accessing data from SalesLogix, or from another data source, ADO is the technology you'll use inside of a SalesLogix VBScript. This article will focus on the aspect of accessing external data using ADO. We'll look at three different examples using Access, Excel, and CSV files.
One of the great things about ADO is that you work with all data, regardless of the source, in the same way. The difference is the connection string. The connection string tells ADO which provider to use and other details of where to find the data. So, whether you are accessing a SQL database, an Access database, an Excel spreadsheet, or a CSV file, you will work with the data in the exact same way. The provider translates the complex details specific to the data source into the interfaces implemented by ADO so you can access the data generically, regardless of where it comes from.
Lets take a look at an example of how to connect to an Access database. We'll use VBScript, but these same examples will work in pure VB as well.
First, the connection string. In this connection string we'll use the Jet 3.51 engine (although we could use Jet 4.0 instead)
Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Data\MyAccessData.mdb
Pretty simple. Let's look at how we would use that. Throughout this article, we'll use the same example of looping though the data to fill a SalesLogix listbox. You'll get the idea of how you could use it to read fields, etc from the data source.
Dim objConn
Dim objRS
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Data\MyAccessData.mdb"
Set objRS = objConn.Execute("select * from customers")
With objRS
ListBox1.Items.Clear
While Not (.EOF Or .BOF)
ListBox1.Items.Add .Fields("CompanyName").Value & ""
.MoveNext
Wend
.Close
End With
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
That was easy enough. To step though what we just did; we made our connection with a connection string using the Jet provider, opened a Recordset, looped through it to fill our listbox, then closed out the objects. Easy.
To read from Excel, there will be little difference. Again, just a different connection string.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Book1.xls;Extended Properties=Excel 8.0;
This uses the Jet 4.0 provider, however, if we wanted to, we could also use the ODBC driver for Excel with a connection string that looked like this:
DBQ=C:\Data\Book1.xls;DRIVER={Microsoft Excel Driver (*.xls)};
Makes little difference, but we'll use the Jet provider. As a note, whenever you see a connection string with a driver specified and curly braces, you can assume that the connection is using an ODBC driver. Let's look at the same example as before, this time using Excel.
Dim objConn
Dim objRS
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Book1.xls;Extended Properties=Excel 8.0;"
'or could use -> objConn.Open "DBQ=C:\Data\Book1.xls;DRIVER={Microsoft Excel Driver (*.xls)};"
'With Excel & ADO, you work with the sheet as a table
Set objRS = objConn.Execute("select * from [Sheet1$]")
With objRS
ListBox1.Items.Clear
While Not (.EOF Or .BOF)
ListBox1.Items.Add .Fields("CompanyName").Value & ""
.MoveNext
Wend
.Close
End With
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
Look familiar? Only the connection string changed. You'll notice from the example above that you work with the spreadsheet as a database, and with the worksheets in the Excel file as the tables.
For our last example, we'll look at using a CSV file as our data source. The connection string for CSV files will differ a little from what we used earlier. First, we will need to indicate in the connection string whether or not the CSV file has a header row. Second, we will be connecting to the directory that contains the CSV file, not the CSV file itself. Once we've connected to the directory, the CSV file will appear as a table in that directory. Let's look at the connection strings.
First, here is how the connection string will look if our CSV file does have a header row (In this example 'C:\Data' is a directory that contains my CSV file):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data;Extended Properties='Text;FMT=Delimited'
This is our connection string if our CSV file does not have a header row (In this example 'C:\Data' is a directory that contains my CSV file):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data;Extended Properties='text;HDR=NO;FMT=Delimited'
Now, when you connect, remember that you are connecting to the directory where the CSV file is located. Any CSV files in that directory would appear as tables. To select the rows from a CSV file, you simply select * from the CSV file. Here's the same example we did earlier to fill the listbox.
Dim objConn
Dim objRS
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data;Extended Properties='text;FMT=Delimited'"
'The CSV file "mydata.csv" is our table
Set objRS = objConn.Execute("select * from mydata.csv")
With objRS
ListBox1.Items.Clear
While Not (.EOF Or .BOF)
ListBox1.Items.Add .Fields("CompanyName").Value & ""
.MoveNext
Wend
.Close
End With
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
Wasn't that easy? Reading CSV files as if they were the same as SQL tables?! Gotta love it.
This article may have only touched upon (and lightly at that) what you can do with connecting to external data from SalesLogix using ADO. You can see that beyond the connection string, there is very little, if any, that differs from data source to data source. Knowing what is possible with ADO, you can make your data access programming a breeze.
Until next time, happy coding.
-Ryan
|
|
|
|
|
|
Rate This Article
|
you must log-in to rate articles. [login here]
|
|
|
|
Please log in to rate article. |
|
|
|
Comments & Discussion
|
you must log-in to add comments. [login here]
|
|
|
- subject is missing.
- comment text is missing.
|
|
|
| ADO Connections
Posted: 11/27/2002 7:07:40 AM | fiogf49gjkf0d Great Stuff......my only problem with this is I'm unclear how to read each record, and each column within the record.
Thanks Ryan! | |
|
| Reading each record...
Posted: 11/27/2002 7:44:03 AM | fiogf49gjkf0d As far as reading each row, that what the loop is doing.
While Not (rs.EOF Or rs.BOF)
'do something with current row here
rs.MoveNext
Wend
Reading the fields is easy. The Recordset has a Fields collection. You can access fields from the collection by name or by index.
sVal = rs.Fields("myfield").value
-or-
sVal = rs.Fields(0).value
I usually append a blank string to the end to avoid having to check if it is null (so if null it will then be a blank string so I won't get errors if I try to use the value)
sVal = rs.Fields("myfield").value & ""
Another article is coming soon (almost done) that will show common ADO items and how to translate the v5 way of doing things (DBOpenSQL to ??? kinds of stuff) to the v6/ADO way.
-Ryan | |
|
| Alternative method of reading fields in a record
Posted: 12/6/2002 5:02:05 PM | fiogf49gjkf0d I have found that using the bang operator (!) in order to retrieve a field value by name is fast, easy, and for me anyway, fairly intuitive. For example:
sval = rs!FieldName
I have read that there may be some performance issues when using this method over using the fields collection (as in Ryan's examples). I have yet to run into a serious issue with it however. | |
|
| True Mike...
Posted: 12/6/2002 7:02:52 PM | fiogf49gjkf0d Thanks for bringing that up. Since the Fields collection is the default indexed property, you can also access the values with the shorter syntax:
sVal = rs(0).Value
And, since the Value property is the default property of a Field object, you can make it even shorter, like this:
sVal = rs(0)
However, I always prefer to be explicit when accessing properties so I go for the full syntax. I feel it leaves less ambiguity for others that may read your code later and leaves less "to chance". Not only that, if you use a With block, you'll have to access it via the Fields collection explicitly anyway:
With rs
sVal = .Fields(0).Value
End With
-Ryan | |
|
| ADO Most valuable with multiple platforms
Posted: 12/9/2002 1:38:02 PM | fiogf49gjkf0d Our order system sits on AS/400 so when user makes a sale
ADO allows sales logix to make query to AS/400 file
then insert as/400 dollar data toSLX table.
If code is kept simple the same code can be used with pervasive, as/400, mssql, 4th demension.... | |
|
| Very Nice
Posted: 2/18/2003 4:26:41 PM | fiogf49gjkf0d Ryan,
Another good Article. And folks, this is not meant to be full definition of ADO. We have books and references for that purpose. I do wish, however, that SalesLogix's manuals included chapters on the use of such things as ADO within the context of Version 6 and VBScript.
Bob Levine
Advantageware, Inc. | |
|
| Updating SLX using ADo and creating TEF files....
Posted: 4/22/2003 8:02:54 AM | fiogf49gjkf0d Is this an easy process and does it happen?
Thanks for any info | |
|
| Re: Updating SLX using ADo and creating TEF files...
Posted: 4/26/2003 2:34:23 PM | fiogf49gjkf0d As long as you use the SalesLogix OLEDB provider for performing your updates or inserts then the creation of TEF files happen automatically. The SalesLogix provider takes care of all of that for you. So, really, the only thing you have in your code other than straight ADO is a connection string to the SLX database via the provider. (See: Understanding the SalesLogix OLE DB Connection String)
If you are dealing with a version of SalesLogix prior to v6, then you cannot write to the database via ADO since v5 and prior does not have an OLEDB provider to use. You'd have to use the SalesLogix API (SlgxApi.DLL) to write to the database from external apps from older versions using methods such as slapi_DBExecuteSQL etc.
-Ryan | |
|
| Re: How to Connect to External Data Using ADO
Posted: 11/11/2004 1:54:03 PM | fiogf49gjkf0d Ryan,
"Another article is coming soon (almost done) that will show common ADO items and how to translate the v5 way of doing things (DBOpenSQL to ??? kinds of stuff) to the v6/ADO way. "
Is this completed yet ? It would help with my conversion a lot.
Patsy
| |
|
| Re: How to Connect to External Data Using ADO
Posted: 11/11/2004 2:16:36 PM | fiogf49gjkf0d Patsy,
Wouldn't it be nice if I could get all the articles I've started at some point completed. Problem is that my interest of what I want to write about changes from time to time and if I didn't complete an article before then it usually doesn't get done.
With the 800 or so users registered on this site, I doubt I am the only one with knowledge to put an article together. I'd love to see articles submitted from others as well (hey, I still have to work for a living too) ;-)
-Ryan | |
|
| Re: How to Connect to External Data Using ADO
Posted: 8/16/2005 8:44:24 AM | fiogf49gjkf0d Thanks Ryan, this article was a real help. Just wondering are there many major differences with regard connections between ADO and ADO.NET? | |
|
| Re: How to Connect to External Data Using ADO
Posted: 8/16/2005 11:08:05 AM | fiogf49gjkf0d Nick,
The concept is the same. The connection string is the key part in connecting to any data source. However the objects used a quite a bit different. You use DataReaders, DataAdapters, and DataSets, instead of Recordsets. While there are still connection and command objects as in classic ADO, the practice of how they are used will differ in some regards. ADO.NET uses primarily a disconnected model, as opposed to classic ADO which is almost entirely connected.
That said, as far as connecting to different data sources, the concepts are the same, more or less, as described in this article.
-Ryan | |
|
| Re: How to Connect to External Data Using ADO
Posted: 9/18/2006 6:33:57 PM | fiogf49gjkf0d Ryan,
I am having trouble getting a Recordset to return from a Stored Procedure in a MAS 500 database. All the SP does is return one row of data that I want to strip out and place into some text controls on a SalesLogix tab. The MAS 500 connection works fine and returns a State of 1 or Open. The SP also executes without any errors, but the RS never Opens and always retruns a State of 0 or closed. I also tired using Output parameters and they came back blank as well. If I try and pull something else from the MAS 500 database like a table the RS works fine and returns the results, but nothing when using the SP. If I run the SP using Query Analyzer connected to the MAS 500 database as SYSDBA user it also works fine from there as well and returns the desired results.
Does Anyone know what this could be?
Thanks | |
|
| Re: How to Connect to External Data Using ADO
Posted: 9/18/2006 6:34:00 PM | fiogf49gjkf0d Ryan,
I am having trouble getting a Recordset to return from a Stored Procedure in a MAS 500 database. All the SP does is return one row of data that I want to strip out and place into some text controls on a SalesLogix tab. The MAS 500 connection works fine and returns a State of 1 or Open. The SP also executes without any errors, but the RS never Opens and always retruns a State of 0 or closed. I also tired using Output parameters and they came back blank as well. If I try and pull something else from the MAS 500 database like a table the RS works fine and returns the results, but nothing when using the SP. If I run the SP using Query Analyzer connected to the MAS 500 database as SYSDBA user it also works fine from there as well and returns the desired results.
Does Anyone know what this could be?
Thanks | |
|
| Re: How to Connect to External Data Using ADO
Posted: 9/18/2006 7:14:14 PM | fiogf49gjkf0d Hi Andrew,
Move this question into the forums, I'll post an answer there (this could get a bit involved and I don't want to clutter up the comments under this article too much and have your question get lost for others searching for it)
Thanks,
-Ryan | |
|
|
|
|
|
Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
|
|
|
|
|
|