Geek asks and answers

Using a binary column as a comparison criterion

I am trying to update a value in MS Access DB (* .mdb) using OleDB in C #. Nothing unusual, except that I need to select a string of binary value VARBINARY(4).

Naively, I thought it UPDATE MYTABLE SET VALUE= 'new' WHERE BINVALUE=01020304;would work. But no. Alternative attempts:

UPDATE MYTABLE SET VALUE= 'new' WHERE BINVALUE='01020304';
UPDATE MYTABLE SET VALUE= 'new' WHERE BINVALUE='0x01020304';
UPDATE MYTABLE SET VALUE= 'new' WHERE BINVALUE=0x01020304;
UPDATE MYTABLE SET VALUE= 'new' WHERE BINVALUE=CAST('01020304' as VARBINARY);
UPDATE MYTABLE SET VALUE= 'new' WHERE BINVALUE=CAST(01020304 as VARBINARY);
UPDATE MYTABLE SET VALUE= 'new' WHERE BINVALUE=CAST('01020304' as VARBINARY(4));
UPDATE MYTABLE SET VALUE= 'new' WHERE BINVALUE=CAST(01020304 as VARBINARY(4));
UPDATE MYTABLE SET VALUE= 'new' WHERE BINVALUE=CONVERT(varbinary(4),'01020304');
UPDATE MYTABLE SET VALUE= 'new' WHERE CAST(BINVALUE as VARCHAR(MAX)) = CAST('01020304' as VARCHAR(MAX));
UPDATE MYTABLE SET VALUE= 'new' WHERE CAST(BINVALUE as VARCHAR(MAX)) = CAST(01020304 as VARCHAR(MAX));
UPDATE MYTABLE SET VALUE= 'new' WHERE CONVERT(VARCHAR(MAX), BINVALUE) = CONVERT(VARCHAR(MAX), '01020304');

Use CASTleads to syntax error exception: missing operator.

Use CONVERTleads to an exception:Undefined function 'CONVERT' in expression.

The number of rows affected is int resultalways zero. Usage UPDATE MYTABLE SET VALUE= 'new' WHERE VALUE= old;works, but sometimes it hits more than one line.

When I tried to find a way around the check, BINVALUEI noticed that you should not use Arabic commas (') around the values ​​in the WHERE clause. UPDATE MYTABLE SET VALUE= 'new' WHERE VALUE='old';NOT working while working UPDATE MYTABLE SET VALUE= 'new' WHERE VALUE=old;.

MS Query :

UPDATE MYTABLE SET VALUE='new' WHERE BINVALUE='01020304' , .

UPDATE MYTABLE SET VALUE='new' WHERE BINVALUE=01020304 " " BINVALUE = 01020304 ".

" BINVALUE ". .

, . , BINVALUE .

, , .

gloomy.penguin, , casting/matching WHERE. , / OleDB Access, CAST , CONVERT - . , CAST , SQL - .

, ?

, :

static String ToHexString(Byte[] buffer)
{
    String str;
    str = BitConverter.ToString(buffer).Replace("-", string.Empty);
    return (str);
}

...

String table = "MYTABLE";
String newvalue = "new";
Byte[] binvalue = { 1, 2, 3, 4 };

String providerStr= @"Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=C:\myDB.mdb";
connection = new OleDbConnection(providerStr);
connection.Open();

String cmd = @"UPDATE " + table + " SET VALUE= '" + newvalue + "'"
    + " WHERE BINVALUE= '" + ToHexString(binvalue) + "'"
    + ";";

OleDbCommand command = new OleDbCommand(cmd, connection);
int result = command.ExecuteNonQuery();

connection.Close();
+5
1

, . -, SQL, .

:

String cmd = @"UPDATE " + table + " SET VALUE= '" + newvalue + "'"
    + " WHERE BINVALUE= '" + ToHexString(binvalue) + "'"
    + ";";

OleDbCommand command = new OleDbCommand(cmd, connection);
int result = command.ExecuteNonQuery();

:

String cmd = @"UPDATE " + table + " SET VALUE= '" + newvalue + "'"
    + " WHERE BINVALUE= @binval" + ";";

OleDbCommand command = new OleDbCommand(cmd, connection);

OleDbParameter param = new OleDbParameter();
param.OleDbType = OleDbType.Binary;
param.ParameterName = "binval";
param.Value = binvalue;
command.Parameters.Add(param);

int result = command.ExecuteNonQuery();

, . , .

, #.

0

More articles:


All Articles