Logging to database VB Question

[seth350]

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... 

Code: [Select]

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

[Archie]

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.

[seth350]

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.

[seth350]

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.

Code: [Select]

INSERT INTO CityPh (Entry, Cityph)
VALUES (Entry=?, CitypH=?)


Code: [Select]

    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

[Archie]

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)

[seth350]

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.

[Noe]

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.

[seth350]

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.

[seth350]

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!

[Archie]

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.

[seth350]

It is decimal.

[seth350]

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.