SQL statement error fixed, now wrong data appending

[dmiller23462]

Hey guys....

I put an error-handling in my page and have it posted at the complete
end of the code, see below(when people were putting in 's I was
getting the delimiter errors). Great, I understand that now and it
seems to be fixed but the data I'm pulling from the HTML fields is not
being appended correctly do my Access DB....The field in the DB now
reads " ' ". I understand why it does that (my function) but what I
need it to read is whatever data was entered into my text area named
"comments". I tried to change the line of code in the SQL statement to
read DoubleUpQuotes(comments) but that didn't work....Any help? The
page processes, which is a step in the right direction but fixing one
thing has broken another....

All of the text input boxes need to have this error-handling
capability...

<%
Mode = request.form("mode")
Name = request.form("name") -DROP DOWN BOX
Shift = request.form("shift") -RADIO BUTTON
Wave = request.form("wave") -TEXT INPUT BOX
Carton = request.form("carton") -TEXT INPUT BOX
Location = request.form("location") -TEXT INPUT BOX
License = request.form("license") -TEXT INPUT BOX
Sku = request.form("sku") -TEXT INPUT BOX
Qty = request.form("quantity") -TEXT INPUT BOX
Reason = request.form("reason") -DROP DOWN BOX
Comments = request.form("comments") -TEXT INPUT BOX

if mode = "Send" then

'*****************************************************************************
'* DATABASE APPENDING
*
'*****************************************************************************
'create db connection
Set dbconn = Server.CreateObject("ADODB.Connection")

'open db in a DSN-less method
dbconn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&
Server.MapPath("/jax/wh/Online_Forms/Secured_Archives/search_files/shortage.mdb")

'SQL statement to be run
stateSQL = "INSERT INTO shortage (name, shift, wave, carton, location,
license, sku, qty, reason, comments)" &_
"VALUES ('" & name & "','" & shift & "','" & wave & "','" & carton
& "','" & location & "','" & license & "','" & sku & "','" &_
qty & "','" & reason & "','" & DoubleUpQuotes(strFormElement) &
"')"

'display results of statement on screen for testing purposes
Response.Write(stateSQL)

'remind the SQL statement who it works for
dbconn.Execute(stateSQL)

'smack around the db connection until it lets go
dbconn.Close

'kill the connection with extreme prejudice
set dbconn = nothing

'*****************************************************************************
'* FILE APPENDING
*
'*****************************************************************************
' Create a text file
shortage = Server.MapPath("/jax/wh/Online_Forms/Secured_Archives/Archive_TextFiles/Shortage_File.txt")
Set fileobject = Server.CreateObject("Scripting.FileSystemObject")
Set textfile = fileobject.OpenTextFile(shortage, 8)

' Append to the newly created text file
textfile.writeline "<BR>"
textfile.writeline "<BR>"
textfile.writeline "<b>Name</b> :" & Request.form("name")
textfile.writeline "<BR>"
textfile.writeline "<b>Shift</b> :" & Request.form("shift")
textfile.writeline "<BR>"
textfile.writeline "<b>Wave</b> :" & Request.form("wave")
textfile.writeline "<BR>"
textfile.writeline "<b>Carton</b> :" & Request.form("carton")
textfile.writeline "<BR>"
textfile.writeline "<b>Location</b> :" & Request.form("location")
textfile.writeline "<BR>"
textfile.writeline "<b>License</b> :" & Request.form("license")
textfile.writeline "<BR>"
textfile.writeline "<b>SKU</b> :" & Request.form("sku")
textfile.writeline "<BR>"
textfile.writeline "<b>Quantity</b> :" & Request.form("quantity")
textfile.writeline "<BR>"
textfile.writeline "<b>Reason</b> :" & Request.form("reason")
textfile.writeline "<BR>"
textfile.writeline "<b>Comments</b> :" & Request.form("comments")
textfile.writeline "<BR>"
textfile.writeline "<b>Date/Time Received</b> :" &
FormatDateTime(Now,4)
textfile.writeline "<BR>"
textfile.writeline "<HR>"

' Close the file and set object to nothing
textfile.close
Set fileobject = nothing

'**************************************************
'* EMAIL STATEMENT *
'**************************************************

Set Mail = Server.CreateObject("Persits.MailSender")
Mail.Host = "SMTP1" ' Specify a valid SMTP server
Mail.From = "someone@somewhere" ' Specify sender's address
Mail.FromName = "Test Warehouse Shortage" ' Specify sender's name
Mail.AddAddress "someoneelse@somewhere" 'email address that will
receive form submission
Mail.IsHTML = True
Mail.Subject = "Test Warehouse Shortage"

Dim mail_body

mail_body = "Name: " & request.form("name") & "<br>" &_
"Shift: " & request.form("shift") & "<br>" &_
"Wave: " & request.form("wave") & "<br>" &_
"Carton: " & request.form("carton") & "<br>" &_
"Location: " & request.form("location") & "<br>"
&_
"License: " & request.form("license") & "<br>" &_
"SKU: " & request.form("sku") & "<br>" &_
"Quantity: " & request.form("quantity") & "<br>"
&_
"Reason: " & request.form("reason") & "<br>" &_
"Comments: " & request.form("comments") & "<br>"

mail.body = mail_body & "Submitted IP Address: " &
request.servervariables("REMOTE_ADDR")

On Error Resume Next
Mail.Send
If Err <> 0 Then
Response.Write "Error encountered: " & Err.Description
End If

End if
%>
<html>
HTML FORM DATA IS IN HERE.
</html>
<%
Function DoubleUpQuotes(strText)
DoubleUpQuotes = "'" & Replace(strText, "'", "''") & "'"
End Function
%>

 

[Ray at]

I suggest that you get in the habit of using variables instead of pulling
from the Request.Form collection each time you need a value. In addition to
that, make yourself a "textIn" function for any text-based data that you're
inserting into a database. The former suggestion can generate additional
lines of code, but it will make things simpler, and possilby [unnoticeably]
faster. Example:

<%
Dim sFirstname, sLastname
sFirstname = Request.Form("firstName")
sLastname = Request.Form("lastName")

'''Say that the firstName and lastName columns in your Access database
'''are text type and have a maximum length of 50.

sSQL = "INSERT INTO [something] ([firstName],[lastName]) VALUES (" &
TextIn(sFirstname,50) & "," & TextIn(sLastname,50) & ")"



''' This function will put ' delimeters on
''' text data for SQL input, double up the '
''' as needed, and be trim away any data that
''' exceeds the limit you specify.
Function TextIn(TheText,MaxLength)
Dim sResult
sResult = TheText
If MaxLength > 0 Then
If Len(sResult) > MaxLength Then sResult = Left(sResult, MaxLength)
End If
sResult = Replace(sResult, "'", "''")
sResult = "'" & sResult & "'"
TextIn = sResult
End Function
%>


I personally feel that it is good practice to never modify the user's actual
input. Like, if you were to do something like:
<% sLastname = Replace(sLastname, "'", "''") %>
so that you could deal with any ' characters, if you later go back to add
code to e-mail this same data to someone, that person would receive
something like this:

First name: John
Last name: O''Brien

That's just silly.

Try to simplify your code and make use of variables, subs, and functions.
Example again:


<%
'' vars with global scope
Dim sMode, sName, sShift, sWave ', etc.
Call AssignValues()
Call InsertData(sName, sShift, sWave)
Call WriteToTextFile(sName, sShift, sWave)
'Call SendEmail(arguments here)


Sub AssignValues()
Mode = Request.Form("mode")
Name = Request.Form("name")
Shift = Request.Form("shift")
Wave = Request.Form("wave")
End Sub



Sub InsertData(name, shift, wave)
Dim dbconn, sSQL

sSQL = "INSERT INTO shortage (name, shift, wave) VALUES (" &
TextIn(name,50) & "," & TextIn(shift,10) & "," TextIn(wave,255) & ")"

Set dbconn = Server.CreateObject("ADODB.Connection")
dbconn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&
Server.MapPath("/jax/wh/Online_Forms/Secured_Archives/search_files/shortage.mdb")
dbconn.Execute sSQL,,129
dbconn.Close : Set dbconn = Nothing
End Sub


Sub WriteToTextFile(name,shift,wave)
Dim sContents
Dim shortage, fileobject, textfile

sContents = ""
sContents = sContents & "<BR>"
sContents = sContents & "<BR>"
sContents = sContents & "<b>Name</b> :" & name
sContents = sContents & "<BR>"
sContents = sContents & "<b>Shift</b> :" & shift
sContents = sContents & "<BR>"
sContents = sContents & "<b>Wave</b> :" & wave
'''etc.


shortage =
Server.MapPath("/jax/wh/Online_Forms/Secured_Archives/Archive_TextFiles/Shortage_File.txt")
Set fileobject = Server.CreateObject("Scripting.FileSystemObject")
Set textfile = fileobject.OpenTextFile(shortage, 8)
textfile.Write sContents
textfile.Close : Set textfile = Nothing
Set fileobject = Nothing
End Sub

'''Sub SendEmail(arguments here)
'''End Sub



Ray at work