Navision - ODBC - VB

Luc_VanDyck
Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
edited 2000-08-01 in Navision Financials
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.
No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)

Comments

  • Luc_VanDyck
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    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.
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • brucem
    brucem Member Posts: 86
    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
    brucem Member Posts: 86
    try this ( pseudo code)

    set connection
    open recordset

    for i = 1 to 100
    rs.addnew
  • brucem
    brucem Member Posts: 86
    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
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    <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 ..." ?
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • brucem
    brucem Member Posts: 86
    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
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    I receive following error on the line rs.addnew: "Object or provider is not capable of performing requested operation".
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • brucem
    brucem Member Posts: 86
    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
    brucem Member Posts: 86
    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
    brucem Member Posts: 86
    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
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    Have you tried this with a native Navision db and ODBC?
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • brucem
    brucem Member Posts: 86
    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

Categories