SQL code to create a custom field

[Cobra7]

We have not yet upgraded to 3.5 yet, the managers like to wait a little while after an upgrade to do it.

Anyway, one of the things I would like to do is create a custom field with each PC's inventory number in it. Each inventory number is 5 digits and at the end of the computer name.

We have around 1100 PC's so entering them in by hand would not be an option. Is there a way to do this? It would help us with some of our reports, either as a search string and reporting.

Also I am just wondering if it is possible to have an MS Access database go into the LS SQL database and pull information (not asking how to do this one, just wondering if it is possible).

[Anonymous]

Well, for mass imports of data I usually script something with AutoIT, before I prepare the lists and create a CSV file.

Here is a small example on how to connect to the database and perform changes:

#include <_CSVLib_V1.3.au3>
#include <Array.au3>

$path = @ScriptDir & "\datafile.csv"

Func _SQLCon($scIP, $scUser, $scPass, $scDB)
	$Objconn = ObjCreate("ADODB.Connection")
	$Objconn.Open ("Provider=SQLOLEDB; Data Source=" & $scIP & "; User ID=" & $scUser & "; Password=" & $scPass & "; database=" & $scDB & ";")
	Return $Objconn
EndFunc   ;==>_SQLCon

Func _SQLQuery($oConnectionObj, $iQuery)
	If IsObj($oConnectionObj) Then
		Return $oConnectionObj.Execute($iQuery)
	EndIf
EndFunc   ;==>_SQLQuery

Func _SQLEnd($Objconn)
	If IsObj($Objconn) Then
		$Objconn.close
	EndIf
EndFunc   ;==>_SQLEnd

$sql = _SQLCon("Server\instance", "lssqluser", "password", "Database")

$records=_CSVFileReadRecords($path)
$numrecords = _CSVRecordsGetFieldValue($records, 0, 0)
For $f = 1 To $numrecords
	$computername=_CSVRecordsGetFieldValue($records, 1, $f)
	$username=_CSVRecordsGetFieldValue($records, 2, $f)
	$sqlstring= "UPDATE tblCustInventory SET UserName = '"& $username & "' WHERE (ComputerName = '" & $computername & "')"
	$query  = _SQLQuery($sql, $sqlstring)	
Next
_SQLEnd($sql)

I'm importing something similar, putting all data together into the new table "tblCompCustom" and I guess it will work greatly this way. 😉