AModestProposal
(Fresh Scripter)
2009-12-30 10:26 PM
Modify VBS to KiXtart Scrpit

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.


AllenAdministrator
(KiX Supporter)
2009-12-30 10:46 PM
Re: Modify VBS to KiXtart Scrpit

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.

AModestProposal
(Fresh Scripter)
2009-12-30 11:14 PM
Re: Modify VBS to KiXtart Scrpit

I believe all of that is part of the SQL statement.

AllenAdministrator
(KiX Supporter)
2009-12-30 11:39 PM
Re: Modify VBS to KiXtart Scrpit

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


Glenn BarnasAdministrator
(KiX Supporter)
2009-12-31 12:31 AM
Re: Modify VBS to KiXtart Scrpit

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


AModestProposal
(Fresh Scripter)
2009-12-31 03:39 PM
Re: Modify VBS to KiXtart Scrpit

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.


Richard H.Administrator
(KiX Supporter)
2009-12-31 03:51 PM
Re: Modify VBS to KiXtart Scrpit

Hmm...

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


Richard H.Administrator
(KiX Supporter)
2009-12-31 04:00 PM
Re: Modify VBS to KiXtart Scrpit

Alternatively:
 Code:
$objRS.ActiveConnection = $objConnection
$objRS.CursorLocation = 2
$objRS.LockType=3
$objRS.CursorType=3
$objRS.Open($SQL)


AModestProposal
(Fresh Scripter)
2009-12-31 04:20 PM
Re: Modify VBS to KiXtart Scrpit

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.

Radimus
(KiX Supporter)
2009-12-31 05:08 PM
Re: Modify VBS to KiXtart Scrpit



 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


AModestProposal
(Fresh Scripter)
2009-12-31 05:47 PM
Re: Modify VBS to KiXtart Scrpit

 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.