database connection error - need assistance please

I really think i just need to get away from using ADO to make my own connection to the DB anyway, but am unsure of how to change it to use QSrules to get my recordset data instead of my ADO connection. currently, as the code shows below, i'm opening an ADO connection, then opening an ADO recordset using my SQL statement. can i use the connection that is already established and use the OpenRecordSet method? And if so, what is the correct way to use this so I can get an openrecordset that I can use to pull data from.

Thanks, Kevin

"ksk> I have a COM add-in that connects to the RMS database using an ADO

thank you Antonio, that is what I'm trying to do - could you help me out with the syntax a bit please? If you can I'd appreciate it

Thanks, Kev>

Antonio, when I place the single quotes into the code around the glbItemLookupCode variable, it gets rid of the error message, but then it doesn't return any of the items in the item table where subdescription2 = the itemlookup code. It only returns the items where subdescription is blank, or equal to ''. Any ideas?

thanks for you help, kev>

Antonio, can I email it to you at your email address on this forum?

"Ant>

antonio, i got my query to work correctly, i was missing double quotes where my query ended and the glbitemlookupcode variable began. it works now - but if you could please look at the code project i sent and see if it can be changed to use the openrecordset method in qsrules instead of using ADO to open a recordset, i would really appreciate it

thank you, kev>

Antonio, I sent it to your email in a zip file. Please let me know if you receive it or not

Thanks, Kev>

I have tried the following modification (Thank you to Evan Culver):

glbSqlStr = "SELECT ItemLookupCode, ID, Description, ExtendedDescription, SubDescription2, DepartmentID, CategoryID FROM Item WHERE DepartmentID IN (56, 57, 58, 59, 60, 61) AND (SubDescription2 = '' OR SubDescription2 = '" & glbItemLookUpCode & "')" Set glbrecset = session.OpenRecordset(glbSqlStr, True)

then in another subroutine i have changed the recordcount, to this instead:

Do While Not glbrecset.EOF If (glbrecset("CategoryID") = 355) And _ (glbrecset("SubDescription2") = "" Or _ glbrecset("SubDescription2") = glbItemLookUpCode) Then cboPlans.AddItem glbrecset("ExtendedDescription") End If glbrecset.MoveNext Loop

The code compiled fine without an error, but when I run it in POS I get a run-time error 44: automation error, followed by a run-time error 91: object variable or with block variable not set.

I'm assuming I'm getting this because RMS thinks that I'm trying to reference the glbRecSet variable, but it is not referencing a valid object...Is there something I can change with how i'm coding so that my adodb recordset object gets created properly? Should I declare my variables right in my form module, instead of declaring them as global variables in a separate module? This hasn't caused me any problems before.

Any ideas?

Thanks, Kevin

"ksk> Antonio, I sent it to your email in a zip file. Please let me know if you

Kevin, it's a problem of your query...

The size of subdescription fields is 25 chars.. and the max size of a integer is 10 chars (for a max value of 2^32-1).

AND (SubDescription2 = '' OR SubDescription2 = " & glbItemLookUpCode & ")"

I think that glbItemLookUpCode is string, but when sql server parse command.. glbItemLookUpCode is a number! the string start with ' and finish with ' ...

Please replace SubDescription2 = '" & glbItemLookUpCode & "')"

Replace in glbItemLookUpCode ' with '' otherwise will be an error when itemlookupcode contain '

bye antonio

"kskinne" ha scritto nel messaggio news: snipped-for-privacy@microsoft.com...

QSRules return RecordSet.. it's the same of your ado connection... remove your connection, and use only QSRules.Database method..

bye antonio

"kskinne" ha scritto nel messaggio news: snipped-for-privacy@microsoft.com...

ok, do you can post me your project?

antonio

"kskinne" ha scritto nel messaggio news: snipped-for-privacy@microsoft.com...

"kskinne" ha scritto nel messaggio news: snipped-for-privacy@microsoft.com...

After the SQL Command.. put into your code msgbox string and verify the query..

after post full query on newsgroup

bye antonio

Hi Antonio, I tried that but I still get the same error message, any other ideas? I am using RMS version 1.2.0171

Thanks, Kevin

"Ant>

"kskinne" ha scritto nel messaggio news: snipped-for-privacy@microsoft.com...

Dim glbrecset as recordset .. not adodb.recordset

try...

antonio

That error usually occurs because you have a null reference - are you sure glbrecret is not Nothing (or null). Not sure what OpenRecordset returns if no rows match your query.

Also, I think the statement should be: Set glbrecset=session.Database.OpenRecordset(glbSqlStr, True)

Hi Glenn, thanks for your reply. I have run the query in query analyzer and I know for certain that there are rows that match my query. So there are definitely going to be data returned to for the recordset.

This is what I have in my code:

In a glbModule where I have all my global variables declared, I have the following variables:

Global glbRecSet As Recordset Global glbsqlstr As String

Then in a form I have a Form_Load subroutine that contains the following code:

glbsqlstr = "SELECT ItemLookupCode, ID, Description, ExtendedDescription, SubDescription2, DepartmentID, CategoryID FROM Item WHERE DepartmentID IN (56, 57, 58, 59, 60, 61) AND (SubDescription2 = '' OR SubDescription2 = '" & glbItemLookUpCode & "')" Set glbRecSet = session.Database.OpenRecordset(glbsqlstr, True)

Then in the same form I have a subroutine that populates a combobox using the recordset data:

Do While Not glbRecSet.EOF If (glbRecSet("CategoryID") = 355) Then cboPlans.AddItem glbRecSet("ExtendedDescription") End If glbRecSet.MoveNext Loop

THe form is loaded when I scan a serialized item, right after the serial number is entered. The code is erroring out as soon as the form loads and I am getting the same error number 91, that seems to be indicating that my recordset object is not being set correctly? Can you tell what is wrong with my code above? Does this code work for you?

Thank you for your help, Kevin

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

Hi Glenn, thank you so much for your help

I did as you suggested and initiated the hook in dev mode and stepped through the code. I can step through the code ok until the line:

Set glbRecSet = Session.Database.OpenRecordset(glbsqlstr, True)

At which point I receive the run-time error 91, so this object is still not getting set correctly.

I put a MsgBox to return my sql query string in the code, then i took the query string that my code generated and ran it in Administrator. The query does return rows. it is the same query that i use when i run this code by creating my own ado connection, and when I do that I am not having any problems with it.

I also tried declaring my glbRecSet object as ADODB.recordset instead of just recordset, and get the same error. My project already had a reference to the Microsoft ActiveX Data Objects 2.7 library so I should be ok there.

So I am unsure as to why it is erroring out on this line of code, if the syntax is correct. Do you have any other ideas as to why this is happening?

Thank you, Kevin

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

Glenn, I inserted:

MsgBox session.Cashier.Name

and when debugging it does not error out on this line. However the message box that it returns is blank. If I place the same message box in my class module obviously I do not get the same problem, it correctly returns my cashier's name. How would I change this so that the session object is accessible in this form's code?

Thank you, Kevin

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

Open your project and set a breakpoint at the first line in the Process Function. Start the program in the dev environment. Open POS on your dev machine and run a test transaction. As soon as the hook fires, you should be switched to your break point. Now step through the function until you get an error. If you get past the OpenRecordset() call, make sure that your recordset variable is Not Nothing.

I don't see anything wrong syntactically - I'll withhold comment on coding style... You may want to declare your recordset as "ADODB.Recordset" and make sure your project references ADODB.