49
« on: March 24, 2017, 12:16:54 PM »
Hello folks,
Been cutting my teeth with VB and I am just about down to my gums lol
I have a seemingly simple app using AHMI to connect to a CLGX plc and log a variable to an Access db. Once I have this working, I will expand to more variables.
Anyways, I have probably coded down a rabbit hole...
My problem is that it seems the code is not opening the connection to the database, or maybe I don't need to handle the connection in the way I am doing it?
Previously when I tried to run a different set of code, it would connect, log only a few variables, and then I would get an exception stating that the connection to the db was already open. I figured, "Ok, Someway or another the code to open the connection is being ran more than once, so I need to control when to open the connection based on the state of the connection."
And this is what I ended up with...
Public Sub DataSubscriber21_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber21.DataChanged
Dim phvalue As Single
If e.PlcAddress = "Process.Waste_Water_Facility.City.Pit.Ph.Reading" Then
phvalue = e.Values(0)
testdisp1.Value = phvalue
End If
If phvalue > 0 Then 'Log only if value is healthy.
Dim sqlcmd As String
Dim objCmd As New OleDb.OleDbCommand
Dim conn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Documents\phdata.accdb")
Dim connstate As Int16
Dim dblogstate As Int16 = 1
If ConnectionState.Closed Then 'Set connstate to 1 if DB Connection is Closed
connstate = 1
ElseIf ConnectionState.Connecting Then 'Set connstate to 2 if DB Connection is Connecting
connstate = 2
ElseIf ConnectionState.Open Then 'Set connstate to 3 if DB Connection is Connected
connstate = 3
End If
Select Case dblogstate
Case 1 'Open connection if one does not exist.
If connstate = 3 Then
dblogstate = 3 'Already connected to db, start logging.
ElseIf connstate = 1 Then
dblogstate = 2
End If
dbstatlabel.ForeColor = Color.Gray
dbstatlabel.Text = "Not Connected To Database"
Case 2 'Connect to database
Try
conn.Open()
dbstatlabel.ForeColor = Color.Blue
dbstatlabel.Text = "Connecting to Database..."
Catch ex As Exce
MsgBox(ex.Message)
dbstatlabel.ForeColor = Color.Red
dbstatlabel.Text = "Database Error!"
End Try
If connstate = 3 Then 'Connected to database, start logging.
dblogstate = 3
ElseIf connstate = 2 Then 'Trying to connect to database.
dblogstate = 2 'Keep trying to connect.
dbstatlabel.ForeColor = Color.Blue
dbstatlabel.Text = "Trying to connect to Database..."
End If
Case 3 'Connected to database
dbstatlabel.ForeColor = Color.Green
dbstatlabel.Text = "Connected to Database"
Try
sqlcmd = "INSERT INTO CitypH (Cityph) VALUES ('" & phvalue & "')"
objCmd = New OleDb.OleDbCommand(sqlcmd, conn) 'Set the command
objCmd.ExecuteNonQuery() 'Execute the SQL command
Catch ex As Exception
MsgBox(ex.Message)
dbstatlabel.ForeColor = Color.Red
dbstatlabel.Text = "Database Error!"
End Try
Case Else
dbstatlabel.ForeColor = Color.Red
dbstatlabel.Text = "Program Error!"
End Select
End If
End Sub
Show Posts