How to determine the value of a parameter when the value is not defined?
I got 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 like this:@tmp
However, when I run it on MySQL (HeidiSQL) it has no problem.
When I add in New ConnectionString
, Allow User Variables = true
error:
The keyword is not supported. Parameter name: allowuservariables
This is mine ConnectionString
, which I put together using Connection Strings :
Server=localhost;Port=3306;Database=testing;Uid='test';Pwd='test';AllowUserVariables=True;
I am using MySQL version 5.6.21
source to share
The connection string indicates that this is a valid connection:
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;AllowUserVariables=True;
Pay attention to AllowUserVariables
. This is how you set it up in the connection string. This seems to make you sad, although I don't understand why this is the way the documentation states . Perhaps this is a specific version.
However, try changing it to:
;Allow User Variables=True
This is how it will look in your connection string:
Server=localhost;Port=3306;Database=testing;Uid='test';Pwd='test';Allow User Variables=True;
I looked around a bit and found several other sources that also set it as ;Allow User Variables=True
.
$connectionstring = "Server=$Server;Port=$port;Database=$DataBase;Uid=$User;Pwd=$Password;allow zero datetime=yes;Allow User Variables=True"
I found this blog which says that with newer versions of the .net Connector you should add
;Allow User Variables=True
This is the connection string string - "Allow custom variables = true"
If set to true, parameters are prefixed with "?"
The OP confirmed that they didn't need to use ?
. They continued to use @
in their request.
source to share
It is not clear to me what you are trying to do. It looks like you can simplify your query, as your functions if
always return the same value, regardless of whether they evaluate to true or false. I'm not very familiar with MySql, so I may have misunderstood 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.
source to share