| re: SQL statement error fixed, now wrong data appending
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
"dmiller23462" <dmiller23462@yahoo.com> wrote in message
news:591ac515.0408090706.5292d8f4@posting.google.c om...[color=blue]
> 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
> %>[/color]
|
