Insert/Update data into tables in SQL Server Express via FTView SE

Hello everyone again
Now I'm dificullty to insertb in Database. When I put a tag in values the tag go to comment and a error shows. If I show dataString via MsgBox the tag value is showed in a popup but in inside insert code not.

WhatsApp Image 2021-02-26 at 10.36.57.jpeg
 
1) You should delete that post, or at lease fuzz out the password in the image.


2) Then I would suggest doing


Code:
MsgBox(SQLStr)


before the Conn1.Open, and posting that result.
 
Can you open the table in SQLExpress and show the table structure i.e. the fields & type, post a screenshot . like this so I can re-create it & see if I can populate it.
One other thing I noticed is there are no spaces between " & etc. I believe there has to be spaces or it takes the variable including the & so it should be "'" & datastring & "'"

Structure.jpg
 
Last edited:
Here is a simple test I did, was the error in the string try comment it out and comment out the execute and just check it opens & closes the database if not then you probably do not have the references for MS ADO etc. in the project.

Here is my test for one variable, notice the spaces between the " and the & and the variable.

SQL.png SQL Table.png
 
Like I said previously, SQL strings have to be exact or they often fail.
another one to watch out for is if the length of the string is very long and you want to put it on two lines you have to use the _ character.
 
Hi,
Insert in table is function fine, but I can't update the table. My code is below

Private Sub Display_Activate()

Dim dataString As String
Dim SQLStr As String
Dim Rs1 As New ADODB.Recordset
Dim Conn1 As New ADODB.Connection
Dim Ogroup As TagGroup
Dim Tag_Placa, Tag_Cod_Coleta, Tag_Silo_Destino, Tag_Nr_Lote_C, Tag_Nr_Lote_S, Tag_Tot_Litros, Nr_RCP, Tag_Save_RCP, Tag_Updt_RCP_1, Tag_Updt_RCP_2, Tag_Updt_RCP_3 As Tag

Set Ogroup = Application.CreateTagGroup(A, 1000)
Ogroup.Add "{[PLC_BETANIA]RCP.PLACA}"
Ogroup.Add "{[PLC_BETANIA]RCP.COD_COLETA}"
Ogroup.Add "{[PLC_BETANIA]RCP.SILO_DESTINO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_LOTE_CAMINHAO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_LOTE_SILO}"
Ogroup.Add "{[PLC_BETANIA]RCP.TOTAL_LITROS_RECEBIDO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_RCP}"
Ogroup.Add "{[PLC_BETANIA]Save_RCP}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_1}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_2}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_3}"

Set Tag_Placa = Ogroup.Item("{[PLC_BETANIA]RCP.PLACA}")
Set Tag_Cod_Coleta = Ogroup.Item("{[PLC_BETANIA]RCP.COD_COLETA}")
Set Tag_Silo_Destino = Ogroup.Item("{[PLC_BETANIA]RCP.SILO_DESTINO}")
Set Tag_Nr_Lote_C = Ogroup.Item("{[PLC_BETANIA]RCP.NR_LOTE_CAMINHAO}")
Set Tag_Nr_Lote_S = Ogroup.Item("{[PLC_BETANIA]RCP.NR_LOTE_SILO}")
Set Tag_Tot_Litros = Ogroup.Item("{[PLC_BETANIA]RCP.TOTAL_LITROS_RECEBIDO}")
Set Tag_Nr_RCP = Ogroup.Item("{[PLC_BETANIA]RCP.NR_RCP}")
Set Tag_Save_RCP = Ogroup.Item("{[PLC_BETANIA]Save_RCP}")
Set Tag_Updt_RCP_1 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_1}")
Set Tag_Updt_RCP_2 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_2}")
Set Tag_Updt_RCP_3 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_3}")

If Tag_Save_RCP.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
dataString = "'" & Date & "','" & Time & "','" & Tag_Placa.Value & "','" & Tag_Cod_Coleta.Value & "','" & Tag_Silo_Destino.Value & "','" & Tag_Nr_Lote_C.Value & "','" & Tag_Nr_Lote_S.Value & "','" & Tag_Nr_RCP.Value & "'"
SQLStr = " Insert into TBL_RCP_SETPOINT (DT_INICIO_RECEBIMENTO, HR_INICIO_RECEBIMENTO, PLACA, COD_COLETA, SILO_DESTINO, NR_LOTE_CAMINHAO, NR_LOTE_SILO, NR_RECEPCAO) values (" & dataString & ");"
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

If Tag_Updt_RCP_1.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
dataString = "'" & Date & "','" & Time & "','" & Tag_Placa.Value & "','" & Tag_Cod_Coleta.Value & "','" & Tag_Silo_Destino.Value & "','" & Tag_Nr_Lote_C.Value & "','" & Tag_Nr_Lote_S.Value & "','" & Tag_Nr_RCP.Value & "'"
SQLStr = " Update TBL_RCP_SETPOINT set (TOTAL_LITROS_RECEBIDO = ""'" & Tag_Tot_Litros.Value & "', DT_FIM_RECEBIMENTO = ""'" & Date & "', HR_FIM_RECEBIMENTO = ""'" & Time & ") where DT_FIM_RECEBIMENTO = '' and NR_RECEPCAO = 1;"
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If




When Tag_Updt_RCP_1 = 1 Show Run-time Error -2147217900 (80040e14) - Automation Error
 
Last edited:
First of all I can see a couple of brackets in your query.
Also what is the and for I think it is all to do with text errors & formatting.

SQLStr = " Update TBL_RCP_SETPOINT set (TOTAL_LITROS_RECEBIDO = ""'" & Tag_Tot_Litros.Value & "', DT_FIM_RECEBIMENTO = ""'" & Date & "', HR_FIM_RECEBIMENTO = ""'" & Time & ") where DT_FIM_RECEBIMENTO = '' and NR_RECEPCAO = 1;"

You do not need the datastring above it as you are not using it,
there are some other errors "" & Tag_Tot_Litros.Value & " should be " & Tag_Tot_Litros.Value & " (no need for two " marks at the beginning you also have this at the Date as well)

This is one of mine & it does not need the brackets

SQLStr = "UPDATE Cooker_Batch SET " & UpTime & " =" & Times & ", " & UpWeight & " =" & Weight & ", " & UpReqWeight & " = " & ReqWeight & ", " & UpTemp & " = " & Temp & ", " & UpReqTemp & " = " & ReqTemp & " Where BatchID =" & BatchIDStr & "
 
Last edited:
If Tag_Updt_RCP_1.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
'SQLStr = " Update TBL_RCP_SETPOINT set (TOTAL_LITROS_RECEBIDO = ""'" & Tag_Tot_Litros.Value & "', DT_FIM_RECEBIMENTO = ""'" & Date & "', HR_FIM_RECEBIMENTO = ""'" & Time & ") where DT_FIM_RECEBIMENTO = '' and NR_RECEPCAO = 1;"
Conn1.Open
Conn1.Execute "Update TBL_RCP_SETPOINT set TOTAL_LITROS_RECEBIDO = "" & Tag_Tot_Litros.Value & ", DT_FIM_RECEBIMENTO = " & Date & ", HR_FIM_RECEBIMENTO = " & Time & " where DT_FIM_RECEBIMENTO = '' and NR_RECEPCAO = 1;"
Conn1.Close
Set Conn1 = Nothing
End If

I can't get this update to work. The capital letters are the columns in the database TOTAL_LITROS - Integer / DT_FIM_RECEBIMENTO - Date / HR_FIM_RECEBIMENTO - Time .... These columns must be updated according to where the DT_FIM_RECEBIMENTO must be null (blank) and the NR_RECEPCAO = 1 ( integer) .... Only errors even without using the DataString SQLStr
 
"Update TBL_RCP_SETPOINT set TOTAL_LITROS_RECEBIDO = "" & Tag_Tot_Litros.Value & ", DT_FIM_RECEBIMENTO = " & Date & ", HR_FIM_RECEBIMENTO = " & Time & " where DT_FIM_RECEBIMENTO = '' and NR_RECEPCAO = 1;"

To start with, remove the quotes in red between TOTAL_LITROS_RECEBIDO and Tag_Tot_Litros.Value. You only need one set here. Next, you need to look at the end of the string, it does not make any sense.

" where DT_FIM_RECEBIMENTO = '' and NR_RECEPCAO = 1;"

It should look something like this: " where DT_FIM_RECEBIMENTO = " & SOME_TAG & " and NR_RECEPCAO = 1;"

I would recommend you make those changes in your SQLStr, uncomment it, and then go back to Conn1.Execute SQLStr. If nothing else it will make that slightly more readable.

Take your time reading your code. Make sure all of your punctuation is correct. SQL queries in VB can be very difficult to read when anybody concatenates this many times. It is easy to get lost in the sea of double quotes and ampersands. Good luck.
 
I agree with Mikey, one other thing to try is start with a small bit of the SQL string i.e. instead of populating all the fields just start with one, like update the total litres, then build on it.

QUOTE: These columns must be updated according to where the DT_FIM_RECEBIMENTO must be null (blank) and the NR_RECEPCAO = 1 ( integer)

Also your saying DT_FIM_RECEBIMENTO must be a null but you have not added the check for a null but have checked for NR_RECEPCAO = 1 for example

(caveat not sure how you check for a NULL at this time)
But I believe it is
DT_FIM_RECEBIMENTO IS NULL AND NR_RECEPCAO = 1
 
You don't say if the error occurs when running the code, or when compiling the code so the code never runs. If the latter then, as noted by @MikeyN, one problem is the double-quotes ("") between [...TOTAL_LITROS_RECEBIDO =] and [& Tag_Tot_Litros.Value ...]

Other tips:

1) Go back to the SqlStr = "..."
1.1) Use MsgBox(SqlStr), so you can see what it looks like.

2) To prevent our eyes and yours from crossing trying to parse the string:
2.1) When using ticks (single quotes) and other delimiters, use variables
2.2) Use continuation lines (end the previous line with underscore, [_]) so the phrases in the statement string are grouped in the program, and you can look at them one at a time and find errors more easily.
2.3) Use extra spaces on similar lines to make it easier to read
2.4) Use blank continuation lines (only an underscore) to group sections of the statement (e.g. UPDATE, SET, WHERE, below)

Code:
tick= "[COLOR=Red][B]'[/B][/COLOR]"
empty = tick & tick
comma = "[COLOR=red][B],[/B][/COLOR]"
space = " "
semic = "[COLOR=red][B];[/B][/COLOR]"
comsp = comma & space

''' N.B. I put ticks around all fields here as an example
''' N.B. I put double ticks into variable [empty] instead of IS NULL to
'''      further make the point, rather than ["IS NULL"], as @parky says,
'''      which is the correct way to test for a null field in a record.
 
SqlStr  = "Update TBL_RCP_SETPOINT"[COLOR=Blue][B] _[/B][/COLOR]
[COLOR=Blue][B][COLOR=Blue][B]_[/B][/COLOR][/B][/COLOR]
& space & "SET"
& space & "TOTAL_LITROS_RECEBIDO = " & tick & Tag_Tot_Litros.Value & tick[COLOR=Blue][B] _[/B][/COLOR]
& comsp & "DT_FIM_RECEBIMENTO = "    & tick & Date                 & tick[COLOR=Blue][B] _[/B][/COLOR]
& comsp & "HR_FIM_RECEBIMENTO = "    & tick & Time                 & tick[COLOR=Blue][B] _[/B][/COLOR]
[COLOR=Blue][B][COLOR=Blue][B]_[/B][/COLOR][/B][/COLOR]
& space & "WHERE" [COLOR=Blue]_[/COLOR]
& space & "DT_FIM_RECEBIMENTO = " & empty [COLOR=Blue]_[/COLOR]
& space &   "AND"[COLOR=Blue][B] _[/B][/COLOR]
& space & "NR_RECEPCAO = 1"[COLOR=Blue][B] _[/B][/COLOR]
& semic

IF Debugging THEN
   MsgBox(SqlStr)
END IF
Starting the continuation lines with ampersands also makes it easier to insert new phrases, or reorder phrases into the string.

Final note: using ampersands [&} to insert variables' values into strings is a security vulnerability, because it enables SQL injection attack cf. Little Bobby Tables. However, if you have complete control over the variables' values (e.g. they come from the program or the PLC or the database, and they do not come from an HMI), then you should be okay.
 
Last edited:
Thanks a lot....Code function ok but don't save in database....I guess that TOTAL_LITROS_RECEBIDO is a integer field but in update code the value it's in quotes (')


The code stay:
tick = "'"
aaaEmpty = tick & tick
comma = ","
aSpace = " "
semic = ";"
comsp = comma & aSpace


If Tag_Updt_RCP_1.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
SQLStr = "Update TBL_RCP_SETPOINT" & aSpace & "SET" & aSpace & "TOTAL_LITROS_RECEBIDO = " & tick & Tag_Tot_Litros.Value & tick _
& comsp & "DT_FIM_RECEBIMENTO = " & tick & Date & tick _
& comsp & "HR_FIM_RECEBIMENTO = " & tick & Time & tick _
& aSpace & "WHERE" _
& aSpace & "DT_FIM_RECEBIMENTO = " & aaaEmpty _
& aSpace & "AND" _
& aSpace & "NR_RECEPCAO = 1" _
& semic

'If Debugging Then
MsgBox (SQLStr)
'End If
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

code_ok.jpg BD.jpg
 
Last edited:
I think the "EMPTY" bit may be the problem

DT_FIM_RECEBIMENTO = " & aaaEmpty _
I have never heard of using this it is normally
DT_FIM_RECEBIMENTO IS NULL
However, I tried to recreate this but I found that the "DATE" & "TIME" ones did not work as passing them, had to create them as text, for example I tried DATE
I.e. Format (date, "YYYY-MM-DD")
This did not work
I think it is not trying to populate the fields because of the check for Empty, remove this to start with but I think then it will fail as I believe the DATE & TIME ones will fail.
Can you post a screenshot of the table layout i.e. the fields & their data types
 

Similar Topics

I'm new to ST so go easy. Studio 5000 L33ER PLC I'm trying to take 2 strings, merge them into 1 string. with a space between the 2 strings...
Replies
3
Views
2,232
Hello everyone. Currently I am using Eplan p8 and faced (while creating a panel) with a challenge in finding a device called DIN mounting rail...
Replies
10
Views
3,277
Hello guys, I am working with a CompactLogix, which communicates over Ethernet/IP with a pin marker. Basically, The PLC sends the texts to be...
Replies
6
Views
1,346
Hi, I have some remote IO connected to my S7-317-2 PN/DP system. I have 1 particular profinet remote IO which is not important to the system, and...
Replies
9
Views
2,958
I am using VbScript in Indusoft to insert data into a MySql database. Immediately after inserting a row, I need to know what that row number is...
Replies
24
Views
5,225
Back
Top Bottom