• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

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

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
lshane
Asked:
lshane
  • 8
  • 7
1 Solution
 
WMIFCommented:
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
 
lshaneAuthor Commented:
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
 
WMIFCommented:
oh sure, sneak the dreamweaver in AFTER someone answers. :)  give a few minutes to go through that mess that dreamweaver creates.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
WMIFCommented:
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
 
lshaneAuthor Commented:
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
 
WMIFCommented:
>>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
 
lshaneAuthor Commented:
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
 
lshaneAuthor Commented:
"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
 
lshaneAuthor Commented:
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
 
WMIFCommented:
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
 
WMIFCommented:
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
 
lshaneAuthor Commented:
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
 
WMIFCommented:
>> 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
 
lshaneAuthor Commented:
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
 
WMIFCommented:
ill be here when you need it.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now