We help IT Professionals succeed at work.

Using ASP/Enc-type forms to insert data into SQL database

mjsutkowski
mjsutkowski asked
on
248 Views
Last Modified: 2012-06-21
Basically, i'm trying to use freeaspupload to upload multiple videos and at the same time insert the data from the form into the database. the structure is a form with an attach field, year/month, and category repeated 8 times. The files will upload just fine, but unfortunately, the database insertion part is failing. I already have it setup to do one file at a time, but its super important that i get the multiple file upload to work. Any help is appreciated!
dim connectstr, oConn
 
%>
<!-- #include file="../scripts/connection.asp" -->
<!-- #include file="freeaspupload.asp" -->
<%
 
IF Request.Cookies("master") = "" THEN
	Response.Redirect "../login.asp"
END IF
 
'***********************************************************************************************************
 
DIM uNo
uNo = Request.Cookies("master")
 
dim qryPTM, rsPTM
 
qryPTM = "SELECT * FROM tblProgramTypesMain ORDER BY ptmName"
SET rsPTM = oConn.Execute(qryPTM) 
 
'***********************************************************************************************************
 
' ****************************************************
  Dim uploadsDirVar
  uploadsDirVar = Server.MapPath("../Video2")
' ****************************************************
 
function TestEnvironment()
    Dim fso, fileName, testFile, streamTest
    TestEnvironment = ""
    Set fso = Server.CreateObject("Scripting.FileSystemObject")
    if not fso.FolderExists(uploadsDirVar) then
        TestEnvironment = "<B>Folder " & uploadsDirVar & " does not exist.</B><br>The value of your uploadsDirVar is incorrect. Open uploadTester.asp in an editor and change the value of uploadsDirVar to the pathname of a directory with write permissions."
        exit function
    end if
    fileName = uploadsDirVar & "\test.txt"
    on error resume next
    Set testFile = fso.CreateTextFile(fileName, true)
    If Err.Number<>0 then
        TestEnvironment = "<B>Folder " & uploadsDirVar & " does not have write permissions.</B><br>The value of your uploadsDirVar is incorrect. Open uploadTester.asp in an editor and change the value of uploadsDirVar to the pathname of a directory with write permissions."
        exit function
    end if
    Err.Clear
    testFile.Close
    fso.DeleteFile(fileName)
    If Err.Number<>0 then
        TestEnvironment = "<B>Folder " & uploadsDirVar & " does not have delete permissions</B>, although it does have write permissions.<br>Change the permissions for IUSR_<I>computername</I> on this folder."
        exit function
    end if
    Err.Clear
    Set streamTest = Server.CreateObject("ADODB.Stream")
    If Err.Number<>0 then
        TestEnvironment = "<B>The ADODB object <I>Stream</I> is not available in your server.</B><br>Check the Requirements page for information about upgrading your ADODB libraries."
        exit function
    end if
    Set streamTest = Nothing
end function
 
function SaveFiles
    Dim Upload, fileName, fileSize, ks, i, fileKey
 
    Set Upload = New FreeASPUpload
    Upload.Save(uploadsDirVar)
 
	' If something fails inside the script, but the exception is handled
	If Err.Number<>0 then Exit function
 
    ' Retrieve the file names that have been uploaded
	SaveFiles = ""
    ks = Upload.UploadedFiles.keys
    if (UBound(ks) <> -1) then
        SaveFiles = ""
        for each fileKey in Upload.UploadedFiles.keys
            SaveFiles = SaveFiles & Upload.UploadedFiles(fileKey).FileName & "/"
        'next
		' Get the length of the entire string of names
		dim getLength, rsUpdateMax
		getLength = LEN(SaveFiles)
		' Remove the trailing / character
		SaveFiles = LEFT(SaveFiles, getLength)
 
		DIM arrNames, rsInsertSt
		arrNames = split(SaveFiles, "/")
		
		'***********************************************************************************************************
		'INSERT VIDEO
		Dim qryNo, rsNo, rsUpdateNo
		qryNo = "SELECT noNo FROM tblNo WHERE noNum = 333"
		SET rsNo = oConn.Execute(qryNo)
		DIM tNo
		tNo = rsNo("noNo")
		rsNo.close
 
				'update and add 1
				set rsUpdateNo = Server.CreateObject("ADODB.Command")
				rsUpdateNo.ActiveConnection = connectstr
				rsUpdateNo.CommandText = "UPDATE tblNo SET noNo = " & tNo + 1 & " WHERE noNum = 333"
				rsUpdateNo.Execute
			
			dim vTime
 
				vTime = Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & " " & (Hour(Time) + 2) & ":" & Minute(Time) & ":00"
		
				'Insert the Video
				set rsInsertSt = Server.CreateObject("ADODB.Command")
				rsInsertSt.ActiveConnection = connectstr
				rsInsertSt.CommandText = "INSERT INTO tblVideo(vNo, vCat, vDate, vStart, vLink, vTitle, vImg, vActive) VALUES(" & _
										 tNo & ", " & _
										 upload.form("vCat") & ", " & _
										 chr(39) & vTime & chr(39) & ", " & _
										 chr(39) & upload.form("Month") & CHR(32) & Upload.form("Year") & chr(39) & ", " & _
										 chr(39) & arrNames(0) & chr(39) & ", " & _
										 chr(39) & arrNames(0) & chr(39) & ", " & _
										 "'generic.jpg', 3)"
				rsInsertSt.Execute
 
				Response.Redirect("index.asp")
 
		'***********************************************************************************************************
		next
	else
        SaveFiles = "The file name specified in the upload form does not correspond to a valid file in the system."
    end if
		
end function
 
%>

Open in new window

Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
unfortunately, i can't make PseudoRequestDictionary work at this moment either. is there any way you could provide a snippet of code showing how to handle the uploading of files to a folder and inserting the rest of the form data in a database with only the file name of the uploaded files? i'd think you could process the array through a loop, but i just can't get it. any help at all is very appreciated.

Commented:
There is some sample code on the pages of the PseudoRequestDictionary.


<%
oRequest = new PseudoRequestDictionary
oRequest.ReadRequest
 
' save the uploaded files to a folder
oRequest.SaveAllFiles "C:\temp"
 
' All non-file formfields are available
' through oRequest.Form("fieldname")
 
sSQL = "INSERT INTO [tablename] (field1, field2) values (" & oRequest.Form("field1") & "," & oRequest.Form("field2") & ")"
connection.Execute sSQL, , 128
 
' or getting the values in multiple formfields
For each element In oRequest.Form("formfield").Keys
    Response.write element & "<br>"
Next
 
' note: of course you have to protect yourself from 
' SQL injection, but this code is just to show the principle
%>

Open in new window

Author

Commented:
i combed through all of the included content in the pseudorequestdictionary and i was able to find everything except for an example of what i actually need. i can upload files, i can insert a file into the database, but for some reason, i'm just having a ridiculous time uploading then inserting each file :/ the code below is what i need to insert for each individual uploaded file.
INSERT INTO tblVideo(vNo, vCat, vDate, vStart, vLink, vTitle, vImg, vActive) VALUES(" & _
                                                                                 tNo & ", " & _
                                                                                 upload.form("vCat") & ", " & _
                                                                                 chr(39) & vTime & chr(39) & ", " & _
                                                                                 chr(39) & upload.form("Month") & CHR(32) & Upload.form("Year") & chr(39) & ", " & _
                                                                                 chr(39) & arrNames(0) & chr(39) & ", " & _
                                                                                 chr(39) & arrNames(0) & chr(39) & ", " & _
                                                                                 "'generic.jpg', 3)"

Open in new window

Commented:
But what is the problem? It doesn't work? Do you get an error? Or is it just slow?

> i can insert a file into the database

Inserting files into a database slows down the application considerably.

Also you should realize that pure-script upload is just for small files. If you are going to have file sizes in terms of megabytes, you should prefer to use an ActiveX component.

I see that your tablename is "tblVideo" which gives me the idea that this is about uploading video's. And video's usually aren't small files.  I read somewhere that the absolute maximum that pure-script can handle is about 16MB. I haven't tested it personally, but I know that when files are getting bigger then 5MB, then pure-script is getting very slow. VBScript just isn't designed very well to handle binaries.

Author

Commented:
Everything goes fine, but it'll only add one out of the X number of videos that should be showing under the files. I'm not sure why that is, being that the database add function is part of the loop under "for each file in filekeys" etc.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.