asked on

Adding user data to a SQL Server table

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

wht1986

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

AsishRaj

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

John Carney

ASKER

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

Thanks,
John

John Carney

ASKER

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

wht1986

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

John Carney

ASKER

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

John Carney

ASKER

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

ASKER CERTIFIED SOLUTION

wht1986

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

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

John Carney

ASKER

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

wht1986

I believe you havent configured the directory you uploaded your files to as an application under IIS.

wht1986

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.

John Carney

ASKER

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

wht1986

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

John Carney

ASKER

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

John Carney

ASKER

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

John Carney

ASKER

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

wht1986

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.