Saving data in real time to table - plotting it also.

[bmind]

I am relatively new with VB (only the 3[SUP]rd[/SUP] week since I started learning) and have tried to find some info on what am trying to do but it has proven to be very difficult. Have created a little program in VB that will display in label boxes measurements that am taking in real time. These are numbers and text (ON/OFF state). What I want to do is:

1. Create a table inside a database that I have created in SQL server 2008. Name table according to date of measurements taken.
2. Want to get the data measured in real time and populate the table (every half a second).
3. Table will have a minimum of 4 columns. The simplest case with 4 columns.

1. First column will be ID number (datapoint number – incremented by 1 with each datapoint added )
2. Second column will be time of measurement (every half a second)
3. Third column will be a number measured (display 2 decimals in table?)
4. Forth column will be a State measured (ON or OFF) text.
5. Save the table in data base when complete – Am not sure if I need to do that or it is automatically done.

ID	Time	Voltage	State
1
2

At the moment I can read from the device and display numbers/text in label boxes. Once I have done the above want to display a real time data graph of the measurements (numbers only) taken (for one measurement) plotting value vs time. One step at a time…
Let me know if have not provided enough information as am still very much learning the basics of vb.

Am currently using:
Visual Studio 2010 express
Microsoft SQL Server 2008

 

[jmcilhinney]

You're wring from step 1 I'm afraid. You would never do anything like that. You would handle it in one of two different ways.

1. Have one table for all measurements and include a date column. Each time you insert a record you include the date of the measurement. To retrieve all records for a particular date, you filter on the date column using your WHERE clause.

2. Have one table for storing the dates on which readings were taken with one record for each date. That table also includes an ID column. You then have one table for all the measurements and that table includes a foreign key from the date table. To retrieve all records for a particular date, you join the two tables and filter on the date column using your WHERE clause.

The better option would depend on whether you would want to store any other information with the date. If you went for option 2 and you only had the ID and the date in the extra table then that's probably a bit of a waste. If you would have one or more additional columns then option 2 would be the better.

As for inserting the data, you simply create a command, set its parameters and call ExecuteNonQuery. As you want to insert records repeatedly I would say that you should create the command with parameters and open the connection only once for the whole session. Each time you want to insert a record you then just need to set the Value property of each parameter and execute.

 

[bmind]

Thank you for your reply. It shows that I know little of Visual Basic. By date column I assume you mean a column that includes the times for each measurement taken?
I would have to say that option 1 would be the most appropriate on this case as I only needed the date for measurements taken. I could have up to 16 measurements being taken simultaneously. So in all this table will have 17 columns. Is option 1 still the better one?
The user will select what measurements will be stored by checking the checkboxes and columns should take the checkbox name. Does this make sense? And the data measured should be saved to the table for a predetermined amount of time which is selected by the user(0 - 48 hours from the start of the test).

Now let's talk some structure and actual code of how am I going to achieve this. Can you let me know which functions and or structure of how would I go about this and I will look for more information on the functions and how to write the code.

Your help is appreciated.
Note: I am a mechanical engineer and find the programming skill quite useful.

 

[bmind]

Update:
I have been learning about databases and did a simple program with the code below. I am getting an error when i save the new entry and searched the web but could not solve it - any idea how to deal with this?

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

Public
Class Database_Example


Private m_cn As New SqlConnection()


Private m_DA As New SqlDataAdapter


Private m_CB As New SqlCommandBuilder


Private m_DataTable As New DataTable


Private m_rowPosition As Integer = 0


　


Private Sub Database_Example_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

m_cn.Close()

m_cn.Dispose()


End Sub



Private Sub Database_Example_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

m_cn.ConnectionString =
"Data Source=.\SQLEXPRESS; AttachDbFilename = " & _


"C:\Users\mleka\Desktop\Simulation group\Calculation Sheet\training\Programming\test\test\Database Example.mdf;Integrated Security=True; Connect Timeout=30;" & _


"User Instance=True"

m_cn.Open()


'm_DataTable.PrimaryKey = New DataColumn() {m_DataTable.Columns("ContactName")}


Dim columns(1) As DataColumn

columns(0) = m_DataTable.Columns(
"ContactName")

m_DataTable.PrimaryKey = columns


m_DA =
New SqlDataAdapter("Select * From Contacts", m_cn)

m_CB =
New SqlCommandBuilder(m_DA)

m_DA.Fill(m_DataTable)


Me.ShowCurrentRecord()



End Sub


　


Private Sub ShowCurrentRecord()



If m_DataTable.Rows.Count = 0 Then

txtContactName.Text =
""

txtState.Text =
""


Exit Sub


End If

txtContactName.Text = _

m_DataTable.Rows(m_rowPosition)(
"ContactName").ToString()

txtState.Text = _

m_DataTable.Rows(m_rowPosition)(
"State").ToString()


End Sub



Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click


' Move to the first row and show the data.

m_rowPosition = 0


Me.ShowCurrentRecord()


End Sub


　


Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click


If m_rowPosition > 0 Then

m_rowPosition = m_rowPosition - 1


Me.ShowCurrentRecord()


End If


End Sub



Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click


' If not on the last row, advance one row and show the record.


If m_rowPosition < (m_DataTable.Rows.Count - 1) Then

m_rowPosition = m_rowPosition + 1


Me.ShowCurrentRecord()


End If


End Sub



Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click


' If there are any rows in the data table, move to the last and show


' the record.


If m_DataTable.Rows.Count > 0 Then

m_rowPosition = m_DataTable.Rows.Count - 1


Me.ShowCurrentRecord()


End If


End Sub



Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click


' If there is existing data, update it.


If m_DataTable.Rows.Count <> 0 Then

m_DataTable.Rows(m_rowPosition)(
"ContactName") = txtContactName.Text

m_DataTable.Rows(m_rowPosition)(
"State") = txtState.Text

m_DA.Update(m_DataTable)


End If


End Sub



Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click


Dim drNewRow As DataRow = m_DataTable.NewRow()

drNewRow("ContactName") = txtNewContactName.Text

drNewRow(
"State") = txtNewState.Text

m_DataTable.Rows.Add(drNewRow)

m_DA.Update(m_DataTable)

m_rowPosition = m_DataTable.Rows.Count - 1


Me.ShowCurrentRecord()


End Sub



Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click


' If there is data, delete the current row.


If m_DataTable.Rows.Count <> 0 Then

m_DataTable.Rows(m_rowPosition).Delete()

m_DA.Update(m_DataTable)

m_rowPosition = 0


Me.ShowCurrentRecord()


End If


End Sub

End
Class