SQL update passing variable

Help Studio
, , , ,
1

Hello guys. I have a problem and I need your help.
I’m trying to update a certain value in SQL using a variable in the querry. The querry looks like this:

"UPDATE dbo.X_PREKES
Set PrekesStatusas = 'Užkelta į RRR'
Where KatalogoKodas = "+Kodas+""

And variable Kodas=CurrentRow.Item(“internal_notes”).ToString

Basically it reads X_PREKES table and updates the Column value of PrekesStatusas to “Užkelta į RRR”, where KatalogoKodas column value is the value from excel (For example it is ZSB111094LXX). But when I start it, i get an error:Run command: Invalid column name ‘ZSB111094LXX’
How to solve this?

2

Hi
Is the column name mentioned correctly
@Povilas_Jonikas

3

Hello, yes, I am confident it is. I tried this querry using SSMS, it looked like this:

UPDATE dbo.X_PREKES
Set PrekesStatusas = 'Užkelta į RRR'
Where KatalogoKodas = 'ZFD111409LXX'

And it worked perfectly

4

Fine
What error u r getting after running this
Can u share the full error message
@Povilas_Jonikas

5

While doing it in debug mode, i get this error:

Run command: Invalid column name ‘ZSB111094LXX’.

6

As i understand, the SQL querry takes the +kodas+ variable value as the column name, and not the “KatalogoKodas”. But why and how to solve it?

7

Instead of passing it as variable try passing that as a parameter like this

"UPDATE dbo.X_PREKES
Set PrekesStatusas = ‘Užkelta į RRR’
Where KatalogoKodas = @kodas "

Cheers @Povilas_Jonikas

1 Like
8

I tried it before. Configured everything like this:

image
image727×669 32.3 KB

But got this error: Run command: Must declare the scalar variable “@kodas”.

9

Hi @Povilas_Jonikas ,

Maybe you could also check with the below :

"UPDATE dbo.X_PREKES
Set PrekesStatusas = 'Užkelta į RRR'
Where KatalogoKodas = '"+Kodas+"'"

Just to recognise it as a value, we might need to enclose within Single Quotes.

1 Like
10

Yeah have parameter declared with a different name and pass the variable as value to it using a assign activity
Put this assign activity before to your update query activity @Povilas_Jonikas

1 Like
11

Ok, I have assign activity before the SQL update. Assing activity is Code=CurrentRow.Item("internal_notes").ToString

Then i have ExcecuteNonQuerry activity parameters with name=kodas, and value=Code but still got the same error:Run command: Must declare the scalar variable “@kodas”.

image
image1475×1014 82.3 KB

12

Hello, I’ll try this method in a bit, because one time I tried like this:

"UPDATE dbo.X_PREKES
Set PrekesStatusas = 'Užkelta į RRR'
Where KatalogoKodas = ""+Kodas+"""

And the whoole SQL table updated, not one value :smiley:

13

@Povilas_Jonikas ,

Do note that there are slight changes in the one suggested and the one provided by you.

However, It should only update if the condition matches.

1 Like
14

Ok, thanks a lot, ill try it in a bit :slight_smile:

15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.