AdvancedHMI Software
General Category => Support Questions => Topic started by: seth350 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
-
I can give you an alternative method that will let VS do all the work for you and save you a lot hassle. VS has a tool called Data Sources. It is a tool that let's you use wizards and graphical tools which in turn VS creates a set of classes for disconnected tables to hold the data and adapters to transfer between the real DB and the disconnected tables.
- In VS go to View->Other Windows->Data Sources
- Once the Data Sources window opens, click Add New Data Source
- Go through the Wizard creating a DataSet and a New Connnection to your DB
Once you did this, VS will have created a Dataset and TableAdapter for you. If you Build the Solution, then open your form in DesignView, you will now see the items in the Toolbox
The Dataset is an in-memory representation of your DB. If you selected more than 1 table, the dataset encapsulates all of those tables. The TableAdapter encapsulates all of your connections, queries, etc. to move between the DataSet and the real DB.
- With your form open in Design View, from the Toolbox add a DataSet and a TableAdapter
The concept for using these is to add a new record to the table in the DataSet, then use the TableAdapter's Update command to push those new records from the DataSet to the real DB.
From this point, if you can give me the names of your DataSet and TableAdapter as you see it in the Form's design view, I can give you the specific code snippets (maybe 3 to 10 lines) to write to the DB.
-
Thank you Archie, that is a lot less hassle!
Let me give this a try and see where I end up. I will report back if I get lost.
-
Got it working Archie. I appreciate the tip!
Much cleaner code and less hassle.
Using the tableadapter .Insert command and passing in the values I want to INSERT is pretty cool. I was going to try using the Query designer to build a query but VS did not like my parameters being undefined.
INSERT INTO CityPh (Entry, Cityph)
VALUES (Entry=?, CitypH=?)
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.
CitypHTableAdapter1.Insert(Now.Date, phvalue)
End If
End Sub
-
When you created the DataSet with the wizard, all of the necessary queries were created for you. Here is the process to add a record to the DB:
1) Create a variable to represent a row in your table
2) Create an instance of the row for the variable to point to
3) Set the field values of the row
4) Add the row to the Datatable within the DataSet
5) Use the Update method of the TableAdapter to send the table additions to the DB
I do not know the names of your DataSet or table so I will try to guess the best I can
1)
Dim NewRow as CitypHDataSet.CitypHRow
2)
NewRow=CitypHDataSet.CitypHTable.NewRow()
3)
NewRow.CitypH=123
4)
CitypHDataSet.CitypHTable.AddCitypHRow(NewRow)
5)
CitypHTableAdapter1.Update(CitypHDataSet.CitypHTable)
-
Dataset: phdataDataSet
Table: Cityph
Hmmm...
I will try that too. It is working using the .Insert command as of now. Just need to figure out why the decimal is not getting to the db.
Perhaps your method will resolve that.
-
Dataset: phdataDataSet
Table: Cityph
Hmmm...
I will try that too. It is working using the .Insert command as of now. Just need to figure out why the decimal is not getting to the db.
Perhaps your method will resolve that.
It may be related to the declared field on the database, if you declared an integer, then it will save integers only, skipping decimals. You can use double, or whatever type you need according with the amount of digits you want after the point. Also, Access can format the displayed values.
-
Im going to try Archie's method here in a bit this morning.
The field in the db is set as data type "Number", Field size "Double", Format "Fixed", Two Decimal Places.
-
Archie's method works as well, but the decimal is still not being transfered to the db. I can manually enter a decimal number in the table and it displays it correctly.
The value displays as a decimal in the application. Not sure what is going on. Anyways, thank you Archie for the tip I appreciate it!
-
In your Data Sources Windows, right click the data source and select Open in Design View
In the table with the problem. click on the particular field, the check the Properties Window to see what the data type is.
-
It is decimal.
-
In your Data Sources Windows, right click the data source and select Open in Design View
In the table with the problem. click on the particular field, the check the Properties Window to see what the data type is.
Excuse me, I was wrong. The Dataset was set as decimal but the TableAdapter was set to Int32. Issue is fixed now.
Sorry for my ignorance.