Solved

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

Posted on 2006-11-09
15
550 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Classic ASP - SQL Server connection problem 5 61
API not working 33 55
File download script VBSCRIPT fail on download PPT files 2 33
Recordset containing single quotes 8 19
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 …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

911 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

21 Experts available now in Live!

Get 1:1 Help Now