MSDE SQL Database commands

[BigDaddyO]

I am setting up a MSDE SQL Database and below is some code I am using Specifically with SQL.

The Database Table for testing is setup like so.

Table Name: Test_Logs

Columns: Test_Name, Computer_Name, Image_Name, Start_Time, Pass_Fail

Column Setup: VarChar(20), VarChar(20), VarChar(20), DateTime, VarChar(10)

The Start_Time column will be the identifier as it should be different for each test.

$Time = @HOUR & ":" & @MIN & ":" & @SEC
;Format Required to import Date into the SQL database
Global $StartDateTime = @YEAR & "/" & @MON & "/" & @MDAY & " " & $Time
;Format Required to search for the Date in the SQL database
Global $PullDateTime = @MON & "/" & @MDAY & "/" & @YEAR & " " & $Time

Dim $1 = "'test1'", $2 = "'HP Vectra'", $3 = "'VectraISO1'", $4 = "'" & $StartDateTime & "'", $5 = "'Passed'"
$SQLFields = "Test_Name, Computer_Name, Image_Name, Start_Time, Pass_Fail
$SQLData = $1 & ", " & $2 & ", " & $3 & ", " & $4 & ", " & $5 & ", "

;Connecting to the SQL Database:
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB; _
              Data Source=ServerIP; _
              User ID=UserName; _
              Password=IDpassword;")

;Insert a new Row with Data into the SQL Database:
$sqlCon.Execute("INSERT INTO Test_Logs(" & $SQLFields & ") Values (" & $SQLData & ")")

;Used to delete rows from the Test_Logs table
$sqlCon.Execute("Delete From Test_Logs where Test_Name = 'test1'")


; This is used to update specific information in a Row of data.  
$sqlCon.execute("Update Test_Logs Set Pass_Fail = 'Passed' Where Start_Time = '" & $PullDateTime & "'")

; Lastly of course close the connection to the database before exiting the script
$sqlCon.close

I am still trying to work on pulling information from the database but as of right now I keep getting Junk returned.

$data = $sqlCon.Execute("Select Start_Time From Test_Logs")

MsgBox (0, "Data", $Data)

This is just showing some eC junk message, If I figure it out, Ill update this post.

EDIT: By looking at the code from ScriptKitty I was able to put this together for pulling data from my SQL Database.

Dim $DataRead
$sqlRs = ObjCreate ("ADODB.Recordset")
$sqlRS.open("Select * From Test_Logs where Start_Time >= '06/27/2005 11:00:00'", $sqlCon);This line allows logical exp[b][/b]ressions for searching out specifics
With $sqlRS
If .RecordCount Then
While Not .EOF
$DataRead = $DataRead & .Fields('Test_Name').Value & " - "
$DataRead = $DataRead & .Fields('Computer_Name').Value & " - " 
$DataRead = $DataRead & .Fields('Image_Name').Value & " - "
$DataRead = $DataRead & .Fields('Start_Time').Value & " - "
$DataRead = $DataRead & .Fields('Pass_Fail').Value & " - "
$DataRead = $DataRead & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$sqlCon.Close
MsgBox(0, "return", $DataRead)

I am starting to really get the hang of this SQL stuff.

Edited June 27, 2005 by MikeOsdx

[SuperChief]

I am setting up a MSDE SQL Database and below is some code I am using Specifically with SQL.

The Database Table for testing is setup like so.

Table Name:  Test_Logs

Columns: Test_Name, Computer_Name, Image_Name, Start_Time, Pass_Fail

Column Setup:  VarChar(20), VarChar(20), VarChar(20), DateTime, VarChar(10)

The Start_Time column will be the identifier as it should be different for each test.

$Time = @HOUR & ":" & @MIN & ":" & @SEC
;Format Required to import Date into the SQL database
Global $StartDateTime = @YEAR & "/" & @MON & "/" & @MDAY & " " & $Time
;Format Required to search for the Date in the SQL database
Global $PullDateTime = @MON & "/" & @MDAY & "/" & @YEAR & " " & $Time

Dim $1 = "'test1'", $2 = "'HP Vectra'", $3 = "'VectraISO1'", $4 = "'" & $StartDateTime & "'", $5 = "'Passed'"
$SQLFields = "Test_Name, Computer_Name, Image_Name, Start_Time, Pass_Fail
$SQLData = $1 & ", " & $2 & ", " & $3 & ", " & $4 & ", " & $5 & ", "

;Connecting to the SQL Database:
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB; _
              Data Source=ServerIP; _
              User ID=UserName; _
              Password=IDpassword;")

;Insert a new Row with Data into the SQL Database:
$sqlCon.Execute("INSERT INTO Test_Logs(" & $SQLFields & ") Values (" & $SQLData & ")")

;Used to delete rows from the Test_Logs table
$sqlCon.Execute("Delete From Test_Logs where Test_Name = 'test1'")
; This is used to update specific information in a Row of data.  
$sqlCon.execute("Update Test_Logs Set Pass_Fail = 'Passed' Where Start_Time = '" & $PullDateTime & "'")

; Lastly of course close the connection to the database before exiting the script
$sqlCon.close

I am still trying to work on pulling information from the database but as of right now I keep getting Junk returned.

$data = $sqlCon.Execute("Select Start_Time From Test_Logs")

MsgBox (0, "Data", $Data) 

This is just showing some eC junk message, If I figure it out, Ill update this post.

<{POST_SNAPBACK}>

I'm new to AutoIt but your code snipit returns to $data and you are displaying $Data in the message box. These would be two diferent values.

[BigDaddyO]

Figured out the Reading of data by looking through ScriptKittys origional Database posts. It is a whole lot more complicated than I thought but eh... what ever. I have updated the my post above with the display code.