How to define a parameter value when the value is not defined?

Question

I have a problem with this MySQL command:

cmdTemp = New MySqlCommand("SET @qty = " & Qty & "; update(tb_harvest) set actual = (case when @qty >= actual " & _
                           "then if(@qty := @qty - actual, 0, 0) when (@tmp := actual - @qty) " & _
                           "then if(@qty := 0, @tmp, @tmp) " & _
                           "else actual end), Status = (case when @qty >= actual then if(@qty := @qty - actual, 0, 0) " & _
                           "when (@tmp := actual - @qty) then if(@qty := 0, 1, 1) else 1 end) order by harvestid;", cn)

When I try to run in VB.NET (VS2008) I get the following error:

@Qty must be defined so do @tmp

However when I run this on MySQL(HeidiSQL) it doesn't have problem.

When I add into the New ConnectionString, Allow User Variables = true the error is:

Keyword not supported. Parameter name: allowuservariables

This is my ConnectionString which I put together using Connection Strings:

Server=localhost;Port=3306;Database=testing;Uid='test';Pwd='‌test';AllowUserVaria‌bles=True;

I am using MySQL Version 5.6.21

I would say to get rid of the `Set @qty` part of the query and then add a parameter to the `cmdTemp.Parameters` collection named `@qty`. — Chris Dunaway, Apr 17 '17 at 13:30
Please Help me Resolve this !, Even I'm already using `cmdTemp.Parameters` its still giving me and error @ChrisDunaway — Shiroze, Apr 18 '17 at 01:22
It's not clear what you are trying to do. What is the purpose of the assignments inside the `IF` functions? — Chris Dunaway, Apr 18 '17 at 13:45
what I try to Accomplish is when the button pressed, it run the code above. On database the qty value update each row from the first one until the Qty that inputed reach 0. Ex : `Qty = 500` `row 1 qty=300` `row 2 qty=400` if the code run `Row 1 qty = 0 and row 2 qty = 200` Clear ? @ChrisDunaway — Shiroze, Apr 18 '17 at 14:28
The question started from [link](http://stackoverflow.com/questions/43437614/update-next-row-if-current-updated-row-0) Then I get an error when tested on VB @Bugs. The question already updated too — Shiroze, Apr 18 '17 at 15:07
Did you have to use `?` for your parameters or was `@` OK? @Shiroze — Bugs, Apr 18 '17 at 18:42

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Connection Strings does specify that this is a valid connection:

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;AllowUserVariables=True;

Notice AllowUserVariables. This is how you have it set within your connection string. This however seems to be causing you grief although I can't see why as this is what is also stated in the documentation. Maybe it's version specific.

However try changing it to:

;Allow User Variables=True

This is how it would look in your connection string:

Server=localhost;Port=3306;Database=testing;Uid='test';Pwd='‌test';Allow User Varia‌bles=True;

I have done some looking around and found a few other sources that also set it as ;Allow User Variables=True.

This answer:

$connectionstring =  "Server=$Server;Port=$port;Database=$DataBase;Uid=$User;Pwd=$Password;allow zero datetime=yes;Allow User Variables=True"

This answer:

I found this blog, which tells, that with newer versions of .net Connector you have to add
;Allow User Variables=True

This answer:

It's a connection string option - "Allow User Variables=true"

When set to true, parameters are prefixed with '?'.

OP has confirmed that they did not have to use ?. They have continued to use @ in their query.

score 0 · Answer 2 · answered Apr 18 '17 at 14:18

It's not clear to me what you are trying to do. It seems like you can simplify your query since your if functions always return the same value whether they evaluate to true or false. I'm not very familiar with MySql, so I may have mis-understood what you are trying to accomplish.

update(tb_harvest) 
set actual = if(@qty >= actual, 0, actual - @qty), 
    Status = if(@qty >= actual, 0, 1)
order by harvestid;

Then just pass in @qty as a parameter.

it update all of the value, not check then update. @ChrisDunaway — Shiroze, Apr 18 '17 at 14:33

How to define a parameter value when the value is not defined?

2 Answers2