Solved

500 pts to the first person to Parameterize this ASP/MS SQL INSERT statement

Posted on 2006-11-13
11
241 Views
Last Modified: 2012-05-05
First person to convert the below code into a working Parameterized Query wins. :P

Basically, I've been having the problem that if someone puts a single quote into any of the fields, it breaks the code. I've read that by using parameters, I won't have that problem anymore. I can include ADOvbs.inc myself, just get me the code that will work gentleman/ladies. Thanks so much.

-----------------------------
The Code
-----------------------------

'Open Database Connection
Set openConn = Server.CreateObject("ADODB.Connection")
openPath= "Driver={SQL Server};" & _
           "Server=(local);" & _
           "Database=KlineGroup;" & _
               "Trusted_Connection=yes"
openConn.open openPath
openConn.close 'make sure old connections are cleaned out
openConn.open openPath

if request.form("formsubmit") = "true" Then
      rp_title = request.Form("title")
      rp_date_month = request.Form("releasedate_month")
      rp_date_day = request.Form("releasedate_day")
      rp_date_year = request.Form("releasedate_year")
      rp_date = rp_date_month & "/" &rp_date_day & "/" & rp_date_year
      rp_desc = request.Form("description")
      rp_category = request.Form("category")
      rp_code = cleantext(request.Form("code"))
      rp_link_page = request.Form("link_page")
      rp_link_brochure = request.Form("link_brochure")
      rp_link_subscribe = request.Form("link_subscribe")
      rp_year_start = request.Form("year_start")
      rp_year_end = request.Form("year_end")
      rp_n_america = request.Form("n_america")
      rp_latin = request.Form("latin")
      rp_e_europe = request.Form("e_europe")
      rp_w_europe = request.Form("w_europe")
      rp_middle_east = request.Form("middle_east")
      rp_asia = request.Form("asia")
      

      SQL_query = "INSERT into report (rp_title, rp_date, rp_desc, rp_category, rp_code, rp_link_page, rp_link_brochure, rp_link_subscribe, rp_year_start, rp_year_end, rp_n_america, rp_latin, rp_e_europe, rp_w_europe, rp_middle_east, rp_asia) values ('" & _
rp_title & "', '" & rp_date & "', '" & rp_desc & "', '" & rp_category & "', '" & rp_code & "', '" & rp_link_page & "', '" & rp_link_brochure & "', '" & rp_link_subscribe & "', '" & rp_year_start & "', '" & rp_year_end & "', '" & rp_n_america & "', '" & rp_latin & "', '" & rp_e_europe & "', '" & rp_w_europe & "', '" & rp_middle_east & "', '" & rp_asia & "');"
      Set RS = openConn.Execute(SQL_query)
      Set RS = Nothing
End if
0
Comment
Question by:bsowards
  • 5
  • 5
11 Comments
 
LVL 4

Expert Comment

by:carlmahon
ID: 17931262
You could use the command object or just fix any input that is not correct. You only need this function on input that allows quotes.

Function InsertAP(n)
      If n <> "" Then
            If Instr(n,"'")<>0 Then
                  InsertAP = Replace(n,"'","''")
            Else
                  InsertAP = n
            End If
      End If
End Function



     rp_title = InsertAP(request.Form("title"))
     rp_date_month = InsertAP(request.Form("releasedate_month"))
     rp_date_day = InsertAP(request.Form("releasedate_day"))
     rp_date_year = InsertAP(request.Form("releasedate_year"))
     rp_date = InsertAP(rp_date_month & "/" &rp_date_day & "/" & rp_date_year
     rp_desc = InsertAP(request.Form("description"))
     rp_category = InsertAP(request.Form("category"))
     rp_code = cleantext(request.Form("code")))
     rp_link_page = InsertAP(request.Form("link_page"))
     rp_link_brochure = InsertAP(request.Form("link_brochure"))
     rp_link_subscribe = InsertAP(request.Form("link_subscribe"))
     rp_year_start = InsertAP(request.Form("year_start"))
     rp_year_end = InsertAP(request.Form("year_end"))
     rp_n_america = InsertAP(request.Form("n_america"))
     rp_latin = InsertAP(request.Form("latin"))
     rp_e_europe = InsertAP(request.Form("e_europe"))
     rp_w_europe = InsertAP(request.Form("w_europe"))
     rp_middle_east = InsertAP(request.Form("middle_east"))
     rp_asia = InsertAP(request.Form("asia"))
0
 
LVL 2

Expert Comment

by:sonicysa
ID: 17933142
need to know the datatypes for each of your params.
Just add in the correct ones.


if request.form("formsubmit") = "true" Then
'Open Database Connection
Set openConn = Server.CreateObject("ADODB.Connection")
openPath= "Driver={SQL Server};" & _
           "Server=(local);" & _
           "Database=KlineGroup;" & _
             "Trusted_Connection=yes"
openConn.open openPath
openConn.close 'make sure old connections are cleaned out
openConn.open openPath
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = openConn
    .CommandText = "INSERT into report (rp_title, rp_date, rp_desc, rp_category, rp_code, rp_link_page, rp_link_brochure, rp_link_subscribe, rp_year_start, rp_year_end, rp_n_america, rp_latin, rp_e_europe, rp_w_europe, rp_middle_east, rp_asia) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"

    .Parameters.Append .CreateParameter("@rp_title", ad_, adParamInput, <--charCount-->)    
    .Parameters("@rp_title") = request.Form("title")
     rp_date_month = request.Form("releasedate_month")
     rp_date_day = request.Form("releasedate_day")
     rp_date_year = request.Form("releasedate_year")
     rp_date = rp_date_month & "/" & rp_date_day & "/" & rp_date_year
.Parameters.Append .CreateParameter("@rp_date", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_date") = rp_date

.Parameters.Append .CreateParameter("@rp_desc", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_desc") = request.Form("description")

     rp_category = request.Form("category")
.Parameters.Append .CreateParameter("@rp_category", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_category") = request.Form("category")

.Parameters.Append .CreateParameter("@rp_code", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_code") = cleantext(request.Form("code"))

.Parameters.Append .CreateParameter("@rp_link_page", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_link_page") = request.Form("link_page")

     rp_link_brochure = request.Form("link_brochure")
.Parameters.Append .CreateParameter("@rp_link_brochure", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_link_brochure") = request.Form("link_brochure")

     rp_link_subscribe = request.Form("link_subscribe")
.Parameters.Append .CreateParameter("@rp_link_subscribe", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_link_subscribe") = request.Form("link_subscribe")

.Parameters.Append .CreateParameter("@rp_year_start", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_year_start") = request.Form("year_start")

.Parameters.Append .CreateParameter("@rp_year_end", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_year_end") = request.Form("year_end")

     rp_n_america = request.Form("n_america")
.Parameters.Append .CreateParameter("@rp_n_america", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_n_america") = request.Form("n_america")

     rp_latin = request.Form("latin")
.Parameters.Append .CreateParameter("@rp_latin", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_latin") = request.Form("latin")

.Parameters.Append .CreateParameter("@rp_e_europe", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_e_europe") = request.Form("e_europe")

     rp_w_europe = request.Form("w_europe")
.Parameters.Append .CreateParameter("@rp_w_europe", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_w_europe") = request.Form("w_europe")

     rp_middle_east = request.Form("middle_east")
.Parameters.Append .CreateParameter("@rp_middle_east", ad_, adParamInput, <--charCount-->)
.Parameters("@rp_middle_east") = request.Form("middle_east")

     rp_asia = request.Form("asia")
.Parameters.Append .CreateParameter("@rp_asia", ad_<!--need Type-->, adParamInput, <--charCount-->)
.Parameters("@rp_asia") = request.Form("asia")
     
<!--- for integers just leave off the " , count "  ---->
 
    .Execute lngRecs, , adExecuteNoRecords

End With

     Set cmd = Nothing
     Set RS = openConn.Execute(SQL_query)
     Set RS = Nothing
End if
0
 
LVL 2

Expert Comment

by:sonicysa
ID: 17933156
Opps sorry after this line:

.CommandText = "INSERT into report (rp_title, rp_date, rp_desc, rp_category, rp_code, rp_link_page, rp_link_brochure, rp_link_subscribe, rp_year_start, rp_year_end, rp_n_america, rp_latin, rp_e_europe, rp_w_europe, rp_middle_east, rp_asia) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"

Add:

.CommandType = adCmdText
0
 

Author Comment

by:bsowards
ID: 17938988
Thanks!

The points are as good as yours. Here are the data-types, if you could just plug in the right settings, that would be great!

rp_title, varchar(MAX)
rp_date, smalldatetime
rp_desc, text
rp_category, int
rp_code, varchar(MAX)
rp_link_page, varchar(MAX)
rp_link_brochure, varchar(MAX)
rp_link_subscribe, varchar(MAX)
rp_year_start, int
rp_year_end, int
rp_n_america, bit
rp_latin, bit
rp_e_europe, bit
rp_w_europe, bit
rp_middle_east, bit
rp_asia, bit

THANK YOU!
0
 

Author Comment

by:bsowards
ID: 17940074
Well... it appears my first expert moved on... anyone else like to finish it for split points?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Expert Comment

by:sonicysa
ID: 17941415
if request.form("formsubmit") = "true" Then
'Open Database Connection
Set openConn = Server.CreateObject("ADODB.Connection")
openPath= "Driver={SQL Server};" & _
           "Server=(local);" & _
           "Database=KlineGroup;" & _
             "Trusted_Connection=yes"
openConn.open openPath
openConn.close 'make sure old connections are cleaned out
openConn.open openPath
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = openConn
    .CommandText = "INSERT into report (rp_title, rp_date, rp_desc, rp_category, rp_code, rp_link_page, rp_link_brochure, rp_link_subscribe, rp_year_start, rp_year_end, rp_n_america, rp_latin, rp_e_europe, rp_w_europe, rp_middle_east, rp_asia) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"

    .Parameters.Append .CreateParameter("@rp_title", adLongVarChar, adParamInput, -1)    
    .Parameters("@rp_title") = request.Form("title")
     rp_date_month = request.Form("releasedate_month")
     rp_date_day = request.Form("releasedate_day")
     rp_date_year = request.Form("releasedate_year")
     rp_date = rp_date_month & "/" & rp_date_day & "/" & rp_date_year
    .Parameters.Append .CreateParameter("@rp_date", adDBTimeStamp, adParamInput)
    .Parameters("@rp_date") = rp_date

    .Parameters.Append .CreateParameter("@rp_desc", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_desc") = request.Form("description")

     rp_category = request.Form("category")
    .Parameters.Append .CreateParameter("@rp_category", adInteger, adParamInput)
    .Parameters("@rp_category") = request.Form("category")

    .Parameters.Append .CreateParameter("@rp_code", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_code") = cleantext(request.Form("code"))

    .Parameters.Append .CreateParameter("@rp_link_page", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_link_page") = request.Form("link_page")

     rp_link_brochure = request.Form("link_brochure")
    .Parameters.Append .CreateParameter("@rp_link_brochure", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_link_brochure") = request.Form("link_brochure")

     rp_link_subscribe = request.Form("link_subscribe")
    .Parameters.Append .CreateParameter("@rp_link_subscribe", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_link_subscribe") = request.Form("link_subscribe")

    .Parameters.Append .CreateParameter("@rp_year_start", adInteger, adParamInput)
    .Parameters("@rp_year_start") = request.Form("year_start")

    .Parameters.Append .CreateParameter("@rp_year_end", adInteger, adParamInput)
    .Parameters("@rp_year_end") = request.Form("year_end")

     rp_n_america = request.Form("n_america")
    .Parameters.Append .CreateParameter("@rp_n_america", adBoolean, adParamInput)
    .Parameters("@rp_n_america") = request.Form("n_america")

     rp_latin = request.Form("latin")
    .Parameters.Append .CreateParameter("@rp_latin", adBoolean, adParamInput)
    .Parameters("@rp_latin") = request.Form("latin")

    .Parameters.Append .CreateParameter("@rp_e_europe", adBoolean, adParamInput)
    .Parameters("@rp_e_europe") = request.Form("e_europe")

     rp_w_europe = request.Form("w_europe")
    .Parameters.Append .CreateParameter("@rp_w_europe", adBoolean, adParamInput)
    .Parameters("@rp_w_europe") = request.Form("w_europe")

     rp_middle_east = request.Form("middle_east")
    .Parameters.Append .CreateParameter("@rp_middle_east", adBoolean, adParamInput)
    .Parameters("@rp_middle_east") = request.Form("middle_east")

     rp_asia = request.Form("asia")
    .Parameters.Append .CreateParameter("@rp_asia", adBoolean, adParamInput)
    .Parameters("@rp_asia") = request.Form("asia")
     
    .Execute lngRecs, , adExecuteNoRecords

End With

     Set cmd = Nothing
     Set RS = openConn.Execute(SQL_query)
     Set RS = Nothing
End if



'K that should be it. We might have to troubleshoot a little.
0
 
LVL 2

Accepted Solution

by:
sonicysa earned 500 total points
ID: 17941433
'needed to add in that other part that I had missed the first time. It's in there now.


if request.form("formsubmit") = "true" Then
'Open Database Connection
Set openConn = Server.CreateObject("ADODB.Connection")
openPath= "Driver={SQL Server};" & _
           "Server=(local);" & _
           "Database=KlineGroup;" & _
             "Trusted_Connection=yes"
openConn.open openPath
openConn.close 'make sure old connections are cleaned out
openConn.open openPath
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = openConn
    .CommandText = "INSERT into report (rp_title, rp_date, rp_desc, rp_category, rp_code, rp_link_page, rp_link_brochure, rp_link_subscribe, rp_year_start, rp_year_end, rp_n_america, rp_latin, rp_e_europe, rp_w_europe, rp_middle_east, rp_asia) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter("@rp_title", adLongVarChar, adParamInput, -1)    
    .Parameters("@rp_title") = request.Form("title")
     rp_date_month = request.Form("releasedate_month")
     rp_date_day = request.Form("releasedate_day")
     rp_date_year = request.Form("releasedate_year")
     rp_date = rp_date_month & "/" & rp_date_day & "/" & rp_date_year
    .Parameters.Append .CreateParameter("@rp_date", adDBTimeStamp, adParamInput)
    .Parameters("@rp_date") = rp_date

    .Parameters.Append .CreateParameter("@rp_desc", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_desc") = request.Form("description")

     rp_category = request.Form("category")
    .Parameters.Append .CreateParameter("@rp_category", adInteger, adParamInput)
    .Parameters("@rp_category") = request.Form("category")

    .Parameters.Append .CreateParameter("@rp_code", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_code") = cleantext(request.Form("code"))

    .Parameters.Append .CreateParameter("@rp_link_page", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_link_page") = request.Form("link_page")

     rp_link_brochure = request.Form("link_brochure")
    .Parameters.Append .CreateParameter("@rp_link_brochure", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_link_brochure") = request.Form("link_brochure")

     rp_link_subscribe = request.Form("link_subscribe")
    .Parameters.Append .CreateParameter("@rp_link_subscribe", adLongVarChar, adParamInput, -1)
    .Parameters("@rp_link_subscribe") = request.Form("link_subscribe")

    .Parameters.Append .CreateParameter("@rp_year_start", adInteger, adParamInput)
    .Parameters("@rp_year_start") = request.Form("year_start")

    .Parameters.Append .CreateParameter("@rp_year_end", adInteger, adParamInput)
    .Parameters("@rp_year_end") = request.Form("year_end")

     rp_n_america = request.Form("n_america")
    .Parameters.Append .CreateParameter("@rp_n_america", adBoolean, adParamInput)
    .Parameters("@rp_n_america") = request.Form("n_america")

     rp_latin = request.Form("latin")
    .Parameters.Append .CreateParameter("@rp_latin", adBoolean, adParamInput)
    .Parameters("@rp_latin") = request.Form("latin")

    .Parameters.Append .CreateParameter("@rp_e_europe", adBoolean, adParamInput)
    .Parameters("@rp_e_europe") = request.Form("e_europe")

     rp_w_europe = request.Form("w_europe")
    .Parameters.Append .CreateParameter("@rp_w_europe", adBoolean, adParamInput)
    .Parameters("@rp_w_europe") = request.Form("w_europe")

     rp_middle_east = request.Form("middle_east")
    .Parameters.Append .CreateParameter("@rp_middle_east", adBoolean, adParamInput)
    .Parameters("@rp_middle_east") = request.Form("middle_east")

     rp_asia = request.Form("asia")
    .Parameters.Append .CreateParameter("@rp_asia", adBoolean, adParamInput)
    .Parameters("@rp_asia") = request.Form("asia")
     
    .Execute lngRecs, , adExecuteNoRecords

End With

     Set cmd = Nothing
     Set RS = openConn.Execute(SQL_query)
     Set RS = Nothing
End if
0
 

Author Comment

by:bsowards
ID: 17943852
Your awesome! thanks so much.
0
 

Author Comment

by:bsowards
ID: 17950100
Whoops! Got an error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Parameterized Query '(@P1 text,@P2 datetime,@P3 text,@P4 int,@P5 text,@P6 text,@P7 te' expects parameter @P11, which was not supplied.

/admin/report2.asp, line 93

Please help!

Thanks
0
 
LVL 2

Expert Comment

by:sonicysa
ID: 17970327
You are going to have to make sure that the proper validation is happening against the request variables.
Make sure that they are not null etc. You asked for a parameterized query.

one thing you could do is response.write out the request variable data so that you can see what is missing or messing up the query.

if you are using the post method you could do this at the start of the processing page.

response.write request.form
response.end



so if n_america is messing you up I would wonder if it is a check box or something like that because those items won't even exist if it isn't checked I believe.
0
 

Author Comment

by:bsowards
ID: 17976421
Hi,

The answer was that checkboxes do not send anything when they aren't checked, so the Reponse.Write value had to be tested and replaced as follows:

'Checkboxes don't send if blank
      if request.Form("n_america") = "" then
        rp_n_america = false
      else
        rp_n_america = true
      end if
    .Parameters.Append .CreateParameter("@rp_n_america", adBoolean, adParamInput)
    .Parameters("@rp_n_america") = rp_n_america

      if request.Form("latin") = "" then
            rp_latin = false
      else
            rp_latin = true
      end if
    .Parameters.Append .CreateParameter("@rp_latin", adBoolean, adParamInput)
    .Parameters("@rp_latin") = rp_latin

      if request.Form("e_europe") = "" then
            rp_e_europe = false
      else
            rp_e_europe = true
      end if
    .Parameters.Append .CreateParameter("@rp_e_europe", adBoolean, adParamInput)
    .Parameters("@rp_e_europe") = rp_e_europe

      if request.Form("w_europe") = "" then
            rp_w_europe = false
      else
            rp_w_europe = true
      end if
    .Parameters.Append .CreateParameter("@rp_w_europe", adBoolean, adParamInput)
    .Parameters("@rp_w_europe") = rp_w_europe

      if request.Form("middle_east") = "" then
            rp_middle_east = false
      else
            rp_middle_east = true
      end if
    .Parameters.Append .CreateParameter("@rp_middle_east", adBoolean, adParamInput)
    .Parameters("@rp_middle_east") = rp_middle_east

      if request.Form("n_asia") = "" then
            rp_asia = false
      else
            rp_asia = true
      end if
    .Parameters.Append .CreateParameter("@rp_asia", adBoolean, adParamInput)
    .Parameters("@rp_asia") = rp_asia
     
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
C# Passing long parameter from one form to another 4 65
Issue with Loop 4 42
Select distinct 25 60
ASP SQL Syntax Duplicate Key 7 67
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now