database connection error - need assistance please

I have a COM add-in that connects to the RMS database using an ADO connection. This worked fine yesterday morning, and had worked fine for us for several months. Yesterday afternoon however when we tried to run this
code in POS we started to receive two error messages:
Run-time error '440': automation error
Run-time error '-2147217833 (80040e57)': arithmetic overflow error converting numeric to data type numeric.
Then the POS shuts down. The only thing we did yesterday between the time it was working and when it stopped working was add some new items to the database, re-indexed the database, and created a backup.
Here is the code that it is erroring out on:
Private Sub Form_Load() 'On Error Resume Next Frame4.Visible = False chkYes.Visible = False chkNo.Visible = False chkEzNo.Value = 1 chkPmNo.Value = 1 chkDepNo.Value = 1 glbSqlStr = "SELECT ItemLookupCode, ID, Description, ExtendedDescription, SubDescription2, DepartmentID, CategoryID FROM Item WHERE DepartmentID IN (20, 21, 22, 23, 24, 25) AND (SubDescription2 = '' OR SubDescription2 = " & glbItemLookUpCode & ")" glbConnStr = "Provider=SQLOLEDB.1;Password=sql;Persist Security Info=True;User ID=sa;Initial Catalog=mytestdb;Data SourceΚRROLLRETAIL" Set glbDbConn = New ADODB.Connection glbDbConn.Open glbConnStr Set glbRecSet = New ADODB.Recordset glbRecSet.Open glbSqlStr, glbDbConn, adOpenKeyset, adLockOptimistic, adCmdText KeyPreview = True If glbCashier = 64 Or glbCashier = 52 Then cmdCancel.Visible = True End If End Sub
any help would be greatly appreciated thank you,
kevin
Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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
"kskinne" wrote:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

QSRules return RecordSet.. it's the same of your ado connection... remove your connection, and use only QSRules.Database method..
bye antonio
Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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, Kevin
"Antonio Mazzeo" wrote:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

ok, do you can post me your project?
antonio
Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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, kevin
"Antonio Mazzeo" wrote:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

After the SQL Command.. put into your code msgbox string and verify the query..
after post full query on newsgroup
bye antonio
Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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, kevin
"Antonio Mazzeo" wrote:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Antonio, can I email it to you at your email address on this forum?
"Antonio Mazzeo" wrote:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

yes
Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Antonio, I sent it to your email in a zip file. Please let me know if you receive it or not
Thanks, Kevin
"Antonio Mazzeo" wrote:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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
"kskinne" wrote:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

Dim glbrecset as recordset .. not adodb.recordset
try...
antonio
Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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
"Antonio Mazzeo" wrote:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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)
--
Glenn Adams
Tiber Creek Consulting
  Click to see the full signature.
Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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.
--
Glenn Adams
Tiber Creek Consulting
  Click to see the full signature.
Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Is "Session" in scope? Try opening a message box that shows "session.Cashier.Name" - if that line errors out your variable "session" is out of scope. I would suspect this is the problem.
--
Glenn Adams
Tiber Creek Consulting
  Click to see the full signature.
Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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:

Add pictures here
βœ–
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

BeanSmart.com is a site by and for consumers of financial services and advice. We are not affiliated with any of the banks, financial services or software manufacturers discussed here. All logos and trade names are the property of their respective owners.

Tax and financial advice you come across on this site is freely given by your peers and professionals on their own time and out of the kindness of their hearts. We can guarantee neither accuracy of such advice nor its applicability for your situation. Simply put, you are fully responsible for the results of using information from this site in real life situations.