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