Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Code & Utilities

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 26th June 2002, 08:20
Ruskin's Avatar
Ruskin Ruskin is offline
Unconfirmed User
 
Join Date: Feb 2002
Location: New Zealand
Posts: 91
Ruskin is on a distinguished road
Baan: All - DB: MS-SQL, Informix, Oracle - OS: UNIX and NT Server
Wink Retrieve ENUM Description from SQL Server with VB

I have had some requests, to use ENUM descriptions through VB. Unfortunately, due to the fact that they are binary objects, they do not come through correctly, using DAO or RDO (or ODBC). As a result, you must use ADO to get the objects. Even then, the enum description is not stored in the binary object. Thus, to get the enum description is rather complex. It involves the following;

a. determine the package combination of the current user
b. find the highest level VRC of that package combination
c. find the highest version of the table in that VRC level to find the domain used by the particular field (this is required, in case the table is customised and the domain of this field is changed)
d. find the highest version of the domain in that VRC level to find the description of the particular enum value (this is required, in case the domain is customised and the description has been changed)


Create a new project and ensure you have ADO activated for your project. To do this, from the menu bar, choose;
Project, References
Ensure 'Microsoft ActiveX Data Objects 2.5 Library' is selected
Add a module to your project and copy and paste the code from the attached text file 'ENUM_CD.TXT' into this module. You can then call this function with something like;
MsgBox FindItemType(<item code>)

If you want to find the enum description of a different table field, then simply plagerise this code and create your own function, similar to the FindItemType function (ensure that you call the 'PreChecksOkay' function the first time you run this, to set the global settings, such as user name, company, package combination, etc. Although, this code calls the PreChecksOkay function each time it runs, this function only needs to be called the first time this code is run. Therefore, you may wish to call this function in the Load event of your form and then not worry about calling it, when you get the enum descriptions).


NOTE: this example code assumes, that you have the tools tables and your company data tables, in the same database of your SQL server. If this is not the case, then you will need to create 2 connection objects. Also beware, this code will automatically connect to your SQL server, when activated, but will not disconnect. Ensure you disconnect (or close) the ADODB connection object either at the end of the 'FindItemType' function or when you close down your app. If you disconnect at the end of the FindItemType function, then ensure that you call the PreChecksOkay function each time, to re-establish the connection.
Attached Files
File Type: txt enum_cd.txt (14.6 KB, 127 views)
Reply With Quote
  #2  
Old 3rd July 2002, 05:54
Ruskin's Avatar
Ruskin Ruskin is offline
Unconfirmed User
 
Join Date: Feb 2002
Location: New Zealand
Posts: 91
Ruskin is on a distinguished road
Baan: All - DB: MS-SQL, Informix, Oracle - OS: UNIX and NT Server
Thumbs up recordsource NOT recordset???

Tip...

If you are adding the enum as a field in a recordsource (eg: you have a db control and db grid based on the db control, then change the recordsource of the db control), you can use the 'CAST' and 'CASE' statements to display the enum descriptions on your grid. The disadvantage with this trick, is that you need to hard code the descriptions of your domains, which means, if you change your domain, you need to change your VB code.

To do this, use something like;

Code:
dbgrid.RecordSource = "SELECT [ttiitm001100].[t_item] 'Item Code', " & _
    "[ttiitm001100].[t_dsca] 'Description', " & _
    "CASE CAST([ttiitm001100].[t_kitm] AS INT)" & _
        "WHEN 0 THEN 'Empty' " & _
        "WHEN 1 THEN 'Purchased' " & _
        "WHEN 2 THEN 'Manufactured' " & _ 
        "WHEN 3 THEN 'Generic' " & _
        "WHEN 4 THEN 'Cost' " & _
        "WHEN 5 THEN 'Service' " & _
        "WHEN 6 THEN 'Subcontracting' " & _
        "END AS 'Item Type' " & _
    "FROM [ttiitm001100]"
dbgrid.Refresh
Reply With Quote
  #3  
Old 3rd July 2002, 18:43
benito's Avatar
benito benito is offline
Guru
 
Join Date: Jan 2002
Location: Eastern US
Posts: 517
benito is on a distinguished road
Baan: ERPLn 10.2.1 / BaanIVc4 - DB: Oracle11/Informix - OS: Unix/Linux
recordset and recordsource

Maybe you can help me on this. I got this ASP code but as you can see, I have problem with the enum field as well, tiitm001.kitm.

I havent explored recordsource all that well yet but maybe you have a quick fix. The code below works except for the enum field. How do I combine recordset and recordsource?

My output eg. objitm, objdsca etc goes into formatted html document.

The "asp sign here" means the symbol for ASP. Reason why I didnt use the actual sign is because Baanboard hides the code below as if it's actual ASP code.

--asp sign here---

Dim SQLString

SQLString = "SELECT Item.t_item,Item.t_dsca,Item.t_citg, " & _
"Item.t_stoc FROM ttiitm001500 As Item"

SQLString = SQLString & " where Item.t_item >=" & _
Chr(39) & Request.Form.Item("item.f") & Chr(39) & " and Item.t_item <=" & _
Chr(39) & Request.Form.Item("item.t") & Chr(39)

Set DatabaseConnection = Server.CreateObject("ADODB.Connection")

DatabaseConnection.Open "Provider=sqloledb;Data Source=servername;Initial Catalog=baandb;User Id=sa;Password=;"

Set UserRecordset = Server.CreateObject("ADODB.Recordset")

UserRecordset.Open SQLString, DatabaseConnection

Set objitem = UserRecordset("t_item")
Set objdsca = UserRecordset("t_dsca")
'Set objkitm = UserRecordset("t_kitm") ---this field is not working
Set objcitg = UserRecordset("t_citg")
Set objstoc = UserRecordset("t_stoc")


If UserRecordset.EOF Then

--asp sign here--
Reply With Quote
  #4  
Old 3rd July 2002, 18:51
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,167
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
Re: recordset and recordsource

Quote:
Originally posted by benito
The "asp sign here" means the symbol for ASP. Reason why I didnt use the actual sign is because Baanboard hides the code below as if it's actual ASP code.

--asp sign here---
Benito, what do you mean by 'hiding code?' Baanboard does not hide code, you merely have the option to reformat the code using the Enscript integration (see this thread) The formatting only happens when you use the right 'CODE' tags however the admins and moderators scan through the posts regularly and insert these tags to improve readability.
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #5  
Old 3rd July 2002, 21:34
benito's Avatar
benito benito is offline
Guru
 
Join Date: Jan 2002
Location: Eastern US
Posts: 517
benito is on a distinguished road
Baan: ERPLn 10.2.1 / BaanIVc4 - DB: Oracle11/Informix - OS: Unix/Linux
This is what I want to do...

I wanted to write the asp symbol "<%" without the quotes and enclosed with "percent sign" and "greater than sign" . (Note: I have to spell it out so it shows). The codes in between disappears. I tried enclosing it with "code" tags but it still wouldn't show up.

How should I do it? Thanks.
Reply With Quote
  #6  
Old 3rd July 2002, 22:56
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
When Patrick said that Baanboard does not hide code, he was correct - but he forgot to mention "except in Code&Utilities forum" :). In this forum HTML tags are legal. I am going to attempt your tags below:

&lt;%&gt;

And here is the code that I wrote to get that stuff:

&amp;lt;%&amp;gt;

May be Patrick will look into turning HTML off for this forum as well. We only use it in the Index thread to get tables.
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #7  
Old 3rd July 2002, 23:24
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,167
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
HTML stuff

Benito, Vamsi,

I forgot that. We will look into the 'enable HTML tag' again.
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #8  
Old 4th July 2002, 07:12
Ruskin's Avatar
Ruskin Ruskin is offline
Unconfirmed User
 
Join Date: Feb 2002
Location: New Zealand
Posts: 91
Ruskin is on a distinguished road
Baan: All - DB: MS-SQL, Informix, Oracle - OS: UNIX and NT Server
not sure, but try this....

benito,

I am not familiar with ASP, but it looks similar to VB. If this is the case, then there shouldn't be to much of a problem, using the 'CAST' and 'CASE' statements in the 'SELECT' part of your SQLString. Since you are creating an ADO recordset, then this select should still work. NOTE: you are not actually selecting the kitm field in your example. But something like the following should still work (unless there is something in ASP that I'm not aware of, that doesn't allow interrogation of binary fields), eg:

SQLString = "SELECT Item.t_item,Item.t_dsca,Item.t_citg, " & _
"Item.t_stoc, " & _
"CASE CAST(Item.t_kitm AS INT)" & _
"WHEN 0 THEN 'Empty' " & _
"WHEN 1 THEN 'Purchased' " & _
"WHEN 2 THEN 'Manufactured' " & _
"WHEN 3 THEN 'Generic' " & _
"WHEN 4 THEN 'Cost' " & _
"WHEN 5 THEN 'Service' " & _
"WHEN 6 THEN 'Subcontracting' " & _
"END AS 't_kitm' " & _
"FROM ttiitm001500 As Item"

hope this helps...
Reply With Quote
Sponsored Links
  #9  
Old 8th July 2002, 17:37
benito's Avatar
benito benito is offline
Guru
 
Join Date: Jan 2002
Location: Eastern US
Posts: 517
benito is on a distinguished road
Baan: ERPLn 10.2.1 / BaanIVc4 - DB: Oracle11/Informix - OS: Unix/Linux
it worked!!!

hey mate!
it worked! you're brilliant. i'd never have thought of this solution. i attached the complete script. thanks a lot.
Attached Files
File Type: doc asp.doc (4.4 KB, 168 views)
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server 7.0 SP4 + BaanERP compatiblity Jabran Tools Administration & Installation 1 4th October 2004 12:49
VB, ODBC and Binary fields on SQL server halvorn Tools Development 6 8th September 2003 07:34
Decision Manager & SQL Server 7.0 BisBoy Open World, Portal & Decision Manager 0 10th July 2003 14:11
Upgrade Baan IV (HP-UX, SQL Server 2000) cfasini Operating Systems & Databases 3 20th May 2003 14:56
Should Baan provide a level 2 database driver for SQL server 2000 on BaanIVc4? patvdv Polls and Surveys 6 28th March 2003 14:22


All times are GMT +2. The time now is 11:45.


©2001-2017 - Baanboard.com - Baanforums.com