1

I've created a project in WINCC where i create table(examples are at bottom) and then put values of temperature transmiter in it, in cycles of one second. My problem is that after some time new data doesn't go on bottom in table, it goes randomly in some spot and starts writing there. It is not overwriting it just start inserting randomly, and after while it goes on bottom and randomly then again etc...

Here is my code for creating table:

Sub Create_new_table ()
Dim conn, rst, SQL_Table, name

On Error Resume Next

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
name =Year(Date) & "_" & Month(Date) & "_" & Day(Date)
'Open data source - Datenquelle öffnen
conn.Open "Provider=MSDASQL;DSN=Database" 'DSN= Name of the ODBC database - DSN= Name der ODBC-Datenbank

'Error routine - Fehlerroutine
If Err.Number <> 0 Then
    ShowSystemAlarm "Error #" & Err.Number & " " & Err.Description
    Err.Clear
    Set conn = Nothing
    Exit Sub
End If


' FORMING TABLE
SQL_Table = "CREATE TABLE Paster_TT43_" & name & "(" &_
        "Signal NVARCHAR(30) ," &_
        "Date NVARCHAR(30) ," &_
        "Time NVARCHAR(30) ," &_
        "Value NVARCHAR(30)  )"
Set rst = conn.Execute(SQL_Table)

' There are more tables to create there is one for example
'Error routine - Fehlerroutine 
If Err.Number <> 0 Then
    ShowSystemAlarm "Error #" & Err.Number & " " & Err.Description
    Err.Clear
    'Close data source - Datenquelle schließen
    conn.close
    Set conn = Nothing
    Set rst = Nothing
    Exit Sub
End If

'Close data source - Datenquelle schließen
conn.close

Set rst = Nothing
Set conn = Nothing

End Sub

That was example for creating one table Now example for adding new elements into it, it goes every second

Sub Add_New_Element()
Dim conn, conn2, rst, SQL_Table,SQL_Table2, name, rssql, rs, insertsql, Date, Time

name =Year(Date) & "_" & Month(Date) & "_" & Day(Date)
Date = Day(Date) & "_" & Month(Date) & "_" & Year(Date) 
Time = Hour(Time) & ":" & Minute(Time) & ":" & Second(Time)

On Error Resume Next

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=MSDASQL;Initial Catalog=BazaPodataka;DSN=Database"
If Err.Number <> 0 Then
    ShowSystemAlarm "Error #" & Err.Number & " " & Err.Description
    Err.Clear
    Set conn = Nothing
    Exit Sub
End If

SQL_Table = "INSERT INTO Paster_TT43_" & name & "(Signal, Date, Time, Value) VALUES ('" & SmartTags("15_Analog_input_TT43.Name") & "' , '" & datum & "' , ' " & vreme & "' , ' " & SmartTags("15_Analog_input_TT43.Scaled_Signal") &  " ')"
Set rst = conn.Execute(SQL_Table)

'more signals after this etc..
conn.close
Set rst = Nothing
Set conn = Nothing

End Sub

Thank You

Milos Buric
  • 11
  • 1
  • How are you checking the values in the table? You could order by date, time to see them in order. – Flakes Nov 24 '20 at 04:10
  • Don't mistake the visual output of the data/query for the actual order in which it's stored in a SQL database. Oversimplified: SQL doesn't store (new) rows in any particular order. Instead, new data gets stored "where it fits". If you want the results of a query to be sorted in a particular order, SQL provides the necessary means with the `ORDER BY` and/or `GROUP BY` clause. – Hel O'Ween Nov 24 '20 at 13:46

0 Answers0