Multiple insertion if textbox is not blank 1

[omoo]

I have 6 textboxes where user can input data (or can leave it blank). I need to insert all the data (if not blank) into the database. How should be SQL query be like?

insEntrydate = pd(DAY(date()),2) & "." & pd(MONTH(date()),2) & "." & pd(YEAR(date()),2)
insOwner = Request.Form("txtselOwner")
insDepartment = Request.Form("txtselDepartment")
insTechnology = Request.Form("txtselTechnology")
insProductname = Request.Form("txtselProductname")
insContents = Request.Form("txtselContents")
insTransferdest = Request.Form("txtselTransferdest")
insExportrelevant = Request.Form("txtselExportrelevant")
insfilename1 = Request.Form("txtselfilename1")
insfilesize1 = Request.Form("txtselfilesize1")
insfilename2 = Request.Form("txtselfilename2")
insfilesize2 = Request.Form("txtselfilesize2")
insfilename3 = Request.Form("txtselfilename3")
insfilesize3 = Request.Form("txtselfilesize3")
insfilename4 = Request.Form("txtselfilename4")
insfilesize4 = Request.Form("txtselfilesize4")
insfilename5 = Request.Form("txtselfilename5")
insfilesize5 = Request.Form("txtselfilesize5")
insfilename6 = Request.Form("txtselfilename6")
insfilesize6 = Request.Form("txtselfilesize6")

sql="INSERT into mask_info(TRANSFER_NO, ENTRY_DATE, OWNER, DEPARTMENT, TECHNOLOGY, PRODUCT_NAME, CONTENTS, TRANSFER_DEST, EXPORT_RELEVANT) values ('"&fcount&"',to_date('"&insEntrydate&"','dd/mm/yyyy'),'"&insOwner&"','"&insDepartment&"','"&insTechnology&"','"&insProductname&"','"&insContents&"','"&insTransferdest&"','"&insExportrelevant&"')"
Set objRS1 = OraDatabase.Execute(sql)


sql2="INSERT into mask_info(TRANSFER_NO, FILENAME, FILESIZE) values ('"&fcount&"','"&insfilename&"','"&insfilesize&"')"
Set objRS2 = OraDatabase.Execute(sql2)

 

[Tarwn]

First: You don't need to set a recordset = to your execute when your doing an Insert, Insert's don't return records.

The easiest way to handle this from the point of view of writing less code would be to just do a loop. If you know that you will always have inputs 1 to 6 then you coud do something like:

'all of your unnumbered inputs up here

'start processing
Dim ctr, insFileName, insFileSize
ctr = 1
Do While ctr <= 6
   'read in the inputs for this number
   insFileName = Request.Form("txtselfilename" & ctr)
   insFileSize - Request.Form("txtselfilesize" & ctr)

   'verify they have values
   If Len(insFileName) > 0 And Len(insFileSize) > 0 Then
      'build your SQL string
      sqlString = "INSERT INTO ... rest of string"
      'execute your SQL string
      OraDatabase.Execute sqlString
   End If

   'increment th counter
   ctr = ctr + 1
Loop

If you don't know the upper limit on the number of inputs then you could instead change your loop to do a:
Do While Request.Form("txtselfilename" & ctr) <> "" And Request.Form("txtselfilesize" & ctr) <> ""

So that it would keep looping through all available inputs until it hit a counter number that had neither of the valus, basically a blank line. Or you could use an Or and only loop until you run into a pair that has at leat one empty value.

hope this helps,
-T