473,547 Members | 2,553 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL statement error fixed, now wrong data appending

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("m ode")
Name = request.form("n ame") -DROP DOWN BOX
Shift = request.form("s hift") -RADIO BUTTON
Wave = request.form("w ave") -TEXT INPUT BOX
Carton = request.form("c arton") -TEXT INPUT BOX
Location = request.form("l ocation") -TEXT INPUT BOX
License = request.form("l icense") -TEXT INPUT BOX
Sku = request.form("s ku") -TEXT INPUT BOX
Qty = request.form("q uantity") -TEXT INPUT BOX
Reason = request.form("r eason") -DROP DOWN BOX
Comments = request.form("c omments") -TEXT INPUT BOX

if mode = "Send" then

'************** *************** *************** *************** *************** ***
'* DATABASE APPENDING
*
'************** *************** *************** *************** *************** ***
'create db connection
Set dbconn = Server.CreateOb ject("ADODB.Con nection")

'open db in a DSN-less method
dbconn.Open "PROVIDER=MICRO SOFT.JET.OLEDB. 4.0;DATA SOURCE="&
Server.MapPath( "/jax/wh/Online_Forms/Secured_Archive s/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_Archive s/Archive_TextFil es/Shortage_File.t xt")
Set fileobject = Server.CreateOb ject("Scripting .FileSystemObje ct")
Set textfile = fileobject.Open TextFile(shorta ge, 8)

' Append to the newly created text file
textfile.writel ine "<BR>"
textfile.writel ine "<BR>"
textfile.writel ine "<b>Name</b> :" & Request.form("n ame")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Shift</b> :" & Request.form("s hift")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Wave</b> :" & Request.form("w ave")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Carton</b> :" & Request.form("c arton")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Locatio n</b> :" & Request.form("l ocation")
textfile.writel ine "<BR>"
textfile.writel ine "<b>License </b> :" & Request.form("l icense")
textfile.writel ine "<BR>"
textfile.writel ine "<b>SKU</b> :" & Request.form("s ku")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Quantit y</b> :" & Request.form("q uantity")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Reason</b> :" & Request.form("r eason")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Comment s</b> :" & Request.form("c omments")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Date/Time Received</b> :" &
FormatDateTime( Now,4)
textfile.writel ine "<BR>"
textfile.writel ine "<HR>"

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

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

Set Mail = Server.CreateOb ject("Persits.M ailSender")
Mail.Host = "SMTP1" ' Specify a valid SMTP server
Mail.From = "someone@somewh ere" ' Specify sender's address
Mail.FromName = "Test Warehouse Shortage" ' Specify sender's name
Mail.AddAddress "someoneelse@so mewhere" 'email address that will
receive form submission
Mail.IsHTML = True
Mail.Subject = "Test Warehouse Shortage"

Dim mail_body

mail_body = "Name: " & request.form("n ame") & "<br>" &_
"Shift: " & request.form("s hift") & "<br>" &_
"Wave: " & request.form("w ave") & "<br>" &_
"Carton: " & request.form("c arton") & "<br>" &_
"Location: " & request.form("l ocation") & "<br>"
&_
"License: " & request.form("l icense") & "<br>" &_
"SKU: " & request.form("s ku") & "<br>" &_
"Quantity: " & request.form("q uantity") & "<br>"
&_
"Reason: " & request.form("r eason") & "<br>" &_
"Comments: " & request.form("c omments") & "<br>"

mail.body = mail_body & "Submitted IP Address: " &
request.serverv ariables("REMOT E_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
%>
Jul 19 '05 #1
1 2103
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("f irstName")
sLastname = Request.Form("l astName")

'''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(sFirstna me,50) & "," & TextIn(sLastnam e,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(sLastna me, "'", "''") %>
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(sNam e, sShift, sWave)
Call WriteToTextFile (sName, sShift, sWave)
'Call SendEmail(argum ents here)
Sub AssignValues()
Mode = Request.Form("m ode")
Name = Request.Form("n ame")
Shift = Request.Form("s hift")
Wave = Request.Form("w ave")
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.CreateOb ject("ADODB.Con nection")
dbconn.Open "PROVIDER=MICRO SOFT.JET.OLEDB. 4.0;DATA SOURCE="&
Server.MapPath( "/jax/wh/Online_Forms/Secured_Archive s/search_files/shortage.mdb")
dbconn.Execute sSQL,,129
dbconn.Close : Set dbconn = Nothing
End Sub
Sub WriteToTextFile (name,shift,wav e)
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_Archive s/Archive_TextFil es/Shortage_File.t xt")
Set fileobject = Server.CreateOb ject("Scripting .FileSystemObje ct")
Set textfile = fileobject.Open TextFile(shorta ge, 8)
textfile.Write sContents
textfile.Close : Set textfile = Nothing
Set fileobject = Nothing
End Sub

'''Sub SendEmail(argum ents here)
'''End Sub

Ray at work

"dmiller234 62" <dm**********@y ahoo.com> wrote in message
news:59******** *************** ***@posting.goo gle.com...
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("m ode")
Name = request.form("n ame") -DROP DOWN BOX
Shift = request.form("s hift") -RADIO BUTTON
Wave = request.form("w ave") -TEXT INPUT BOX
Carton = request.form("c arton") -TEXT INPUT BOX
Location = request.form("l ocation") -TEXT INPUT BOX
License = request.form("l icense") -TEXT INPUT BOX
Sku = request.form("s ku") -TEXT INPUT BOX
Qty = request.form("q uantity") -TEXT INPUT BOX
Reason = request.form("r eason") -DROP DOWN BOX
Comments = request.form("c omments") -TEXT INPUT BOX

if mode = "Send" then

'************** *************** *************** *************** *************** ***
'* DATABASE APPENDING
*
'************** *************** *************** *************** *************** ***
'create db connection
Set dbconn = Server.CreateOb ject("ADODB.Con nection")

'open db in a DSN-less method
dbconn.Open "PROVIDER=MICRO SOFT.JET.OLEDB. 4.0;DATA SOURCE="&
Server.MapPath( "/jax/wh/Online_Forms/Secured_Archive s/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_Archive s/Archive_TextFil es/Shortage_File.t xt")
Set fileobject = Server.CreateOb ject("Scripting .FileSystemObje ct")
Set textfile = fileobject.Open TextFile(shorta ge, 8)

' Append to the newly created text file
textfile.writel ine "<BR>"
textfile.writel ine "<BR>"
textfile.writel ine "<b>Name</b> :" & Request.form("n ame")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Shift</b> :" & Request.form("s hift")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Wave</b> :" & Request.form("w ave")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Carton</b> :" & Request.form("c arton")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Locatio n</b> :" & Request.form("l ocation")
textfile.writel ine "<BR>"
textfile.writel ine "<b>License </b> :" & Request.form("l icense")
textfile.writel ine "<BR>"
textfile.writel ine "<b>SKU</b> :" & Request.form("s ku")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Quantit y</b> :" & Request.form("q uantity")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Reason</b> :" & Request.form("r eason")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Comment s</b> :" & Request.form("c omments")
textfile.writel ine "<BR>"
textfile.writel ine "<b>Date/Time Received</b> :" &
FormatDateTime( Now,4)
textfile.writel ine "<BR>"
textfile.writel ine "<HR>"

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

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

Set Mail = Server.CreateOb ject("Persits.M ailSender")
Mail.Host = "SMTP1" ' Specify a valid SMTP server
Mail.From = "someone@somewh ere" ' Specify sender's address
Mail.FromName = "Test Warehouse Shortage" ' Specify sender's name
Mail.AddAddress "someoneelse@so mewhere" 'email address that will
receive form submission
Mail.IsHTML = True
Mail.Subject = "Test Warehouse Shortage"

Dim mail_body

mail_body = "Name: " & request.form("n ame") & "<br>" &_
"Shift: " & request.form("s hift") & "<br>" &_
"Wave: " & request.form("w ave") & "<br>" &_
"Carton: " & request.form("c arton") & "<br>" &_
"Location: " & request.form("l ocation") & "<br>"
&_
"License: " & request.form("l icense") & "<br>" &_
"SKU: " & request.form("s ku") & "<br>" &_
"Quantity: " & request.form("q uantity") & "<br>"
&_
"Reason: " & request.form("r eason") & "<br>" &_
"Comments: " & request.form("c omments") & "<br>"

mail.body = mail_body & "Submitted IP Address: " &
request.serverv ariables("REMOT E_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
%>

Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
7987
by: John F Dutcher | last post by:
I use code like the following to retrieve fields from a form: recd = recd.append(string.ljust(form.getfirst("lname",' '),15)) recd.append(string.ljust(form.getfirst("fname",' '),15)) etc., etc. The intent is to finish by assigning the list to a string that I would write to disk: recstr = string.join(recd,'')
4
5597
by: Simom Thorpe | last post by:
Hi, I'm trying to insert a line into a MS access DB using ASP on IIS 5. This is the line: con.execute "INSERT INTO newProds(title,desc,catcode) VALUES ('Champagne Muff Scarf','','AC304B')" But it throws up this error:
8
1224
by: Drew | last post by:
I have an app that I am building that takes user input on 1 page then inserts/updates it on the next page. There are a total of 20 pages, with data from page1 being modified by page2 and so on. I am trying to figure out how to incorporate back buttons into this app, so the user can go back. Currently I have tried many different ways to do...
3
6784
by: Klemens | last post by:
Hi, I get an implicit -101 on an alter table statement on changing the length of one column ALTER TABLE XTRADE.LIEFERANT ALTER COLUMN ZENTRALE_NUMMER SET DATA TYPE VARCHAR(13) DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0727N An...
15
2786
by: Nerox | last post by:
Hi, If i write: #include <stdio.h> int foo(int); int main(void){ int a = 3; foo(a); }
5
8316
by: Arthur Mnev | last post by:
This is probably beaten to death subject... Does anyone have a good idea of what penalties are for using Fixed statement in c#. On one side it allows for much greater flexibility with casts and array manipulations (i'm leaving access to legacy code out of the scope of this message) on the other hand fixed statement does consume resources to...
7
6652
by: kosta | last post by:
hello! one of my forms communicates with a database, and is supposed to add a row to a table using an Insert statement... however, I get a 'oledb - syntax error' exception... I have double checked, and the insert works fine (tried to use it from access)... im using visual C# express 2k5... what could be wrong? thanks!
8
1831
by: Brian Basquille | last post by:
Hello all, Bit of a change of pace now. As opposed to the typical questions regarding my Air Hockey game, am also working on a Photo Album which uses an Access Database to store information about photos. This information is held inside the database (photoDB.mdb) in a table called 'photos' - information being recorded in there are photo...
118
4558
by: Chuck Cheeze | last post by:
This might be in the wrong group, but... Here is an example of my data: entry_id cat_id 1 20 2 25 3 30 4 25 5 35
0
7510
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7437
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7463
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6032
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5362
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5081
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3493
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1050
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
748
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.