Converting bytes to string

Hi guys,

I'm currently trying to insert image into my SQL db. I have tried a number of methods that were posted online, and so farwith no luck.

My current code reads:

Dim conn As New Data.SqlClient.SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("MainDBConnection").ToString
conn.Open()

Dim cmd As New Data.SqlClient.SqlCommand("SP_SAVEImage", conn)

cmd.CommandType = Data.CommandType.StoredProcedure

Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName

Dim sImageType As New Data.SqlClient.SqlParameter("@.sImageType", Data.SqlDbType.VarChar, 50)
sImageType.Value = fileType

Dim sImageData As New Data.SqlClient.SqlParameter("@.sImageData", Data.SqlDbType.Image, uploadedFile.Length)
sImageData.Value = uploadedFile

cmd.Parameters.Add(sImageName)
cmd.Parameters.Add(sImageType)
cmd.Parameters.Add(sImageData)

Dim reader1 As Data.SqlClient.SqlDataReader

reader1 = cmd.ExecuteReader

Runningthrough debug, everything runs up until the last line, where an erroris caught saying : Failed to convert parameter value from aSqlParameter to a String

I reckon it's to do with the input sImageData being input as a byte array - but I can't seem to find a way around it.

Any help greatly appreciated!!

In http://www.codeproject.com/useritems/images_in_sql_server.asp
private void GuardarImagen(byte[] matriz)
{
this.cmd.CommandText = "insert into tabla(DESCRIPCION, IMAGEN) " +
"VALUES(@.DESCRIPCION, @.IMAGEN)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("Descripcion", this.NombreDeArchivoCorto).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("Imagen", matriz).SqlDbType = SqlDbType.Image;
cmd.ExecuteNonQuery();
}

suggests that yours should be
Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName
Dim sImageType As New Data.SqlClient.SqlParameter("@.sImageType", Data.SqlDbType.VarChar, 50)
sImageType.Value = fileType
Dim sImageData As New Data.SqlClient.SqlParameter("@.sImageData", Data.SqlDbType.Image)
sImageData.Value = uploadedFile

If you are using SQL 2005
cmd.Parameters.AddWithValue("sImageName", sImageName).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("sImageType", sImageType).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("sImageData", uploadedFile).SqlDbType = SqlDbType.Image;

sqlsql

Converting bytes [] to an SQL CE 3 image type to store

Hi,

I was wondering if anyone knows how to convert an array of bytes to an SQL CE 3 image type and vice versa.

I am using the SDF Signature control and I would like to store the signature as an Image. It needs to be an image so it can be synced with a desktop access 2003 database.

Cheers

Simon

I have got around this by changing the Image type to nText then converted the byte array to a base64 string and this works a treat.

Cheers

Simon.

|||

Better way to do it would be to create memory stream from byte array from SQL and pass it to bitmap constructor:

Bitmap bmp = new Bitmap(new MemoryStream(byteArrayFromSqlCe));

Converting Blob fields to Text on a Report

On SQL Reporting Services, Blobs (type = image) do not even get exposed
to the report. Note: I am using blobs since I need virtually
unlimited text. So, to get the data out of blobs, I normally use ADO
methods of getchunk and actualsize. I tried creating a class with the
logic that I needed. I could pass it SQL and it would return the value
of the blob as a text string. Then I created the following Custom Code
in the report:
Public Function BlobText(strColumn$, strTaskID$) As String
dim t as object
t = createobject("MyReportClass.Functions")
BlobText = t.blob2text("MySQLServer","select " & strColumn & _
" from SQLDatabase..task where ID = '" & strTaskID & "'")
End Function
When I preview the report it works perfectly. When I deploy the
report, the textbox that calls the method puts "#Error" on the report.
There does not seem to be any meaningful log to help.
Anyone have any guesses?
Thanks,
SteveHi Steve,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to write
embedded code in the reporting services. If I have misunderstood your
concern, please feel free to point it out.
Based on my knowledge, you are recommanded to read the article below
Writing Custom Code in SQL Server Reporting Services
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
Embedded Code In Reporting Services
http://odetocode.com/Articles/130.aspx
If this still does not resolve your issue, would you please generate a
sample rdl file with your function based on AdventureWorks database and
send it to me? my direct email address is v-mingqc@.online.microsoft.com
(remember remove "online" before you click SEND as "online" is only
prepared for SPAM), you may send the file to me directly and I will keep
secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Lost Customer, Close: Not Resolved

Converting Binary Image to Readable Text

I have a table that contains the following two columns:

BITS (image(16))
BIT_LENGTH (int(4))

When I look at the table, I see "OLE Object" in the BITS column. What
syntax should I use in a SELECT statement to convert the binary image
info contained in "BITS" into simple text that I can read? What role
does the BIT_LENGTH field play?An image column can store any binary data, so you need to know what the
data represents before you can display it - it could be a Word
document, a PDF, an MP3 etc. Do you already know exactly what the
column is storing ('text' is the normal data type for large amounts of
text data)? As for BIT_LENGTH, it could mean anything, depending on the
data model - there's no need for an image column to have an associated
length column with it.

It sounds a little like you've just taken over a database from someone
else? If so, you might want to review this chapter from the SQL2000
Resource Kit, which provides more detailed information about storing
and retrieving BLOBs:

http://www.microsoft.com/technet/pr...art3/c1161.mspx

Simon

converting binary data to another data type

I have a client application written in C++ to takes an array of doubles and
stores it into a SQL Server 2000 database as an image data type.
We just upgraded to Visual Studio 2005 and SQL Server 2005.
Can the Reporting Services take this image data and convert it to an array
of doubles so that it can be displayed using Reporting Services?
Thanks,
GloriaGloria (Gloria@.discussions.microsoft.com) writes:
> I have a client application written in C++ to takes an array of doubles
> and stores it into a SQL Server 2000 database as an image data type.
> We just upgraded to Visual Studio 2005 and SQL Server 2005.
> Can the Reporting Services take this image data and convert it to an array
> of doubles so that it can be displayed using Reporting Services?
I don't know Reporting Services, so I canot answer the question with any
certainty, but my gut feeling is that you would have to call some piece
of code to unpack that array. Tip: there is a Reporting Services newsgroup,
microsoft.public.sqlserver.reportingsvcs.
The main reason I post, is that I can't refrain from making the comment
table design appears a bit unorthodox to me. Or to put it more bluntly, a
serious violation of first normal form since it includs a repearing
group. The normal way of storing the data would be have a subtable,
and store one float value on each row.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

convert images to hexadecimal

Hello guys,

Have any one tried to convert an image to a hexadecimal string, and saving it to sql server?

Thanks..

You might find base64 strings a bit more compact. Try this for starters. It only takes TWO lines to do the work!

<%@.PageLanguage="C#" %>

<%@.ImportNamespace="System.IO" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

protectedvoid LinkButton1_Click(object sender,EventArgs e)

{

using (StreamReader sr =newStreamReader(MapPath("TestPicture.JPG")))

{

BinaryReader br =newBinaryReader(sr.BaseStream);

byte[] data = br.ReadBytes((int)br.BaseStream.Length);

string dataToSave =Convert.ToBase64String(data);

// show it

TextBox1.Text = dataToSave;

}

}

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:LinkButtonID="LinkButton1"runat="server"OnClick="LinkButton1_Click">GetPictureAsText</asp:LinkButton></div>

<asp:TextBoxID="TextBox1"runat="server"TextMode="MultiLine"Width="100%"Height="24em"></asp:TextBox>

</form>

</body>

</html>

|||

Thanks for the code. It worked fine. But I want to convert it to hexadecimal string. Its a requirement asked by the client...

Thanks for the help

|||

Try this

TextBox1.Text =BitConverter.ToString(data).Replace("-","");

Convert image to hexadecimal

I want to export data from my tables by generating insert statements,
including data of type image. To avoid having to use textcopy.exe or
textptr, I want to have the image data part of the insert-statements by
converting the binary image data to hexadecimal strings. Also, the
image data is larger then 8000 so a simple convert won't work. How do I
do the conversion from image to hex?As part of the DB Ghost evaluation there is a free scipter component for
scripting databases including data into insert statements. It handles image
and binary data by converting to hexidecimal and has a COM interface which
you can use and distribute freely.
http://www.dbghost.com
"Jacques Roumimper" wrote:

> I want to export data from my tables by generating insert statements,
> including data of type image. To avoid having to use textcopy.exe or
> textptr, I want to have the image data part of the insert-statements by
> converting the binary image data to hexadecimal strings. Also, the
> image data is larger then 8000 so a simple convert won't work. How do I
> do the conversion from image to hex?
>|||If you can use c# do this:
public static string BinToString(Byte[] binValue)
{
char[] hexCode =
{'0','1','2','3','4','5','6','7','8','9'
,'A','B','C','D','E','F'};
StringBuilder sb = new StringBuilder();
foreach (byte b in binValue)
{
sb.Append(Convert.ToString(hexCode[b >> 4]));
sb.Append(Convert.ToString(hexCode[b & 0xF]));
}
return "0x" + sb.ToString();
}
I am not 100% sure if web data administrator
http://www.microsoft.com/downloads/...&displaylang=en
can script image fields. Worth a try!
Mathias|||I need to do this in SQL, what would be the Transact SQL equivalent of
your C# code?

Convert image datatype to varchar

I have a table Table1 which has a Col called "Msg" datatype image<binary>. Msg alreay has the plain text or RTF text as a image datatype (binary)
If I execute the following query "Select Msg from Table1 where id =3" then this query is returning the following ASCII/Binary data.
Msg = "0x7B5C727466315C616E73695C616E7369637067313235325C64656666305C6465666C616E673130333
37B5C666F6E7474626C7B5C66305C6673776973735C66707271325C6663686172736574302041726961
6C3B7D7B5C66315C6673776973735C66707271325C666368617273657430204D6963726F736F667420"
Can any body tell me how can I convert the above binary data to plain text from my query?
Thanks for any reply.
There is no Conversion of Image to Varchar but you can convert Image to Varbinary with limitations, the text below is from the BOL( books online).
Automatic data type conversion is not supported for the text andimagedata types. You can explicitly converttextdata to character data, andimage data tobinaryorvarbinary, but the maximum length is 8000. If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to anint), SQLServer generates an error message.
Hope this helps.

Convert Image Datatype To Int

Hi,
I have a table which has a field defined as Image. I guess it was defined
that way to hold any value or type.
From what I am reading it seems that I can cast from image to binary and
then from binary to varchar or int...
Whenever I try to convert I keep getting zero as a value. I set up the test
so that I should get an integer value of 5050, but it just does not seem to
work. Is there something wrong with my code:
declare @.c varchar(100)
declare @.trimmed_c varchar(100)
declare @.i int
select @.c = cast(cast(s.VariableValue as varbinary) as varchar)
from SWVariables s where s.VariableName = 'deal_id' and s.ExecutionID = 5
select @.trimmed_c = substring(@.c, 1, CHARINDEX(char(0), @.c, 1) - 1)
select @.i = cast(@.trimmed_c as int)
select @.i as 'deal_id'
Any help would be great, thanks.Depending on how the data was inserted into the image column. Here is an
example that shows successful conversion both ways.
create table tmp(img image default '0x0')
insert tmp values(default)
go
declare @.i int, @.b varbinary(4), @.ptr binary(16)
set @.i=123
set @.b=convert(binary(4),@.i)
select @.ptr=textptr(img)
from tmp
writetext tmp.img @.ptr @.b
go
select img,convert(int,convert(binary(4),img)) [i]
from tmp
go
drop table tmp
-oj
"Dianna" <Dianna@.discussions.microsoft.com> wrote in message
news:F9DA6D91-5256-495A-A7AB-700F955483BD@.microsoft.com...
> Hi,
> I have a table which has a field defined as Image. I guess it was defined
> that way to hold any value or type.
> From what I am reading it seems that I can cast from image to binary and
> then from binary to varchar or int...
> Whenever I try to convert I keep getting zero as a value. I set up the
> test
> so that I should get an integer value of 5050, but it just does not seem
> to
> work. Is there something wrong with my code:
> declare @.c varchar(100)
> declare @.trimmed_c varchar(100)
> declare @.i int
> select @.c = cast(cast(s.VariableValue as varbinary) as varchar)
> from SWVariables s where s.VariableName = 'deal_id' and s.ExecutionID = 5
> select @.trimmed_c = substring(@.c, 1, CHARINDEX(char(0), @.c, 1) - 1)
> select @.i = cast(@.trimmed_c as int)
> select @.i as 'deal_id'
> Any help would be great, thanks.
>

Convert HEX to Text

I have an image data type in a table (it is a digital signature) and one of my users wants me to search the image data and see how many people have a certain attribute in their digital signature. I know the image data is HEX, but how, in query analyzer, can I view it as the XML digital signature data that it really is? So, how can I convert hex to text in sql server?

hi
you can use this function:

CREATE function HEXTODEC(@.s VARCHAR(255) )
--Converts an hexadecimal number to decimal.
returns int
as
BEGIN
DECLARE @.i int, @.temp char(1), @.result int
SELECT @.i=1
SELECT @.result=0
WHILE (@.i<=LEN(@.s))
BEGIN
SELECT @.temp=UPPER(SUBSTRING(@.s,@.i,1))
IF (@.temp>='0') AND (@.temp<='9')
SELECT @.result=@.result+ (ASCII(@.temp)-48)*POWER(16,LEN(@.s)-@.i)
ELSE
IF (@.temp>='A') AND (@.temp<='F')
SELECT @.result=@.result+ (ASCII(@.temp)-55)*POWER(16,LEN(@.s)-@.i)
SELECT @.i=@.i+1
END
return @.result
END

good luck

|||

Thanks, but that doesn't help. I had actually found that on a Google search, but it only works for numbers, when the data is really XML.

I did, however, find that SQL Manager 2005 for SQL Server can show the image field as the XML that it really is, so I was able to kind of get the data out that I needed. A bit of a convoluted way, and I only have a trial version of SQL Manager 2005 for SQL Server, but it got me the results I needed.