SQL Server Parameter transfer

AadKabel

New Member
Joined
Oct 3, 2018
Messages
3
I am trying to update a record at SQL Server by using parameters.
The VBA-code does not initiate an error, but the files at SQL Server Table are not updatet with worksheet data.
Can anybody see what I am doing wrong?


Description = Range("E42").Value
Mitigation = Range("E51").Value
Supervision = Range("E60").Value

Dim cmd As New ADODB.Command
Dim conn As ADODB.Connection
Dim prm As ADODB.Parameter
Dim strConn As String

'Developping
Server_Name = "localhost"
Database_Name = "AIM"

strConn = "Provider=SQLNCLI11;" & "Server=" & Server_Name & _
";Database=" & Database_Name & _
";Integrated Security=SSPI;"

Set conn = New ADODB.Connection
conn.Open strConn
Set cmd = New ADODB.Command
cmd.CommandText = "UPDATE dram.Register " & _
"SET Description = Description, " & _
"Mitigation = Mitigation, " & _
"Supervision = Supervision " & _
"WHERE DRAM_ID = DRAM_ID"
cmd.CommandType = adCmdText
cmd.ActiveConnection = conn

Set prm = cmd.CreateParameter("DRAM_ID", adVarChar, adParamInput, 255, CaseId)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("Description", adVarChar, adParamInput, 255, Description)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("Mitigation", adVarChar, adParamInput, 255, Mitigation)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("Supervision", adVarChar, adParamInput, 255, Supervision)
cmd.Parameters.Append prm

cmd.Execute
conn.Close

Thanks.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,919
Office Version
  1. 365
Platform
  1. Windows
Have you tried manually running the query in SSMS or using some other method?

Does it work if you hard-code values rather than use parameters?
 

AadKabel

New Member
Joined
Oct 3, 2018
Messages
3
Thanks for your response.

Hard-code values remains equal as by using variables.

i haven't manually the query in SSMS and don't know how to do. Looked at it, but can't find it.
Do you have some guidance?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,030
Members
410,721
Latest member
adi772
Top