Passing information from a forum field to MSSQL thru ASP

Posted on 2008-11-18
Last Modified: 2012-05-05
I'm having problems getting information from a contact me forum to tables in my MS SQL Server.  I'm not sure how it is done.  I have created the forum fields and I don't know where to go from there.  I can hit submit at this point and it just resets the page and the information I input shows up in the address bar above.  After I get past that point I then need the information to pass to a stored procedure in SQL Server. Anyone have any walk through's for this?
Question by:N R
    LVL 11

    Author Comment

    by:N R
    Ok I have made some headway I now have my sql server connected to dreamweaver, but still unsure of how to code it, here is the code I have from making the form.  I need to insert each of those values into the following columns in the table:


     <label for="name">your name </label>
            <input onfocus="" onblur="if (this.value==''){this.value='enter your name'}" name="name" id="name" type="text" value="enter your name" size="20" />
            <label for="email">your email</label>
            <input onfocus="" onblur="if (this.value==''){this.value='enter your email address'}" name="email" id="email" type="text"  value="enter your email address" size="20" />
            <label for="postalcode">postal code </label>
            <input onfocus="" onblur="if (this.value==''){this.value='enter your postal code'}" name="postalcode" id="postalcode" value="enter your postal code" size="20" />
            <label for="favmag1">Select your favorite interest </label>
    		<select id="favmag1" name="favmag1" >
    		  <option value="0">- - Select your favorite interest - -</option>
    		  <option value="1">Health &amp; Wellness</option>
              <option value="2">Technology</option>
              <option value="3">Travel</option>
              <option value="5">Financial Services</option>
              <option value="4">Internet Advertising</option>
              <option value="6">Gas &amp; Utilities</option>
            <legend>Select your preference:</legend>
            <label for="radioformat1">
            <input name="radioformat" type="radio" id="radioformat1" title="html format" value="" checked="checked"  />
            Full Time </label>
            <label for="radioformat2">
            <input title="text format" type="radio" name="radioformat" id="radioformat2" value=""  />
            Part Time</label>
            <legend>Select your level of interest:</legend>
            <label for="radiosite1">
            <input type="radio" name="radiosite" id="radiosite1" title="" checked="checked"  />
            <label for="radiosite2">
            <input type="radio" name="radiosite" id="radiosite2" title=""  />
            <label for="radiosite3">
            <input type="radio" name="radiosite" id="radiosite3" title=""  />
            <label for="check1">
            <input title="Subscribe" type="checkbox" name="check1" id="check1" value=""  />
            Subscribe to our mailing list</label>
            <input class="button-big" name="Join" style="width:75px" type="submit" value="Submit"  />

    Open in new window

    LVL 8

    Accepted Solution

    Well, you're missing a few fields in your form (phone and income) and you've got a few in your form that aren't in your table (postalcode, favmag1, check1) but assuming you add in those fields to your form, you'll need code like this in the top of your page:

    '      Define variables
    Dim insertCmd, strSQL

    '      Build your SQL INSERT command
    strSQL = "INSERT INTO Table1 (Name, Phone, email, interest, interestlvl, income, createdate) VALUES (" _
          & "'" & Request.Form("name") & "'," _
          & "'" & Request.Form("phone") & "'," _
          & "'" & Request.Form("email") & "'," _
          & "'" & Request.Form("radioformat") & "'," _
          & "'" & Request.Form("radiosite") & "'," _
          & "'" & Request.Form("income") & "'," _
          & "'" & now() & "')" _

    Set insertCmd = Server.CreateObject("ADODB.Command")
    insertCmd.ActiveConnection = YOUR_CONNECTION_STRING            ' Ususally something in the form of MM_name_STRING
    insertCmd.CommandText = strSQL
    insertCmd.CommandType = 1
    'Response.Write(insertCmd.CommandText & "<br>")                  ' Debug line, uncomment to view your command
    Set insertCmd = Nothing

    Ideally you'd be using a stored procedure to prevent SQL injection -- this method has no security on it whatsoever, but it's a good place to start so you can see how things work.
    LVL 11

    Author Comment

    by:N R
    Got the following error:

    Compiler Error Message: BC30807: 'Let' and 'Set' assignment statements are no longer supported.

    Source Error:

    Line 21:       & "'" & now() & "')" _
    Line 22:
    Line 23: Set insertCmd = Server.CreateObject("ADODB.Command")
    Line 24: insertCmd.ActiveConnection = "MM_CONNECTION_STRING_nathan" value="Data; Initial Catalog=gallitin; User ID=blah; Password='test';"            ' Ususally something in the form of MM_name_STRING
    Line 25: insertCmd.CommandText = strSQL
    LVL 8

    Expert Comment

    Are you using ASP.NET or ASP Classic?
    LVL 11

    Author Comment

    by:N R, I got passed that error, now its giving me an error about the connection string let me get it again.
    LVL 11

    Author Comment

    by:N R
    Compiler Error Message: BC30205: End of statement expected.

    Line 22:
    Line 23: insertCmd = Server.CreateObject("ADODB.Command")
    Line 24: insertCmd.ActiveConnection = "MM_CONNECTION_STRING_nathan" value="Data; Initial Catalog=gallitin; User ID=blah; Password='test'"            ' Ususally something in the form of MM_name_STRING
    Line 25: insertCmd.CommandText = strSQL
    Line 26: insertCmd.CommandType = 1
    LVL 11

    Author Comment

    by:N R
    ok got past all of that now and seems like it will work fine except my host doesn't allow partially trusted connections error now.  after research I see that you were right about probably needing to do it with a Stored procedure which I have written, but don't know how to do in the web code.  Would you show me how with this code?  Or I can post another question if you like.
    <%@ Page Language="VB" ContentType="text/html" %>
    <%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls" Assembly="DreamweaverCtrls,version=,publicKeyToken=836f606ede05d46a,culture=neutral" %>
    CommandText='<%# "INSERT INTO dbo.Splash (name, zip, interest, ft, pt, interestlvl1, interestlvl2, interestlvl3, ""check"", phone, email) VALUES (@name, @zip, @interest, @ft, @pt, @interestlvl1, @interestlvl2, @interestlvl3, @check, @phone, @email)" %>'
    ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_nathan") %>'
    DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_nathan") %>'
    Expression='<%# Request.Form("MM_insert") = "signup" %>'
    SuccessURL='<%# "technology.html" %>'
    FailureURL='<%# "Health.html" %>'
        <Parameter Name="@name" Value='<%# IIf((Request.Form("name") <> Nothing), Request.Form("name"), "") %>' Type="VarChar" />
        <Parameter Name="@zip" Value='<%# IIf((Request.Form("zip") <> Nothing), Request.Form("zip"), "") %>' Type="VarChar" />
        <Parameter Name="@interest" Value='<%# IIf((Request.Form("interest") <> Nothing), Request.Form("interest"), "") %>' Type="VarChar" />
        <Parameter Name="@ft" Value='<%# IIf((Request.Form("ft") <> Nothing), Request.Form("ft"), "") %>' Type="VarChar" />
        <Parameter Name="@pt" Value='<%# IIf((Request.Form("pt") <> Nothing), Request.Form("pt"), "") %>' Type="VarChar" />
        <Parameter Name="@interestlvl1" Value='<%# IIf((Request.Form("radiosite1") <> Nothing), Request.Form("radiosite1"), "") %>' Type="VarChar" />
        <Parameter Name="@interestlvl2" Value='<%# IIf((Request.Form("radiosite2") <> Nothing), Request.Form("radiosite2"), "") %>' Type="VarChar" />
        <Parameter Name="@interestlvl3" Value='<%# IIf((Request.Form("radiosite3") <> Nothing), Request.Form("radiosite3"), "") %>' Type="VarChar" />
        <Parameter Name="@check" Value='<%# IIf((Request.Form("check") <> Nothing), Request.Form("check"), "") %>' Type="VarChar" />
        <Parameter Name="@phone" Value='<%# IIf((Request.Form("phone") <> Nothing), Request.Form("phone"), "") %>' Type="VarChar" />
        <Parameter Name="@email" Value='<%# IIf((Request.Form("email") <> Nothing), Request.Form("email"), "") %>' Type="VarChar" />
    <MM:PageBind runat="server" PostBackBind="true" />

    Open in new window

    LVL 8

    Expert Comment

    You might want to post this in the ASP.NET forum instead of the ASP Classic forum -- you'll probably get a lot more help there.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how the fundamental information of how to create a table.

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now