How to connect JDE database by using ODBC in Visual Basic?

[tee]

Hi,

How to connect JDE database by using ODBC in Visual Basic.
I tried the following connection.
Set db = OpenDatabase("Business Data - TEST", _
' dbDriverNoPrompt, True, _
"ODBC;DATABASE=TESTDTA;UID=JDESMS;PWD=JDESMS;DSN=Business Data - TEST")

but i hit error "ODBC-Call Failed"

In iSeries Access for Windows ODBC Setup, Data source name :Business Data - TEST, System:S65F996B, SQL default Library : TESTDTA, Package Library :TESTDTA, Driver: iSeries Access ODBC Driver


Please give some guidance.

Thanks

tee

 

[HMX]

hi,
i think that connecting with the oda dsn is more secure when u r in when u have only to read data. i've inserted this part of code in a vba module:
Public Function connecter(UserName_ As String, Password_ As String)
cnnstring = "CONVERTCURRENCY=N;CONVERTJULIANDATES=;CONVERTUDC=N;ConvertFun=ffffffff;DISPLAYOPTIONS=2;DSN=OneWorld ODA;DateTimeFun=ffffffff;ENVIRONMENT=PROD2;LONGCOLUMNNAMES=Y;LONGTABLENAMES=N;NumericFun=ffffffff;PWD=" & Password_ & ";SHIFTDECIMALS=;StringFun=ffffffff;SystemFun=ffffffff;UID=" & UserName_ & ""
cnn.Open cnnstring
Set cmd.ActiveConnection = cnn
rs.CursorLocation = adUseServer
cmd.CommandText = " select distinct COMCU from F4105 "
rs.Open cmd, , adOpenDynamic, adLockBatchOptimistic
rs.MoveFirst
i = 1
Do While Not rs.EOF
Feuil1.Cells(i, 1) = rs.Fields.Item(0)
i = i + 1
rs.MoveNext
Loop
rs.Close
cnn.Close
End Function

 

[brother_of_karamazov]

OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SERVERNAME;User ID=ID;Password=PASSWORD'
).jde_dv7334.dv7334.f983051

 

[tee]

Hi HMX,

May I know what JDE library or other library should I add into the Project References?
From your sample coding, what is the object of "cnn" and "cmd". I thought "cnn" is defined as Connection buy the "Open" function not exist. The Connection only have "OpenRecordset" method.

For the cnnstring,"ConvertFun", "DateTimeFun","NumericFun","StringFun",and "SystemFun", where should I refer to for the value?

Please advice me.

Thanks

tee

 

[tee]

Hi brother of karamazov,

Any library file or JDE librabry should I add to Project Reference to use your "OPENDATASOURCE" function.

Thanks

tee

 

[tee]

Hi,

FYI, I am using ERP 8.0.

 

[HMX]

hi,
this is the declaration of the connection and command:
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
they r included in the Activex Data Object library

 

[HMX]

i forgot to say that the values of "ConvertFun", "DateTimeFun","NumericFun", "StringFun",and "SystemFun" are configured in the DSN in your system ODBC so u can keep the values presented in the code

 

[tee]

Hi,

My script as the following,
sConnString = "CONVERTCURRENCY=N;CONVERTJULIANDATES=;CONVERTUDC=N;ConvertFun=ffffffff;DISPLAYOPTIONS=2;DSN=Business Data - TEST;DateTimeFun=ffffffff;ENVIRONMENT=PROD2;LONGCOLUMNNAMES=Y;LONGTABLENAMES=N;NumericFun=ffffffff;PWD=" & Password_ & ";SHIFTDECIMALS=;StringFun=ffffffff;SystemFun=ffffffff;UID=" & UserName_ & ""
conn.Open sConnString
Set cmd.ActiveConnection = conn
cmd.CommandText = "select * from F41021 "
cmd.CommandType = adCmdText
Set rs = cmd.Execute
MsgBox rs.RecordCount

All the ConvertFun,DateTimeFun,NumericFun,StringFun,SystemFun variable I set to "ffffffff". I can't see any of the variable in the DSN setting.

The result the I get is record count = -1. Where's go wrong?

Please advise me.

thanks

 

[ice_cube210]

Hi Tee,

I have used the following VB code to connect to my AS/400 JDe database


Dim con as Connection
Set con = New Connection
Dim rs as Recordset
Set rs = New Recordset

con.ConnectionString = "DSN=Business Data -TEST;UID=JDE;PWD=xxx"

rs.open "your SQL query",con,<cursor options>

I am not aware of the other settings you were trying to use, for the conversions to take place

 

[tee]

hi,

I used the following vb code for connection:

Dim conn As New adodb.Connection
Dim rs As New adodb.Recordset
conn.ConnectionString = "DSN=Business Data -TEST;UID=JDESMS;PWD=jdesms"
rs.Open "Select * from F41021", conn, adOpenDynamic

but i hit Runtime Error "3709" - The connection cannot be used to perform this operation.It is either closed or invalid in this context.

Please help me

Thanks

 

[JDEAT]

Hi,
if your databese resides on an as400 i would prefer to create your own ODBC DataSource which points to the DataBase and make sure you set the nameing convention parameter of the odbc driver to set to '*SQL' instead of (*SYS). It's on the Second Tab of the ODBC Connection Setup.

I wrote several VB Applications which are connecting to JDE DataSources without any Problems.
Please let me know if you need more Details.

Hope this Helps..

 

[lhernandez]

Dim conn As New adodb.Connection
Dim rs As New adodb.Recordset
conn.ConnectionString = "DSN=Business Data -TEST;UID=JDESMS;PWD=jdesms"
rs.Open "Select * from F41021", conn, adOpenDynamic

have you tried
rs.Source = "Select * from F421021"
rs.Open , conn, adOpenStatic, adLockReadOnly

This one used to work for me (using the server PATH) but that shouldn't do any difference

 

[ice_cube210]

Hi tee,

You did not instantiate the conn and rs objects

you code should look like this

Dim conn As New adodb.Connection
Dim rs As New adodb.Recordset
set conn = New adodb.Connection
set rs = New adodb.Recordset
conn.ConnectionString = "DSN=Business Data -TEST;UID=JDESMS;PWD=jdesms"
rs.Open "Select * from F41021", conn, adOpenDynamic


Give it a try

 

[tee]

Hi JDEAT,
I have changed the naming convention to "*SQL" and tried the following coding that advised by lhernandez and ice_cube210.

Dim conn As New adodb.Connection
Dim rs As New adodb.Recordset
Set conn = New adodb.Connection
Set rs = New adodb.Recordset
conn.ConnectionString = "DSN=Business Data -TEST;UID=JDESMS;PWD=jdesms"
'rs.Open "Select * from F41021", conn, adOpenDynamic
rs.Source = "Select * from F41021"
rs.Open , conn, adOpenDynamic, adLockReadOnly

But I still hit Runtime Error "3709" - The connection cannot be used to perform this operation.It is either closed or invalid in this context.

Any problem with my coding, I suspected may be is my ODBC DataSource is not created properly. Can you please give me some sample code for me to connect to JDE DataSources or explain for me more details.

Attached is the captured screen of ODBC Setup which is more details.

Please assist me.

thanks alot

 

[tee]

hi,

Attached is the captured screen of iSeries Navigator, may be it help you to know more details of my setting.

Thanks

 

[dschlieder]

"3709" - The connection cannot be used to perform this operation.It is either closed or invalid in this context.


You haven't opened the connection. Ergo it is closed.

conn.open

before you try to open the recordset.

Remember to close your connections and recordsets when you are done with them. Some programmers like to also set them to nothing when they are through with them as well.

Dave

 

[tee]

Hi,

The following is my amended coding,

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New Command
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New Command

conn.ConnectionString = "DSN=TESTDATA;UID=JDESMS;PWD=jdesms"
conn.Open
Set cmd.ActiveConnection = conn
cmd.CommandText = "Select * from F41021"
cmd.CommandType = adCmdText

Set rs = cmd.Execute

MsgBox rs.RecordCount

Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

Unfortunately, the record count is -1.
I tried to retrieve the data from F41021 using Ms Excel, it has data but when I tried above coding, I get -1 record count. Where goes wrong?

Please advise me,

TQ

 

[JDEAT]

Hi tee,
this week i am out of my office, but i will be back on monday next week. As soon as i have access to my sources i will provide you a small sample vb application ;-)
Don't worry and be patient

 

[SQLGAL]

We may be able to help you look deeper with our iSeries Navigator (OpsNav) plug in. Go to:

http://www.centerfieldtechnology.com/tools/deltatracker.asp

Go thru the reports and grab a freebie. Free is always good.