当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - SQL VARBINARY IN ASP.NET UPDATE

问题描述:

Possible Duplicate:

How handle Varbinary an asp.net update

Hi this is the following of my previous question.

The problem is when I'm tryng to update a varbinary filed by one listview in an aspx form.

Every time I retrieve the same error:

I dati di tipo string o binary verrebbero troncati. L'istruzione è stata interrotta. So string or binary would be truncated the command was interrupted.

I tougth that was problem of asp.net and I tried to do across server side with c# intercepting the event ItemInserting of the list view with following code:

RunSqlCommand is a class that i made on bottom the code:

 protected void ListView1_ItemUpdating(object sender, ListViewUpdateEventArgs e)

{

IOrderedDictionary NuoviValori = e.NewValues;

Label CodiceArticolo = (Label)ListView1.Items[e.ItemIndex].FindControl("CODICE_ARTICOLOLabel1");

string Descrizione = "";

string UM = "";

string Foto = "";

string TipoArt = "";

foreach (DictionaryEntry entry in NuoviValori)

{

switch (entry.Key.ToString())

{

case "DESCRIZIONE":

Descrizione = entry.Value.ToString();

break;

case "UM":

UM = entry.Value.ToString();

break;

case "FOTO":

if (entry.Value == null)

{

Foto = EncodeTo64("ADBC");

}

else

{

Foto = entry.Value.ToString();

}

break;

case "TIPO_ART":

TipoArt = entry.Value.ToString();

break;

}

}

e.Cancel = true;

string strCmd = "UPDATE TBL_ARTICOLI SET DESCRIZIONE = '" + Descrizione +"'," +

"UM = '" + UM +"'," +

"FOTO = @Foto," +

"TIPO_ART = '" + TipoArt + "' " +

"WHERE CODICE_ARTICOLO = '" + CodiceArticolo.Text + "'";

RunSqlCommand.Parametro parametro = new RunSqlCommand.Parametro();

parametro.Name = "Foto";

parametro.Tipo = SqlDbType.VarBinary;

parametro.Value = Convert.FromBase64String(Foto);

RunSqlCommand.Parametro[] Parametri = new RunSqlCommand.Parametro[1];

Parametri[0] = parametro;

string retCmd = new RunSqlCommand().RunSqlCmdParamDeltaSSL(strCmd, Parametri);

ListView1.EditIndex = -1;

}

static public string EncodeTo64(string toEncode)

{

byte[] toEncodeAsBytes

= System.Text.ASCIIEncoding.ASCII.GetBytes(toEncode);

string returnValue

= System.Convert.ToBase64String(toEncodeAsBytes);

return returnValue;

}

public struct Parametro

{

public SqlDbType Tipo;

public object Value;

public string Name;

}

public string RunSqlCmdParamDeltaSSL(string Command, Parametro[] Parameters)

{

int NumeroAffetto;

Connection ConnString = new Connection();

SqlConnection Conn = new SqlConnection();

Conn.ConnectionString = ConnString.ConnectDeltaSSL();

SqlCommand Comando = new SqlCommand(Command, Conn);

foreach (Parametro param in Parameters)

{

SqlParameter picparameter = new SqlParameter();

picparameter.SqlDbType = param.Tipo;

picparameter.ParameterName = param.Name;

picparameter.Value = param.Value;

Comando.Parameters.Add(picparameter);

}

Comando.CommandType = System.Data.CommandType.Text;

try

{

Comando.Connection.Open();

NumeroAffetto = Comando.ExecuteNonQuery();

return NumeroAffetto.ToString();

}

catch (Exception ex)

{

return ex.Message;

}

finally

{

Comando.Connection.Close();

}

}

But with this code is thesame everytime the same mistake.

I controlled the table but the all is correct.

Really I dont know what need to do more.

Thankyou

Piercarlo

网友答案:

Ok I found a resolution that is following: I made two function in sql server that convert varbinary in strinBase64 e viceversa in this way I can handle data like a string in asp.net:

    ALTER FUNCTION dbo.FUN_BASE64_TO_VARBINARY
    (
        @str VARCHAR(MAX)
    )
    RETURNS varbinary(max)
    AS
    BEGIN
    RETURN  cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)');
    END

    ALTER FUNCTION dbo.FUN_BINARY_TO_BASE64
    (
        @bin varbinary(MAX)
    )
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
        return cast(N'' as  xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(max)');
    END

and use it in the select and in the update commad as follow:

SelectCommand="SELECT CODICE_ARTICOLO, DESCRIZIONE, UM, dbo.FUN_BINARY_TO_BASE64(FOTO) AS FOTO, TIPO_ART FROM TBL_ARTICOLI
                       WHERE (SUBSTRING(CODICE_ARTICOLO, 1, CHARINDEX('*', CODICE_ARTICOLO) - 1) = @CATEGORIA_ARTICOLO)" 
UpdateCommand="UPDATE [TBL_ARTICOLI] SET [DESCRIZIONE] = @DESCRIZIONE, [UM] = @UM, [FOTO] = dbo.FUN_BASE64_TO_VARBINARY(@FOTO), [TIPO_ART] = @TIPO_ART WHERE [CODICE_ARTICOLO] = @CODICE_ARTICOLO">

The all work fine

Piercarlo

分享给朋友:
您可能感兴趣的文章:
随机阅读: