i have the below code to insert a file in a filestream table in sql server 
2008, but when i run it i get an error that there is a error near the From 
word so maybe my sql statment have some syntext error ?

connObject = New SqlConnection(My.Settings.AlTaybatConnectionString)
        connObject.Open()

        str = "DECLARE @img AS VARBINARY(MAX)" + "SELECT @img = 
CAST(bulkcolumn AS VARBINARY(MAX)" + _
              "FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) 
AS x" + _
              "INSERT INTO Attachments (ID, SvrFile)" + "SELECT NEWID(), @img"

        comObject = New SqlCommand
        comObject.CommandType = CommandType.Text
        comObject.CommandText = str
        comObject.Connection = connObject
        comObject.ExecuteNonQuery()

        comObject.Dispose()
        connObject.Close()
        connObject.Dispose()

Hi Ammar,
It looked that in your T-SQL, there was no blank space between two strings. 
What is the result if you add a blank space like this?
============================
        str = "DECLARE @img AS VARBINARY(MAX) " + "SELECT @img = 
CAST(bulkcolumn AS VARBINARY(MAX)" + _
              " FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB 
) 
AS x;" + _
              " INSERT INTO Attachments (ID, SvrFile)" + " SELECT NEWID(), 
@img"
============================

Best regards,
Charles Wang

"Ammar S. Mitoori" <msnews08@nospam.nospam> wrote in message 
news:B9DEE54D-7506-46A0-B55F-1EA282514C06@microsoft.com...
> i have the below code to insert a file in a filestream table in sql server
> 2008, but when i run it i get an error that there is a error near the From
> word so maybe my sql statment have some syntext error ?
>
> connObject = New SqlConnection(My.Settings.AlTaybatConnectionString)
>        connObject.Open()
>
>        str = "DECLARE @img AS VARBINARY(MAX)" + "SELECT @img =
> CAST(bulkcolumn AS VARBINARY(MAX)" + _
>              "FROM OPENROWSET(BULK 
> 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB )
> AS x" + _
>              "INSERT INTO Attachments (ID, SvrFile)" + "SELECT NEWID(), 
> @img"


as well as the missing spaces and semi-colon noted by Charles Wang (you need 
one before SELECT, one before FROM, and one before INSERT), you're also 
missing a ) before the FROM to close your CAST expression - you have

CAST(bulkcolumn AS VARBINARY(MAX)

when it should be

CAST(bulkcolumn AS VARBINARY(MAX))

Try this:


        str = "DECLARE @img AS VARBINARY(MAX); " + "SELECT @img = 
CAST(bulkcolumn AS VARBINARY(MAX)) " + _
              "FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) 
AS x; " + _
              "INSERT INTO Attachments (ID, SvrFile); " + "SELECT NEWID(), 
@img"


-- 
Dan 

hi Dan

i got the below error when i tried your code

Incorrect syntax near ';'

also there is a missing " before the CAST :)

"Dan" wrote:

> 
> "Ammar S. Mitoori" <msnews08@nospam.nospam> wrote in message 
> news:B9DEE54D-7506-46A0-B55F-1EA282514C06@microsoft.com...
> > i have the below code to insert a file in a filestream table in sql server
> > 2008, but when i run it i get an error that there is a error near the From
> > word so maybe my sql statment have some syntext error ?
> >
> > connObject = New SqlConnection(My.Settings.AlTaybatConnectionString)
> >        connObject.Open()
> >
> >        str = "DECLARE @img AS VARBINARY(MAX)" + "SELECT @img =
> > CAST(bulkcolumn AS VARBINARY(MAX)" + _
> >              "FROM OPENROWSET(BULK 
> > 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB )
> > AS x" + _
> >              "INSERT INTO Attachments (ID, SvrFile)" + "SELECT NEWID(), 
> > @img"
> 
> 
> as well as the missing spaces and semi-colon noted by Charles Wang (you need 
> one before SELECT, one before FROM, and one before INSERT), you're also 
> missing a ) before the FROM to close your CAST expression - you have
> 
> CAST(bulkcolumn AS VARBINARY(MAX)
> 
> when it should be
> 
> CAST(bulkcolumn AS VARBINARY(MAX))
> 
> Try this:
> 
> 
>         str = "DECLARE @img AS VARBINARY(MAX); " + "SELECT @img = 
> CAST(bulkcolumn AS VARBINARY(MAX)) " + _
>               "FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) 
> AS x; " + _
>               "INSERT INTO Attachments (ID, SvrFile); " + "SELECT NEWID(), 
> @img"
> 
> 
> -- 
> Dan 
> 
> .
> 

hi dan i used the below code dont know why they made the window here so small 
so the line will brake cant it be bigger, anyway below is the code

        str = "DECLARE @img AS VARBINARY(MAX); " + "SELECT @img = 
CAST(bulkcolumn AS ARBINARY(MAX)) " + _
              "FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) 
AS x; " + _
              "INSERT INTO Attachments (ID, SvrFile); " + "SELECT NEWID(), 
@img"
same as urs but i got the error in the last post

regards

"Ammar S. Mitoori" wrote:

> hi Dan
> 
> i got the below error when i tried your code
> 
> Incorrect syntax near ';'
> 
> also there is a missing " before the CAST :)
> 
> "Dan" wrote:
> 
> > 
> > "Ammar S. Mitoori" <msnews08@nospam.nospam> wrote in message 
> > news:B9DEE54D-7506-46A0-B55F-1EA282514C06@microsoft.com...
> > > i have the below code to insert a file in a filestream table in sql server
> > > 2008, but when i run it i get an error that there is a error near the From
> > > word so maybe my sql statment have some syntext error ?
> > >
> > > connObject = New SqlConnection(My.Settings.AlTaybatConnectionString)
> > >        connObject.Open()
> > >
> > >        str = "DECLARE @img AS VARBINARY(MAX)" + "SELECT @img =
> > > CAST(bulkcolumn AS VARBINARY(MAX)" + _
> > >              "FROM OPENROWSET(BULK 
> > > 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB )
> > > AS x" + _
> > >              "INSERT INTO Attachments (ID, SvrFile)" + "SELECT NEWID(), 
> > > @img"
> > 
> > 
> > as well as the missing spaces and semi-colon noted by Charles Wang (you need 
> > one before SELECT, one before FROM, and one before INSERT), you're also 
> > missing a ) before the FROM to close your CAST expression - you have
> > 
> > CAST(bulkcolumn AS VARBINARY(MAX)
> > 
> > when it should be
> > 
> > CAST(bulkcolumn AS VARBINARY(MAX))
> > 
> > Try this:
> > 
> > 
> >         str = "DECLARE @img AS VARBINARY(MAX); " + "SELECT @img = 
> > CAST(bulkcolumn AS VARBINARY(MAX)) " + _
> >               "FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) 
> > AS x; " + _
> >               "INSERT INTO Attachments (ID, SvrFile); " + "SELECT NEWID(), 
> > @img"
> > 
> > 
> > -- 
> > Dan 
> > 
> > .
> > 

Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> hi dan i used the below code dont know why they made the window here so
> small so the line will brake cant it be bigger, anyway below is the
> code 
> 
>         str = "DECLARE @img AS VARBINARY(MAX); " + "SELECT @img = 
> CAST(bulkcolumn AS ARBINARY(MAX)) " + _
>            "FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) 
> AS x; " + _
>               "INSERT INTO Attachments (ID, SvrFile); " + "SELECT NEWID(), 
> @img"
> same as urs but i got the error in the last post
 
Remove the semicolon after INSERT. It appears that Dan inserted one
semicolon too many.

Also, when you build SQL Strings like this, add some debug so that you
easily can view the SQL String in whole. Then you would easily have posted
the error you had initially.

 str = " DECLARE @img AS VARBINARY(MAX); " + _
       " SELECT @img = CAST(bulkcolumn AS ARBINARY(MAX)) " + _
       " FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) " + _
       " AS x; " + _
       " INSERT INTO Attachments (ID, SvrFile) " + _
       "     SELECT NEWID(), @img"

Also, it helps to structure code in several lines that lines up, and 
have an initial space on all lines.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

thanx erland, from the way you organized the code i felt it will work
normally i do  he same but for me the editing window of msdn is so small 
that it cut the lines, but first  time i run the code i got the errorbinray 
is not a defined type then i noticed there is a "v" missing :)

so now if the file path i want to pass it as a parameter depending on file 
path selected from a file dialog control how that can be done ? and can i 
retrive the file extension from that path selected by the dialog ? is there a 
property or function to do so ? can i retrive also just the path, just the 
file name without extension ?

regards

"Erland Sommarskog" wrote:

> Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> > hi dan i used the below code dont know why they made the window here so
> > small so the line will brake cant it be bigger, anyway below is the
> > code 
> > 
> >         str = "DECLARE @img AS VARBINARY(MAX); " + "SELECT @img = 
> > CAST(bulkcolumn AS ARBINARY(MAX)) " + _
> >            "FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) 
> > AS x; " + _
> >               "INSERT INTO Attachments (ID, SvrFile); " + "SELECT NEWID(), 
> > @img"
> > same as urs but i got the error in the last post
>  
> Remove the semicolon after INSERT. It appears that Dan inserted one
> semicolon too many.
> 
> Also, when you build SQL Strings like this, add some debug so that you
> easily can view the SQL String in whole. Then you would easily have posted
> the error you had initially.
> 
>  str = " DECLARE @img AS VARBINARY(MAX); " + _
>        " SELECT @img = CAST(bulkcolumn AS ARBINARY(MAX)) " + _
>        " FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) " + _
>        " AS x; " + _
>        " INSERT INTO Attachments (ID, SvrFile) " + _
>        "     SELECT NEWID(), @img"
> 
> Also, it helps to structure code in several lines that lines up, and 
> have an initial space on all lines.
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 
> .
> 

Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> so now if the file path i want to pass it as a parameter depending on
> file path selected from a file dialog control how that can be done ? and
> can i retrive the file extension from that path selected by the dialog ?
> is there a property or function to do so ? can i retrive also just the
> path, just the file name without extension ? 
 

I'm afraid that you lost me entirely there. This is a forum for SQL
programming, and now you starting talking about getting file paths from a
dialog. The only dialogs that appear in SQL Server are those in Service
Broker, but it sounds to me that you are talking about a dialog in a GUI. 

I think you should ask that question in a forum devoted to the development
environment for your GUI code.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

ok erland and how about sending the file path in the last sql code as a 
parameter how can that be done ?

"Erland Sommarskog" wrote:

> Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> > so now if the file path i want to pass it as a parameter depending on
> > file path selected from a file dialog control how that can be done ? and
> > can i retrive the file extension from that path selected by the dialog ?
> > is there a property or function to do so ? can i retrive also just the
> > path, just the file name without extension ? 
>  
> 
> I'm afraid that you lost me entirely there. This is a forum for SQL
> programming, and now you starting talking about getting file paths from a
> dialog. The only dialogs that appear in SQL Server are those in Service
> Broker, but it sounds to me that you are talking about a dialog in a GUI. 
> 
> I think you should ask that question in a forum devoted to the development
> environment for your GUI code.
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> .
> 

Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> ok erland and how about sending the file path in the last sql code as a 
> parameter how can that be done ?
 
You have to use dynamic SQL. The syntax does unforunately permit for
variables.

But since you had your command in client code, it's not a big deal:

str = " DECLARE @img AS VARBINARY(MAX); " + _
       " SELECT @img = CAST(bulkcolumn AS ARBINARY(MAX)) " + _
       " FROM OPENROWSET(BULK '" + file + "',SINGLE_BLOB ) " + _
       " AS x; " + _
       " INSERT INTO Attachments (ID, SvrFile) " + _
       "     SELECT NEWID(), @img"


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Erland

so you mean the file is a string variable that store the path of the file ?

"Erland Sommarskog" wrote:

> Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> > ok erland and how about sending the file path in the last sql code as a 
> > parameter how can that be done ?
>  
> You have to use dynamic SQL. The syntax does unforunately permit for
> variables.
> 
> But since you had your command in client code, it's not a big deal:
> 
> str = " DECLARE @img AS VARBINARY(MAX); " + _
>        " SELECT @img = CAST(bulkcolumn AS ARBINARY(MAX)) " + _
>        " FROM OPENROWSET(BULK '" + file + "',SINGLE_BLOB ) " + _
>        " AS x; " + _
>        " INSERT INTO Attachments (ID, SvrFile) " + _
>        "     SELECT NEWID(), @img"
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 
> .
> 

Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> so you mean the file is a string variable that store the path of the file 
 
Yes. Anything strange with that? If you can put an SQL command in a string,
why not a file path?


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

hi Erland, ok so suppose for the below code i have to columns 
"FileDiscription" and "FileExt" i need to put a value for them in the same 
code below how that will be ? assuming the value will be stored in a variable 
same as the file variable ?

also there is some thing more complicated a have a class called clsIcon, it 
grabs the default icon associated with a file extension from a registry i 
posted the class below, so how to store the value return from the class 
inside a third column say its name is "extIcon" all in the same sql statment 
below ??!!

Regards

the class is

'=====================================================================================
'  clsIcon
'  class to work with icons
'=====================================================================================
'  Created By: Marc Cramer
'  Published Date: 12/31/2002
'  Legal Copyright: Marc Cramer © 12/31/2002
'=====================================================================================
'  Adapted From...
'  Author: spotchannel (spotchannel@hotmail.com)
'  Website: forum post at http://www.devcity.net/forums/topic.asp?tid=7422
'=====================================================================================

Imports System
Imports System.Drawing
Imports System.Runtime.InteropServices

Public Class clsIcon

	'=====================================================================================
	' Enumerations
	'=====================================================================================
	<Flags()> Private Enum SHGFI
		SmallIcon = &H1
		LargeIcon = &H0
		Icon = &H100
		DisplayName = &H200
		Typename = &H400
		SysIconIndex = &H4000
		UseFileAttributes = &H10
	End Enum

	Public Enum IconSize
		SmallIcon = 1
		LargeIcon = 0
	End Enum

	'=====================================================================================
	' Structures
	'=====================================================================================
	<StructLayout(LayoutKind.Sequential)> _
	Private Structure SHFILEINFO
		Public hIcon As IntPtr
		Public iIcon As Integer
		Public dwAttributes As Integer
		<MarshalAs(UnmanagedType.LPStr, SizeConst:=260)> Public szDisplayName As 
String
		<MarshalAs(UnmanagedType.LPStr, SizeConst:=80)> Public szTypeName As String

		Public Sub New(ByVal B As Boolean)
			hIcon = IntPtr.Zero
			iIcon = 0
			dwAttributes = 0
			szDisplayName = vbNullString
			szTypeName = vbNullString
		End Sub
	End Structure

	'=====================================================================================
	' API Calls
	'=====================================================================================
	Private Declare Auto Function SHGetFileInfo Lib "shell32" (ByVal pszPath As 
String, ByVal dwFileAttributes As Integer, ByRef psfi As SHFILEINFO, ByVal 
cbFileInfo As Integer, ByVal uFlagsn As SHGFI) As Integer

	'=====================================================================================
	' Functions and Procedures...
    
'=====================================================================================

	Public Shared Function GetDefaultIcon(ByVal Path As String, Optional ByVal 
IconSize As IconSize = IconSize.SmallIcon, Optional ByVal SaveIconPath As 
String = "") As Icon
		Dim info As New SHFILEINFO(True)
		Dim cbSizeInfo As Integer = Marshal.SizeOf(info)
        Dim flags As SHGFI = SHGFI.Icon Or SHGFI.UseFileAttributes
		flags = flags + IconSize
		SHGetFileInfo(Path, 256, info, cbSizeInfo, flags)
        GetDefaultIcon = Icon.FromHandle(info.hIcon)
		If SaveIconPath <> "" Then
			Dim FileStream As New IO.FileStream(SaveIconPath, IO.FileMode.Create)
			GetDefaultIcon.Save(FileStream)
			FileStream.Close()
		End If
	End Function	  'GetDefaultIcon(ByVal Path As String, Optional ByVal 
IconSize As IconSize = IconSize.SmallIcon, Optional ByVal SaveIconPath As 
String = "") As Icon
	'=====================================================================================
	Public Shared Function ImageToIcon(ByVal SourceImage As Image) As Icon
		' converts an image into an icon
		Dim TempBitmap As New Bitmap(SourceImage)
		ImageToIcon = Icon.FromHandle(TempBitmap.GetHicon())
		TempBitmap.Dispose()
	End Function	  'ImageToIcon(ByVal SourceImage As Image) As Icon
	'=====================================================================================

End Class




"Erland Sommarskog" wrote:

> Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> > ok erland and how about sending the file path in the last sql code as a 
> > parameter how can that be done ?
>  
> You have to use dynamic SQL. The syntax does unforunately permit for
> variables.
> 
> But since you had your command in client code, it's not a big deal:
> 
> str = " DECLARE @img AS VARBINARY(MAX); " + _
>        " SELECT @img = CAST(bulkcolumn AS ARBINARY(MAX)) " + _
>        " FROM OPENROWSET(BULK '" + file + "',SINGLE_BLOB ) " + _
>        " AS x; " + _
>        " INSERT INTO Attachments (ID, SvrFile) " + _
>        "     SELECT NEWID(), @img"
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 
> .
> 

Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> hi Erland, ok so suppose for the below code i have to columns
> "FileDiscription" and "FileExt" i need to put a value for them in the
> same code below how that will be ? assuming the value will be stored in
> a variable same as the file variable ?
>
> also there is some thing more complicated a have a class called clsIcon,
> it grabs the default icon associated with a file extension from a
> registry i posted the class below, so how to store the value return from
> the class inside a third column say its name is "extIcon" all in the
> same sql statment below ??!!

I'm sorry, but I don't grasp what you are talking about. The code
you posted now, includes no SQL statement, and I can't see any
relation to SQL.

Maybe you should post your questions to a newsgroup devoted to
Visual Basic .Net instead?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

hi

no it have see lets make it step by step, my question is 

 suppose for the below code i have to columns
"FileDiscription" and "FileExt" i need to put a value for them in the same
code below how that will be ? assuming the value will be stored in a variable
same as the file variable ?

regards


"Ammar S. Mitoori" wrote:

> hi Erland, ok so suppose for the below code i have to columns 
> "FileDiscription" and "FileExt" i need to put a value for them in the same 
> code below how that will be ? assuming the value will be stored in a variable 
> same as the file variable ?
> 
> also there is some thing more complicated a have a class called clsIcon, it 
> grabs the default icon associated with a file extension from a registry i 
> posted the class below, so how to store the value return from the class 
> inside a third column say its name is "extIcon" all in the same sql statment 
> below ??!!
> 
> Regards
> 
> the class is
> 
> '=====================================================================================
> '  clsIcon
> '  class to work with icons
> '=====================================================================================
> '  Created By: Marc Cramer
> '  Published Date: 12/31/2002
> '  Legal Copyright: Marc Cramer © 12/31/2002
> '=====================================================================================
> '  Adapted From...
> '  Author: spotchannel (spotchannel@hotmail.com)
> '  Website: forum post at http://www.devcity.net/forums/topic.asp?tid=7422
> '=====================================================================================
> 
> Imports System
> Imports System.Drawing
> Imports System.Runtime.InteropServices
> 
> Public Class clsIcon
> 
> 	'=====================================================================================
> 	' Enumerations
> 	'=====================================================================================
> 	<Flags()> Private Enum SHGFI
> 		SmallIcon = &H1
> 		LargeIcon = &H0
> 		Icon = &H100
> 		DisplayName = &H200
> 		Typename = &H400
> 		SysIconIndex = &H4000
> 		UseFileAttributes = &H10
> 	End Enum
> 
> 	Public Enum IconSize
> 		SmallIcon = 1
> 		LargeIcon = 0
> 	End Enum
> 
> 	'=====================================================================================
> 	' Structures
> 	'=====================================================================================
> 	<StructLayout(LayoutKind.Sequential)> _
> 	Private Structure SHFILEINFO
> 		Public hIcon As IntPtr
> 		Public iIcon As Integer
> 		Public dwAttributes As Integer
> 		<MarshalAs(UnmanagedType.LPStr, SizeConst:=260)> Public szDisplayName As 
> String
> 		<MarshalAs(UnmanagedType.LPStr, SizeConst:=80)> Public szTypeName As String
> 
> 		Public Sub New(ByVal B As Boolean)
> 			hIcon = IntPtr.Zero
> 			iIcon = 0
> 			dwAttributes = 0
> 			szDisplayName = vbNullString
> 			szTypeName = vbNullString
> 		End Sub
> 	End Structure
> 
> 	'=====================================================================================
> 	' API Calls
> 	'=====================================================================================
> 	Private Declare Auto Function SHGetFileInfo Lib "shell32" (ByVal pszPath As 
> String, ByVal dwFileAttributes As Integer, ByRef psfi As SHFILEINFO, ByVal 
> cbFileInfo As Integer, ByVal uFlagsn As SHGFI) As Integer
> 
> 	'=====================================================================================
> 	' Functions and Procedures...
>     
> '=====================================================================================
> 
> 	Public Shared Function GetDefaultIcon(ByVal Path As String, Optional ByVal 
> IconSize As IconSize = IconSize.SmallIcon, Optional ByVal SaveIconPath As 
> String = "") As Icon
> 		Dim info As New SHFILEINFO(True)
> 		Dim cbSizeInfo As Integer = Marshal.SizeOf(info)
>         Dim flags As SHGFI = SHGFI.Icon Or SHGFI.UseFileAttributes
> 		flags = flags + IconSize
> 		SHGetFileInfo(Path, 256, info, cbSizeInfo, flags)
>         GetDefaultIcon = Icon.FromHandle(info.hIcon)
> 		If SaveIconPath <> "" Then
> 			Dim FileStream As New IO.FileStream(SaveIconPath, IO.FileMode.Create)
> 			GetDefaultIcon.Save(FileStream)
> 			FileStream.Close()
> 		End If
> 	End Function	  'GetDefaultIcon(ByVal Path As String, Optional ByVal 
> IconSize As IconSize = IconSize.SmallIcon, Optional ByVal SaveIconPath As 
> String = "") As Icon
> 	'=====================================================================================
> 	Public Shared Function ImageToIcon(ByVal SourceImage As Image) As Icon
> 		' converts an image into an icon
> 		Dim TempBitmap As New Bitmap(SourceImage)
> 		ImageToIcon = Icon.FromHandle(TempBitmap.GetHicon())
> 		TempBitmap.Dispose()
> 	End Function	  'ImageToIcon(ByVal SourceImage As Image) As Icon
> 	'=====================================================================================
> 
> End Class
> 
> 
> 
> 
> "Erland Sommarskog" wrote:
> 
> > Ammar S. Mitoori (msnews08@nospam.nospam) writes:
> > > ok erland and how about sending the file path in the last sql code as a 
> > > parameter how can that be done ?
> >  
> > You have to use dynamic SQL. The syntax does unforunately permit for
> > variables.
> > 
> > But since you had your command in client code, it's not a big deal:
> > 
> > str = " DECLARE @img AS VARBINARY(MAX); " + _
> >        " SELECT @img = CAST(bulkcolumn AS ARBINARY(MAX)) " + _
> >        " FROM OPENROWSET(BULK '" + file + "',SINGLE_BLOB ) " + _
> >        " AS x; " + _
> >        " INSERT INTO Attachments (ID, SvrFile) " + _
> >        "     SELECT NEWID(), @img"
> > 
> > 
> > -- 
> > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> > 
> > Links for SQL Server Books Online:
> > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> > 
> > .
> > 

Ammar,
It looks that you were asking an ADO.NET question instead of a T-SQL 
question. Here is an article for letting you know how to execute a command 
with parameters:
http://msdn.microsoft.com/en-us/library/ms254953.aspx

At T-SQL side, you can create a stored procedure with parameters of 
FileDiscription and FileExt. For more information of stored procedure, you 
can refer to:
http://msdn.microsoft.com/en-us/library/ms187926.aspx

Best regards,
Charles Wang

Hi

NO its a SQL Question see the sql code below, it have an argument being 
passed called file, and cause its little complicated i just wanted to know if 
i have 2 more columns lets say "MyID" amd "IDType" where to put them and 
where to put the arguments such as +myid and + idtype

regards


        str = " DECLARE @img AS VARBINARY(MAX); " + _
              " SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX)) " + _
              " FROM OPENROWSET(BULK '" + file + "',SINGLE_BLOB ) " + _
              " AS x; " + _
              " INSERT INTO Attachments (ID, SvrFile) " + _
              " SELECT NEWID(), @img"
""Charles Wang [MSFT]"" wrote:

> Ammar,
> It looks that you were asking an ADO.NET question instead of a T-SQL 
> question. Here is an article for letting you know how to execute a command 
> with parameters:
> http://msdn.microsoft.com/en-us/library/ms254953.aspx
> 
> At T-SQL side, you can create a stored procedure with parameters of 
> FileDiscription and FileExt. For more information of stored procedure, you 
> can refer to:
> http://msdn.microsoft.com/en-us/library/ms187926.aspx
> 
> Best regards,
> Charles Wang
> 
> .
> 

Hi,
Do you mean that you have two more columns "MyID" and "IDType" in the table 
Attachments?

As I have mentioned, you can create a stored procedure to resolve this 
issue. For example, create a procedure in your SQL Server like this:
===============================
CREATE PROCEDURE usp_storeAttachment
(
@filename nvarchar(300),
@myId int,
@idType nvarchar(20)
)
AS
DECLARE @strSQL nvarchar(1000)
DECLARE @img AS VARBINARY(MAX);
DECLARE @paramDefinition NVARCHAR(500)

SET @strSQL=N'SELECT @imgOUT=CAST(BULKCOLUMN AS VARBINARY(MAX)) FROM 
OPENROWSET(BULK ''' + @filename + ''',SINGLE_BLOB) AS x;'
SET @paramDefinition = N'@imgOUT varbinary(max) output'
execute sp_executeSQL @strSQL,@paramDefinition, @imgOUT=@img OUTPUT

INSERT INTO Attachments (ID, SvrFile, MyID, IDType)  SELECT NEWID(), @img, 
@myId, @idType
================================
Note: The code is just for your reference and not fully tested. Please 
modify it and test it to make sure that it works per your requirements.

Then you can directly execute the stored procedure with passing the 
parameters in your application. 
http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

Best regards,
Charles Wang