Image without code!

Is it possible to insert a image in SQL Management Studio without use codes?

Like you search the image with a explorer window, click on it and... done!

The short answer is No.

Now it might be possible to visit a website that handles images for you -but it will be using SQL Code behind the scenes to SELECT/INSERT images for the browser.

|||As Arnie referred you have to depend upon the code to perform this, may be with a VBscript or any .NET script to attach that image.

image resource returned in code

I'm trying to display an image that has been returned from an external assembly, but am having no luck at all.

Essentially, I have a utility class that goes to a resource file and pulls out a JPG and returns it. I have set the value property of the image control on the report to hit this assembly, like so :

= Code.getLogo()

where the function Code.getLogo() actually interfaces with the utility class.

I have tried several variations of this code. I have returned the image as an Image object, a JPG, a TIFF and a BMP. I've also been adventurous and tried converting it to a base64 string before it gets to the report and then having the report convert it back to bytes before shoving it into the image value. Consistently I get this error :

The value expression used in image ‘logo’ returned a data type that is not valid.

A reply to a similar post on the www.sqlreportingservices.net site suggested that it's a Code Access Security issue, but I'm sure it's not, because when I dump my base64 string into a text field, it fills half the page.

What is this control expecting? Any thoughts?

Have you tried setting Image.Source to Database? If you specify Embedded, the value of Value is the resource's name embedded in the report, not the image's binary data. If you specify External, it is expecting a URL path to the image. Specifying Database indicates that the image contents is coming from a database field and Value is then the image's binary data. It's not actually coming from the database in your case, but Image should interpret the Value properly all the same.|||

Yes, I did indeed have it set to Embedded.

When I set to Database, I have a whole new set of errors, but they seem more straightforward. I'll fiddle with it for a while and see what I can come up with.

Got me over a big hurdle. Thanks.

|||Next you'll want to make sure your MIMEType is set correctly. If you're returning JPEG data, you want to set your MIMEType to image/jpeg. If you have a mismatch, it will try to interpret your JPEG as a BMP (i.e. MIMEType = image/bmp) rather than the JPEG that it actually is.|||

Your function has to return the image as byte array (i.e. byte[]). In addition, the image type has to be set to "Database", otherwise the Value property is not interpreted as the actual image data.

--Robert

|||Awesome, it worked. When I changed my Source property to Database, I also took out my conversion statements so that it would return just an image file. Once I put the conversion-to-byte-array logic back in, it worked.

Thank ye very much for your help.

image load

Hi all

I wonder if someone can help me with a snippit of code or a referance to a tutorial.

I have my database grid veiw on a vb 2005 form and the data is related to equipment data capturing.

With each set of data I want a image to be loaded into a image box to show the piece of equipment.

If anyone can help with the code needed it will be much appreciated.

Thanks

Rob

Hi,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=622943&SiteID=1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Image data type

I have been asked to write a piece of code that will insert an image object into a database using a stored procedure and the Microsoft Enterprise Library. Has anyone done this before? Do you have any code examples about how to update a database with an image datatype that needs to be chunked, etc...

In this instance, I need to open up a word document and save the contents as an image in a database.

basically you want to get the uploaded file into a byte array and then you can assign it to a sql param of type image, no chunking needed

here's a snip from one of my projects. I'm using business objects so its not showing the actual sql code but behind the scenes it is just assigning it to a param of type image

byte[] fileBytes = new byte[replacementFileInput.ContentLength];
Stream contentStream = replacementFileInput.FileContent;

contentStream.Read(fileBytes, 0, (int) replacementFileInput.ContentLength);
contentStream.Close();

DocumentFile newFile = new DocumentFile(true);
newFile.DocumentID = originalDocument.ID;
newFile.DocumentType = documentType.Name;
newFile.DocumentImage = fileBytes;
newFile.Save();

I should note that in my example I'm usingNeatUpload, so replacementFileInput is the NeatUpload file input and has a little different syntax then the regular .NET file input

NeatUpload can handle large file uploads gracefully with a progress bar and is free and open source

Hope it helps,

Joe|||

I downloaded the NeatUpload. This does look pretty cool. However, in the instance I'm currently in, I have a Word Document already on the server that I need to convert to a binary object and upload to SQL. The answer is probably right in front of me, so... following your initial tip.

How do I convert an existingWord Document into a byte array?

|||Yes, you can easily do this. In my previous example I was using the contentStream from the uploaded file, but any subclass of stream could be used, so in your case FileStream which you can get with something like this:

FileStream fileStream = File.Open("pathtoyourfile", FileMode.Open);
byte[] fileBytes = new byte[fileStream.Length];
fileStream.Read(fileBytes, 0, (int)fileStream.Length);

now fileBytes has the file and you can assign it to your sql image param

Hope it helps,

Joe|||

This is great, thanks! I think I got the record in there... Now I just have to read it!

Thanks for your help, Joe.

-Scott

|||

Okay, I have been struggling to convert the following code from C# to VB. Particularly at the point of creating the New Byte. Can anyone help?

SqlCommand cmdSelect=new SqlCommand("select Picture" +
" from tblImgData whereID=@.ID",this.sqlConnection1);
cmdSelect.Parameters.Add("@.ID",SqlDbType.Int,4);
cmdSelect.Parameters["@.ID"].Value=this.editID.Text;

this.sqlConnection1.Open();
byte[] barrImg=(byte[])cmdSelect.ExecuteScalar();
string strfn=Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs=new FileStream(strfn,
FileMode.CreateNew, FileAccess.Write);
fs.Write(barrImg,0,barrImg.Length);
fs.Flush();
fs.Close();
pictureBox1.Image=Image.FromFile(strfn);

Im sure this is an easy one...Error trap to skip over a "bad" object.

Hello, I have the following code to iterate through each view in a SQL
Server and call the "sp_refreshview" command against it. It works
great until it finds a view that is damaged, or otherwise cannot be
refreshed. Then the whole routine stops working.

Can someone please help me re-write this code so that any views that
fail the "sp_refreshview" command get skipped. I'm sure it's just a
matter of putting some basic error trapping into the loop, but I've had
a few goes at it and failed.

Many thanks.

DECLARE @.DatabaseObject varchar(255)
DECLARE ObjectCursor CURSOR
FOR SELECT table_name FROM information_schema.tables WHERE table_type =
'view'
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @.DatabaseObject
Print @.DatabaseObject + ' was successfully refreshed.'
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO(rod.weir@.gmail.com) writes:
> Hello, I have the following code to iterate through each view in a SQL
> Server and call the "sp_refreshview" command against it. It works
> great until it finds a view that is damaged, or otherwise cannot be
> refreshed. Then the whole routine stops working.
> Can someone please help me re-write this code so that any views that
> fail the "sp_refreshview" command get skipped. I'm sure it's just a
> matter of putting some basic error trapping into the loop, but I've had
> a few goes at it and failed.

If you are on SQL 2005, lookup TRY-CATCH in Books Online.

If you are on SQL 2000, you could possibly do the linked-server trick:
http://www.sommarskog.se/error-hand...#linked-servers.

I'm not into refreshing views myself, but I can't think of a way to
detect this condition before-hand.

--
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|||Thanks Erland,

I need to refresh all views because there are some views that have
embedded views within them, using a Select * statement. When the
underlying view changes (new column etc), the parent view does not pick
up the new column in the embedded view that it references.

Using SQL Server 2000. Surely there must be a simple way to trap the
error and skip over it right?

Perhaps just after the following line...

EXEC sp_refreshview @.DatabaseObject

...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
primarily a VB developer, so this TSQL has got me a little puzzled.

I'll give your website a read. Thanks again.|||> ...you examine @.@.Error and ignore or continue in the loop?

Some errors will abort the batch so you are SOL after the error. If the
linked server doesn't work for you, you might try preceeding the
sp_refreshview with a select statement with SET FMTONLY ON. That will allow
you detect the error and skip the sp_refreshview for problem views.

DECLARE @.DatabaseObject nvarchar(261)
DECLARE ObjectCursor CURSOR FAST_FORWARD READ_ONLY
FOR SELECT
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'VIEW'

OPEN ObjectCursor
WHILE 1 = 1
BEGIN
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
IF @.@.FETCH_STATUS = -1 BREAK
PRINT 'Refreshing view ' + @.DatabaseObject
EXEC ('SET FMTONLY ON SELECT * FROM ' + @.DatabaseObject)
IF @.@.ERROR = 0
BEGIN
EXEC sp_refreshview @.DatabaseObject
PRINT 'View ' + @.DatabaseObject + ' refreshed'
END
ELSE
BEGIN
PRINT 'Error refreshing view ' + @.DatabaseObject
END
END

CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rod.weir@.gmail.com> wrote in message
news:1143705825.644611.51750@.u72g2000cwu.googlegro ups.com...
> Thanks Erland,
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement. When the
> underlying view changes (new column etc), the parent view does not pick
> up the new column in the embedded view that it references.
> Using SQL Server 2000. Surely there must be a simple way to trap the
> error and skip over it right?
> Perhaps just after the following line...
> EXEC sp_refreshview @.DatabaseObject
> ...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
> primarily a VB developer, so this TSQL has got me a little puzzled.
> I'll give your website a read. Thanks again.|||[My newsserver had an outage, and my original post got lost. Now that it's
back, I'm reposting]

(rod.weir@.gmail.com) writes:
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement.

Did anyone tell you that this is bad practice? :-)

> Using SQL Server 2000. Surely there must be a simple way to trap the
> error and skip over it right?
> Perhaps just after the following line...
> EXEC sp_refreshview @.DatabaseObject
> ...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
> primarily a VB developer, so this TSQL has got me a little puzzled.

The problem is that there are quite few errors that abort the batch, and
those you cannot trap easily in SQL 2000. I seem to recall that refreshview
errors belongs to this group. The linked-server trick is a serious kludge,
but for this case it could be worth the pain.

Then again, if you are a VB developer, just code the loop in a VB program
or in VB script. That's probably easier than setting up linked servers for
this task.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Dan,

Many thanks for your response. This code does exactly what I'm after.
It skipped over the bad queries and kept refreshing the good ones. I
modified the following line to speed it up a little bit.

EXEC ('SET FMTONLY ON SELECT * FROM ' + @.DatabaseObject + 'Where 1=0')

Notice the Where 1 = 0 clause? Much quicker now.

Thanks Dan and Erland. Problem solved.

p.s. Erland. I am going to start another thread on the evils of
embedded queries. I have heard a lot of people say that this is a bad
practice, however I've never heard any really compelling evidence to
say why.

IIS/MS SQL/ASP... cant connect

I have an ASP script with the following code in the global.asa:

Application("DBConnectString")="DSN=Test_Server;UID=Michael;PWD=xxxx"

But I get the error:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/TheResort/i_asputils.asp, line 8

Problem:

I setup the ODBC portion and it correctly saw the MS SQL server, and the test connection worked fine. Yet when I ever I enter that ODBC name "Test_Server", and the password of the Win XP Pro user I am signed in as, it fails to connect.

How can I be sure what domain,user,password I need to enter?Ttry using "DSN=asset;UID=;PWD=" and don't forget to insert the user id and password.

Also, when you set up the odbc "system" dsn, remember to click on the test connection button to verify the connection.

http://www.google.com/url?sa=U&start=9&q=http://support.microsoft.com/support/kb/articles/Q169/3/77.asp&e=7415 information.

IIS Authentication for RDA not working

My Code:

rda.InternetLogin = "domain\username"

rda.InternetPassword = "password"

rda.SubmitSql("SELECT * FROM Table", rdaOleDbConnectString)

I have a client trying to use my application through SSL.

https://DOMAINCONTROLLER/sqlmobile/sqlcesa30.dll

they do not have certificiates setup...and it isn't working at all.

Any help?

You cannot use SSL without a public certificate installed on the server, and that certificate's root must be present on the device.

IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown', DATEPART(yyyy.SalesAnalyse.Verz

Hello,
I think i am missing something. This code give's an error... Can someone
tell me what it is'
IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown',
DATEPART(yyyy.SalesAnalyse.Verzenddatum))
tnx in advance..
eric1) IIF is not a function is T-SQL.
2) ISNULL accepts 2 arguments, not 1.
3) DATEPART accepts 2 arguments; separate the datepart (yyyy) from the date
with a comma, not a period.
I assume you want this?
ISNULL(CAST(DATEPART(yyyy, SalesAnalyse.Verzenddatum) AS VARCHAR(8)),
'unknown')
Jacco Schalkwijk
SQL Server MVP
"Judith van der Niet" <jniet@.mit.com> wrote in message
news:e5QpjKfDFHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I think i am missing something. This code give's an error... Can someone
> tell me what it is'
> IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown',
> DATEPART(yyyy.SalesAnalyse.Verzenddatum))
> tnx in advance..
> eric
>

Iif use

I am using this code, trying to set the bit value of @.temp1 using the Iif statement:

declare @.temp1 bit,
@.var1 varchar,
@.var2 varchar,
@.var3 varchar

select @.var1='testing', @.var2='testing2'

Select @.temp1 = Iif((@.var1 = @.var2), 1, 0)

select @.temp1

I get this error:
Line 9: Incorrect syntax near '='.

Any suggestions?
ThanksI belive yu are using IIF in the wrong context try:

case when @.var1 = @.var2 then 1 else 0 end|||declare @.temp1 bit,
@.var1 varchar,
@.var2 varchar,
@.var3 varchar

select @.var1='testing', @.var2='testing2', @.temp1=0

Select @.temp1 = case when @.var1 = @.var2 then 1 else 0 end

select @.temp1 as result

OK, I changed it to the codeabove. But now result comes back as 1, how is that possible since the 2 variables arent equal?|||try:

declare @.temp1 bit,
@.var1 varchar(10),
@.var2 varchar(10),
@.var3 varchar(10)
select @.var1='testing'
, @.var2='testing2'
, @.temp1=0
Select @.temp1 = case when @.var1 = @.var2 then 1 else 0 end
select @.temp1 as result|||That worked. Wonder why the first one didnt. Thanks!

Originally posted by Paul Young
try:

declare @.temp1 bit,
@.var1 varchar(10),
@.var2 varchar(10),
@.var3 varchar(10)
select @.var1='testing'
, @.var2='testing2'
, @.temp1=0
Select @.temp1 = case when @.var1 = @.var2 then 1 else 0 end
select @.temp1 as result|||By default Varchar (or Char, NChar, NVarchar, etc.) is a 1 character string. Adding the (10) makes it a 10 byte string.

IIF PROBLEM

in my table if ProductPrices_ID >15 then I want to change this field else
i want it to not change.
i wrote that code but it returns an error. how can i fix it ?
SELECT iif( ProductPrices_ID > 15 ; 0 ;ProductPrices_ID ) as pele FROM
ProductPricesThere is no IIF in T-SQL. For a comparable expression, look up CASE in SQL
Server Books Online.
Anith|||SELECT pele = CASE
WHEN ProductPrices_ID > 15 THEN 0
ELSE ProductPrices_ID
END
FROM ProductPrices
Coming from Access? (There is no IIF in SQL Server.) This article might
help:
http://www.aspfaq.com/2214
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:u2qnFnr6FHA.1248@.TK2MSFTNGP14.phx.gbl...
> in my table if ProductPrices_ID >15 then I want to change this field
> else i want it to not change.
> i wrote that code but it returns an error. how can i fix it ?
> SELECT iif( ProductPrices_ID > 15 ; 0 ;ProductPrices_ID ) as pele FROM
> ProductPrices
>
>

IIF & IsNull Functions

Below in "ACCESS SQL CODE" is a query that I use in Access. It uses the IIf
and IsNull functions to concatenate the full name. If there is a middle
initial, my statement returns a comma after the LastName field, the middle
initial followed by a period. If it doesn't exist, sql returns nothing.
I'm upsizing the access database to an access adp project file and
converting my queries into views. When I try to run the below "SQL VIEW
CODE", I get an error saying "The isnull function requires 2 arguments".
I thought my syntax was right. What am I doing wrong? Is the IIf function
causing the problem in my Access ADP view?
ACCESS SQL CODE *************
SELECT LastName, FirstName, MiddleInit, LastName & ", " & FirstName &
IIf(IsNull(MiddleInit),""," " & MiddleInit & ".") AS Name
FROM myTable
SQL VIEW CODE ****************
SELECT FirstName, t_Users.MiddleInit,
LastName + ", " + FirstName + IIf(IsNull(MiddleInit),'',' ' + MiddleInit &
'.') AS Name
FROM myTableOn Wed, 26 Apr 2006 16:54:07 -0500, scott wrote:

>SELECT FirstName, t_Users.MiddleInit,
>LastName + ", " + FirstName + IIf(IsNull(MiddleInit),'',' ' + MiddleInit &
>'.') AS Name
>FROM myTable
Hi Scott,
IIf and IsNull are Access-specific functions that won't work in SQL
Server or in any ANSI-compliant relational database.
SQL Server also has an ISNULL function; it's function is roughly the
same as Access' Nz function. And it''s allso non-ANSI-compliant.
The ANSI-compliant version of yoour query (that will work on SQL Server
and other ANNSI-compliant databases, but not on Access) is
SELECT FirstName, t_Users.MiddleInit,
LastName + ', ' + FirstName + COALESCE(' ' + MiddleInit + '.'),
'') AS Name
FROM myTable
Hugo Kornelis, SQL Server MVP|||thanks.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:5ssv42tvikim4oi8cnl3b6taln8bq76hhf@.
4ax.com...
> On Wed, 26 Apr 2006 16:54:07 -0500, scott wrote:
>
> Hi Scott,
> IIf and IsNull are Access-specific functions that won't work in SQL
> Server or in any ANSI-compliant relational database.
> SQL Server also has an ISNULL function; it's function is roughly the
> same as Access' Nz function. And it''s allso non-ANSI-compliant.
> The ANSI-compliant version of yoour query (that will work on SQL Server
> and other ANNSI-compliant databases, but not on Access) is
> SELECT FirstName, t_Users.MiddleInit,
> LastName + ', ' + FirstName + COALESCE(' ' + MiddleInit + '.'),
> '') AS Name
> FROM myTable
> --
> Hugo Kornelis, SQL Server MVP