need urgent help w/ COM add-in problem

to give you a little more detail into the problem, here is a snippet of code from our project:

For i = 1 To glbRecSet.RecordCount If (glbRecSet("CategoryID") = 98) And _ (glbRecSet("SubDescription2") = "" Or _ glbRecSet("SubDescription2") = glbItemLookUpCode) Then cboPlans.AddItem glbRecSet("ExtendedDescription") End If glbRecSet.MoveNext Next i

where we are running into problems is, there are items appearing in the combobox that have a value in the SubDescription2 field that does not match the ItemLookupCode, and this piece of code should prevent such items from being added to the combobox. any help is appreciated.

thanks, kevin

Thank you Glenn - I am using VB 6.0 IDE. The funny thing is that this works fine for items that were already set up in the item table before we wrote the customization. It's just not working right for new items we've added since then.

I will definitely look into the database class, I am hoping that maybe by using that it will reduce some overhead and the code will run a little faster...Any helps for using this particular feature that you can point me to?

Thanks again, Kevin

"Glenn Adams [MVP - Retail Mgmt]" wrote:

What development environment are you using? If it's Visual Studio you can open the project, set a breakpoint in the Process Function and run it, then open RMS POS and test your add-in. POS will call the copy running in the IDE and you can step through the code and watch the results.

Do you have a copy of Enterprise Manager and SQL Profiler (Some versions of Visual Studio come with SQL Server Developer edition which has the tools)? If you have SQL Profiler, you can see EXACTLY what queries are being run.

QSRules has a database class that includes functions to run action queries or return recordsets. This allows you to piggyback on RMS' db connection rather than managing your own.

Run the project in the VB6 IDE, and step through the code - you'll be able to see what's being compared in real time.

It's not likely to run noticeably faster using the Database or Gateway class, they're just using ADO, same as you would. The only thing it saves you is the need to manage your own connection settings.

This is from memory, so it may be off...

Session.Database returns a Gateway object the Gateway class has 2 methods that take a SQL String and run it against the RMS database. One method returns a recordset, the other does not.

The Object Browser in the VB IDE is your friend! It will let you browse all of the QSRules classes...

thanks Glenn! i'll definitely look into that - this is how i'm connecting to the db right now:

On the form_load event:

glbSqlStr = "SELECT ItemLookupCode, ID, Description, ExtendedDescription, SubDescription2, DepartmentID, CategoryID FROM Item" glbConnStr = "Provider=SQLOLEDB.1;Password=sql;Persist Security Info=True;User ID=sa;Initial CatalogÊrroll;Data SourceÊrrollretail" Set glbDbConn = New ADODB.Connection glbDbConn.Open glbConnStr Set glbRecSet = New ADODB.Recordset glbRecSet.Open glbSqlStr, glbDbConn, adOpenKeyset, adLockOptimistic, adCmdText

on the combobox's setfocus event

If (glbRecSet("CategoryID") = 86) And _ (glbRecSet("SubDescription2") = "" Or _ glbRecSet("SubDescription2") = glbItemLookUpCode) Then cboPlans.AddItem glbRecSet("ExtendedDescription") End If

the above is one case in a select case statement - the categoryID changes based on which checkboxes are checked or unchecked on my userform.

if this works just as efficiently as piggybacking RMS's connection to the db, then i'll just stick with this - unless using RMS's db connection is easier as far as maintaining the code

thanks kevin

It's never good practice to hardcode anything. It's VERY poor practice to post your sa password, so I hope this code was scrubbed... Normally connection data is encrypted and stored in the registry or some other location other than compiled code. If you wanted to move your database to a different server, or even just change the sa password, you would have to edit code and recompile. That's not good for maintainability, and that is the main reason that you may want to go to the Gateway class - that way your connection comes from SO Administrator.

You should ALWAYS test your code on a test database before you deploy it, which is another reason that it's nice to have an easy way to change the connection string.

Don't you want a Where clause on that Select statement? As is it's returning ALL records in the Item table. Are you updating the recordset? If not you don't need a KeySet - Static Read Only Forward Only (The 'default' recordset) is far more efficient. You're using a lot of 'magic numbers' - that's not a good idea - "If (glbRecSet("CategoryID") = 86) And ..." What happens if Category ID 86 gets deleted or renamed? Also, this is hard to maintain because the meaning of '86' is not obvious - if you must hardcode the value at least create a constant with a meaningful name... You may want to put parentheses around your if conditions - it's not obvious what order the AND & OR will be applied in with out that...

If ((glbRecSet("CategoryID") = 86) And (glbRecSet("SubDescription2") = "") Or glbRecSet("SubDescription2") = glbItemLookUpCode) Then ^ ^

-is different than If (glbRecSet("CategoryID") = 86) And ((glbRecSet("SubDescription2") = "" Or glbRecSet("SubDescription2") = glbItemLookUpCode)) Then ^ ^

Forgive the word-wrap at the end of my last post, I tried to highlight how adding parentheses to your IF Statement could totally change the way it's evaluated, but It doesn't look very clear. Maybe this way will be readable...

((glbRecSet("CategoryID") = 86) And (glbRecSet("SubDescription2") = "") Or glbRecSet("SubDescription2") = glbItemLookUpCode)

-is different than

(glbRecSet("CategoryID") = 86) And ((glbRecSet("SubDescription2") = "" Or glbRecSet("SubDescription2") glbItemLookUpCode))

In the first case, any time that SubDescription2 is equal to the lookupcode the expression is true. In the second, the expression is ONLY true if Category = 86.

Thanks for the tips Glenn - on my IF statement I purposely have the two conditions for the subdescription two grouped together in their own paretheses because in order to evalate to true the Subdescription2 field can either match the ItemLookup code of the entered item or be blank, but then if one of those is true, it must also match the categoryID referred to in the code in order to be returned to the combobox.

you made some really good points on the benefits of using the gateway class, it sounds like i need to do this for a number of reasons. not really sure where to start but i will begin be looking through the CG, and testing on a test DB with a modified copy of my code. any other suggestions for using the gateway class?

thanks for your help, kevin

"Glenn Adams [MVP - Retail Mgmt]" wrote: