• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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

0
John Carney
Asked:
John Carney
  • 10
  • 7
1 Solution
 
wht1986Commented:
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
 
AsishRajCommented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
This looks very promising. I've run out of time tonight, but I'll check it out tomorrow morning.

Thanks,
John
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
wht1986Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
wht1986Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
wht1986Commented:
I believe you havent configured the directory you uploaded your files to as an application under IIS.
0
 
wht1986Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
wht1986Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
wht1986Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now