Solved

ASP VBScript:  How to adjoin or concatenate 2 form fields to insert into one database field.

Posted on 2006-11-09
15
545 Views
Last Modified: 2008-02-01
Hello. I am using ASP VBScript with an Access database.

I have a simple INSERT form for an event.

Table name:  event_eve
Field names: {id_eve; title_eve; desc_eve; datestart_eve; dateend_eve; timestart_eve; timeend_eve}

The "timestart/timeend" fields are formatted as "12:00 PM", or "Medium Date" in Access.

I have a LIST form element for the "timestart/timeend" fields.  My labels/values are listed as "1:00; 1:30; 2:00; etc.".  I added a radio-button pair for each time list for "AM" and "PM".  One pair is named "starttime" and the other is "endtime".

I would like to append/adjoin/concatenate the "AM" / "PM" values to be inserted with the time selected from each time list.


Example:  If selecting "12:00" for "timestart", I would like to select the "PM" radio button and have the value of "12:30 PM" inserted into the database field of "timestart_eve".


Is this possible?
If so, I would greatly appreciate assistance in doing this.

Thanks so much,
Shane
0
Comment
Question by:lshane
  • 8
  • 7
15 Comments
 
LVL 22

Expert Comment

by:WMIF
ID: 17910687
not a problem.  we can try just concatinating them together first:

query = "insert into table set datecol = #" & request.form("date") & " " & request.form("time") & " " & request.form("ampm") & "#"
0
 

Author Comment

by:lshane
ID: 17910764
Thanks for replying, WMIF.  I'm not a purist on ASP, yet, so I have to depend on Dreamweaver.  I know the Experts are pretty much anti-Dreamweaver, but if you could help me with it, I would appreciate it.  If not, I may need some assistance setting it up and inserting from the purist standpoint.  I'm not exactly sure where to place your phrase.
=================================================================
Here is the INSERT code from DW:

If (CStr(Request("MM_insert")) <> "") Then

  ' create the sql insert statement
  MM_tableValues = ""
  MM_dbValues = ""
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_tableValues = MM_tableValues & ","
      MM_dbValues = MM_dbValues & ","
    End If
    MM_tableValues = MM_tableValues & MM_columns(MM_i)
    MM_dbValues = MM_dbValues & MM_formVal
  Next
  MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

  If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>
=================================================================

If that's too much, I understand.

Thank you,
Shane
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17910891
oh sure, sneak the dreamweaver in AFTER someone answers. :)  give a few minutes to go through that mess that dreamweaver creates.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17910918
i think this will take longer to try to make that dreamweaver code work than it would to just start from scratch.  you need to put all the column names in the insert statement.  i just realized that my first comment was slightly off.  set is used with an update, not an insert.


query = "insert into table (col1, col2, col3) values (" & request.form("col1") & "," & request.form("col2") & ",#" & request.form("date") & " " & request.form("time") & " " & request.form("ampm") & "#)"
0
 

Author Comment

by:lshane
ID: 17910957
Hi, WMIF.  I'm sure you're right, and I want to learn this method anyway.  So, I have a couple of questions before I begin trying to construct the INSERT statement:

What are the "#" signs for?  I see that the commas seem to separate each field/column data, but I'm not sure what the "#" signs do.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17910965
>>I'm sure you're right, and I want to learn this method anyway.  So, I have a couple of questions before I begin trying to construct the INSERT statement:

this is what i love to hear.  dreamweaver becomes a crutch and you will become a much better developer if you can stop using that crutch.  i use dreamweaver to develop my pages, but it doesnt write a single line of code for me.  i like the environment that it has.  ask all the questions you need to understand whats going on here.

the # signs are specific to access.  in the sql query, access uses those signs to signify that the value between them is a date or time value.  sql uses single quotes ' to do this.
0
 

Author Comment

by:lshane
ID: 17911033
Great.  So, if I understand some things correctly, it seems I have to establish a variable for the name of the INSERT "query".  I have this, so far:


Dim Conn
Dim query

title_eve = request("title_eve")
desc_eve = request("desc_eve")
datestart_eve = request("datestart_eve")
dateend_eve = request("dateend_eve")
timestart_eve = request("timestart_eve")
timeend_eve = request("timeend_eve")
starttime = request("starttime")
endtime = request("endtime")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\database\ccdata.mdb")& ";"
Conn.Open

SQLTemp = "INSERT INTO event_eve (title_eve, desc_eve, datestart_eve, dateend_eve, timestart_eve, timeend_eve, starttime, endtime) VALUES ('" & title_eve & "', '"& desc_eve &"', '" & datestart_eve & "',  '" & dateend_eve & "', '" & timestart_eve & " &" " & "starttime & "', '" & timeend_eve & " & " " & "endtime & "')"


Does that look right?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:lshane
ID: 17911075
"Starttime" and "Endtime" are the name of the 2 radio-button pairs that have values of "AM" and "PM".  

When the user selects a time from the "Start" list, I would want them to select the "AM" or "PM" radio button.  I'm trying to get those 2 values entered into the "timestart_eve" field of the database.

User selects:  "12:00"  from the "timestart_eve" form field list.
Then selects:  "PM"  from the "starttime" radio button.

The value entered into the "timestart_eve" database field should be:  "12:00 PM".

That's my ultimate objective.
0
 

Author Comment

by:lshane
ID: 17911084
I have to drive to my home, which will take me about 35 minutes.  I'll check the post again at that time.  Thanks so much for your help, WMIF.  I really want to learn this method, as it also seems to allow for more customizing and flexibility.


I'll touch base in an hour.

Thanks,
Shane
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17911107
you are definately on the right track.  first thing i would say is that you should not get into the habit of using request().  that is the parent object and can make things confusing when debuging.  its best to specify where exactly it is coming from.  usually query string or form.

title_eve = request.form("title_eve")
desc_eve = request.form("desc_eve")
datestart_eve = request.form("datestart_eve")
dateend_eve = request.form("dateend_eve")
timestart_eve = request.form("timestart_eve")
timeend_eve = request.form("timeend_eve")
starttime = request.form("starttime")
endtime = request.form("endtime")


you dont really need to assign all those variables before the query is built, but its fine either way.  while you are doing that though, you might as well handle the sql injection.  any field in the db that is a string needs any single quotes escaped by adding another single quote in front of it.  do a google search on sql injection and you can find out more information about it.  basically though it could allow a malicious user to put data in, or get data out of your db.  to do this, we use a simple replace() function to replace all ' with '' (2 single quotes).

title_eve = replace(request.form("title_eve"),"'","''")
desc_eve = replace(request.form("desc_eve"),"'","''")
datestart_eve = request.form("datestart_eve")
dateend_eve = request.form("dateend_eve")
timestart_eve = request.form("timestart_eve")
timeend_eve = request.form("timeend_eve")
starttime = request.form("starttime")
endtime = request.form("endtime")



now onto the query.  since you are going against an access db, you need those # signs for the date/time values.  you also need to have the actual names of the fields in your db.  in that query you have all the names of the form fields, but you wanted to combine a couple into one field.

SQLTemp = "INSERT INTO event_eve (title_eve, desc_eve, datestart_eve, dateend_eve, timestart_eve, timeend_eve, starttime, endtime) VALUES ('" & title_eve & "', '"& desc_eve &"', #" & datestart_eve & "#,  #" & dateend_eve & "#, #" & timestart_eve & " " & starttime & "#, #" & timeend_eve & " " & endtime & "#)"
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17911125
hmm, are you storing those as strings then?  if so just change all those # to ' back.  i straightened out the & signs and quotes for you though.  ill be leaving here shortly as well, but ill be off for the rest of the night.  ill be right back here for you in the morning though.
0
 

Author Comment

by:lshane
ID: 17911458
Hi, WMIF.  That's great.  Thanks for all your help.  I know you're gone for the night, but I'll post my next question anyway.

I think I see how you have joined the 2 values together ("timestart_eve" and "starttime", etc.).  Is there a way to place a space between the two?  Maybe &" " &, or something?

Also, I've seen the "SQLTemp =" around the net some.  Does that have to be used that way, or can "SQLTemp" be named anything I want, as long as it is declared at the top?
0
 
LVL 22

Accepted Solution

by:
WMIF earned 500 total points
ID: 17916134
>> Is there a way to place a space between the two?  Maybe &" " &, or something?

if you look closely at the query i gave you, you will see the space in between.  it is exactly as you are saying though.
...#" & timestart_eve & " " & starttime & "#...


>>Also, I've seen the "SQLTemp =" around the net some.  Does that have to be used that way, or can "SQLTemp" be named anything I want, as long as it is declared at the top?

that is exactly correct.  the variable name doesnt matter one bit.  i use "query" for my variable name because it happens to fit nicely with indents and such.
query = "select ...
  [tab]   "from ....
  [tab]   "where ...
0
 

Author Comment

by:lshane
ID: 17940477
This was great, WMIF.  I appreciate it.  I may be back for more questions, but I think I'm OK for now.


Thanks again!

Shane
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17943223
ill be here when you need it.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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/…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

22 Experts available now in Live!

Get 1:1 Help Now