Avatar of CaringIT
CaringIT
Flag for United Kingdom of Great Britain and Northern Ireland 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

ASP.NETVisual Basic.NET

Avatar of undefined
Last Comment
Nasir Razzaq

8/22/2022 - Mon
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;")
Your help has saved me hundreds of hours of internet surfing.
fblack61
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Nasir Razzaq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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" />&nbsp;<br />
                    <br />
                    <asp:Label ID="Label1" runat="server"></asp:Label></div>
                </form>
            </td>
      </tr>
</table>
</body>
</html>
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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!!!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Nasir Razzaq

Glad to help :-)