|
|
|||||||
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. |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
I believe all of that is part of the SQL statement. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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 ? Glenn |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
Hmm... Did you try just: Code: $objRS.Open($SQL, $objConnection, 3, 3) |
||||||||
|
|
|||||||
Alternatively: Code: $objRS.ActiveConnection = $objConnection $objRS.CursorLocation = 2 $objRS.LockType=3 $objRS.CursorType=3 $objRS.Open($SQL) |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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. |