The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Database Error
Posted: 26 Dec 07 6:36 PM
|
I just upgraded to V7.2 from V6.2. In my code I frequently use
Dim oConn
Set oConn = Application.GetNewConnection
rs = oConn.Execute(strSql) it has an error. The code works perfectly in 6.2. I have seen this in other parts of my code and replaced it with get fields but I would rather not have to do this. Any ideas would be greatly appeciated. It says it can't find the database.
Happy Holidays
Paul |
|
|
|
Re: Database Error
Posted: 27 Dec 07 4:03 AM
|
This is the "normal" way to do that:
Set objCon = Application.GetNewConnection
objCon.Execute strSQL,,adExecuteNoRecords
Set objCon = Nothing
|
|
|
|
Re: Database Error
Posted: 27 Dec 07 7:54 AM
|
I dont see anything wrong with either piece of code.
Paul - yours returns a forward only, read only recordset (I assume you are passing in a SELECT)
Mike - your doesnt return an RS, I assume you are passing in an Insert, Update or Delete.
It tells you the db cannot be found? hmmm. I responded to a similar post within the Sage Newsgroups. That person had an extra comma in his code, but ultimately was having a similar problem.... Upgrade then connection issues.
What do you get if you print application.connectionstring? |
|
|
|
Re: Database Error
Posted: 27 Dec 07 9:06 AM
|
Paul when you create a recordset using your method it uses the ADO defaults which include a cursorlocation of server side. The SLX client needs to use a client side cursor.
Try this instead
set oconn = application.getnewconnection
set rs = createobject("ADODB.Recordset")
rs.activeconnection = conn
rs.cursorlocation = aduseclient
rs.cursortype = adopenforwardonly 'This may need to change depending on your objective
rs.locktype = adlockoptimistic 'This may need to change depending on your objective
.Open strSQL
Please be aware that (I think) in 7.2 with Vista the built in contstants for the ADO properties are not built in and you would need to use their value equivalent (i.e)
aduseclient would be "3" in the above example.
|
|
|
|
Re: Database Error
Posted: 27 Dec 07 9:30 AM
|
| If you continue to have problems, please post your SQL statement. I discovered that nested subqueries that worked in 6.2.x were no longer supported in 7.2 e.g. SELECT * FROM CONTACT WHERE CONTACTID IN (SELECT CONTACTID FROM MYTABLE WHERE MYFIELD = 'something') |
|
|
|
Re: Database Error
Posted: 27 Dec 07 5:24 PM
|
Thank you so much for your help.
This syntax works every time. This works where there is no recordset
SQL = "update sysdba.C_PROPERTYACCOUNT set Contactid = '" & pPrimary & "' where Contactid = '" & pSource & "'"
objSLXDB.Execute sql
The syntax when I have a record set (yes, I only use it for reading) - seems to crash - it throws me our of saleslogix entirely - if I reuse my rs. object which I tend to do. Seeing how I only use this style when reading - I have two options - use getfields or update my code to close it and then create the database object again and this seems to be working. Once I finish this - I need to find out what I did with my standard navbar.... never dull....
Thanks for your help. It put me on the right track.
Paul |
|
|
|
Re: Database Error
Posted: 27 Dec 07 9:56 PM
|
Hi Jason
I assumed that a RS wasn't required (just an update or some direct SQL).
The standard method for getting a RS for read/write would be one of the following (I've also included the DBExecute method too). They're all interchangeable of course !
=======================================
Updating database (DBExecuteSQL style):
=======================================
#Include: SLX Common, SLX Database Support, SLX Error Support
Dim strSQL, objCon
strSQL = "update E1TB_EVENTS set PLACES_CONFIRMED = " & cstr(conf) & ", PLACES_ALLOCATED = " & cstr(alloc - conf) & ", PLACES_AVAILABLE = " & cstr(Available) & " where E1TB_EVENTSID = '" & EventID & "'"
Set objCon = Application.GetNewConnection
If ErrorCheck (Application.Translator.Localize("Error getting connection information:")) > 0 Then exit sub
objCon.Execute strSQL,,adExecuteNoRecords
If ErrorCheck(Application.Translator.Localize("Error updating (EventQty) information:")) > 0 Then exit sub
Set objCon = Nothing
=================================
Updating database (DBEdit style):
=================================
#Include: SLX Database Support, SLX Error Support
Dim objSLXDB
Dim objRS
Dim strSQL
If IsEmpty(objSLXDB) Then
Set objSLXDB = New SLX_DB
End If
On Error Resume Next
Set objRS = objSLXDB.GetNewRecordSet
strSQL = "Select USERID, ALLOW_ADD_ATT,ALLOW_ADD_PAY,ALLOW_ADD_EVT " & _
"From E1TB_EVENTSEC " & _
"Where USERID = '" & strKeyFieldID & "'"
objRS.Open strSQL, objSLXDB.Connection
If objRS.Fields("KEYFIELDID").Value = strKeyFieldID Then
With objRS
If Not (objRS.BOF And objRS.EOF) Then
objRS.Fields("USERID").Value = "VALUE"
objRS.Fields("ALLOW_ADD_ATT").Value = "VALUE"
objRS.Fields("ALLOW_ADD_PAY").Value = "VALUE"
objRS.Fields("ALLOW_ADD_EVT").Value = "VALUE"
objRS.Update
If ErrorCheck (Application.Translator.Localize("Error updating...")) > 0 Then exit sub
End If
End With
End If
objRS.Close
Set objRS = Nothing
====================================
Updating a record set in batch mode:
====================================
#Include: SLX Database Support, SLX Error Support
Dim objSLXDB
Dim strSQL
Dim objRS
Dim i
Set objSLXDB = New SLX_DB
LastRun = cdate(now) ' Today's Date
'Get a RS containing all past courses
strSQL = "select * from E1TB_Events where datediff(d,StartDate,'" & Application.BasicFunctions.DateToISO(LastRun) & "') >= 0"
Set objRS = objSLXDB.GetNewRecordSet
'Loop through the RS and apply the new Status for each record
With objRS
'Set lock type to execute a batch update
.LockType = adLockBatchOptimistic
.Open strSQL, objSLXDB.Connection
For i = 0 To .RecordCount - 1
If Not (.BOF And .EOF) Then
.Fields.Item("EVENT_STATUS").Value = "Closed Course"
.MoveNext
End If
Next
'Apply the updated RS to the database
.UpdateBatch
.Close
End With
'Cleanup
Set objRS = Nothing
|
|
|
|
Re: Database Error
Posted: 28 Dec 07 6:24 AM
|
Originally posted by Rick Smith
If you continue to have problems, please post your SQL statement. I discovered that nested subqueries that worked in 6.2.x were no longer supported in 7.2 e.g. SELECT * FROM CONTACT WHERE CONTACTID IN (SELECT CONTACTID FROM MYTABLE WHERE MYFIELD = 'something') |
|
Hi Rick
Not sure where/how you heard that - but they definitely are working fine.
Mike
|
|
|
| |
|
Re: Database Error
Posted: 28 Dec 07 7:57 AM
|
| Actually, I think it's the LEFT part - I heard a rumour that this was not working for some reason. You appear to use those a lot ! Also, you might want to specify the join type and tidy it up a tad e.g. inner join table a on (c.col = a.col) but irrespective of that - a "normal" sub-query does work without issues. |
|
|
|
Re: Database Error
Posted: 28 Dec 07 7:59 AM
|
Originally posted by Mike Spragg
Hi Jason
I assumed that a RS wasn't required (just an update or some direct SQL).
The standard method for getting a RS for read/write would be one of the following (I've also included the DBExecute method too). They're all interchangeable of course !
|
|
I like
dim rs
set rs = conn.execute(strSQL)
.. I like it for opening a forward only, read only rs (which is what I use 99% of the time).
I assumed he wasnt using an update, insert or delete since he was opening an RS.
I am still puzzled what could be his problem. It isnt getting to the code for his rs, it tells him the db cannot be found.
This should eliminate what we are talking about or his SQL. |
|
|
|
Re: Database Error
Posted: 28 Dec 07 8:08 AM
|
| Wow, if LEFT(myfield, myint) isn't working, that seems like a glaring bug. Yes, I use it in that particular query to build a unique "key" using the first characters of several fields. From my eclectic SQL learnings, I'd understood that JOIN is equivalent to INNER JOIN, but its worth a try to tweak the syntax. If someone from Sage would like to explain here what changes were made to the provider that has caused SQL that was working in 6.2 to break in 7.2, that would be very interesting! |
|
|
|
Re: Database Error
Posted: 28 Dec 07 8:11 AM
|
| I know - personally, I've not tested it - if you get time let us know please !! |
|
|
|
Re: Database Error
Posted: 28 Dec 07 11:51 AM
|
Wow... Thanks for the dialog.
I use this code over and over.
set rs = conn.execute(strSQL)
The issue appears to be that I create the connection object, do a select - no joins and looks to see if rows are returned. If they are I used the rs object and then close it. I did not think I should have to redim the RS - its closed its not NOTHING The error occurs on the next select statement when it has a record set as well - the object is closed - in v6.2 it creates a new rs object but in 7.2 catastrophic crash - saleslogix closes - no option to go into the debugger. Its very harsh. I can step the code and watch it crash. This code has been working for close to 2 years - very odd. I am almost done working around it.
Happy Holidays.
Paul |
|
|
|
Re: Database Error
Posted: 28 Dec 07 12:07 PM
|
I talked to who I can talk to downstairs (*Ackhem - St. Ew, JP - names obfuscated to protect the innocent).
They couldnt tell me anything that *should* cause this or anything they had seen before.
I can tell you that I do this ALL THE TIME in class and customizations in SLX and outside SLX using vbscript and all is well.
MDAC version was thrown about... Anything else that is different? Can you try it similiarly using a vbs file on each machine to see if it occurs outside SLX?
You should be able to take the vb script and past it into a vbs file and run it on both machines... eliminate the differences this way. |
|
|
|
Re: Database Error
Posted: 31 Dec 07 7:51 AM
|
I use this code over and over.
set rs = conn.execute(strSQL)
The issue appears to be that I create the connection object, do a select - no joins and looks to see if rows are returned. If they are I used the rs object and then close it. I did not think I should have to redim the RS - its closed its not NOTHING The error occurs on the next select statement when it has a record set as well - the object is closed - in v6.2 it creates a new rs object but in 7.2 catastrophic crash - saleslogix closes - no option to go into the debugger. Its very harsh. I can step the code and watch it crash. This code has been working for close to 2 years - very odd. I am almost done working around it. |
|
Pardon what may seem like dumb questions, I'm new to SLX dev. I assume this is being done via VBScript - correct? How are you checking for the existence of rows? Is it possible your cursor is already at the end of the RS because of how you are checking? |
|
|
|
Re: Database Error
Posted: 01 Jan 08 5:51 AM
|
Originally posted by Paul Pacun
Wow... Thanks for the dialog.
I use this code over and over.
set rs = conn.execute(strSQL)
...
|
|
I stopped using the connection.execute to get a recordset way back in 6.0 because it didn't work properly. Mostly, I don't use connection objects to get the recordset either - just the connection string. Something like this:
Dim rs
Set rs = Application.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open "SELECT blah blah blah", Application.ConnectionString
Note the "Application.CreateObject" which is supposed to be more efficient for recordsets.
If I want to do an "Execute" for purposes of insert, update, etc., I will often not even instantiate a connection object:
Application.GetNewConnection.Execute "UPDATE xxx blah blah blah"
Stephen
|
|
|
|
Re: Database Error
Posted: 03 Jan 08 2:19 PM
|
Stephen,
Thanks. I like the simplier syntax. When I am inserting it is cleaner but it still throws an error and I get the "SalesLogix has encountered a problem and needs to close." What I realized was that my error only happens with inserting into my custom tables. It is very odd, they were created through the administrator - I can read them - but I can't add rows. This was working in 6 - 6.2....
THANKS to everyone who has tried to help. The problem is perplexing and while I was able to work around it - now I am stuck.
Paul
|
|
|
|
Re: Database Error
Posted: 03 Jan 08 2:36 PM
|
Hi Paul,
Anything interesting happening in SLXProfiler?
Double-check the entries in SECTABLEDEFS and RESYNCTABLEDEFS and then reboot the server.
Stephen
|
|
|
|
Re: Database Error
Posted: 03 Jan 08 3:28 PM
|
Thank you.
Its really odd. Its only on certain tables (2 out of about 20). I have been stepping the code and taking the failed SQL and putting it in the QUERY Analyzer and it works every time (the insert). It only happens on 1 to many realationships.
I forgot about the profiler. Thanks I will try that shortly.
Paul |
|
|
|
Follow UP
Posted: 10 Jan 08 1:38 PM
|
Hi,
Thanks for all of your help. When my insert failed not only would it kick me out of saleslogix but the profiler too so I could not figure it out. What I did to get around it ... It happended in two places.. I used insert into (xxxx) values (xxx) instead of insert into (). It seems like explictly stating the fields worked. My insert was for every field so I did not do this in the first place. The data is typical for any CRM, a combination of string, numerics and datatime. The only thing I noticed was that I was putting a datatime field into a varchar in one case. When I specified the fields names in the insert - the problem went away plus the code is more readable. Very odd... I am using SQL Server 2000 as my backend.
I appreciate all the help from all of you.
Happy 2008
Paul
|
|
|
|