slxdeveloper.com Community Forums

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!

Architect Forums - SalesLogix Scripting & Customization
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to SalesLogix Scripting & Customization | New Thread

View: Search:

Author

Thread: VBS: Example Query Anyone?

thepip3r
Posts: 16

VBS: Example Query Anyone? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 17 Aug 09 1:36 PM

Does anyone have an example query to run against an slx db? I'm using:

Sub SLX_Test()

Set objConn = CreateObject("ADODB.Connection")
Set objCmd = CreateObject("ADODB.Command")
Set objRS = CreateObject("ADODB.Recordset")

objConn.ConnectionString = "Provider=SLXOLEDB.1;Integrated Security=True;Initial Catalog=slxcat;Data Source=myslxserver;Extended Properties=PORT=1706;LOG=ON;Location=;Mode=ReadWrite"

objCmd.CommandText = "SELECT * FROM TICKET WHERE TICKETID='001-00-025850'"

objRS.Open objCmd

While Not objRS.EOF
MsgBox objRS("AREA")
objRS.MoveNext
Wend

objRS.Close
objConn.Close
Set objRS = Nothing
Set objCmd = Nothing
Set objConn = Nothing
End Sub

But I get an error: Run-time error '3709': The connection cannot be used to perform this operation. It is either closed or invalid in this context. TIA

[Reply][Quote]

Walter Shpuntoff
Posts: 167

Re: VBS: Example Query Anyone? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 18 Aug 09 12:30 AM

The error you are getting is because your query is not returning any records.

TicketId's are 12 characters and start with t.

The value you are passing is actually the TicketNumber field, which is calculated.
What you want to do is to modify your while loop

While not (objRS.EOF or objRS.BOF)
....
wend

That will take care of the error.

The null recordset means that you either need to change to passing it a ticketid, or query against the component parts of the ticketnumber field - alternatekeyprefix & alternatekeysuffix.

hth

ws

[Reply][Quote]

Raul A. Chavez
Posts: 1300

Re: VBS: Example Query Anyone? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 18 Aug 09 2:59 AM

Quote:

Originally posted by thepip3r

Does anyone have an example query to run against an slx db? I'm using:

Sub SLX_Test()

Set objConn = CreateObject("ADODB.Connection")
Set objCmd = CreateObject("ADODB.Command")
Set objRS = CreateObject("ADODB.Recordset")

objConn.ConnectionString = "Provider=SLXOLEDB.1;Integrated Security=True;Initial Catalog=slxcat;Data Source=myslxserver;Extended Properties=PORT=1706;LOG=ON;Location=;Mode=ReadWrite"

objCmd.CommandText = "SELECT * FROM TICKET WHERE TICKETID='001-00-025850'"

objRS.Open objCmd

While Not objRS.EOF
MsgBox objRS("AREA")
objRS.MoveNext
Wend

objRS.Close
objConn.Close
Set objRS = Nothing
Set objCmd = Nothing
Set objConn = Nothing
End Sub

But I get an error: Run-time error '3709': The connection cannot be used to perform this operation. It is either closed or invalid in this context. TIA

Actually, the error message that you are getting is because you are never opening a connection.
After you set the ConnectionString property, you need to open the connection:
objConn.Open

Towards the end you have remembered to "close" the connection, but as stated, you never opened it to begin with.

[Reply][Quote]

thepip3r
Posts: 16

Re: VBS: Example Query Anyone? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 18 Aug 09 12:29 PM

thanx guys; especially Raul. here is the code that works:

Sub SLX_Test()

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")

objConn.ConnectionString = "Provider=SLXOLEDB.1;Integrated Security=True;Initial Catalog=SLX_DB;Data Source=SLX_Server;Extended Properties=PORT=1706;LOG=ON;Location=;Mode=ReadWrite"
objConn.Open

strSQL = "SELECT * FROM sysdba.TICKET WHERE AREA LIKE 'Manager'"

objRS.Open strSQL, objConn, 3, 3

While Not objRS.EOF
MsgBox objRS("AREA")
objRS.MoveNext
Wend

objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
End Sub

[Reply][Quote]

Page 1 of 1

You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2025 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 8/29/2025 1:58:48 PM