Howto save a Filename uploaded file to db?

GigaBear · June 18th, 2006, 05:22 PM

I'm trying to build an intranet on which users can, amongst other things, upload a file to present other users some info on their performance. This file will be stored on the server and other info (name, URL, description) will be stored in a database.

The upload part is going OK:

Code:

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Uploaden KPI bestand" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If FileUpload1.HasFile Then
            Try
                FileUpload1.SaveAs("H:\\Visual websites\\Communicator\\KPI_bestanden\\" & FileUpload1.FileName)
                Label1.Text = "Het bestand " & FileUpload1.FileName & " is verstuurd."

            Catch ex As Exception
                Label1.Text = "FOUT: " & ex.Message.ToString()
            End Try
        End If

End Sub
</script>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <p>Je wilt een bestand toevoegen / wijzigen aan de volgende KPI:</p>

    <asp:FileUpload ID="FileUpload1" runat="server" Style="position: relative; left: 7px;" /><br />
    <br />
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Style="left: 31px;
        position: relative; top: 0px;" Text="Bestand versturen" /><br />
    <br />
    <asp:Label ID="Label1" runat="server" Style="left: 4px; position: relative; top: 0px"
        Width="231px"></asp:Label>
</asp:Content>

The part I can't figure out is how to get the filename of the uploaded file in a database record, preferebly in one go with uploading.
Since it must be possible for the users to change the file when the performance data changes I have to provide the possibility to insert a new record (and upload a new file) and to update the record with a new filename.

Anyone any suggestions???

Imar · June 19th, 2006, 04:43 PM

Hi there,

Right after you uploaded the file and saved it, you can execute an INSERT statement against the database. If you're unfamiliar with accessing database, I suggest you get a book like Beginning ASP.NET 2, or Professional ASP.NET 2.0, and check out the ASP.NET 2 quickstarts from Microsoft here: http://asp.net/QuickStart/howto/doc/...teCommand.aspx

These quick starts and books talk about inserting data, but also about updating data. In your case, you should display the old record so users can edit it. Once you edit the record, you can execute an UPDATE statement to update the existing record with the new path.

Let me know if you need some help with a particular part of the process.

Imar

P.S. Did you ask this question on my website as well?

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

GigaBear · June 20th, 2006, 06:36 AM

Imar,

Thanks for your reply, I'll check out the quickstarts. And I posted this message after I asked the question at your website!

Imar · June 20th, 2006, 12:42 PM

OK, good luck and have fun. You know where to come when you have problems.

Veel plezier morgen van 9 tot 11..... ;)

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

GigaBear · June 21st, 2006, 10:09 AM

I found the code to update the record after the file is uploaded. Uploading works, i see the new file in the directory it is supposed to be in.
Then the toruble starts. I think the code will work to update the record, but when I run the page I get an Error:

FOUT: Unable to open the physical file "H:\Visual Websites\Communicator\App_Data\KPI.mdf".
Operating system error 32: "32(Het proces heeft geen toegang tot het bestand omdat het bestand door een ander proces wordt gebruikt.)".
An attempt to attach an auto-named database for file H:\Visual Websites\Communicator\App_Data\KPI.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I suppose this has to do with having Visual Web Developer connected to the database, but I'm not sure. Will the same problem occur when I go live and one user looks at data and another tries to update???

The code I have so far:

Code:

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Uploaden KPI bestand" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If FileUpload1.HasFile Then
            Try
                FileUpload1.SaveAs("H:\\Visual websites\\Communicator\\KPI_bestanden\\" & FileUpload1.FileName)
                Label1.Text = "Het bestand " & FileUpload1.FileName & " is verstuurd."
                Run()
            Catch ex As Exception
                Label1.Text = "FOUT: " & ex.Message.ToString()
            End Try
        End If
    End Sub

    Public Sub Run()
        Dim myConnection As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\KPI.mdf;Integrated Security=True;")
        Dim myCommand As SqlCommand = New SqlCommand
        Dim myTrans As SqlTransaction
        Dim strPath As String = "KPI_bestanden\" & FileUpload1.FileName
        Dim strID As Integer = Request.QueryString("kpiid")

        ' Open the connection.
        myConnection.Open()

        ' Assign the connection property.
        myCommand.Connection = myConnection

        ' Begin the transaction.
        myTrans = myConnection.BeginTransaction()

        ' Assign transaction object for a pending local transaction
        myCommand.Transaction = myTrans

        Try
            ' Update.
            myCommand.CommandText = "UPDATE tblKPI KPI_URL = " & strPath & " WHERE KPI_ID = " & strID & ";"
            myCommand.ExecuteNonQuery()

            myTrans.Commit()
        Catch e As Exception
            Label2.Text = e.ToString()
        Finally
            myConnection.Close()
        End Try
    End Sub
 
 
</script>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <p>Je wilt een bestand toevoegen / wijzigen aan de volgende KPI:</p>

    <asp:FileUpload ID="FileUpload1" runat="server" Style="position: relative; left: 7px;" /><br />
    <br />
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Style="left: 31px;
        position: relative; top: 0px;" Text="Bestand versturen" /><br />
    <br />
    <asp:Label ID="Label1" runat="server" Style="left: 4px; position: relative; top: 0px"
        Width="231px"></asp:Label><br />
    <asp:Label ID="Label2" runat="server" Style="left: 5px; position: relative; top: 10px"
        Width="230px"></asp:Label>
</asp:Content>

Imar · June 22nd, 2006, 03:39 AM

Is H:\ a mapped network drive, or a local disk?

If it's a network drive, this may be a security, where the account used by the webserver and database, does not have correct permissions to write on the network drive. It's also possible the account used by the webserver does not "see" the drive mapping of H:\ because it may have been created under your account.

To test this out, move the entire site to a local drive (like C or D) and try again....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

GigaBear · June 22nd, 2006, 06:36 AM

Imar,

Thanks, I'll give it a try and let you know.

En? Genoten woensdag?
Op naar Portugal!

Martin

Imar · June 22nd, 2006, 07:03 AM

You're welcome and good luck.

Enorm genoten: ik was er live aanwezig...
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=396

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

GigaBear · June 22nd, 2006, 03:56 PM

Imar,

I tried another drive, the error was still there. Then I made some changes to the connection, that gave some progress, I got a different error, something to do with the Update statement.

In order to narrow down the possible errors, I replaced the variables in the SQL with actual values and had the variables displayed in a label. The upload and update went allright.

So the problem is with the variables. After the Update the variables are correctly displayed, so they contain the right values. The problem that remains is how to get the variables in the SQL:
The old version:
myCommand.CommandText = "UPDATE tblKPI KPI_URL = " & strPath & " WHERE KPI_ID = " & strID & ";"

This is the commandtext that works:
myCommand.CommandText = "UPDATE tblKPI SET KPI_URL = 'KPI_bestanden\kpi_helaas.aspx' WHERE KPI_ID = 8;"

Any suggestions?

Martin

Guus for President!!!!

Imar · June 22nd, 2006, 04:00 PM

If you look at the stuff that works and that doesn't, you'll see there's a difference in quotes. When you send statements to a database, values like text need to be enclosed in single quotes. Your latter statement has that; the former not. This should work:

"UPDATE tblKPI KPI_URL = '" & strPath & "' WHERE KPI_ID = " & strID & ";"

Notice the single quotes before and after strPath. Not sure if the WHERE clause needs them as well. If KPI_ID is a number, you don't need them; if it's text, you need to wrap the value of strID in quotes as well.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004