Page 1 of 1 1
Topic Options
#197243 - 09/12/30 10:26 PM Modify VBS to KiXtart Scrpit
AModestProposal Offline
Fresh Scripter

Registered: 09/12/30
Posts: 5
Loc: Tonadawanda, NY
Hello, I'm rather new to this so go easy on me. I am in the process of converting scripts at work written in VBS and converting them to KitXtart scripts. I have run into a problem trying to convert the following lines of code.

 Code:
Dim SQL
SQL = "INSERT INTO Login (UserName, ComputerName, LoginDateTime)VALUES('" & UserName & "', '" & ComputerName & "', '" & Now() & "' )"
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; data source=\\server\login.mdb; User ID=user; password=password; Persist Security Info=True; Jet OLEDB:System database=\\server\security.MDW;"

objRS.ActiveConnection = objConnection
objRS.CursorLocation = 2
objRS.Open SQL, objConnection, 3, 3
objConnection.Close


I converted the following

 Code:
$SQL = "INSERT INTO Login (UserName, ComputerName, LoginDateTime)VALUES('" & $UserName & "', '" & $ComputerName & "', '" & $CurrentDate & "' )"
$objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; data source=\\server\login.mdb; User ID=user; password=password; Persist Security Info=True; Jet OLEDB:System database=\\server\security.MDW;')
$objRS.ActiveConnection = $objConnection
$objRS.CursorLocation = 2


I can't get this code to convert "objRS.Open SQL, objConnection, 3, 3"

Any help would be appreciated.


Edited by Allen (09/12/30 11:35 PM)
Edit Reason: added code tags

Top
#197244 - 09/12/30 10:46 PM Re: Modify VBS to KiXtart Scrpit [Re: AModestProposal]
Allen Moderator Offline
KiX Supporter
*****

Registered: 03/04/19
Posts: 4252
Loc: USA
Try replacing the &'s with +'s, and see how far you get. Now() does not exist in Kixtart natively, but I'm sure there is a UDF or a simple workaround for this.

Edited by Allen (09/12/30 10:48 PM)

Top
#197245 - 09/12/30 11:14 PM Re: Modify VBS to KiXtart Scrpit [Re: Allen]
AModestProposal Offline
Fresh Scripter

Registered: 09/12/30
Posts: 5
Loc: Tonadawanda, NY
I believe all of that is part of the SQL statement.
Top
#197246 - 09/12/30 11:39 PM Re: Modify VBS to KiXtart Scrpit [Re: AModestProposal]
Allen Moderator Offline
KiX Supporter
*****

Registered: 03/04/19
Posts: 4252
Loc: USA
You are basically creating a long string with vars. In VBS you use &, but in Kix you use +.

For example:

? "This is a test. " + @userid + ":" + @date

Top
#197247 - 09/12/31 12:31 AM Re: Modify VBS to KiXtart Scrpit [Re: AModestProposal]
Glenn Barnas Moderator Offline
KiX Supporter
*****

Registered: 03/01/28
Posts: 4205
Loc: New Jersey
Welcome to KORG!

Indeed, you should follow Allen's advice and use "+" for concatenation. Another good practice is to build the strings in small steps and display the command string before running it, at least during development.
 Code:
Dim $Sql
$Sql = "INSERT INTO Login (UserName, ComputerName, LoginDateTime)VALUES('"
$Sql = $Sql + $UserName + "', '"
$Sql = $Sql + $ComputerName & "', '" 
$Sql = $Sql + $CurrentDate + "' )"
'Sql Statement is' ? $Sql ?
By building the Sql or Command string that you will execute, the lines are shorter and less complex, making them easier to debug. The display line lets you see what the computer is going to run just before it tries to execute it. I can't tell you how many errors we've solved by breaking complex lines down and seeing them printed out.

Glenn
_________________________

Actually I am a Rocket Scientist! \:D

Top
#197254 - 09/12/31 03:39 PM Re: Modify VBS to KiXtart Scrpit [Re: Glenn Barnas]
AModestProposal Offline
Fresh Scripter

Registered: 09/12/30
Posts: 5
Loc: Tonadawanda, NY
I have tried the suggestion here but has not changed the outcome. How do I convert this statement in VBS to KiXtart,

"objRS.Open SQL, objConnection, 3, 3"

That is the only section that errors out. I should have pointed out that my variable objRS is my ADODB.recordset and objConnection is my ADODB.Connection.

Thanks for the help so far.


Edited by AModestProposal (09/12/31 03:39 PM)

Top
#197255 - 09/12/31 03:51 PM Re: Modify VBS to KiXtart Scrpit [Re: AModestProposal]
Richard H. Administrator Offline
Administrator
*****

Registered: 00/01/24
Posts: 4945
Loc: Leatherhead, Surrey, UK
Hmm...

Did you try just:
 Code:
$objRS.Open($SQL, $objConnection, 3, 3)

Top
#197256 - 09/12/31 04:00 PM Re: Modify VBS to KiXtart Scrpit [Re: Richard H.]
Richard H. Administrator Offline
Administrator
*****

Registered: 00/01/24
Posts: 4945
Loc: Leatherhead, Surrey, UK
Alternatively:
 Code:
$objRS.ActiveConnection = $objConnection
$objRS.CursorLocation = 2
$objRS.LockType=3
$objRS.CursorType=3
$objRS.Open($SQL)

Top
#197258 - 09/12/31 04:20 PM Re: Modify VBS to KiXtart Scrpit [Re: Richard H.]
AModestProposal Offline
Fresh Scripter

Registered: 09/12/30
Posts: 5
Loc: Tonadawanda, NY
First thing I tried, my error catching is a returning an error. Also looking at said Database I can see my entries are not appearing.
Top
#197259 - 09/12/31 05:08 PM Re: Modify VBS to KiXtart Scrpit [Re: AModestProposal]
Radimus Moderator Offline
Moderator
*****

Registered: 00/01/06
Posts: 5186
Loc: Tampa, FL


 Code:
$cnstring = "DRIVER={SQL Server};SERVER=sql2;UID=Inventory;PWD=script;DATABASE=invdb"

$cn = CreateObject("ADODB.Connection")
$cmd= CreateObject("ADODB.Command")
$rs = CreateObject("ADODB.RecordSet")

$cn.connectionstring = $cnstring
$cn.open
$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd

$cmdtxt = "select * from dbo._tbl_Main where SerialNumber = '$serNo'"
$cmd.commandtext = $cmdtxt $rs.open ($cmd)			;? 'Error = '+@ERROR+' - '+@SERROR
	IF $rs.eof = -1		$rs.addnew	ENDIF 

	$rs.fields.item("SerialNumber").value  = $serNo
	$rs.fields.item("ComputerName").value  = @wksta
	$rs.fields.item("AssignedTo").value    = $assigned
	$rs.fields.item("NetworkID").value     = $network
	$rs.fields.item("IPAddress").value     = $ip
	$rs.fields.item("InvDate").value       = @date
	$rs.fields.item("Version").value       = $InvVer
$rs.update							;? 'Error = '+@ERROR+' - '+@SERROR
$rs.close
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#197260 - 09/12/31 05:47 PM Re: Modify VBS to KiXtart Scrpit [Re: Radimus]
AModestProposal Offline
Fresh Scripter

Registered: 09/12/30
Posts: 5
Loc: Tonadawanda, NY
 Code:
$cnstring = "Provider=Microsoft.Jet.OLEDB.4.0; data source=\\server\login.mdb; User ID=blank; password=blank; Persist Security Info=True; Jet OLEDB:System database=\\server\Mueller.MDW;'"
$cn = CreateObject("ADODB.Connection")
$cmd= CreateObject("ADODB.Command")
$rs = CreateObject("ADODB.RecordSet")

Dim $Sql
$Sql = "INSERT INTO Login (UserName, ComputerName, LoginDateTime)VALUES('"
$Sql = $Sql + $UserName + "', '"
$Sql = $Sql + $ComputerName & "', '" 
$Sql = $Sql + $CurrentDate + "' )"
;'Sql Statement is' ? $Sql ?

$cn.connectionstring = $cnstring
$cn.open
$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd

$cmdtxt = "$SQL"
$cmd.commandtext = $cmdtxt $rs.open ($cmd)			? 'Error = '+@ERROR+' - '+@SERROR
	IF $rs.eof = -1		$rs.addnew	ENDIF 

	; $rs.fields.item("SerialNumber").value  = $serNo
	; $rs.fields.item("ComputerName").value  = @wksta
	; $rs.fields.item("AssignedTo").value    = $assigned
	; $rs.fields.item("NetworkID").value     = $network
	; $rs.fields.item("IPAddress").value     = $ip
	; $rs.fields.item("InvDate").value       = @date
	; $rs.fields.item("Version").value       = $InvVer
	$rs.fields.item("UserName").value		= @USERID
	$rs.fields.item("ComputerName").value	= @WKSTA
	$rs.fields.item("LoginDateTime").value	= @DATE
$rs.update							? 'Error = '+@ERROR+' - '+@SERROR
$rs.close

Now i am getting an error message saying delete, insert, update, select not allow with a closed object. Sorry, i just don't seem to be getting this.

Top
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Chris S., Glenn Barnas, ShaneEP, Arend_, Mart 
Hop to:
Shout Box

Who's Online
1 registered (AndreLuiz) and 128 anonymous users online.
Newest Members
OKCPCS, mprell, Dad24Lads, Cutebear, kstieers
17434 Registered Users

Generated in 0.035 seconds in which 0.01 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org