Default Image URL in DataList control!

Hey,

I have two questions:

1- In my page there should be a list of all system users, each of them may have an image or may not.

So what I need to do is how to make the next SQL query return a default ImageURL - e.g. "noAvator.jpg" - in case of null image value?

SELECT UserName , image, notes
FROM Members

2- When I try to write an image url in the SQL Server 2005 Management Studio - as "images/noAvator.jpg" or any other name - it always through me this error messagebox:

The changed value is not recignized as valid.
.NET Framework Data Type: Byte[]
Error Message: You can not use the Result pane to set this Field data to value other than NULL.

Why is that? is there a problem with the format of typing the image url?

I appreciate your help!

I'm not sure why you're receiving that error, but how about doing this within your ASPX page instead? If you are displaying your users in some type of GridView, then simply modify the data-binding expression for your image like so:

<asp:imageid="Image1"runat="server"imageurl='<%# (Eval("image") == DBNull.Value) ? "images/noAvator.jpg" : Eval("image") %>'/>

|||

ecbruck:

I'm not sure why you're receiving that error, but how about doing this within your ASPX page instead? If you are displaying your users in some type of GridView, then simply modify the data-binding expression for your image like so:

<asp:imageid="Image1"runat="server"imageurl='<%# (Eval("image") == DBNull.Value) ? "images/noAvator.jpg" : Eval("image") %>'/>

Hiecbruck,

Thanks for you reply,
Just for records, can you give me an example of image type format?

|||

You really shouldn't be using image as a column name, especially not quoted since image is a reserved datatype. Considering that the error message is complaining of trying to convert to a byte array, this leads me to believe that something is getting confused and thinking that you are talking about an actual image. Assuming the image column is of a varchar, or nvarchar datatype, then you can also change your query to be:

SELECT ISNULL(MyColumnNotNamedImage,'images/noAvator.jpg') FROM ...

|||

Motley:

You really shouldn't be using image as a column name, especially not quoted since image is a reserved datatype. Considering that the error message is complaining of trying to convert to a byte array, this leads me to believe that something is getting confused and thinking that you are talking about an actual image. Assuming the image column is of a varchar, or nvarchar datatype, then you can also change your query to be:

SELECT ISNULL(MyColumnNotNamedImage,'images/noAvator.jpg') FROM ...

Thanks alot, Motley!

Default image in SQL Server

Hi,

Is it at all possible to have a default image in an image field in sql server?

If so how?

Thanks,

JB::Is it at all possible to have a default image in an image field in sql server?

Yes.

::If so how?

Post insert trigger. If nothing is in (the inserted field), update the value with the default image.|||thona,

Many thanks for your reply.

This is what I need - If the user does not upload an image, then I need to set a default image in its place.

If I need to make a trigger, how do I trigger the default image.
I would need to know the binary data of the default image, no?

Can we access this binary data once we store it in a field of type 'image?'
I am probably making this harder than it is, although both myself and another guy have been posting trying to solve this one.

What on earth would you put in the default value box for a field of type image?

Thanks,
JB|||::If I need to make a trigger, how do I trigger the default image.

RTFM. I assume you have not read up the documentation about triggers. Why do you not do so before coming here first? I pointed to in the correct direction. Looking up the documentation would have answered this (keyword to look for: trigger).

::I would need to know the binary data of the default image, no?

Depends. Maybe you can have an esxternal hardare device that connects to a crystal ball. Sorry to sound sarcastiv, but this is obvious: if you want to set a default value, you naturally HAVE TO KNOW the default value.

::Can we access this binary data once we store it in a field of type 'image?'

No, SQL Server allows you to store data buit not to retrieve it.

Heck, what do you think they store it for - what value would data stored in a database have if you are not able to retrieve it again?

Sure you can access data in a SQL Server once it is stored.

::What on earth would you put in the default value box for a field of type image?

Who am I to tell you this?

Image is a storage for binary data (not a picture) per definition.

Without KNOWING the project, how do you think I could propose a sensible default value? This is totally dependant on the context.

::I am probably making this harder than it is, although both myself and another guy have
::been posting trying to solve this one.

With all respect, your questions show a tremendous amount of cluelessness. I mean, given the "can we access this data once we store it" is either a very badly formulated question, or it indicates you ahve no clue what you are doing and have no clue about the tools you deal with.

Btw - I would suggeest getting some books and reading documentation. That you can use a post insert trigger to set a default value should be well known - it is not necessarily something you think of in the first moment.

But this second batch of questions really gives me the idea you better need to get some basics in what SQL databases are for.|||I have read the documentation and I cannot find anywhere where it tells us how to set a default for a field type of "image." This data is stored as Binary Data.

In my app, users upload their images and are stored in sql server in a field type "image"
If they do not upload an image, I need to be able to define either a default or somehow send my own image to the database.

I have re-read about triggers in books online, but cannot find an answer how to do this.

thona, please help if you can. This is not such a simple question. Books online is extensive and this forum should help us find the answers we need quickly.

Thanks
JB|||::If they do not upload an image, I need to be able to define either a default or somehow
::send my own image to the database.

Then why d o you not do it?

The way you describe it ("send my own image to the database") is a code issue - something you do in your program. No need to store this image in the database.

::I have re-read about triggers in books online, but cannot find an answer how to do this.

* Create a post insert trigger
* Check the inserted colukmns list for allcolumns that hava NULL in the image
* Update the image with some other value for these entries.

SQL 101 once you get over the CREATE TRIGGER statement.

Decoding MIME Base64

Hi

How do you decode an image (found inside an xml file) which has been encoded in MIME Base 64. If you read in the string, how would you get the image?

I know other database systems (like ASA) that has a build in function to do it for you but I cant find a similar function in SQL Express?

Thanks

Hi

I found out that I can encode images to base64 by using the FOR XML functionality. As far as I can tell I am suppose to be able to use the OPENXML function to turn it back into an image. This does not work in SQL Express, but it does work in ASA?

The only deferens is that in SQL Express I have to get a handle on the xml doc before calling OPENXML. Why will this make a difference and how do I get the correct end result?

Here is the code for SQL EXPRESS (the image encoding is not the full version since it takes to much space):

begin

declare @.doc varchar(max);

DECLARE @.idoc int;

declare @.pic varbinary(max);

set @.doc = '<row><itm_pic>/9j/4FFFFAH//2Q==</itm_pic></row>';

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;

SELECT *

FROM OPENXML( @.idoc,'/row/itm_pic')

WITH ( picture image 'text()');

EXEC sp_xml_removedocument @.idoc;

end;

To get the string for the xml doc with the image run this select on a image in a table:

SELECT picture

FROM prod_images

where id_pic = 1'

FOR XML RAW, BINARY BASE64, ELEMENTS

Thanks

Decode Base64 to Image!

Hi,

I have a xml string which is consist of some images encoded in base64; I have to extract these images in a stored procedure and save them in some tables.

The problem is that I can't decode this base64s to images. Is there a way to do it? (I use SQL Server 2005 Enterprise)

Thanx

Convert.FromBase64String
You probably want to take a look at that. You could possibly create a memory stream from the byte array and then create a bitmap from the stream.

|||

Thank you for your help but as I've mentioned I have to extract images in a stored procedure, so I have to decode them in it too.

Sorry for my late reply.

|||hwat about usin a CLR procedure if you have SQL2k5 already. YOu could use the mentioned class in the last post. That should work for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

declaring ntext, text and image variable

I want to declare ntext, text and image variable to be used in sql script.
How can I do that?
Rohit
Hi,
You cant declare those datatypes in a declare statement.Instead you can pass
those as a parameter of a stored procedure.
create proc test_procedure @.k image,@.k1 text
as
begin
select @.k,@.k1
end
Thanks
Hari
MCDBA
"Rohit" <rohitk@.grapecity.com> wrote in message
news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
> I want to declare ntext, text and image variable to be used in sql script.
> How can I do that?
> Rohit
>
|||I am using cursor and fetching all the variables of tables in variables. One
of the variable has image datatype. If I want to fetch it into cursor, how
can I do that?
is there any way thru which we can declare variable that doesnt have local
scope?
Thanks
Rohit
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23$MT5ktGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You cant declare those datatypes in a declare statement.Instead you can
pass
> those as a parameter of a stored procedure.
> create proc test_procedure @.k image,@.k1 text
> as
> begin
> select @.k,@.k1
> end
>
> Thanks
> Hari
> MCDBA
>
>
> "Rohit" <rohitk@.grapecity.com> wrote in message
> news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
script.
>
|||FYI
Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.
JBandi
|||Thats why I have asked the question
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
> FYI
> Server: Msg 2739, Level 16, State 1, Line 1
> The text, ntext, and image data types are invalid for local variables.
> JBandi
|||It is by design. There is no way to declare a local variable for blob
datatype (i.e. text/ntext/image) in current version of sqlserver. You will
have to wait for the next version.
-oj
http://www.rac4sql.net
"Rohit" <rohitk@.grapecity.com> wrote in message
news:Oxz6$UvGEHA.3064@.tk2msftngp13.phx.gbl...
> Thats why I have asked the question
> "Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
> news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
>

declaring ntext, text and image variable

I want to declare ntext, text and image variable to be used in sql script.
How can I do that?
RohitHi,
You cant declare those datatypes in a declare statement.Instead you can pass
those as a parameter of a stored procedure.
create proc test_procedure @.k image,@.k1 text
as
begin
select @.k,@.k1
end
Thanks
Hari
MCDBA
"Rohit" <rohitk@.grapecity.com> wrote in message
news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
> I want to declare ntext, text and image variable to be used in sql script.
> How can I do that?
> Rohit
>|||I am using cursor and fetching all the variables of tables in variables. One
of the variable has image datatype. If I want to fetch it into cursor, how
can I do that?
is there any way thru which we can declare variable that doesnt have local
scope?
Thanks
Rohit
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23$MT5ktGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You cant declare those datatypes in a declare statement.Instead you can
pass
> those as a parameter of a stored procedure.
> create proc test_procedure @.k image,@.k1 text
> as
> begin
> select @.k,@.k1
> end
>
> Thanks
> Hari
> MCDBA
>
>
> "Rohit" <rohitk@.grapecity.com> wrote in message
> news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
script.
>|||FYI
Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.
JBandi|||Thats why I have asked the question
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
> FYI
> Server: Msg 2739, Level 16, State 1, Line 1
> The text, ntext, and image data types are invalid for local variables.
> JBandi|||It is by design. There is no way to declare a local variable for blob
datatype (i.e. text/ntext/image) in current version of sqlserver. You will
have to wait for the next version.
-oj
http://www.rac4sql.net
"Rohit" <rohitk@.grapecity.com> wrote in message
news:Oxz6$UvGEHA.3064@.tk2msftngp13.phx.gbl...
> Thats why I have asked the question
> "Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
> news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
>