drbitboy
Lifetime Supporting Member
A few quick comments:
...
Dim cmd as ADODB.Command
...
I only show the INSERT above; for the UPDATES, the command text and parameter assignments would look something like this:
References:
- The [Dim Rs1 ...] statement can be removed
- The [Set Conn1 = Nothing] statement inside IF-ENDIF clauses can all be removed, and add one just before the final END statement. This is currently an error:
- If more than one of the Tag_Save_... or Tag_Updt_... .Values are 1, then when the second one runs, Conn1 will be Nothing and the [Conn1.ConnectionString = "..."] statement will throw an error.
- I did some more research, and VB/SQL has methods to do parameterized queries, so you don't have to fiddle around with ticks and ampersands. See the example below.
...
Dim cmd as ADODB.Command
...
IF Tag_....Value THEN
' Open connection
Conn1.ConnectionString = "dsn=..."
Conn1.Open
' Create command object
Set cmd = New ADODB.Command
cmd.ActiveConnection = Conn1
' Assign SQL statement to command object, with 8 parameter tokens (?s), which tokens will be replaced by parameter values
cmd.CommandText = "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 (?,?,?,?,?,?,?,?);"
cmd.CommandType = adCmdText
' Add parameter values; N.B. adDate and adTime may need to be adDbDate and adDbTime
cmd.Parameters.Append cmd.CreateParameter( , adDate, adParamInput, , Date)
cmd.Parameters.Append cmd.CreateParameter( , adTime, adParamInput, , Time)
cmd.Parameters.Append cmd.CreateParameter( , adVarChar, adParamInput, 12, Tag_Placa.Value)
cmd.Parameters.Append cmd.CreateParameter( , adInteger, adParamInput, , Tag_Cod_Coleta.Value)
cmd.Parameters.Append cmd.CreateParameter( , adInteger, adParamInput, , Tag_Silo_Destino.Value)
cmd.Parameters.Append cmd.CreateParameter( , adVarChar, adParamInput, 50, Tag_Nr_Lote_C.Value)
cmd.Parameters.Append cmd.CreateParameter( , adVarChar, adParamInput, 50, Tag_Nr_Lote_S.Value)
cmd.Parameters.Append cmd.CreateParameter( , adInteger, adParamInput, , Tag_Nr_RCP.Value)
' Execute command with parameters replacing ?s in statement
cmd.execute
' Cleanup: delete command; close, but do not delete, connection, so it can be used again
Set cmd = Nothing
Conn1.Close
Conn1.ConnectionString = "dsn=..."
Conn1.Open
' Create command object
Set cmd = New ADODB.Command
cmd.ActiveConnection = Conn1
' Assign SQL statement to command object, with 8 parameter tokens (?s), which tokens will be replaced by parameter values
cmd.CommandText = "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 (?,?,?,?,?,?,?,?);"
cmd.CommandType = adCmdText
' Add parameter values; N.B. adDate and adTime may need to be adDbDate and adDbTime
cmd.Parameters.Append cmd.CreateParameter( , adDate, adParamInput, , Date)
cmd.Parameters.Append cmd.CreateParameter( , adTime, adParamInput, , Time)
cmd.Parameters.Append cmd.CreateParameter( , adVarChar, adParamInput, 12, Tag_Placa.Value)
cmd.Parameters.Append cmd.CreateParameter( , adInteger, adParamInput, , Tag_Cod_Coleta.Value)
cmd.Parameters.Append cmd.CreateParameter( , adInteger, adParamInput, , Tag_Silo_Destino.Value)
cmd.Parameters.Append cmd.CreateParameter( , adVarChar, adParamInput, 50, Tag_Nr_Lote_C.Value)
cmd.Parameters.Append cmd.CreateParameter( , adVarChar, adParamInput, 50, Tag_Nr_Lote_S.Value)
cmd.Parameters.Append cmd.CreateParameter( , adInteger, adParamInput, , Tag_Nr_RCP.Value)
' Execute command with parameters replacing ?s in statement
cmd.execute
' Cleanup: delete command; close, but do not delete, connection, so it can be used again
Set cmd = Nothing
Conn1.Close
END IF
' Delete connection variable after all the IF-ENDIFs
Set Conn1 = Nothing
END SUB' Delete connection variable after all the IF-ENDIFs
Set Conn1 = Nothing
I only show the INSERT above; for the UPDATES, the command text and parameter assignments would look something like this:
IF ... THEN
This approach also has the advantages of not being open to SQL injection security attacks, and of being more clear and easier to read, even if it is more verbose...
cmd.CommandText = "UPDATE TBL_RCP_SETPOINT SET TOTAL_LITROS_RECEBIDO=?, DT_FIM_RECEBIMENTO=? WHERE DT_FIM_RECEBIMENTO IS NULL AND NR_RECEPCAO=1;"
cmd.Parameters.Append cmd.CreateParameter(,adDate, adParamInput, , Date)
cmd.Parameters.Append cmd.CreateParameter(, adTime, adParamInput, , Time)
...
END IFcmd.CommandText = "UPDATE TBL_RCP_SETPOINT SET TOTAL_LITROS_RECEBIDO=?, DT_FIM_RECEBIMENTO=? WHERE DT_FIM_RECEBIMENTO IS NULL AND NR_RECEPCAO=1;"
cmd.Parameters.Append cmd.CreateParameter(,adDate, adParamInput, , Date)
cmd.Parameters.Append cmd.CreateParameter(, adTime, adParamInput, , Time)
...
References:
- https://stackoverflow.com/questions...and-end-date-parameter-to-sql-using-excel-vba
- https://stackoverflow.com/questions/3975042/how-to-deal-with-single-quote-in-word-vba-sql-query
- https://docs.microsoft.com/en-us/sq...eter-methods-example-vb?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sq...ateparameter-method-ado?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/datatypeenum?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sq.../parameterdirectionenum?view=sql-server-ver15
Last edited:
