sql command error

[Ammar S. Mitoori]

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()

[Charles Wang [MSFT]]

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

[Dan]

"Ammar S. Mitoori" <msne...@nospam.nospam> wrote in message
news:B9DEE54D-7506-46A0...@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]

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]

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

[Erland Sommarskog]

Ammar S. Mitoori (msne...@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, esq...@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]

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:

> .
>

[Erland Sommarskog]

Ammar S. Mitoori (msne...@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, esq...@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]

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:

> .
>

[Erland Sommarskog]

Ammar S. Mitoori (msne...@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, esq...@sommarskog.se

Links for SQL Server Books Online:

[Ammar S. Mitoori]

Hi Erland

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

"Erland Sommarskog" wrote:

> .
>

[Erland Sommarskog]

Ammar S. Mitoori (msne...@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, esq...@sommarskog.se

Books Online for SQL Server 2005 at

[Dan]

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CD2ACAA4...@127.0.0.1...

> Ammar S. Mitoori (msne...@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.

Oops, sorry about that ...

--
Dan

[Ammar S. Mitoori]

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 (spotc...@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:

> .
>

[Erland Sommarskog]

Ammar S. Mitoori (msne...@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?

[Ammar S. Mitoori]

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

[Charles Wang [MSFT]]

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

[Ammar S. Mitoori]

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:

> .
>

[Charles Wang [MSFT]]

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