Home Automation Domotica Forum Europe, Bwired Forum

Code: Select all

6-11-2012 15:14:15  - Error - Script compile error: Het type MySqlConnection is niet gedefinieerd.on line 21

Code: Select all

Imports System.Data
Imports MySql.Data.MySqlClient

Dim Public ID As String = "statusCallBack.vb"
Sub checkStatus(ByVal aParm as Object)
 dim stat, hc, dc, hdc,refID, sName as String 

 Dim conn As MySqlConnection
 Dim myCommand As New MySqlCommand
 Dim varDate as String
 Dim varTime as String
 Dim varDeviceName as String
 Dim varDeviceCode as String 

'---------------------------------------------------------------
'PLACE THIS STATEMENT IN YOUR STARTUP SCRIPT
' hs.RegisterStatusChangeCB "statusCallBack.vb","checkStatus"
'---------------------------------------------------------------
 hc = aParm(0)
 dc = aParm(1)
 stat = aParm(2)
 refID = aParm(3)
 hdc = hc & dc 

 Dim device
 Dim dn
 Dim type as String
 Dim tijd

 tijd = Datepart("yyyy",Now()) & "-" & Right("0" & DatePart("m",Now()),2) & "-" & Right("0" & DatePart("d",Now()),2) & " " & Right("0" & DatePart("h",Now()),2) & ":" & Right("0" & DatePart("n",Now()),2) & ":" & Right("0" & DatePart("s",Now()),2)

  device = hs.GetDeviceByRef(refID)

  dn = device.name

'
'Detect type of device
'

Select Case (hc) 

  Case "#"
     type = "temp"

  Case "$"
     type = "minmax"

  Case "["
     type = "ping"

  Case "A"
     type = "Schakelaar"

  Case "C"
     type = "Schakelaar"

  Case "H"
     type = "Schakelaar"

  Case "K"
     type = "Schakelaar"

  Case "P"
     type = "Schakelaar"

  Case "R"
     type = "Remeha Calenta"

  Case "W"
     type = "Weer"

  Case "Z"
     type = "Server"

  Case "_"
     type = "Plugwise"

End Select 

'hs.writelog(ID,"House Code: " & hc & " Device Code " & dc & " Status: " & stat & " Device Value: " & hs.DeviceValue(hdc) & " Device String: " & hs.DeviceString(hdc) & " Name: " & dn & " Type: " & type & " Time: " & tijd)

 conn = New MySqlConnection()
 conn.ConnectionString = "server=localhost; user id=xxxxxxx; password=xxxxxxxx; database=HomeSeerDB"

Try
    conn.Open()
    'hs.writelog ("mySQL", "Connection Opened Successfully!")
    myCommand.CommandText = "INSERT INTO tbldevices (hc,devicename,type,dc,value,status,ts)" & " VALUES ('" & hc & "','" & dn & "','" & type & "','" & dc & "','" & hs.DeviceValue(hdc) & "','" & stat & "','" & tijd & "')"
    myCommand.Connection = conn
    myCommand.ExecuteNonQuery()
    'hs.writelog ("mySQL", "Logged to database: " & varDeviceName & ", " & varDeviceCode & ", " & iStatus)
    conn.Close()
    Catch myerror As MySqlException
    hs.writelog ("mySQL", "Error Connecting to Database: " & myerror.Message)
End Try

End Sub

Code: Select all

hs.RegisterStatusChangeCB "statusCallBack.vb","checkStatus"

Code: Select all

6-11-2012 15:14:15  - Error - Script compile error: Het type MySqlConnection is niet gedefinieerd.on line 21

Code: Select all

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 11, 2010 at 11:05 PM
-- Server version: 5.1.37
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `homeseerdb2`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbldevices`
--

CREATE TABLE IF NOT EXISTS `tbldevices` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `hc` varchar(1) DEFAULT NULL,
  `devicename` varchar(40) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `dc` varchar(3) DEFAULT NULL,
  `value` varchar(10) DEFAULT NULL,
  `status` int(10) DEFAULT NULL,
  `ts` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pkID2` (`id`),
  KEY `idcDC2` (`dc`),
  KEY `idxHC2` (`hc`),
  KEY `idxTS2` (`ts`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3231458 ;

Code: Select all

mySQL - Error Connecting to Database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Woonkamer T','RFXcom','3','191','2','2012-11-13 21:32:01')' at line 1

Code: Select all

mySQL - Error Connecting to Database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Woonkamer T','RFXcom','3','191','2','2012-11-13 21:32:01')' at line 1

Code: Select all

    Dim strSQL as String = ""
'  Note: I removed the " & "  between ) and VALUES. There is no need for that
    strSQL = "INSERT INTO tbldevices (hc,devicename,type,dc,value,status,ts) VALUES ('" & hc & "','" & dn & "','" & type & "','" & dc & "','" & hs.DeviceValue(hdc) & "','" & stat & "','" & tijd & "')"
    hs.writelog ("mySQL", "strSQL: " & strSQL) 'this will tell you what you send to the server
    myCommand.CommandText = strSQL
    hs.writelog ("mySQL", "strSQL executed")