asked on

Add DB record in ASPX.NET

Hi guys - this is a script to upload a file to our server for a helpdesk system, but I need it to now add a DB records with the following fields in the table uploadtable

callref - called ID1 in this code from the previous page
filename
filesize
contenttype

I also then need it automatically encrement fileid by one for each file it adds.

I can kind of do this in ASP, but not yet familiar with ASPX to do this here.

Any help gratefully received.

<script runat="server">
	Dim ID As String
	Dim DirectoryPath As String
	Dim FilePath As String
    Protected Sub Button1_Click(ByVal sender As Object, _
      ByVal e As System.EventArgs)
        If FileUpload1.HasFile Then
            Try
				ID = request.QueryString("ID1") 
				DirectoryPath = "C:\inetpub\wwwroot\Gremlinv4\mainsystem\UploadedFiles\" & ID
				System.IO.Directory.CreateDirectory(DirectoryPath)
                        
				FilePath = DirectoryPath & "\" & FileUpload1.FileName

				FileUpload1.SaveAs(FilePath)
                Label1.Text = "File name: " & _
                   FileUpload1.PostedFile.FileName & "<br>" & _
                   "File Size: " & _
                   FileUpload1.PostedFile.ContentLength & " kb<br>" & _
                   "Content type: " & _
                   FileUpload1.PostedFile.ContentType
            Catch ex As Exception
                Label1.Text = "ERROR: " & ex.Message.ToString()
            End Try
        Else
            Label1.Text = "You have not specified a file."
        End If
    End Sub
</script>

Open in new window

Nasir Razzaq

What DB are you using? You can use something like below (use an Identity column for fieldid)

Dim dbcon as new SqlClient.SqlConnection("connection string")
dbcon.Open
Dim dbcmd As New SqlClient.SqlCommand
dbcmd.Connection = dbcon
dbcmd.CommandText = "Insert Into tablename (FileName, FileSize, ContentType) Values ('" & FileUpload1.PostedFile.FileName & "', " & FileUpload1.PostedFile.ContentLength & ", '" & FileUpload1.PostedFile.ContentType & "')"

CaringIT

ASKER

We have the following as the conn.inc which can be added for the DB connnection to SQL 2008 in ASP - but this obviously does not load in ASP.NET so needs amending, perhaps just for this one page at the moment

<%
strConn = "Provider=SQLNCLI10;Server=chintranet2;Database=Gremlin;Uid=Gremlin;Pwd=jgjgjgj;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString= strConn
conn.Open
%>

Then to run the SQL statement

Nasir Razzaq

That is ADO. You need ADO.NET as shown in my example

Just use the connection string from existing code

Dim dbcon as new SqlClient.SqlConnection("Provider=SQLNCLI10;Server=chintranet2;Database=Gremlin;Uid=Gremlin;Pwd=jgjgjgj;")

CaringIT

ASKER

Tried this and get:

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30002: Type 'SqlClient.SqlConnection' is not defined.

Source Error:

Line 26: Label1.Text = "ERROR: " & ex.Message.ToString()
Line 27:
Line 28:                   Dim dbcon as New SqlClient.SqlConnection("Provider=SQLNCLI10;Server=chintranet2;Database=Gremlin;Uid=Gremlin;Pwd=119kings*;")
Line 29:                   dbcon.Open
Line 30:                   Dim dbcmd As New SqlClient.SqlCommand

Sorry - really not up to speed on this yet!!

CaringIT

ASKER

This is the whole page's code as it stands

<%@ Page Language="VB" %>

<script runat="server">
	Dim ID As String
	Dim DirectoryPath As String
	Dim FilePath As String
    Protected Sub Button1_Click(ByVal sender As Object, _
      ByVal e As System.EventArgs)
        If FileUpload1.HasFile Then
            Try
				ID = request.QueryString("ID1") 
				DirectoryPath = "C:\inetpub\wwwroot\Gremlinv4\mainsystem\UploadedFiles\" & ID
				System.IO.Directory.CreateDirectory(DirectoryPath)
                        
				FilePath = DirectoryPath & "\" & FileUpload1.FileName

				FileUpload1.SaveAs(FilePath)
                Label1.Text = "File name: " & _
                   FileUpload1.PostedFile.FileName & "<br>" & _
                   "File Size: " & _
                   FileUpload1.PostedFile.ContentLength & " kb<br>" & _
                   "Content type: " & _
                   FileUpload1.PostedFile.ContentType
            Catch ex As Exception
                Label1.Text = "ERROR: " & ex.Message.ToString()
            
			Dim dbcon as New SqlClient.SqlConnection("Provider=SQLNCLI10;Server=chintranet2;Database=Gremlin;Uid=Gremlin;Pwd=119kings*;")
			dbcon.Open
			Dim dbcmd As New SqlClient.SqlCommand
			dbcmd.Connection = dbcon
			dbcmd.CommandText = "insert into uploadtable (filename,filesize,contenttype,callref)" & "values (" & FileUpload1.PostedFile.FileName & "," & FileUpload1.PostedFile.ContentLength & ", " &FileUpload1.PostedFile.ContentType & ", " & ID & ")"

            End Try
        Else
            Label1.Text = "You have not specified a file."
        End If
    End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">
    <title>Upload Files</title>
</head>

<body>

<table width="50%" align="center">
	<tr>
		<td align="center">
			<b>Adding Files to Gremlin : <%=request.querystring("ID1")%></b>
		</td>
	</tr>
	<tr>
		<td align="center">
		    <form id="form1" runat="server">
		    <div>
		        <asp:FileUpload ID="FileUpload1" runat="server" /><br />
		        <br />
		        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" 
		         Text="Upload File" />&nbsp;<br />
		        <br />
		        <asp:Label ID="Label1" runat="server"></asp:Label></div>
		    </form>
		</td>
	</tr>
</table>
</body>
</html>

Open in new window

Nasir Razzaq

Try

System.Data.SqlClient.SqlConnection

If that does not work then add a reference to System.Data

CaringIT

ASKER

That works fine but I do not see any entries in the DB - am I missing something?

Many thanks

macksm

You can do it the same way you did it in classic asp there is no difference be tween an asp and aspx,Except that aspx is the file extension for .net script You use the same Vb script there too

CaringIT

ASKER

Adding dbcmd.Open() results in the following error:

Compiler Error Message: BC30456: 'Open' is not a member of 'System.Data.SqlClient.SqlCommand'.

Source Error:

Line 31: dbcmd.CommandText = "insert into uploadtable (filename,filesize,contenttype,callref)" & "values (" & FileUpload1.PostedFile.FileName & "," & FileUpload1.PostedFile.ContentLength & ", " &FileUpload1.PostedFile.ContentType & ", " & ID & ")"
Line 32:
Line 33: dbcmd.Open()
Line 34:
Line 35: End Try

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CaringIT

ASKER

Added this - page runs, uploads the file to the filestore, reports the details, but does not add to the DB.

No errors are generated on the web browser.

Do I need to adjust the code order at all?

Nasir Razzaq

Show us the complete code that you have now.

CaringIT

ASKER

<%@ Page Language="VB" %>

<script runat="server">
      Dim ID As String
      Dim DirectoryPath As String
      Dim FilePath As String
Protected Sub Button1_Click(ByVal sender As Object, _
ByVal e As System.EventArgs)
If FileUpload1.HasFile Then
Try
                        ID = request.QueryString("ID1")
                        DirectoryPath = "C:\inetpub\wwwroot\Gremlinv4\mainsystem\UploadedFiles\" & ID
                        System.IO.Directory.CreateDirectory(DirectoryPath)

                        FilePath = DirectoryPath & "\" & FileUpload1.FileName

                        FileUpload1.SaveAs(FilePath)
Label1.Text = "File name: " & _
FileUpload1.PostedFile.FileName & "<br>" & _
"File Size: " & _
FileUpload1.PostedFile.ContentLength & " kb<br>" & _
"Content type: " & _
FileUpload1.PostedFile.ContentType
Catch ex As Exception
Label1.Text = "ERROR: " & ex.Message.ToString()

                  Dim dbcon as New System.Data.SqlClient.SqlConnection("Provider=SQLNCLI10;Server=chintranet2;Database=Gremlin;Uid=Gremlin;Pwd=119kings*;")
                  dbcon.Open
                  Dim dbcmd As New System.Data.SqlClient.SqlCommand
                  dbcmd.Connection = dbcon
                  dbcmd.CommandText = "insert into uploadtable (filename,filesize,contenttype,callref)" & "values (" & FileUpload1.PostedFile.FileName & "," & FileUpload1.PostedFile.ContentLength & ", " &FileUpload1.PostedFile.ContentType & ", " & ID & ")"

                  dbcmd.ExecuteNonQuery()

End Try
Else
Label1.Text = "You have not specified a file."
End If
End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">
<title>Upload Files</title>
</head>

<body>

<table width="50%" align="center">
      <tr>
            <td align="center">
                  <b>Adding Files to Gremlin : <%=request.querystring("ID1")%></b>
            </td>
      </tr>
      <tr>
            <td align="center">
              <form id="form1" runat="server">
              <div>
              <asp:FileUpload ID="FileUpload1" runat="server" /><br />
              <br />
              <asp:Button ID="Button1" runat="server" OnClick="Button1_Click"
              Text="Upload File" /> <br />
              <br />
              <asp:Label ID="Label1" runat="server"></asp:Label></div>
              </form>
            </td>
      </tr>
</table>
</body>
</html>

Nasir Razzaq

You have DB code inside CATCH which would only execute if there was an error. Move it to the TRY block.

<%@ Page Language="VB" %>

<script runat="server">
      Dim ID As String
      Dim DirectoryPath As String
      Dim FilePath As String
    Protected Sub Button1_Click(ByVal sender As Object, _
      ByVal e As System.EventArgs)
        If FileUpload1.HasFile Then
            Try
                        ID = request.QueryString("ID1") 
                        DirectoryPath = "C:\inetpub\wwwroot\Gremlinv4\mainsystem\UploadedFiles\" & ID
                        System.IO.Directory.CreateDirectory(DirectoryPath)
                        
                        FilePath = DirectoryPath & "\" & FileUpload1.FileName

                        FileUpload1.SaveAs(FilePath)
                Label1.Text = "File name: " & _
                   FileUpload1.PostedFile.FileName & "<br>" & _
                   "File Size: " & _
                   FileUpload1.PostedFile.ContentLength & " kb<br>" & _
                   "Content type: " & _
                   FileUpload1.PostedFile.ContentType
            
                  Dim dbcon as New System.Data.SqlClient.SqlConnection("Provider=SQLNCLI10;Server=chintranet2;Database=Gremlin;Uid=Gremlin;Pwd=119kings*;")
                  dbcon.Open
                  Dim dbcmd As New System.Data.SqlClient.SqlCommand
                  dbcmd.Connection = dbcon
                  dbcmd.CommandText = "insert into uploadtable (filename,filesize,contenttype,callref)" & "values (" & FileUpload1.PostedFile.FileName & "," & FileUpload1.PostedFile.ContentLength & ", " &FileUpload1.PostedFile.ContentType & ", " & ID & ")"

                  dbcmd.ExecuteNonQuery()
            Catch ex As Exception
                Label1.Text = "ERROR: " & ex.Message.ToString()
                  
            End Try
        Else
            Label1.Text = "You have not specified a file."
        End If
    End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">
    <title>Upload Files</title>
</head>

<body>

<table width="50%" align="center">
      <tr>
            <td align="center">
                  <b>Adding Files to Gremlin : <%=request.querystring("ID1")%></b>
            </td>
      </tr>
      <tr>
            <td align="center">
                <form id="form1" runat="server">
                <div>
                    <asp:FileUpload ID="FileUpload1" runat="server" /><br />
                    <br />
                    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" 
                     Text="Upload File" />&nbsp;<br />
                    <br />
                    <asp:Label ID="Label1" runat="server"></asp:Label></div>
                </form>
            </td>
      </tr>
</table>
</body>
</html>

Open in new window

CaringIT

ASKER

I now get this error:

ERROR: Keyword not supported: 'provider'.

Obviously relates to the DB connection by the looks of it

CaringIT

ASKER

Removed this and it lets the code run, but now generates all sort of errors about filenames with spaces in them and the content type field.

Just trying a couple of bits on the SQL statement.

CaringIT

ASKER

When it gets to the file extension on a file with no spaces/characters, it says:

ERROR: Incorrect syntax near 'txt'.

When there is a space in the name, i.e. Accounts June 2010, it says:

ERROR: Incorrect syntax near 'June'.

CaringIT

ASKER

Fixed that with the extra quotes in the SQL statement. Now imports correctly.

Thanks for the help!!!

CaringIT

ASKER

Superb help and patience!!!

Nasir Razzaq

Glad to help :-)