Images from SQL Server

I'm populating an Access continuous form with lots of icons from a SQL
Server backend. If I remove the field holding the icons from the
stored procedure, the form loads 5X faster. Is there any sort of trick
to improve the performance of this sort of scheme?
lqHi

The loading of the form is not just the returning of the dataset, but also
the conversion of the data into the icon. You may want to test the speed of
the queries through query analyser. You may also want to validate if the
size and datatype of the column to see if you can cut the size down, another
alternative is to load the icons from disk and only have references to them
in the database.

HTH

John

"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0311080647.2655d2f3@.posting.google.c om...
> I'm populating an Access continuous form with lots of icons from a SQL
> Server backend. If I remove the field holding the icons from the
> stored procedure, the form loads 5X faster. Is there any sort of trick
> to improve the performance of this sort of scheme?
> lq

Images from a Database in Header

Hi All,
I'm trying to embed an image in a report header and so far I have had no
luck, the image comes from a DB field based on the report parameters. I have
read the info at the link http://msdn2.microsoft.com/ms159677.aspx, tried it
and still did not work.
If I create an image as specified in the aforementioned link - nothing is
displayed, if I copy that image object and paste it in the body of the report
- the image is shown.
Am I missing something? Has somebody got this to work?
Thanks for your help,
ChrisHi Chris,
Are you using SQL Server 2005 Reporting Services?
Are you adding a fixed image in the Report Header or adding data bounded
images in the Header?
If you are adding a fixed image, make sure you select Embeded Image in the
Wizard
Would you please let me know the detailed steps you are using to reproduce
this error message?
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.|||Hi Michael,
Yes I'm using SQL Server 2005 Reporting Services.
I'm adding a data bound image in the Header. I'm following the steps
mentioned in the link I added to my original post. Basically the recommended
workaround is to add a hidden textbox in the body of the report and reference
that textbox in the image in the header. The image in the header doesn't give
me any errors but it doesn't display either. If using the report designer I
copy the image from the header and paste it as part of the body of the
report, the image displays.
Thanks for your help,
Chris
"Michael Cheng [MSFT]" wrote:
> Hi Chris,
> Are you using SQL Server 2005 Reporting Services?
> Are you adding a fixed image in the Report Header or adding data bounded
> images in the Header?
> If you are adding a fixed image, make sure you select Embeded Image in the
> Wizard
> Would you please let me know the detailed steps you are using to reproduce
> this error message?
> 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.
>|||Hi Chris,
How did your image stored in database as base64 encoded? If not, I am
afraid you cannot display the image in this way. It would be appreciated if
you could provide me a sample database file (that I could use to attach
directly on my side) to reproduce it on my side.
I understand the information may be sensitive to you, my direct email
address is v-mingqc@.ONLINEmicrosoft.com (please make sure you have removed
ONLINE before you click SEND), you may send the file to me directly and I
will keep secure.
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.|||Hi Chris,
Thanks so much for your email and sample solution in zipped file.
I have reproduced it on my side and I am looking into this, which might
take a day or two. I will keep you updated as soon as I find there is
anything useful and thanks for your understanding and patience in advance.
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.|||I am having the same exact problem. Just one more piece of information: when
the image does not display in the header, if I go to print the report and
look at a print preview, the image IS displaying. The image also displays if
I export the report to PDF format. It just won't display in the Report
Manager web page. I've tried it with BMP and JPG images; both behave the same.
Also, if I try to set the background image for a text box control in the
header, using the same mechanism, the image does not show up at all.
--
Brian G.
"Christians Izquierdo" wrote:
> Hi Michael,
> Yes I'm using SQL Server 2005 Reporting Services.
> I'm adding a data bound image in the Header. I'm following the steps
> mentioned in the link I added to my original post. Basically the recommended
> workaround is to add a hidden textbox in the body of the report and reference
> that textbox in the image in the header. The image in the header doesn't give
> me any errors but it doesn't display either. If using the report designer I
> copy the image from the header and paste it as part of the body of the
> report, the image displays.
> Thanks for your help,
> Chris
> "Michael Cheng [MSFT]" wrote:
> > Hi Chris,
> >
> > Are you using SQL Server 2005 Reporting Services?
> > Are you adding a fixed image in the Report Header or adding data bounded
> > images in the Header?
> >
> > If you are adding a fixed image, make sure you select Embeded Image in the
> > Wizard
> >
> > Would you please let me know the detailed steps you are using to reproduce
> > this error message?
> >
> > 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.
> >
> >|||Hi Brian and Christians,
Thanks Brian for the inputs.
Yes, we have noticed it will be displayed correctly in Print Preview mode
and it will also be displayed when rendered. I believe this should be a
problem in our product and I have notify the devlopment team about this,
they will look into this.
However, if this will impact your business much, I would recommend you open
an incident with Microsoft Customer Service and Support so that a dedicated
Support Professional can assist with this case. If you need any help in
this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
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.

image question

hello friends

I want to specify a field to save icons I want to give this field a maximum
of 120kb for each icon to be stored in the db.

what datatype should I assign to this field and how do I specify the size ?

Thanks
Tom"Tom" <tomgaomail@.optushome.com.au> wrote in message
news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
> hello friends
> I want to specify a field to save icons I want to give this field a
> maximum
> of 120kb for each icon to be stored in the db.
> what datatype should I assign to this field and how do I specify the size
> ?
> Thanks
> Tom

The image data type is the one used for large binary data, but you can't
specify a size - 2GB is the maximum allowed. Another common approach is to
leave the binary files in the filesystem, and simply store the path to the
file in a varchar column.

Simon|||I considered that option.. however storing path is not the ideal option as
many users may have the same image or icon thus resulting the same image
being sent to all users when the image are suppose to be different as the
image gets overridden when they're the same file name

any other ideas ?
thanks
Tom

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:415175e2$1_2@.news.bluewin.ch...
> "Tom" <tomgaomail@.optushome.com.au> wrote in message
> news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
> > hello friends
> > I want to specify a field to save icons I want to give this field a
> > maximum
> > of 120kb for each icon to be stored in the db.
> > what datatype should I assign to this field and how do I specify the
size
> > ?
> > Thanks
> > Tom
> The image data type is the one used for large binary data, but you can't
> specify a size - 2GB is the maximum allowed. Another common approach is to
> leave the binary files in the filesystem, and simply store the path to the
> file in a varchar column.
> Simon|||"Tom" <tomgaomail@.optushome.com.au> wrote in message
news:41519884$0$20125$afc38c87@.news.optusnet.com.a u...
>I considered that option.. however storing path is not the ideal option as
> many users may have the same image or icon thus resulting the same image
> being sent to all users when the image are suppose to be different as the
> image gets overridden when they're the same file name
> any other ideas ?
> thanks
> Tom

<snip
Not really - those are pretty much the two options you have. Either use the
image data type and store the file in the database, or leave it in the
filesystem and store the path. It comes down to which approach is easier for
you, given your application and development environment.

Personally I'd go for the path, since then changing a user's icon just means
updating a single column, instead of loading the whole file into the
database. Also, any default or common icons only have to be stored once,
rather than maintaining a separate copy for each user.

You should also check out "Managing ntext, text, and image Data" in Books
Online, which covers the details of working with image data, and there's a
code sample for ADO, if that's what you're using.

Simon|||I had a similar problem where i wanted people to store files on my web
server, because i was worried about file name clashes i was considering
storing them in the database. Instead I came up with two ideas, one was to
put each users files into a subdirectory named after their user name, and
two the one that im using, was to first upload the file to the server, add a
database entry and return the result of the @.@.IDENTITY to my program then
preclude the name of the file with the identity value.

thus... myresume.doc would become 243_myresume.doc

Hope those help,
Muhd

"Tom" <tomgaomail@.optushome.com.au> wrote in message
news:41519884$0$20125$afc38c87@.news.optusnet.com.a u...
>I considered that option.. however storing path is not the ideal option as
> many users may have the same image or icon thus resulting the same image
> being sent to all users when the image are suppose to be different as the
> image gets overridden when they're the same file name
> any other ideas ?
> thanks
> Tom
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:415175e2$1_2@.news.bluewin.ch...
>>
>> "Tom" <tomgaomail@.optushome.com.au> wrote in message
>> news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
>> > hello friends
>>> > I want to specify a field to save icons I want to give this field a
>> > maximum
>> > of 120kb for each icon to be stored in the db.
>>> > what datatype should I assign to this field and how do I specify the
> size
>> > ?
>>> > Thanks
>> > Tom
>>>>
>> The image data type is the one used for large binary data, but you can't
>> specify a size - 2GB is the maximum allowed. Another common approach is
>> to
>> leave the binary files in the filesystem, and simply store the path to
>> the
>> file in a varchar column.
>>
>> Simon
>>
>>|||thanks thats a great idea

but I'm also curious would it be possible to store the icons into a binary
field and set the size of that binary field ?

Thanks
Tom

"Muhd" <eat@.joes.com> wrote in message
news:cRj4d.493991$gE.369174@.pd7tw3no...
> I had a similar problem where i wanted people to store files on my web
> server, because i was worried about file name clashes i was considering
> storing them in the database. Instead I came up with two ideas, one was
to
> put each users files into a subdirectory named after their user name, and
> two the one that im using, was to first upload the file to the server, add
a
> database entry and return the result of the @.@.IDENTITY to my program then
> preclude the name of the file with the identity value.
> thus... myresume.doc would become 243_myresume.doc
> Hope those help,
> Muhd
>
> "Tom" <tomgaomail@.optushome.com.au> wrote in message
> news:41519884$0$20125$afc38c87@.news.optusnet.com.a u...
> >I considered that option.. however storing path is not the ideal option
as
> > many users may have the same image or icon thus resulting the same image
> > being sent to all users when the image are suppose to be different as
the
> > image gets overridden when they're the same file name
> > any other ideas ?
> > thanks
> > Tom
> > "Simon Hayes" <sql@.hayes.ch> wrote in message
> > news:415175e2$1_2@.news.bluewin.ch...
> >>
> >> "Tom" <tomgaomail@.optushome.com.au> wrote in message
> >> news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
> >> > hello friends
> >> >> > I want to specify a field to save icons I want to give this field a
> >> > maximum
> >> > of 120kb for each icon to be stored in the db.
> >> >> > what datatype should I assign to this field and how do I specify the
> > size
> >> > ?
> >> >> > Thanks
> >> > Tom
> >> >> >>
> >> The image data type is the one used for large binary data, but you
can't
> >> specify a size - 2GB is the maximum allowed. Another common approach is
> >> to
> >> leave the binary files in the filesystem, and simply store the path to
> >> the
> >> file in a varchar column.
> >>
> >> Simon
> >>
> >>|||Tom wrote:
> thanks thats a great idea
> but I'm also curious would it be possible to store the icons into a binary
> field and set the size of that binary field ?
> Thanks
> Tom

NO, you can't specify the size of an image columt - it's variable up to 2 GB.
Here are all the types MS SQL has:

http://www.databasejournal.com/feat...le.phpr/2212141

WYGL,
Andrey|||
As has already been stated, you can't 'set' the size of field, but since your code would load an image
into the field, it could check that the size is within limits before loading it.

--
__________________________________________________ _____
http://www.ammara.com/
Image Handling Components, Samples, Solutions and Info
DBPix 2.0 - lossless jpeg rotation, EXIF, asynchronous

"Tom" <tomgaomail@.optushome.com.au> wrote:
>thanks thats a great idea
>but I'm also curious would it be possible to store the icons into a binary
>field and set the size of that binary field ?
>Thanks
>Tom
>"Muhd" <eat@.joes.com> wrote in message
>news:cRj4d.493991$gE.369174@.pd7tw3no...
>> I had a similar problem where i wanted people to store files on my web
>> server, because i was worried about file name clashes i was considering
>> storing them in the database. Instead I came up with two ideas, one was
>to
>> put each users files into a subdirectory named after their user name, and
>> two the one that im using, was to first upload the file to the server, add
>a
>> database entry and return the result of the @.@.IDENTITY to my program then
>> preclude the name of the file with the identity value.
>>
>> thus... myresume.doc would become 243_myresume.doc
>>
>> Hope those help,
>> Muhd
>>
>>
>> "Tom" <tomgaomail@.optushome.com.au> wrote in message
>> news:41519884$0$20125$afc38c87@.news.optusnet.com.a u...
>> >I considered that option.. however storing path is not the ideal option
>as
>> > many users may have the same image or icon thus resulting the same image
>> > being sent to all users when the image are suppose to be different as
>the
>> > image gets overridden when they're the same file name
>>> > any other ideas ?
>> > thanks
>> > Tom
>>> > "Simon Hayes" <sql@.hayes.ch> wrote in message
>> > news:415175e2$1_2@.news.bluewin.ch...
>> >>
>> >> "Tom" <tomgaomail@.optushome.com.au> wrote in message
>> >> news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
>> >> > hello friends
>> >>> >> > I want to specify a field to save icons I want to give this field a
>> >> > maximum
>> >> > of 120kb for each icon to be stored in the db.
>> >>> >> > what datatype should I assign to this field and how do I specify the
>> > size
>> >> > ?
>> >>> >> > Thanks
>> >> > Tom
>> >>> >>> >>
>> >> The image data type is the one used for large binary data, but you
>can't
>> >> specify a size - 2GB is the maximum allowed. Another common approach is
>> >> to
>> >> leave the binary files in the filesystem, and simply store the path to
>> >> the
>> >> file in a varchar column.
>> >>
>> >> Simon
>> >>
>> >>
>>>>
>>

Image in SQL2000

Hello!
What function in SQL SERVER 2000 to test an image field if empty or not? Tried NoT NULL but no success.
I want to return only items with pictures...
THanksDid you try DATALENGTH function?sql

Image data type to character string

I have a table with an image datatype field.

When I retrieve it it displays as a binary array. How do I convert that array back and forth to get the underlying text?

Post the SQL statement used in this regard, you need to use READTEXT statement, also http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp fyi..

If you are using any application to display that image column then refer to http://www.akadia.com/services/dotnet_load_blob.html link for more information.

Image from Business Object property?

Hello!
I'm using the local reporting services engine within a WinForm
application. I have a Business Object that contains a field which returns a
Bitmap. The Bitmap is created from other values within the same business
object. An example of the properties are below.
I'm binding my report to my BusinessObject and everything is going quite
well with the exception of displaying the graph bitmap within an Image
ReportItem. I've tried different Source types for the Image properties -
Database, Embedded, External but none seem to like the Bitmap within a
property on the Object Data Source.
I saw a few ways to develop a CustomerReportItem and will head down that
road but it seems like I'm missing something really simple. Any thoughts or
helpful tips are welcome.
Thanx,
Mike Q
public class MyBusinessObject
{
public decimal IndexValue
{
get
{
some calculations;
}
}
public Bitmap IndexGraph
{
get
{
Bitmap Result = Bitmap.FromFile("GraphTemplate.bmp");
Graphics g = Graphics.FromImage(Result);
Pen RedPen = new Pen(Brushes.Red, 5);
g.DrawLine(RedPen, 40, 0, 40, 50); // These values are calculated based on
the Index value.
return Result;
}
}
}I found the answer - instead of returning a Bitmap - return a byte[] from
the business object property
Thanx,
-q
"Mike Q" <MQuinn_q@.Yahoo.com> wrote in message
news:OMHy2SVrIHA.4280@.TK2MSFTNGP02.phx.gbl...
> Hello!
> I'm using the local reporting services engine within a WinForm
> application. I have a Business Object that contains a field which returns
> a Bitmap. The Bitmap is created from other values within the same
> business object. An example of the properties are below.
> I'm binding my report to my BusinessObject and everything is going quite
> well with the exception of displaying the graph bitmap within an Image
> ReportItem. I've tried different Source types for the Image properties -
> Database, Embedded, External but none seem to like the Bitmap within a
> property on the Object Data Source.
> I saw a few ways to develop a CustomerReportItem and will head down that
> road but it seems like I'm missing something really simple. Any thoughts
> or helpful tips are welcome.
> Thanx,
> Mike Q
>
> public class MyBusinessObject
> {
> public decimal IndexValue
> {
> get
> {
> some calculations;
> }
> }
> public Bitmap IndexGraph
> {
> get
> {
> Bitmap Result = Bitmap.FromFile("GraphTemplate.bmp");
> Graphics g = Graphics.FromImage(Result);
> Pen RedPen = new Pen(Brushes.Red, 5);
> g.DrawLine(RedPen, 40, 0, 40, 50); // These values are calculated based
> on the Index value.
> return Result;
> }
> }
> }

Image Filepath in sql

Hi in my table i have a field which would store the file path of an image, the datatype is a varchar, and i wrote the following filepath in it but it didnt work, when i tried to call it up in the browser it didnt show, is the way i saved it below the right format?

C:\Millio\Documents\Project\Images\car.jpg

That's fine, but you will have to post some of your code so we can see why it might not be diplaying.

|||

Hi thanks for responding this is the part where i am calling the image from;

<ItemTemplate>

<span><spanstyle="font-size: 14pt">

<asp:ImageID="Image1"ImageUrl='<%# Eval("Images") %>'runat="server"/>

<br/>

|||

You should store the virtual path of your image in the database rather than the physical path.

|||

If you use a physical path like the one you are showing, you will need to use an HttpHandler to stream the image to the browser. The img tag takes a url value for the src attribute, so you could move the images to a folder within your web site, and reference them with a virtual path. Or if Project is already your virtual directory (the root folder for your web site), the path that you should store in the database is "images/car.jpg" or "~/images/car.jpg". Both will work, but the second one is ASP.NET specific, and can only be used when setting the ImageURL property of an asp server control. I would opt for the first one, just in case I decided to convert the site to PHP.

Or more realistically, decide to use the value to build up the src attribute of an <img> tag in code, and don't bother with a server control.

|||

Hi Mike the reason i did it that way is because i am using a user control to load up my pages, and i didnt want to have to make a page for each category, i will show you what i mean;

<%@.ControlLanguage="C#"AutoEventWireup="true"CodeFile="CategoryList.ascx.cs"Inherits="CategoryList" %>

<scriptrunat="server">

string _Category = "washing machines";

public string Category

{

get { return _Category; }

set { _Category = value; }

}

protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)

{

Label ID = (Label)DataList1.SelectedItem.FindControl("categoryIDLabel");

// Label AD = (Label)DataList1.SelectedItem.FindControl("PriceLabel");

Label MD = (Label)DataList1.SelectedItem.FindControl("categoryNameLabel");

Trace.Write("category " + ID.Text);

//Trace.Write("Price " + AD.Text);

Trace.Write("categoryName " + MD.Text);

Session["scategoryID"] = ID.Text;

//Session["scategorydescription"] = AD.Text;

Session["scategoryName"] = MD.Text;

}

protected void Page_Load(object sender, EventArgs e)

{

SqlDataSource1.SelectParameters["categoryName"].DefaultValue = _Category;

}

</script>

<asp:DataListID="DataList1"runat="server"BackColor="White"BorderColor="Transparent"

BorderStyle="None"BorderWidth="1px"CellPadding="4"DataSourceID="SqlDataSource1"

ForeColor="Black"GridLines="Horizontal"OnSelectedIndexChanged="DataList1_SelectedIndexChanged">

<FooterStyleBackColor="#CCCC99"ForeColor="Black"/>

<SelectedItemStyleBackColor="White"Font-Bold="True"ForeColor="White"Font-Italic="False"Font-Overline="False"Font-Strikeout="False"Font-Underline="False"/>

<HeaderStyleBackColor="#333333"Font-Bold="True"ForeColor="White"/>

<SeparatorStyleBackColor="Black"BorderColor="Black"BorderStyle="Solid"BorderWidth="2px"/>

<AlternatingItemStyleBackColor="White"Font-Bold="False"Font-Italic="False"Font-Overline="False"

Font-Strikeout="False"Font-Underline="False"/>

<ItemTemplate>

<span><spanstyle="font-size: 14pt">

<asp:ImageID="Image1"ImageUrl='<%# Eval("categoryImage") %>'runat="server"/>

<br/>

<asp:LabelID="categoryNameLabel"runat="server"Text='<%# Eval("categoryName") %>'ForeColor="DimGray"Font-Size="11pt"Font-Names="Trebuchet MS"></asp:Label><br/>

<br/>

<spanstyle="color: black"></span>

<asp:LabelID="CategoryIDLabel"runat="server"Visible="False"Text='<%# Eval("categoryID") %>'ForeColor="DimGray"Font-Size="11pt"></asp:Label><br/>

<br/>

<asp:LabelID="categoryDescriptionLabel"runat="server"Visible="True"Text='<%# Eval("categoryDescription") %>'ForeColor="DimGray"Font-Size="11pt"></asp:Label><br/>

<br/>

<br/>

</ItemTemplate>

</asp:DataList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:streamConnectionString %>"

SelectCommand="SELECT [categoryID], [categoryImage], [categoryName], [categoryDescription] FROM [Categories] WHERE ([categoryName] = @.categoryName)">

<SelectParameters>

<asp:ParameterName="categoryName"Type="string"/>

</SelectParameters>

</asp:SqlDataSource>

Image field in Trigger

Hi , i have a trigger that start on a record insert . . .This take de ID of the inserted record and start to fill other table in the database.In this triggher i have to update an image field of a table , getting the value from another table .I've casted this value in varbinary , but var binary can take only 8000 byte , and this fileis bigger than 8000 byte . . .How can i proced to solve the problem ?ThanksIf you are using SQL Server 2005 you can use the VARBINARY(MAX) type. if you are on a version prior to SQL Server 2k5 you can try to code your statement set based, eliminating the need for interim variables.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Hi , i'm using Sql Server 2000 . . . How can i eliminate the need for interim variables ?I've tryed to do this :UPDATA table1 SET img1 = (SELECT img2 FROM table2 where ...) where ...But the message is that i can use image field in trigger ...|||Hi,

did you have a look on:

http://msdn2.microsoft.com/en-us/library/ms189799.aspx

"In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is set to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.

If the compatibility level is 80 or higher, SQL Server allows for the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views. "

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

IMAGE FIELD & Incremental population

Hi guys,
I'm doing some experiments with the Full Text Retrieval functionalities offered
by SQL-Server. I'm going to store documents in IMAGE fields to leverage the indexing capabilities of MS-Search service and there're some point which are not clear to me.
I want to use the incremental indexing (Timestamp-based or Change tracking) but in BOL it's stated that "changes made with WRITETEXT and UPDATETEXT are not detected".
I've a table where FT is enabled on some text fields (varchar(n)) and on an IMAGE field where the document content is stored.
Change Tracking & Update Index in background are enabled on this test table.
If the IMAGE field is loaded when the indexing on the other fields has been already performed, no change is detected and the IMAGE field is not indexed (loading is perfomed using textcopy.exe which use WRITETEXT).
Forcing the insert of all FT fields at the same time (i.e. same transaction), they are all indexed.
Unfortunately there're situations in which the doc. body is updated (e.g. new doc. version). The only way I found out to obtain the re-indexing of IMAGE field is the following: after the IMAGE update, I also update one of the other text field with FT enab
led.
It's a fake update: the field is "updated" with its current content. In this way the record is "marked" as changed and MS-Search indexes the IMAGE field too.
Of couse I can start a full population but this is not an option in a production environment.
I'm wondering which is the clean way to handle this situation.
How can I be sure that all the fields of a record have been indexed ?
Can I signal to MS-Search that a record must be re-indexed ?
Many thanks
Max
I think you have the cleanest solution to this problem.
There is a way of logging every record that is indexed/reindexed, but there
is a slight performance penaly to pay for this and then figuring out which
record is being indexed is also difficult.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"MadMax" <MadMax@.discussions.microsoft.com> wrote in message
news:0C589D14-E149-4F7D-BE23-779E2E1D9380@.microsoft.com...
> Hi guys,
> I'm doing some experiments with the Full Text Retrieval functionalities
offered
> by SQL-Server. I'm going to store documents in IMAGE fields to leverage
the indexing capabilities of MS-Search service and there're some point which
are not clear to me.
> I want to use the incremental indexing (Timestamp-based or Change
tracking) but in BOL it's stated that "changes made with WRITETEXT and
UPDATETEXT are not detected".
> I've a table where FT is enabled on some text fields (varchar(n)) and on
an IMAGE field where the document content is stored.
> Change Tracking & Update Index in background are enabled on this test
table.
> If the IMAGE field is loaded when the indexing on the other fields has
been already performed, no change is detected and the IMAGE field is not
indexed (loading is perfomed using textcopy.exe which use WRITETEXT).
> Forcing the insert of all FT fields at the same time (i.e. same
transaction), they are all indexed.
> Unfortunately there're situations in which the doc. body is updated (e.g.
new doc. version). The only way I found out to obtain the re-indexing of
IMAGE field is the following: after the IMAGE update, I also update one of
the other text field with FT enabled.
> It's a fake update: the field is "updated" with its current content. In
this way the record is "marked" as changed and MS-Search indexes the IMAGE
field too.
> Of couse I can start a full population but this is not an option in a
production environment.
> I'm wondering which is the clean way to handle this situation.
> How can I be sure that all the fields of a record have been indexed ?
> Can I signal to MS-Search that a record must be re-indexed ?
> Many thanks
> Max
>

Image field

Dear friends,
After inserting a word document in an image field, is it possible to
retrieve the same file and store it to some of the windows folder:
1. through SQL Server Stored Procedure?
2. though any other means like .Net code etc..
Thnaks in anticipation
Regards
Sathian> After inserting a word document in an image field, is it possible to
> retrieve the same file and store it to some of the windows folder:
> 1. through SQL Server Stored Procedure?
No idea, but would you want to? T-SQL was not designed for this.

> 2. though any other means like .Net code etc..
Absolutely. It's a piece of cake. Remember that an image field is nothing mo
re
than an array of bytes. All you do is creata a FileStream and set it's conte
nts
to that of the byte array from the image field.
Thomas|||Hello thomas,
Thank you very much.
Any sample piece of code wiich does so..?
I tried in net.. so far I wasn't sucessful..
Regards
Sathian
"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:eoguWJXQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> No idea, but would you want to? T-SQL was not designed for this.
>
> Absolutely. It's a piece of cake. Remember that an image field is nothing
more
> than an array of bytes. All you do is creata a FileStream and set it's
contents
> to that of the byte array from the image field.
>
> Thomas
>|||Although this is not the forum for this sort of question...
private const string connStr = ...
public void ReadFromDBToFile(string destFilePath)
{
byte[] data = null;
using (SqlConnection conn = new SqlConnection(connStr))
{
StringBuilder sb = new StringBuilder();
sb.Append("Select Content, DataLength(Content) As Len");
sb.AppendFormat("\nFrom dbo.TableName");
sb.AppendFormat("\nWhere PK = @.PK");
conn.Open();
using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
{
cmd.Parameters.Add("@.PK", SqlDbType...);
cmd.Parameters[0].Value = <pk>
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int len = dr.GetInt32(1);
data = new byte[len];
dr.GetBytes(0, 0, data, 0, data.Length);
}
}
}
using (FileStream fs = new FileStream(destFilePath, FileMode.Create,
FileAccess.Write, FileShare.Write))
{
fs.Write(data, 0, data.Length);
}
}
public void WriteFromFileToDB(string sourceFilePath)
{
byte[] data;
using (FileStream fs = new FileStream(sourceFilePath, FileMode.Open,
FileAccess.Read, FileShare.Read))
{
data = new byte[fs.Length];
fs.Read(data, 0, (int)fs.Length);
}
using (SqlConnection conn = new SqlConnection(connStr))
{
StringBuilder sb = new StringBuilder();
sb.Append("Update dbo.TableName");
sb.AppendFormat("\nSet Content = @.Content");
sb.AppendFormat("\nWhere PK= @.PK");
conn.Open();
using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@.Content", SqlDbType.Image);
cmd.Parameters.Add("@.PK", SqlDbType...
cmd.Parameters[0].Value = data;
cmd.Parameters[1].Value = <pk>
cmd.ExecuteNonQuery();
}
}
}
I used a dynamic SQL statement, but you could just as easily use a stored pr
oc.
HTH
Thomas
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d3nfqc$oem$1@.ns2.fe.internet.bosch.com...
> Hello thomas,
> Thank you very much.
> Any sample piece of code wiich does so..?
> I tried in net.. so far I wasn't sucessful..
> Regards
> Sathian
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:eoguWJXQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> more
> contents
>

Image data type, doesn't return the data on selected

I don't know much about the Image data type. When I query a record and get
only 16bits of the an Imaeg field, instead of the actual data?
Any documentation and guidance for this problem? It seems not on the Books
Online.
Thanks very much.Check out the 'Retrieving ntext, text, or image Values' topic in the SQL
Server Books Online.
HTH
Jerry
"zhaounknown" <zhaounknown@.discussions.microsoft.com> wrote in message
news:686F627B-885B-4C6E-9134-4148CFFD824F@.microsoft.com...
>I don't know much about the Image data type. When I query a record and get
> only 16bits of the an Imaeg field, instead of the actual data?
> Any documentation and guidance for this problem? It seems not on the Books
> Online.
> Thanks very much.|||Thanks for your reply.
I checked the topic of "retrieving ntext, ...".
What it says is :
The full amount of data is returned if the length is less than TEXTSIZE.
The DB-Library API also supports a dbtextsize parameter that controls the
length of ntext, text, and image data that can be selected. The Microsoft OL
E
DB Provider for SQL Server and the SQL Server ODBC driver automatically set
@.@.TEXTSIZE to its maximum of 2 GB.
I am using MSDE, and @.@.TextSize is 64512. However, my object's length counts
56424, which is less than 64512 and is supposed to return the data in full.
But it seems doesn't.
"Jerry Spivey" wrote:

> Check out the 'Retrieving ntext, text, or image Values' topic in the SQL
> Server Books Online.
> HTH
> Jerry
> "zhaounknown" <zhaounknown@.discussions.microsoft.com> wrote in message
> news:686F627B-885B-4C6E-9134-4148CFFD824F@.microsoft.com...
>
>|||I found the problem is the data has not been stored into the SQL Server.
The reason is:
I create a update command using ADO.Net wizard by entering the command text
manually in the wizard, which will generate the Image field to have size at
16, instead of 2147483647, which causes the problem.
Hope, this may help anyone.
"zhaounknown" wrote:
> Thanks for your reply.
> I checked the topic of "retrieving ntext, ...".
> What it says is :
> The full amount of data is returned if the length is less than TEXTSIZE.
> The DB-Library API also supports a dbtextsize parameter that controls the
> length of ntext, text, and image data that can be selected. The Microsoft
OLE
> DB Provider for SQL Server and the SQL Server ODBC driver automatically se
t
> @.@.TEXTSIZE to its maximum of 2 GB.
> I am using MSDE, and @.@.TextSize is 64512. However, my object's length coun
ts
> 56424, which is less than 64512 and is supposed to return the data in full
.
> But it seems doesn't.
>
> "Jerry Spivey" wrote:
>

Image data type to character string

I have a table with an image datatype field.

When I retrieve it it displays as a binary array. How do I convert that array back and forth to get the underlying text?

Post the SQL statement used in this regard, you need to use READTEXT statement, also http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp fyi..

If you are using any application to display that image column then refer to http://www.akadia.com/services/dotnet_load_blob.html link for more information.

image data type

I have documents stored in an image field. How can I mass
convert them to inividual disk files?You might want to try the TEXTCOPY.EXE utility that comes with SQL Server.
It is found in the \Binn folder with your SQL Server Installation location.
The utility copies a single text or image value into or out of SQL Server.
Navigate to that folder (in your DOS command prompt) and type TEXTCOPY.EXE
/? for more help.
Nathan H.O.

Image Data

What is the correct (or best way) to
Insert Into an Image data field.
I have an app that displays records containing Equipment Pictures.
I am able to display the record and display the picture.
I want to allow the user to add an new record and also insert a
new picture associated with the record.
The table was imported so the existing pictures came in fine
but I now need to build the app to facilitate this needed
functionality.
any help is much appreciated.
thanks in advance,
bob mcclellanYou can store the physical path of the pictures in one column and it is
better to hide that file
Madhivanan|||One of the option is this script wriiten by (If I remember well) Dan Guzman
Dim ADOCmd As New ADODB.Command
Dim ADOprm As New ADODB.Parameter
Dim ADOcon As ADODB.Connection
Dim intFile As Integer
Dim ImgBuff() As Byte
Dim ImgLen As Long
Set ADOcon = New ADODB.Connection
With ADOcon
.Provider = "MSDASQL"
.CursorLocation = adUseClient
.ConnectionString = "driver=
{SQL Server};server=(local);uid=<username>;pwd=<strong
password>;database=pubs"
.Open
End With
'Change this to the path of a GIF file you want to use for testing.
IMG_FILE_GIF = "E:\Graphics\GIF\Image.gif"
'Read/Store GIF file in ByteArray
intFile = FreeFile
Open IMG_FILE_GIF For Binary As #intFile
ImgLen = LOF(intFile)
ReDim ImgBuff(ImgLen) As Byte
Get #intFile, , ImgBuff()
Close #intFile
Set ADOCmd.ActiveConnection = ADOcon
ADOCmd.CommandType = adCmdStoredProc
ADOCmd.CommandText = "uspInsertBLOB"
Set ADOprm = ADOCmd.CreateParameter(, adChar, adParamInput, 1, "1")
ADOCmd.Parameters.Append ADOprm
'The datatype must be specified as adLongVarBinary
'For the code to function correctly comment this line.
Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
adParamInput, ImgLen)
'Uncomment this line.
'Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
adParamInput, (ImgLen + 1))
ADOCmd.Parameters.Append ADOprm
'Set the Value of the parameter with the AppendChunk method.
ADOprm.AppendChunk ImgBuff()
'The preceding example assumes you are using a small image file.
'See the article reference in the REFERENCES section for handling a
'large image file.
ADOCmd.Execute
Set ADOCmd = Nothing
Set ADOprm = Nothing
"John316" <bobmcc@.tricoequipment.com> wrote in message
news:O5XzhRmHFHA.3588@.TK2MSFTNGP14.phx.gbl...
> What is the correct (or best way) to
> Insert Into an Image data field.
> I have an app that displays records containing Equipment Pictures.
> I am able to display the record and display the picture.
> I want to allow the user to add an new record and also insert a
> new picture associated with the record.
> The table was imported so the existing pictures came in fine
> but I now need to build the app to facilitate this needed
> functionality.
> any help is much appreciated.
> thanks in advance,
> bob mcclellan
>|||Thanks Uri !
I will try this out.
thanks much for the reply.
It is much appreciated.
bob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O89CQdmHFHA.4048@.TK2MSFTNGP15.phx.gbl...
> One of the option is this script wriiten by (If I remember well) Dan
> Guzman
> Dim ADOCmd As New ADODB.Command
> Dim ADOprm As New ADODB.Parameter
> Dim ADOcon As ADODB.Connection
> Dim intFile As Integer
> Dim ImgBuff() As Byte
> Dim ImgLen As Long
> Set ADOcon = New ADODB.Connection
> With ADOcon
> .Provider = "MSDASQL"
> .CursorLocation = adUseClient
> .ConnectionString = "driver=
> {SQL Server};server=(local);uid=<username>;pwd=<strong
> password>;database=pubs"
> .Open
> End With
> 'Change this to the path of a GIF file you want to use for testing.
> IMG_FILE_GIF = "E:\Graphics\GIF\Image.gif"
> 'Read/Store GIF file in ByteArray
> intFile = FreeFile
> Open IMG_FILE_GIF For Binary As #intFile
> ImgLen = LOF(intFile)
> ReDim ImgBuff(ImgLen) As Byte
> Get #intFile, , ImgBuff()
> Close #intFile
> Set ADOCmd.ActiveConnection = ADOcon
> ADOCmd.CommandType = adCmdStoredProc
> ADOCmd.CommandText = "uspInsertBLOB"
> Set ADOprm = ADOCmd.CreateParameter(, adChar, adParamInput, 1, "1")
> ADOCmd.Parameters.Append ADOprm
> 'The datatype must be specified as adLongVarBinary
> 'For the code to function correctly comment this line.
> Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
> adParamInput, ImgLen)
> 'Uncomment this line.
> 'Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
> adParamInput, (ImgLen + 1))
> ADOCmd.Parameters.Append ADOprm
> 'Set the Value of the parameter with the AppendChunk method.
> ADOprm.AppendChunk ImgBuff()
> 'The preceding example assumes you are using a small image file.
> 'See the article reference in the REFERENCES section for handling a
> 'large image file.
> ADOCmd.Execute
> Set ADOCmd = Nothing
> Set ADOprm = Nothing
>
> "John316" <bobmcc@.tricoequipment.com> wrote in message
> news:O5XzhRmHFHA.3588@.TK2MSFTNGP14.phx.gbl...
>

image and text

After I upload a image file to a table with image field, how do I get the
file name? In other words if there 1000 records in one table (1000 images),
how do I know their file names?
Is there a way I can display those images in sql query analyzer using a
T-SQL command.
Using a textcopy, can I retrieve more than 1 record/1 image at a time?
ex: if there are 10 products under one category. Is there a way to retrieve
all 10 products with images using one T-sql statement, and display them on a
web page?
thanks
Vignesh
Uhway
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
It is very good practice to store only the pathes to files located on disk.
It consumes a lot of system resource to deal with images.
There are pretty good examples provided by Microsoft to display images on
the client.

> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
The data is stored in binary format so you will not be able to see the
image.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>
|||If you are storing the images in an image data type, there is no filename,
only the PK of the row where the image is stored. If you wish to keep the
original filename and store the image in an image field, you must also
create a column to store the filename in.
Reasonable people differ on whether or not it is better to store the data in
an image field or only the filename, leaving the data in a physical file.
There are pros and cons to both methods,
Test to see which is better for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>

image and text

After I upload a image file to a table with image field, how do I get the
file name? In other words if there 1000 records in one table (1000 images),
how do I know their file names?
Is there a way I can display those images in sql query analyzer using a
T-SQL command.
Using a textcopy, can I retrieve more than 1 record/1 image at a time?
ex: if there are 10 products under one category. Is there a way to retrieve
all 10 products with images using one T-sql statement, and display them on a
web page?
thanks
VigneshUhway
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
It is very good practice to store only the pathes to files located on disk.
It consumes a lot of system resource to deal with images.
There are pretty good examples provided by Microsoft to display images on
the client.

> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
The data is stored in binary format so you will not be able to see the
image.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>|||If you are storing the images in an image data type, there is no filename,
only the PK of the row where the image is stored. If you wish to keep the
original filename and store the image in an image field, you must also
create a column to store the filename in.
Reasonable people differ on whether or not it is better to store the data in
an image field or only the filename, leaving the data in a physical file.
There are pros and cons to both methods,
Test to see which is better for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>

image and text

After I upload a image file to a table with image field, how do I get the
file name? In other words if there 1000 records in one table (1000 images),
how do I know their file names?
Is there a way I can display those images in sql query analyzer using a
T-SQL command.
Using a textcopy, can I retrieve more than 1 record/1 image at a time?
ex: if there are 10 products under one category. Is there a way to retrieve
all 10 products with images using one T-sql statement, and display them on a
web page?
thanks
VigneshUhway
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
It is very good practice to store only the pathes to files located on disk.
It consumes a lot of system resource to deal with images.
There are pretty good examples provided by Microsoft to display images on
the client.
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
The data is stored in binary format so you will not be able to see the
image.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>|||If you are storing the images in an image data type, there is no filename,
only the PK of the row where the image is stored. If you wish to keep the
original filename and store the image in an image field, you must also
create a column to store the filename in.
Reasonable people differ on whether or not it is better to store the data in
an image field or only the filename, leaving the data in a physical file.
There are pros and cons to both methods,
Test to see which is better for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>

I'm confused with the non-unicode

I was confused with the non-unicode when i insert chinese characters in
a varchar field using asp page, it accepted the characters and
displayed correctly. It is good for me, but i was wondering what
exactly non-unicode is and why acting like that way(I expected to
receive strange characters in the feedback asp page but i didn't). Can
anyone tell me the reason?What is the collation of the column in the table? Also, did you try
comparing two columns with that data?
MC
"jane" <bidepan@.msn.com> wrote in message
news:1167926378.282209.127980@.6g2000cwy.googlegroups.com...
>I was confused with the non-unicode when i insert chinese characters in
> a varchar field using asp page, it accepted the characters and
> displayed correctly. It is good for me, but i was wondering what
> exactly non-unicode is and why acting like that way(I expected to
> receive strange characters in the feedback asp page but i didn't). Can
> anyone tell me the reason?
>|||Thanks for your reply MC, it's all default setting. I can't understand
your other question. It appears to be like ऒझ stored in the
database but it is displayed correctly(the correct character)in asp
pages. what i'm conserning is why the Unicode data can be stored in a
varchar(for non-Unicode) column and retrieved correctly, which i
suppose should not.
On Jan 4, 1:03 pm, "MC" <marko.NOSPAMc...@.gmail.com> wrote:
> What is the collation of the column in the table? Also, did you try
> comparing two columns with that data?
> MC
> "jane" <bide...@.msn.com> wrote in messagenews:1167926378.282209.127980@.6g2000cwy.googlegroups.com...
>
> >I was confused with the non-unicode when i insert chinese characters in
> > a varchar field using asp page, it accepted the characters and
> > displayed correctly. It is good for me, but i was wondering what
> > exactly non-unicode is and why acting like that way(I expected to
> > receive strange characters in the feedback asp page but i didn't). Can
> > anyone tell me the reason... Hide quoted text -- Show quoted text -|||Well, basically you're reading the same characters you inserted. My second q
was, did you try comparing on those characters? For example:
Table1 has col1 varchar(100)
Table2 has col2 varchar(100)
Insert same string in both tables (chinese chars). Then see what happens
when you say WHERE col1 = col2 or something like that (thats what I meant by
comparing values). Try the same thing with parameter and comparing values
in columns to parameter...
MC
"jane" <bidepan@.msn.com> wrote in message
news:1167936168.510779.204100@.s80g2000cwa.googlegroups.com...
> Thanks for your reply MC, it's all default setting. I can't understand
> your other question. It appears to be like ऒझ stored in the
> database but it is displayed correctly(the correct character)in asp
> pages. what i'm conserning is why the Unicode data can be stored in a
> varchar(for non-Unicode) column and retrieved correctly, which i
> suppose should not.
> On Jan 4, 1:03 pm, "MC" <marko.NOSPAMc...@.gmail.com> wrote:
>> What is the collation of the column in the table? Also, did you try
>> comparing two columns with that data?
>> MC
>> "jane" <bide...@.msn.com> wrote in
>> messagenews:1167926378.282209.127980@.6g2000cwy.googlegroups.com...
>>
>> >I was confused with the non-unicode when i insert chinese characters in
>> > a varchar field using asp page, it accepted the characters and
>> > displayed correctly. It is good for me, but i was wondering what
>> > exactly non-unicode is and why acting like that way(I expected to
>> > receive strange characters in the feedback asp page but i didn't). Can
>> > anyone tell me the reason... Hide quoted text -- Show quoted text -
>

I'm confused with the non-unicode

I was confused with the non-unicode when i insert chinese characters in
a varchar field using asp page, it accepted the characters and
displayed correctly. It is good for me, but i was wondering what
exactly non-unicode is and why acting like that way(I expected to
receive strange characters in the feedback asp page but i didn't). Can
anyone tell me the reason?What is the collation of the column in the table? Also, did you try
comparing two columns with that data?
MC
"jane" <bidepan@.msn.com> wrote in message
news:1167926378.282209.127980@.6g2000cwy.googlegroups.com...
>I was confused with the non-unicode when i insert chinese characters in
> a varchar field using asp page, it accepted the characters and
> displayed correctly. It is good for me, but i was wondering what
> exactly non-unicode is and why acting like that way(I expected to
> receive strange characters in the feedback asp page but i didn't). Can
> anyone tell me the reason?
>|||Thanks for your reply MC, it's all default setting. I can't understand
your other question. It appears to be like ऒझ stored in the
database but it is displayed correctly(the correct character)in asp
pages. what i'm conserning is why the Unicode data can be stored in a
varchar(for non-Unicode) column and retrieved correctly, which i
suppose should not.
On Jan 4, 1:03 pm, "MC" <marko.NOSPAMc...@.gmail.com> wrote:[vbcol=seagreen]
> What is the collation of the column in the table? Also, did you try
> comparing two columns with that data?
> MC
> "jane" <bide...@.msn.com> wrote in messagenews:1167926378.282209.127980@.6g2
000cwy.googlegroups.com...
>
>|||Well, basically you're reading the same characters you inserted. My second q
was, did you try comparing on those characters? For example:
Table1 has col1 varchar(100)
Table2 has col2 varchar(100)
Insert same string in both tables (chinese chars). Then see what happens
when you say WHERE col1 = col2 or something like that (thats what I meant by
comparing values). Try the same thing with parameter and comparing values
in columns to parameter...
MC
"jane" <bidepan@.msn.com> wrote in message
news:1167936168.510779.204100@.s80g2000cwa.googlegroups.com...
> Thanks for your reply MC, it's all default setting. I can't understand
> your other question. It appears to be like ऒझ stored in the
> database but it is displayed correctly(the correct character)in asp
> pages. what i'm conserning is why the Unicode data can be stored in a
> varchar(for non-Unicode) column and retrieved correctly, which i
> suppose should not.
> On Jan 4, 1:03 pm, "MC" <marko.NOSPAMc...@.gmail.com> wrote:
>

I'm confused about "Null" fields and Default values

I have defined a table with a "DateCreated" field. I put "(getdate())"
(w/o quotes) in the Default Value field when I defined the table (Allow
Nulls). First I tried adding records (via a VB.NET app) and the records
added fine, but no dates. So I turned off "Allow Nulls". Now, I am getting
an error if I don't fill in the date field from the app..."Null not
allowed." I thought (wrongly, obviously) that the point of "Default Value"
was to furnish a "Default Value."
Please straighten me out...
TIA,
Larry WoodsNULL != empty string! If your column doesn't allow NULLs, and you want to
supply a default value, you should not specify that column at all in your
insert statement.
"Larry Woods" <larry@.lwoods.com> wrote in message
news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> I have defined a table with a "DateCreated" field. I put "(getdate())"
> (w/o quotes) in the Default Value field when I defined the table (Allow
> Nulls). First I tried adding records (via a VB.NET app) and the records
> added fine, but no dates. So I turned off "Allow Nulls". Now, I am
getting
> an error if I don't fill in the date field from the app..."Null not
> allowed." I thought (wrongly, obviously) that the point of "Default
Value"
> was to furnish a "Default Value."
> Please straighten me out...
> TIA,
> Larry Woods
>|||I had "hoped" that I could either (1) insert the date myself or, if not,
then SQL would insert the default date (getdate()) that I had specified.
Guess not, huh?
Larry
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> NULL != empty string! If your column doesn't allow NULLs, and you want to
> supply a default value, you should not specify that column at all in your
> insert statement.
>
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
> news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > I have defined a table with a "DateCreated" field. I put "(getdate())"
> > (w/o quotes) in the Default Value field when I defined the table (Allow
> > Nulls). First I tried adding records (via a VB.NET app) and the records
> > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> getting
> > an error if I don't fill in the date field from the app..."Null not
> > allowed." I thought (wrongly, obviously) that the point of "Default
> Value"
> > was to furnish a "Default Value."
> >
> > Please straighten me out...
> >
> > TIA,
> >
> > Larry Woods
> >
> >
>|||> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
Yes, you can! You need to make sure you understand what "it not" means.
Leave the column out of your INSERT statement, rather than setting it (or
not setting it).
CREATE TABLE blat
(
id INT,
dateColumn DATETIME DEFAULT GETDATE()
)
-- now compare:
INSERT blat(id) VALUES(1)
INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
INSERT blat(id, dateColumn) VALUES(3, NULL)
INSERT blat(id, dateColumn) VALUES(4, '')
SELECT * FROM blat
DROP TABLE blat|||Aaron is it possible to do somthing like this, I'm sure I've read it
somewhere:
INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Al.
On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
<aaron@.TRASHaspfaq.com> wrote:
>> I had "hoped" that I could either (1) insert the date myself or, if not,
>> then SQL would insert the default date (getdate()) that I had specified.
>> Guess not, huh?
>Yes, you can! You need to make sure you understand what "it not" means.
>Leave the column out of your INSERT statement, rather than setting it (or
>not setting it).
>CREATE TABLE blat
>(
> id INT,
> dateColumn DATETIME DEFAULT GETDATE()
>)
>-- now compare:
>INSERT blat(id) VALUES(1)
>INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
>INSERT blat(id, dateColumn) VALUES(3, NULL)
>INSERT blat(id, dateColumn) VALUES(4, '')
>SELECT * FROM blat
>DROP TABLE blat
>|||> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Sure. You can use the DEFAULT keyword to let SQL Server apply a
timestamp/rowversion, value generated by a default constraint or a NULL. Not
an identity, though.
--
Tibor Karaszi
"Harag" <harag@.softGETRIDOFCAPLETTERShome.net> wrote in message
news:ro2cqv86knq0mjl89av3enng7nv3s4gemd@.4ax.com...
> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
> Al.
> On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
> <aaron@.TRASHaspfaq.com> wrote:
> >> I had "hoped" that I could either (1) insert the date myself or, if
not,
> >> then SQL would insert the default date (getdate()) that I had
specified.
> >> Guess not, huh?
> >
> >Yes, you can! You need to make sure you understand what "it not" means.
> >Leave the column out of your INSERT statement, rather than setting it (or
> >not setting it).
> >
> >CREATE TABLE blat
> >(
> > id INT,
> > dateColumn DATETIME DEFAULT GETDATE()
> >)
> >
> >-- now compare:
> >INSERT blat(id) VALUES(1)
> >INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
> >INSERT blat(id, dateColumn) VALUES(3, NULL)
> >INSERT blat(id, dateColumn) VALUES(4, '')
> >
> >SELECT * FROM blat
> >
> >DROP TABLE blat
> >
> >
>|||To All:
Thanks for the advice. The problem comes down to this: I am using ADO.NET
and a Data Adapter. The Data Adapter gens the INSERT and obviously doesn't
handle the DEFAULT... The INSERT has a placeholder defined for the date
field, and seemingly no logic to handle the test for a default value. Guess
I will have to plug in the date myself.
My problem is that I am coming from the "kiddie" world of Access, and it
handled this situation just fine; i.e., if you didn't enter a value it
plugged in the default.
Oh, well.
Thanks, again.
Larry
"Larry Woods" <larry@.lwoods.com> wrote in message
news:%23VyH%23hZoDHA.2272@.tk2msftngp13.phx.gbl...
> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
> Larry
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > NULL != empty string! If your column doesn't allow NULLs, and you want
to
> > supply a default value, you should not specify that column at all in
your
> > insert statement.
> >
> >
> >
> >
> >
> > "Larry Woods" <larry@.lwoods.com> wrote in message
> > news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > > I have defined a table with a "DateCreated" field. I put
"(getdate())"
> > > (w/o quotes) in the Default Value field when I defined the table
(Allow
> > > Nulls). First I tried adding records (via a VB.NET app) and the
records
> > > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> > getting
> > > an error if I don't fill in the date field from the app..."Null not
> > > allowed." I thought (wrongly, obviously) that the point of "Default
> > Value"
> > > was to furnish a "Default Value."
> > >
> > > Please straighten me out...
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> >
> >
>|||Hi Larry,
Thanks for your feedback. I think this article will help you a lot.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I found the Google response. It says to put the default value in the xsd
property for the field. If I want either "now()" or "date()" as the
default, how do I specify that? I tried both and got errors both times. By
looking at the XML I can see why, I just don't know enough about the format
of the XSD to know how to specify "code" in the XSD.
Please advise...
TIA,
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:RTDs5$toDHA.2700@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback. I think this article will help you a lot.
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
>
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
>
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
> 6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Larry,
Thanks for your feedback. As I understand, you are using ADO.NET with
Dataadapter to connect SQL Server. You want to find a way to set the
default value of the column before you update the dataset. If I have
misunderstood, please feel free to let me know.
Based on my research, I would like you to try to add the following
statements in the codes to see if they solve your problem.
this.dataset11.Tables["<TableName>"].Columns["<ColumnName>"].DefaultValue =DateTime.Now;
dataset11 is the name of the Dataset object.
For additional information regarding this issue, please refer to the
following article.
DataColumn.DefaultValue Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataColumnClassDefaultValueTopic.asp
Also, it seems that current problem in this issue is related to ADO.NET
programming. I think the current news group is not the best one for this
problem. To resolve this problem, you may need to program the code with
DefaultValue property of the DataColumn. Therefore, I suggest that you post
this question in the microsoft.public.dotnet.framework.adonet newsgroup,
which is primarily for issues involving ADO.NET programming.
The reason why we recommend posting appropriately is you will get the most
qualified pool of respondents, and other partners who read the newsgroups
regularly can either share their knowledge or learn from your interaction
with us. I hope the problem can be resolved quickly.
Thank you for using our Newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.