Navision - ODBC - VB

Luc_VanDyck

Can someone post some code how to connect to a Navision database using ODBC in a Visual Basic v6 application?

I created a ODBC DataSource using the Control Panel (named Navision) and use the following code in VB:

Dim connNavision As New ADODB.Connection
connNavision.ConnectionString = "DSN=Navision"
connNavision.Open
connNavision.Close

At run-time, VB gives an error "[Navision Software a/s][Navision Financials ODBC Driver]ISAM error" on the connNavision.Open line.

Luc_VanDyck

This is the code I use in VB 6:

Set connNavision = New ADODB.Connection
connNavision.Open "DSN=Navision"
Set rsNavision = New ADODB.Recordset

For i = 1 To 100
strSQL = "INSERT INTO ""MessageQueue Input"" (ID,Sequence,Key,Value) VALUES (" & i & "," & i & ",'Key" & i & "','Value " & i & "')"
rsNavision.Open strSQL, connNavision
Next i

Set rsNavision = Nothing
Set connNavision = Nothing

After record 24, following error is displayed "Run-time error 80010108; Method 'Open' of object '_Recordset' failed". The records (24) are created in the database.

brucem

I might be missing somehting here, but doesn't this code actually open a record set every time you go thru the loop ?
If so are you trying to open 100 connections / recordsets to your database ? What is you license count ?

Would you not wnat to open rs.

then loop

then close, disconect?

Bruce

brucem

try this ( pseudo code)

set connection
open recordset

for i = 1 to 100
rs.addnew

brucem

try this ( pseudo code)

set connection
open recordset

for i = 1 to 100
rs.addnew

set fields as required using (i)

rs.update
rs.movenext
next i

rs.close
set all to nothing

Luc_VanDyck

<BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by brucem:
open recordset
<HR></BLOCKQUOTE>

How do I open the recordset when I use rs.addnew? Say I want to add records to the table Item, how do I write "rs.Open ..." ?

brucem

Luc,

Try this

dim rs as new ADODB.Recordset
rs.open "Select * from items", DSN="YOURNavisionDSN",

rs.movefirst ''Goto the first record
for i = 1 to 100
rs.addnew 'add the new record

rs.("description")="item description"

rs.update ' update the record

rs.movenext

next i

rs.close
set rs = nothing

Luc_VanDyck

I receive following error on the line rs.addnew: "Object or provider is not capable of performing requested operation".

brucem

Luc,

forgive me if i'm teaching you to suck eggs, but the link below from the MS website should show you the way...Assuming this isn't a navision Bug. BTW your DSN is a system object is it.
http://support.microsoft.com/support/kb/articles/Q168/3/36.asp

HTH

Bruce

brucem

Also before i forget, you may need to update the data access pack to mdac2.5 again from MS in the universal data access area

search on mdac_typ.exe

Bruce

brucem

Luc,

this code is a complete example showing connection string, commands etc... this should work for you, please amend as required with your data for input, you will neeed to change the connection string and table name

Public Sub AddNewX()

Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset

Dim strCnn As String
Dim strID As String

Dim strFirstName As String
Dim strLastName As String

Dim booRecordAdded As Boolean

' Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"

''PUT YOUR CONN STRING IN HERE INSTEAD

cnn1.Open strCnn

' Open Employee table.
''PUT YOU TABLE NAME HERE
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable

' Get data from the user.

strID = Trim(InputBox("Enter employee ID:"))
strFirstName = Trim(InputBox("Enter first name:"))
strLastName = Trim(InputBox("Enter last name:"))

' add the record
rstEmployees.AddNew
rstEmployees!emp_id = strID
rstEmployees!fname = strFirstName
rstEmployees!lname = strLastName
rstEmployees.Update
booRecordAdded = True

'close the recordset
rstEmployees.Close

'close the connection
cnn1.Close

End Sub

Luc_VanDyck

Have you tried this with a native Navision db and ODBC?

brucem

Luc,

I have just tried it on a stand alone copy of 2.01 and it works no problem. If you want to send me your eamil address i can send you a VB class / project which you can try ?

Bruce

Brucem@aston-chemicals.com