Solved

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

Posted on 2006-11-09
15
566 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

729 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