Click here to Skip to main content
13,804,063 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
hi...
I have two tables and I want save value from senser when the sensor value>0 in first table and when senser value=0 I want calculate the average sensor value in first table and save the resulat in second table and delete the data in first table in vb.net

What I have tried:

hi...
I have two tables and I want save  value from senser when the sensor value>0 in first table and when senser value=0 I want calculate the average sensor value in first table and save the resulat in second table and delete the data in first table in vb.net
Posted
Updated 8-Dec-18 0:40am
Comments
MadMyche 5-Dec-18 14:11pm
   
SQL Server?
Member 14044492 5-Dec-18 14:51pm
   
yes with sql server
Member 14044492 5-Dec-18 14:52pm
   
this my code

Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick

If s1.IsOpen = False Then

s1.PortName = "COM4"
s1.BaudRate = 9600
s1.Open()

Dim reading As String
reading = s1.ReadLine()

'lb_case.ForeColor = Color.Green
'lb_case.Text = "connected"

txt_read.Text = Convert.ToString(reading)
If txt_read.Text <> "" Then
Select Case Val(txt_read.Text)

Case 0.0
''''فحص جدول القراءات

Comm = New SqlClient.SqlCommand("SELECT * FROM first_tab", con)
SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)

SQLAdap.Fill(ds, "ds_used1")

If ds.Tables("ds_used1").Rows.Count > 0 Then
'حساب معدل الوقود الصاعد
Comm = New SqlClient.SqlCommand("select AVG (reading) from first_tab ", con)


SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)
SQLAdap.Fill(ds, "ds_avup")

Dim av_fuel_up As Double
av_fuel_up = ds.Tables("ds_avup").Rows(0).Item(0)

'حساب تاريخ التشغيل
Comm = New SqlClient.SqlCommand("select read_date from first_tab where id=1", con)


SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)
SQLAdap.Fill(ds, "ds_read_date")

Dim work_date As Date
work_date = ds.Tables("ds_read_date").Rows(0).Item(0)

'حساب مدة التشغيل
Comm = New SqlClient.SqlCommand("select DATEDIFF(s,(select read_date from first_tab where id=1),(select read_date from first_tab where id=(select MAX(id)from first_tab)))", con)





SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)
SQLAdap.Fill(ds, "ds_working_period")

Dim working_period As Integer
working_period = ds.Tables("ds_working_period").Rows(0).Item(0)

'اضافة البيانات الى جدول التشغيل
Comm = New SqlClient.SqlCommand("SELECT * FROM second_table", con)
SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)
SQLAdap.Fill(ds, "ds_working")
Dim dr2 As DataRow

dr2 = ds.Tables("ds_working").NewRow

dr2.Item("working_date") = work_date
dr2.Item("read_av") = av_fuel_up
dr2.Item("period") = working_period

ds.Tables("ds_working").Rows.Add(dr2)

comBild.GetUpdateCommand()
SQLAdap.Update(ds, "ds_working")
con.Close()
'تصفير جدول الاستخدام

Dim cmd As New SqlClient.SqlCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "truncate table first_tab"
cmd.Connection = con

con.Open()
cmd.ExecuteNonQuery()

con.Close()
Else
MadMyche 5-Dec-18 17:22pm
   
It would be best to use the "Improve Question" widget and paste this in yur ofirinal post.
Member 14044492 7-Dec-18 14:01pm
   
thank you very much ...
can you write vb.net cod in timer event to work your sql code which send to me
MadMyche 7-Dec-18 14:12pm
   
I would rather not for a few reasons:
1. My opinion is that this is a database task, and that your application should call the procedure- which contains multiple SQL statements
2. The code will need to be modified to your database schema, which I do not know.
3. I feel you would be better off if you learned how to do Stored Procedures yourself. It may take more time to write them and incorporate them, but when written well they almost always outperform any application-side routine or ORM.
Member 14044492 8-Dec-18 4:01am
   
thnk you very much....
I have successfully solved the problem that I have encountered and I thank you for helping me solve it
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

If you are doing this via an SQL Server; the best option would probably be to utilize a Stored Procedure to do the logic and subsequent actions.

Without more specific information from you, I came up with a concept which will meet your needs.
It has a few additional items:
- Ability to use multiple sensors
- Tracks the individual time while using Table1
- Aggregates the sensor times so that you will have the range (time span) in table2.

Never a finished project... you do need to come up with an action plan for sub-zero readings. You could add a "Reading Count" column to the second table as well

CREATE TABLE dbo.Table1 (
	ndx         INT IDENTITY(1,1) NOT NULL,
	SensorID    INT,
	SensorTime  DATETIME,
	SensorValue INT,
	CONSTRAINT PK_Table1 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE dbo.Table2 (
	ndx             INT IDENTITY(1,1) NOT NULL,
	SensorID        INT,
	SensorTimeStart DATETIME,
	SensorTimeEnd   DATETIME,
	SensorValue      INT,
	CONSTRAINT PK_Table2 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.Sensor_LogValue (
	@SensorID     INT,
	@SensorValue  INT 
) AS 
BEGIN
	IF (@SensorValue > 0) BEGIN
		INSERT Table1 ( SensorID,  SensorValue, SensorTime)
		VALUES (       @SensorID, @SensorValue, GetDate() )
	END; ELSE IF (@SensorValue = 0) BEGIN
		
		INSERT Table2 (	SensorID,	SensorTimeStart,	SensorTimeEnd,		SensorValue)
		SELECT			SensorID,	Min(SensorTime),	Max(SensorTime),	Avg(SensorValue)
		FROM		Table1 
		WHERE	SensorID = @SensorID

		DELETE Table1	WHERE SensorID = @SensorID

	END; ELSE BEGIN
		-- ToDo: What happens if negative value passed in
		Print 'Some error occurred......'
	END
END
GO
  Permalink  
Comments
Member 14044492 8-Dec-18 5:38am
   
I have successfully solved the problem
Member 14044492 8-Dec-18 5:39am
   
c# code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace csharp_save_reading
{
public partial class Form1 : Form
{
cls_products prd=new cls_products();
public Form1()
{
InitializeComponent();
}

private void timer1_Tick(object sender, EventArgs e)
{
if (s1.IsOpen == false)
{

s1.PortName = "COM4";
s1.BaudRate = 9600;
s1.Open();
String reading;
reading = s1.ReadLine();
txt_read.Text = Convert.ToString(reading);

if (txt_read.Text != null)
{
double x;
x = Convert.ToDouble(txt_read.Text);
prd.add_product(10,x);

}



}

else
{
s1.Close();
}

}
}
}
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

CREATE TABLE dbo.Table1 (
	ndx         INT IDENTITY(1,1) NOT NULL,
	SensorID    INT,
	SensorTime  DATETIME,
	SensorValue Decimal,
	CONSTRAINT PK_Table1 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO




CREATE TABLE dbo.Table2 (
	ndx             INT IDENTITY(1,1) NOT NULL,
	SensorID        INT,
	SensorTimeStart DATETIME,
	SensorTimeEnd   DATETIME,
        period          Int,
	SensorValue     Decimal,
	CONSTRAINT PK_Table2 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.Sensor_LogValue (
@SensorID     INT,
	@SensorValue  Decimal 
) AS 
BEGIN
	IF (@SensorValue > 0) BEGIN
		INSERT Table1 ( SensorID,  SensorValue, SensorTime)
		VALUES (       @SensorID, @SensorValue, GetDate() )
	END; ELSE IF (@SensorValue = 0) BEGIN
		
		INSERT Table2 (	SensorID,	SensorTimeStart,	SensorTimeEnd , period,		SensorValue)
		SELECT			SensorID,	Min(SensorTime),	Max(SensorTime),DATEDIFF(s,Min(SensorTime),Max(SensorTime)),	Avg(SensorValue)
		FROM		Table1 
		WHERE	SensorID = @SensorID group by SensorID

		truncate table Table1

END; 
END
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.181218.1 | Last Updated 8 Dec 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100