Hi Guys

I wrote a script that checks the WIFI signal and some other stuff. I want to put that in a SQLite DB.

DB creation:

Add-Type -Path "C:\Program Files\System.Data.SQLite\2010\bin\System.Data.SQLite.dll"
$cn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$cn.ConnectionString = "Data Source=C:\Database\Status.db;New=True;
"$cn.Open()
$cm = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$sql = create table network_State_Polling(create_date  TEXT, LAN_Name TEXT, LAN_State varchar(70), LAN_Signal varchar(50), WiFi_Name varchar(50), WiFi_State varchar(50), WiFi_Signal varchar(50), MBN_Name varchar(50), MBN_State varchar(50), MBN_Signal varchar(50));
$cm.Connection = $cn
$cm.CommandText = $sql
$cm.ExecuteNonQuery()
$cn.Close()

To test the input of variables i did the following:

Function AddRecord ($Date, $Date1){

Add-Type -Path C:\Program Files\System.Data.SQLite\2010\bin\System.Data.SQLite.dll

$cn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$cn.ConnectionString = Data Source=C:\Database\Status.db
$cn.Open()

$cm = New-Object -TypeName System.Data.SQLite.SQLiteCommand
$sql = "insert into network_State_Polling values($Date, $Date1,'test2','test3','test','test1','test2','test3','test2','test3');"

$cm.Connection = $cn
$cm.CommandText = $sql



$cm.ExecuteNonQuery()
$cn.Close()
}


$Date = ((Get-Date).ToString('ddMMyyyyHHmmss'))


#WIFI   
 $enabledWIFI = Get-WmiObject -Namespace "root\cimv2" -Query 'select NetEnabled from win32_networkadapter where Description = "Intel(R) Dual Band Wireless-AC 7260"'

 write-host "WiFi connection"

if ($enabledWIFI.NetEnabled){


    $wlanraw = netsh wlan show interface


    #$objWLAN = "" | Select-Object SSID, Signal, State

    ForEach ($Line in $wlanraw) {
      
               
	if ([regex]::IsMatch($Line,"    SSID")) {
     	$objWLANSSID = $Line -Replace"    SSID                   : ",""
           	}         
    if ([regex]::IsMatch($Line,"    Signal")) {
    	   	$objWLANSignal = $Line -replace "    Signal                 : ",""
		}
        	
   if ([regex]::IsMatch($Line,"    State")) {
    	   	$objWLANState = $Line -replace "    State                  : ",""
		}
        	}
   
   
   if ([regex]::IsMatch($objWLANSSID, "NmbsSncb")) {
        $State = $objWLANState
        write-host "SSID: "$objWLANSSID
        write-host "Signal: "$objWLANSignal
        Write-Host ""

        
        }
   }else {
   
        Write-Host "Device is not on WIFI or WIFI is disabled"
        Write-host ""
       
        
   
   }

AddRecord $Date $objWLANState.ToString()

Don't focus on the names (LAN, WIFI etc), it's just for testing.

Unfortunaly i get an error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error or missing database
no such column: connected"
At C:\Temp\AddingLines.ps1:17 char:1
+ $cm.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

Connect is the value of$objWLANState.

Could somebody help me please? I have no idea what i'm doing wrong..

Thanks!

Steini46

This isn't a SQLite support forum.

Your insert statement is wrong.  It requires a list of columns. YOU also have defined "create_date" as "TEXT" so it needs single quotes.

Use the utilities to learn how to create an insert statement before trying to program this.

INSERT INTO <Tablename>(col1,col21col3) VALUES('val1','val2','val3')

Hi,

Thanks for your answer.

I convert the Date to a String, so I can put it in TEXT. It's a variable in PS, so it doesn't need quotes. When i call my function I include it.

$Date = ((Get-Date).ToString('ddMMyyyyHHmmss'))

I changed it, but I still have the some problem:

$sql = "insert into network_State_Polling(create_date, LAN_Name, LAN_State, LAN_Signal, WiFi_Name, WiFi_State, WiFi_Signal, MBN_Name, MBN_State, MBN_Signal) values($Date,$Date1,'test2','test3','test','test1','test2','test3','test2','test3');"

The problem is when I insert the second value into my DB. I receive the same error.

Thanks for looking into this.

All text fields in SL MUST be enclosed I single quotes.

$sql=@"
   INSERT INTO network_State_Polling(
        create_date, LAN_Name, LAN_State, LAN_Signal, WiFi_Name, WiFi_State, WiFi_Signal, MBN_Name, MBN_State, MBN_Signal
   )
   VALUES(
          '$Date','$Date1','test2','test3','test','test1','test2','test3','test2','test3'
   )
"@

For further assistance in how to use SQLite pleas post in the correct forum.

Hi jrv

Thanks for your help, it was indeed the quotes..

It's strange that it works for the Date without quotes and not for Date1, but know it is working!

Thank you!

Steini46