FileUpload control and MS SQL

I am trying to use the FileUpload control to save a filename to a SQL database. I'm using the OnUpdating event for my SQLDataSource to add the filename to the UpdateParameters. The OnUpdating event is firing but the FileName doesn't get added to the database. Any pointers as to why that might be would be very helpful.

Here's the code. Thanks much.

<%

@.PageLanguage="VB"MasterPageFile="~/MasterPage.master"Title="File Upload Testing" %>

<

scriptrunat="server">Sub Page_Load()

Response.Write(

"UpdateParameters(0)=" & dsLabel.UpdateParameters(0).Name &"<br />")

Response.Write(

"UpdateParameters(1)=" & dsLabel.UpdateParameters(1).Name &"<br />")EndSubSub dsLabel_OnUpdating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Dim FileUploadControlAs FileUpload = DetailsView1.FindControl("FileUpload1")Dim strFileNameAsString = FileUploadControl.FileName

dsLabel.UpdateParameters(0).DefaultValue = strFileName

dsLabel.UpdateParameters(1).DefaultValue = Request.QueryString(

"rgstnID")

Response.Write(

"dsLabel.UpdateParameters(0).DefaultValue = " & dsLabel.UpdateParameters(0).DefaultValue &"<br />")

Response.Write(

"dsLabel.UpdateParameters(1).DefaultValue = " & dsLabel.UpdateParameters(1).DefaultValue &"<br />")EndSub

</

script>

<

asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server">

<

h1>File Upload</h1>

This page should allow me to use the FileUpload Control to save a filename to an MS SQL Database.

<br/><br/> <asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataSourceID="dsLabel"Height="50px"Width="125px"><Fields><asp:TemplateFieldHeaderText="File"SortExpression="labelName"><EditItemTemplate><asp:FileUploadID="FileUpload1"runat="server"/></EditItemTemplate><ItemTemplate><asp:LabelID="Label1"runat="server"Text='<%# Bind("labelName") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:CommandFieldButtonType="Button"ShowEditButton="True"/></Fields></asp:DetailsView><br/><br/><asp:SqlDataSourceID="dsLabel"runat="server"ConnectionString="Data Source=FILESERVER1;Initial Catalog=IM;Integrated Security=True"OnUpdating="dsLabel_OnUpdating"ProviderName="System.Data.SqlClient"SelectCommand="SELECT labelName FROM tblProductRegistration WHERE (registrationID = @.registrationID)"UpdateCommand="UPDATE tblProductRegistration SET labelName = @.LabelName WHERE (registrationID = @.registrationID)"><UpdateParameters><asp:ParameterConvertEmptyStringToNull="True"Name="LabelName"/><asp:QueryStringParameterType="Int64"Name="registrationID"QueryStringField="rgstnID"/></UpdateParameters><SelectParameters><asp:QueryStringParameterName="registrationID"QueryStringField="rgstnID"/></SelectParameters></asp:SqlDataSource>

</

asp:Content>Everything in the code seems to be ok. The code is to update an existing row in database but not insert. So make sure you have a rowwith the rgstnID in your databasae when updating.|||

Thanks very much for the quick reply,Iori_Jay. That's what's puzzling me, I only want to support UPDATE and the code looks good to me. Am I missing anything that would cause the Updating event to fire but prevent the UPDATE command from actually executing?

Thanks.

Charlie

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg as int

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg = (@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg, '@.jpg.jpg',

bulkcolumn from Openrowset (Bulk 'D:\Data\Pics\@.jpg.jpg', Single_Blob) as 'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg asint

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg =(@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg,'@.jpg.jpg',

bulkcolumn fromOpenrowset(Bulk'D:\Data\Pics\@.jpg.jpg', Single_Blob)as'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROMOPENROWSET('MSDASQL','Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;','SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris

FILENAME option in CREATE DATABASE statement

Hi,

I have a problem with CREATE DATABASE statement, since it needs to specify the absolut path for a file in the FILENAME= option. I would need instead a relative path, i.e. only the name of the file because the script that launch this SQL command is dependant on the installation path decided by the user, during the installation.
Is there a way to pass to the FILENAME only the name of the file?
Hope to have been clear :-)

Thanks in advanceTry KB article 307283:How To Create a SQL Server Database Programmatically by Using ADO.NET and Visual C# .NET

Terri|||Hi, Terry

I have created a batch file that run the script sql for the creating the database, but in the option FILENAME if the path does not exist on the clients PC - the script will fail.

Any suggestions appreciated.
Thanks.
Fabio.|||I'm really sorry, I missed the "relative path" part of your question. As far as I know SQL Server cannot use a relative path for the filename.

I wonder if you could make use of one of the system or extended stored procedures sp_helpfile, master..xp_getfiledetails, sp_helpdevice?? Or perhaps use the undocumented stored procedure XP_REGREAD to read the installation path out of the registry?

Terri|||Hello,

I have exactly the same question.

I've got a ASP.NET application, where I want to automate the creation of the database for the Webmaster (once they fill in the appropriate SQL Server/MSDE Database Server location and login info, etc...).

My dynamically written CREATE DATABASE script wants a full absolute filepath for the Data and Log files.

And obviously I need to take into account the fact the Database Server may not be local to the Webserver.

I can't seem to find any easy way to find this information, other than to do a query of the "Master" database, like so:

--------
USE [Master]
SELECT filename
FROM sysfiles
WHERE (name = 'master')
--------

The above returns a value like "C:\Data\MSSQL\data\master.mdf".

From this I could easily extract just the directory path portion.

Am I crazy? Is this reliable?

Or is there a better way to get this information?

Any insight would be appreciated....

filename in Subscription

Hello,

I have a question regarding the report file which is included in an email, when a subscription is created:

Is it possible to programmatically add something to the name of the created file of the subscription? I want to have one parameter of the report, which is a date, added at the end of the filename to let the clients distinguish between different report files.

Thank you in advance.

Sandra Geisler

The way I'd reslove this situation is by using data-driven subscription where in step 3 you put a query such as

SELECT FN=CAST('Your file name-' + CONVERT(char(20),GETDATE(),105) AS CHAR(20));

and on step 4; for filename you pick get the value from database for FILENAME and select FN based on above query.

Thanks

filename in Subscription

Hello,

I have a question regarding the report file which is included in an email, when a subscription is created:

Is it possible to programmatically add something to the name of the created file of the subscription? I want to have one parameter of the report, which is a date, added at the end of the filename to let the clients distinguish between different report files.

Thank you in advance.

Sandra Geisler

The way I'd reslove this situation is by using data-driven subscription where in step 3 you put a query such as

SELECT FN=CAST('Your file name-' + CONVERT(char(20),GETDATE(),105) AS CHAR(20));

and on step 4; for filename you pick get the value from database for FILENAME and select FN based on above query.

Thanks

sql

Filename for database dump no longer with timestamp?

Hi there!

When performing a database dump to a file with SQL Server 6.5, the timestamp was automatically used as the extension for the file. After migrating to SQL Server 2000, this no longer seems to be the case :-(

Is there a way to have the timestamp included in the filename (apart from writing a Windows script to change the filename after the file has been created, of course)?

Rgds,
MauritsOriginally posted by hafkenscheid
Hi there!

When performing a database dump to a file with SQL Server 6.5, the timestamp was automatically used as the extension for the file. After migrating to SQL Server 2000, this no longer seems to be the case :-(

Is there a way to have the timestamp included in the filename (apart from writing a Windows script to change the filename after the file has been created, of course)?

Rgds,
Maurits
In SQL7 if you go in the EM unser the Server Group -> Management -> DB Maint Plans and build a mint plan. I think the auto-delete after days puts the date time stamp on the file name.

Probably hasn't changed in SQL2K|||That's it! Thank you very, very much!

Regards,
Maurits

File Task Moving with variables...

I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.

Source Path & Filename:

@.[User:: DataSourceFolder]+"\\"+ @.[User::CD_PaidClaimSource]

Outputs:

\\umrdwh2\FTP\CLAIMDAILY.DATA

Destination Path Now: (Filename not specified per another thread.)

@.[User::WorkingFolder]

Original Destination Path: (Which did not work.)

@.[User::WorkingFolder]+"\\"+ @.[User::CD_PaidClaimSource]

I receive validation errors:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

How do I overcome this without hard coding the path in the task?

You should be editing the file connection managers (source and destination) via variable expressions, not the file system task, I believe.|||

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.
|||

JayH wrote:

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.

Oh, I think I get it. Did you specify that expression as the Source Path in the File System Task? I didn't think the UI allowed that. If so, you need to create a new variable, set it EvaluateAsExpression=true and put the expression there. Then reference that variable from your File System Task's SourceVariable property.
|||An example to illustrate what we're saying:

http://www.ssistalk.com/file_system_task.jpg|||

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

|||Try deleting the file system task and starting over.|||

Deleting everything variable, task, container, etc and recreating all of it results in the same error. Ideas? The funny part is the system loves the 'Source'; there are no errors, and it is the same syntax. It is only the 'destination' that is having trouble.

Rog

|||

CaptainMyCaptain wrote:

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

Roger,

I have an example that uses the file system task to move/rename files in case you are interested: http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

If I recall correctly, when using variables in the file system task, IsDestiantionPathVariable and IsSourcePathVarible, and depending on the operation used; some times the task expect only the path that points to the file and some times it expects the path AND the file name. In your case it looks like it only expects the path; so remove the file name from the expression. Do not assume both variables need path and file name.

|||

That was the answer. Thank you all for helping this greenhorn to get his job done!

Roger

File Rotation

Hi,
I currently have a job setup to export a view from a MS
SQL 2000 database to a CSV file on a network drive..
Let's say the filename is: filename.csv
The job runs every 10minutes, and I need it to save to
different file names for 7 days, then start back..
day 1: filename1.csv ..10minutes later would be
filename2.csv etc...
After 7 days it would go back to filename1.csv..
I hope I'm being clear here but I'm sure someone gets the
idea.. anyone has a tip?Guillaume
How about using the day of the week as part of the
filename and overwriting if the file name exists. ie
filename-mon.csv, filename-tue.csv etc or you could just
set up a small (one row, one column) table on your
database where you keep a number or some other identifier
to append to the file name. If you start with '1' as your
initial value, add 1 each day and when it gets to 8 reset
to 1. Filename1.csv, filename2.csv etc
Hope this helps
Regards
John

File name with sp_xml_preparedocument - Not working ?

Is it legal to give filename to sp_xml_preparedocument

Can i do

DECLARE @.hdoc int DECLARE @.doc varchar(1000) SET @.doc ='C:/XML/myXmlFile.xml' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.doc -- Remove the internal representation. exec sp_xml_removedocument @.hdoc

I know the XML is well formed because when the paste the file in @.doc above it worksI tried doing it and i get the following error: The XML parse error 0xc00ce556 occurred on line number 1, near the XML text "C:\XML_Processing\Test.xml".Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1The error description is 'Invalid at the top level of the document.'.Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1sp_xml_removedocument: The value supplied for parameter number 1 is invalid. The XML is :<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>


Hemanshu:

I think the argument to the sp_xml_preparedocument stored procedure is that actual XML text and NOT a file reference. Please give a look to the SP_XML_PREPAREDOCUMENT stored procedure in books online.

You might be able to do something like:

Code Snippet

select * from openrowset( BULK 'C:/XML/myXmlFile.xml' SINGLE_BLOB)

to fetch the data. Give a look to OPENROWSET in books online.

file handling

Is there function in SQL to return just the filename. For eg.
if the filename is 'C:\test\job.txt' , i want to extract job from the
filename. Is is possible? Thanks in advance!There's nothing built-in but you can accomplish the desired result with some
Transact-SQL string functions:
DECLARE @.FilePath varchar(255)
SET @.FilePath = 'C:\test\job.txt'
SELECT REVERSE(LEFT(REVERSE(@.FilePath), CHARINDEX('',
REVERSE(@.FilePath))-1))
Hope this helps.
Dan Guzman
SQL Server MVP
"HP" <HP@.discussions.microsoft.com> wrote in message
news:9368C79B-35DF-4D68-9A56-E97613D74416@.microsoft.com...
> Is there function in SQL to return just the filename. For eg.
> if the filename is 'C:\test\job.txt' , i want to extract job from the
> filename. Is is possible? Thanks in advance!|||HP,
If the path is stored in a column, the T-SQL string functions (i.e.,
CHARINDEX, SUBSTRING, etc...) can be used to return just the name. Also,
there are a few undocumented extended stored procedures that can be used to
work with files using T-SQL.
HTH
Jerry
"HP" <HP@.discussions.microsoft.com> wrote in message
news:9368C79B-35DF-4D68-9A56-E97613D74416@.microsoft.com...
> Is there function in SQL to return just the filename. For eg.
> if the filename is 'C:\test\job.txt' , i want to extract job from the
> filename. Is is possible? Thanks in advance!|||Thanks!
"Jerry Spivey" wrote:

> HP,
> If the path is stored in a column, the T-SQL string functions (i.e.,
> CHARINDEX, SUBSTRING, etc...) can be used to return just the name. Also,
> there are a few undocumented extended stored procedures that can be used t
o
> work with files using T-SQL.
> HTH
> Jerry
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:9368C79B-35DF-4D68-9A56-E97613D74416@.microsoft.com...
>
>