Results 1 to 7 of 7
  1. #1
    cgschlangen
    Guest

    Combine multiple form fields into one db field...

    Greetings all -
    I'm building an ASP form that users will use to post data to a database before it get sent off to the National Weather Service... For ease of use I created a bunch of drop town tables with different options for the users... hence where NWS wants "CYF02" - the users can select Cloudy from one drop down box, Foggy from another, and 02 Miles of Visibility from another - now from there - I want to be able to include the 3 selections from the drop down box and merge them into one field... There are several occasions throughout the form where I have need of this - and I'm absolutely stumped - it seems like it should be easy to me... but I'm fairly new to ASP and am not quite sure how to make it work.... Here's the code I'm using...

    <%
    Dim conn

    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open(Server.Mappath("weather.mdb"))
    conn.Execute("insert into Weather (ID, WXVSB, WIND, WAVE, SEA, AIR, PRESSURE, REMARKS, STATION) VALUES (" _
    & Request.Form("id") & ", " _
    & Request.Form("wx") + Request.Form("add") + Request.Form("vis") & ", " _
    & Request.Form("wind") + Request.Form("speed") & ", " _
    & Request.Form("wave") & ", " _
    & Request.Form("sea") & ", " _
    & Request.Form("air") & ", " _
    & Request.Form("pressure") & ", " _
    & Request.Form("plus") + Request.Form("inches") & ", " _
    & Request.Form("station") & ")")
    conn.Close
    Set conn = Nothing
    %>

    Any suggestions would be greatly appreciated.
    Thanks
    Dan

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine multiple form fields into one db field...

    Hi Dan,

    There are several ways to accomplish this and it looks like you're very close.

    I'm not familiar with whic fields need to be concatenated, but I would suggest replacing your plus (+) with ampersand (&), like this:

    Request.Form("wx") & Request.Form("add") & Request.Form("vis")

    Normally, you could use plus as a concatenation operator in SQL Server, but since you're doing this with VBScript you need ampersand. Also, FYI: Access uses ampersand as a concatenation character rather than plus.

    Finally, if you have any string modification needs (such as Trim(), Right(), or Left()) I would suggest doing them ASP side rather than database. It's just a little cleaner at this point.

    Hope this helps!

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Combine multiple form fields into one db field...

    What, if anything, is ending up in the new record you're inserting? Or do you get an ADODB.Error message back?

  4. #4
    cgschlangen
    Guest

    Re: Combine multiple form fields into one db field...

    It's giving me this error:
    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error in INSERT INTO statement.
    /d9/d/units/groups/detroit/asp/weather/addwx.asp, line 7

    Line 7 is the beginning of my execute statement which I have posted above... I'm going to try a couple other things to see if I can figure it out - but I still look forward to your advice - if you have any for me [img]/forums/images/smilies/smile.gif[/img]

    Mark - I'm not sure how the Trim() Left() RIght() would help me in this situation --- the values of my form fields (that are sent to the database) are all the required code - as long as I can get two values to appear next to each other in the same field I would be ecstatic... I'll try and explain a little more what I'm trying to do...

    The form has 15 fields - that lay out the weather of the area they are at... So it looks something like this
    Station ID: (30G, 31G, 24K, etc...)
    Station Name: (Marblehead, Toledo, Cleveland, etc...)
    Wave Height
    Water Temperature
    Air Temperature
    Air Pressure

    The options below need to be combined together as a group in one field...
    Wind (NW, NNW, SSW, etc...)
    Speed (20 knots, 30 knots, etc...)

    Weather ( Cloudy, Partly Cloudy, Clear, etc...)
    Additional (Hazy, Smokey, Foggy, Thunderstorm, etc...)
    Visibility (7 miles, 6 miles, 5 miles etc...)

    Plus ( +, - )
    Water Level ( 20, 30, 40, etc...)

    When they are all inputted into the database I'll be able to use CDONTS to mail them directly to the NWS's processor in a format that looks like the added attachment... (which I'll probably be back here for - lol)
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine multiple form fields into one db field...

    Dan,

    As I mentioned before, your on the right track. Switch your Plus signs (+) with Ampersands (&) and you'll get exactly the result that you need.

    For exampe, this line:
    & Request.Form("wx") + Request.Form("add") + Request.Form("vis") & ", " _
    should actually read
    & Request.Form("wx") <font color=red>&</font color=red> Request.Form("add") <font color=red>&</font color=red> Request.Form("vis") & ", " _

    And this line:
    & Request.Form("plus") + Request.Form("inches") & ", " _
    should now read:
    & Request.Form("plus") <font color=red>&</font color=red> Request.Form("inches") & ", " _

    Does that make sense? The proper VBScript concatenation operator is Ampersand rather than plus. Plus is currently acting as an addition operator - trying to add the values together. Since one or more of these values are strings (text), this is one reason for your error.

    Hope this helps

    By the way, CDONTS is a breeze! Check out this page for a quick tutorial.

  6. #6
    cgschlangen
    Guest

    Re: Combine multiple form fields into one db field...

    I figured it out using the ADO Methods instead... I couldn't get the SQL to work no matter what I tried... but it's a small program for the most part - so I don't think users will be able to really tell any difference in speed... and it's on our intranet so that should help too....
    Thanks for your help [img]/forums/images/smilies/smile.gif[/img]
    Dan

  7. #7
    New Lounger
    Join Date
    Aug 2003
    Location
    Schenectady, New York, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine multiple form fields into one db field...

    I know you said you had fixed this, but for future reference, there are two problems with that SQLstatement. First of all was already stated changing the + to &. The second your missing the ' before and after each data set your introducing into your database. I'm going to assume from what you described what your doing your entering the data into a text formatted field. so Basically replace your "," with "', '" where ever you need. Hope that helps for the next project you work on.

    Conan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •