Solved

Adding user data to a SQL Server table

Posted on 2009-05-06
18
258 Views
Last Modified: 2012-06-21
Please take a look at this page: http://dijitalrealm.com/Default_beta1E.aspx. How do I complete the simple registration form so that upon submit, the data will be added to a new row in the table "members" in the database "DJRTest1"?

Te connection string below is how it appears in the web.config.

Is that enough info to go on?

Thanks,
John
Data Source=DJRTest1.db.3717092.hostedresource.com;

 Initial Catalog=DJRTest1;

 User ID=User1;

 Password='1234';

Open in new window

0
Comment
Question by:gabrielPennyback
  • 10
  • 7
18 Comments
 
LVL 12

Expert Comment

by:wht1986
ID: 24321868
Without going into a lot of error checking, the basics for doing a sql insert would look like

Adjust field names and control names as necessary
        SqlConnection conn = new SqlConnection("Data Source=DJRTest1.db.3717092.hostedresource.com;Initial Catalog=DJRTest1;User ID=User1;Password='1234';");

        SqlCommand cmd = new SqlCommand("Insert INTO members (FirstName, LastName, EmailAddress) VALUES (@FirstName, @LastName, @EmailAddress)", conn);

        cmd.Parameters.AddWithValue("@FirstName", this.TextBoxFirstName.Text);

        cmd.Parameters.AddWithValue("@LastName", this.TextBoxLastName.Text);

        cmd.Parameters.AddWithValue("@EmailAddress", this.TextBoxEmailAddress.Text);

        conn.Open();

        cmd.ExecuteNonQuery();

        conn.Close();

Open in new window

0
 
LVL 9

Expert Comment

by:AsishRaj
ID: 24322348
using webconfiguration details

 SqlConnection Connection = new SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString()); }

 SqlCommand cmd = new SqlCommand("Insert INTO members (FirstName, LastName, EmailAddress) VALUES (@FirstName, @LastName, @EmailAddress)", conn);

 cmd.Parameters.AddWithValue("@FirstName", this.TextBoxFirstName.Text);

 cmd.Parameters.AddWithValue("@LastName", this.TextBoxLastName.Text);

 cmd.Parameters.AddWithValue("@EmailAddress", this.TextBoxEmailAddress.Text);

 Connection.Open();

 cmd.ExecuteNonQuery();

 Connection.Close();

Open in new window

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 24322492
This looks very promising. I've run out of time tonight, but I'll check it out tomorrow morning.

Thanks,
John
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 24327867
Where would I put this code in the context of my html? And what would have as the action on the Submit button?

Thanks,
John
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24327981
yes on the button click, below is an example aspx page with the markup and code behind.  You can use inline for the connection string or follow Asish example substituing your web.config key in the configuration manager
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <table>

        <tr>

            <td>First Name:</td>

            <td><asp:TextBox ID="TextBoxFirstName" runat="server" /></td>

        </tr>

        <tr>

            <td>Last Name:</td>

            <td><asp:TextBox ID="TextBoxLastName" runat="server" /></td>

        </tr>

        <tr>

            <td>Email:</td>

            <td><asp:TextBox ID="TextBoxEmailAddress" runat="server" /></td>

        </tr>

        <tr>

            <td>&nbsp;</td>

            <td><asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /></td>

        </tr>

    </table>

    </form>

</body>

</html>
 
 

========================
 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;
 

public partial class _Default : System.Web.UI.Page 

{

    protected void Button1_Click(object sender, EventArgs e)

    {

        SqlConnection conn = new SqlConnection("Data Source=DJRTest1.db.3717092.hostedresource.com;Initial Catalog=DJRTest1;User ID=User1;Password='1234';");

        SqlCommand cmd = new SqlCommand("Insert INTO members (FirstName, LastName, EmailAddress) VALUES (@FirstName, @LastName, @EmailAddress)", conn);

        cmd.Parameters.AddWithValue("@FirstName", this.TextBoxFirstName.Text);

        cmd.Parameters.AddWithValue("@LastName", this.TextBoxLastName.Text);

        cmd.Parameters.AddWithValue("@EmailAddress", this.TextBoxEmailAddress.Text);

        conn.Open();

        cmd.ExecuteNonQuery();

        conn.Close(); 

    }

}

Open in new window

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 24328824
Hi wht1986, when I run your code, I get a server error on this line:
<td><asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /></td>

The error is:

               Compiler Error Message: CS1061: 'ASP.registrationtest2_aspx' does not contain a definition for 'Button1_Click' and no extension method 'Button1_Click' accepting a first argument of type 'ASP.registrationtest2_aspx' could be found (are you missing a using directive or an assembly reference?)

Does that make sense?

If it makes a difference my 3 fields are actually named first, last and email.

Thanks,
John




0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 24328850
Here's my actual code, with the field names corrected.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <table>

        <tr>

            <td>First Name:</td>

            <td><asp:TextBox ID="TextBoxFirstName" runat="server" /></td>

        </tr>

        <tr>

            <td>Last Name:</td>

            <td><asp:TextBox ID="TextBoxLastName" runat="server" /></td>

        </tr>

        <tr>

            <td>Email:</td>

            <td><asp:TextBox ID="TextBoxEmailAddress" runat="server" /></td>

        </tr>

        <tr>

            <td> </td>

            <td><asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /></td>

        </tr>

    </table>

    </form>

</body>

</html>

 

 

========================

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

 

public partial class _Default : System.Web.UI.Page 

{

    protected void Button1_Click(object sender, EventArgs e)

    {

        SqlConnection conn = new SqlConnection("Data Source=DJRTest1.db.3717092.hostedresource.com;Initial Catalog=DJRTest1;User ID=DJRTest1;Password='Password1';");

        SqlCommand cmd = new SqlCommand("Insert INTO members (first, last, email) VALUES (@first, @last, @email)", conn);

        cmd.Parameters.AddWithValue("@first", this.first.Text);

        cmd.Parameters.AddWithValue("@last", this.last.Text);

        cmd.Parameters.AddWithValue("@email", this.email.Text);

        conn.Open();

        cmd.ExecuteNonQuery();

        conn.Close(); 

    }

}

Open in new window

0
 
LVL 12

Accepted Solution

by:
wht1986 earned 500 total points
ID: 24329150
what you posted wouldnt work.  

i.e. you have blocks like this: "this.first.Text"
there is no textbox element with name "first", it should have an ID of TextBoxFirstName

Regardless, to help you out, please download a fully working web site I wrote for you at http://www.kevinwhite.net/ForGabriel.zip

It's just one page and a web.config file. I know it works fine because you posted an actual username/password in the post, and I was able to insert a record.

I would strongly suggest changing the password soon, before someone notices and uses it for their own purposes.
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 24329523
Wow, thanks! I can see that you did add a record ... two in fact. Let me try it out and see if I can adapt it successfully.

John
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:gabrielPennyback
ID: 24329613
Kevin, I really appreciate your help, I'm glad I inadvertently left the real password in. I'll change it once I get this up and running. I uploaded the three files to the server and now I get this error when I run Default.aspx:

Parser Error Message: It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level.  This error can be caused by a virtual directory not being configured as an application in IIS.

              Source Error:

                                                                        Line 27:             ASP.NET to identify an incoming user.
Line 28:         -->
Line 29:             <authentication mode="Windows"/>
Line 30:             <!--
Line 31:             The <customErrors> section enables configuration                                                                
               Source File:  D:\Hosting\3717092\html\basicforms\web.config    Line:  29            
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24329730
I believe you havent configured the directory you uploaded your files to as an application under IIS.
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24329768
It's a subdirectory under an already existing asp.net application.  Just copy the connection string section to the web.config of the root application, and you can get rid of the one in the subdirectory.  But my personal preference would be to make "D:\Hosting\3717092\html\basicforms" (i believe from what i see) an IIS application.
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 24330400
Wow, so many places to get lost I don't know where to begin! :-)

1)What is the name of the subdirectory which I haven't configured?
2) What do you mean byt it's "under an already existing asp.net application"? By "existing asp.net application," what do you mean? Another directory? the root of the site itself? The fact that it is a Windows server? ???
3) The  web.config on my server already has the connection string. What;' the subdirectory I should remove it from?

Also, where is   "D:\Hosting\3717092\html\basicforms"?  How do I navigate to it?   All I can see is this: DJRTest1.db.3717092.hostedresource.com

While I'm waiting to hear back from you, I'll call the web host and see if they can help me with the IIS7 thing.

Thanks for your patience, which I hope isn't wearing too thin yet.

John

  <connectionStrings>

    <add name="DJRTest1ConnectionString" connectionString="Data Source=DJRTest1.db.3717092.hostedresource.com;Initial Catalog= DJRTest1;User ID=DJRTestXX;

Password=PasswordXX"

 providerName="System.Data.SqlClient" />

  </connectionStrings>

Open in new window

0
 
LVL 12

Expert Comment

by:wht1986
ID: 24330608
Ok this is starting to get way beyond the scope of your initial question, which I have solved
but ...

The following is an example of how I would set up this appliaction on one of our server.
The default root folder for IIS is c:\inetpub\wwwroot
Let's say this machine name is called "Dagobah"
By default, I can browse to http://Dagobah and see whatever pages I have copied to c:\inetpub\wwwroot

Now let's say I want to create an application based on that zip file I gave you.  
First I would create a subdirectory under c:\inetpub\wwwroot on Dagobah and maybe I will call it Gabriel
Thus the path on the system is c:\inetpub\wwwroot\Gabriel

if I were to browse to http://Dagobah/Gabriel right now, i would get that "Parser Error Message" you got before.

I need to login to the server, open IIS manager, and create a virtual directory (application) to the path c:\inetpub\wwwroot\Gabriel

I would also need to make sure the virtual directory (application) is set to run asp.net 2.0

Then when I browsed to http://Dagobah/Gabriel the application would come up.

If you are hosting the files at a remote internet service provider, you will have to work with your ISP via their control panel to get an application running.

If you have full access to the server and to IIS manager, here are some links to help get you set up

IIS 7: http://www.affiliatewiz.com/support/appstartpoint.asp
IIS 6: http://www.startvbdotnet.com/aspsite/extras/virtualdirectory.aspx
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 24330738
Ignore my last post. It works perfectly. I just had to put it on the root level of the site, I had it in its own folder.

Thanks so much! I'm going to ask a followup question. When I've done that, I'll come back here and post the link to it before closing the question.

Thanks, Kevin
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 24331098
Kevin, I'm sorry I didn't see your last post. Let me just award you points and then I'll ask followup questions in small increments.

Thanks,
John
0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 31578840
Thanks, Kevin. I'm sorry for not putting your files on the root level, which would have saved you some aggravation. I'll try to make it up to you on the followup questions.

- John
0
 
LVL 12

Expert Comment

by:wht1986
ID: 24331192
I'm not too worried about points, its just when you cover many topics under a single question, when someone else searches for an answer to a related question they might not be able to find the solution because it is buried down in one of the posts.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
In this tutorial viewers will learn how to position items using CSS's three positioning types Create a new HTML document with an internal stylesheet.: Create another div in CSS and name it Absolute : Type "position:absolute;" and "top:10px; left:50p…
In this tutorial viewers will learn how to embed videos in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <video> tag to insert a video. Define the src as the URL of your video; this is similar to …

747 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

12 Experts available now in Live!

Get 1:1 Help Now