ASP.NET & MS SQL -- Putting Form Data into a Table Entry

[dt]

Hi everyone,

I'm really a beginner at ASP.NET and haven't much prior knowledge but I have worked with other languages like Java, C++, HTML and so forth so hopefully I can pick this up lately.

What I'd like to do in ASP.NET is create a registration form so that users on a site can register for an account. The account data is then transferred to a row in a MS SQL database table (the password needs to be encrypted somehow). Then there needs to be a login mechanism so that the user can access a "logged in only" page.

Now I have Microsoft Expression Web 2 ( as well as MS Visual Web Developer 2008 Express ) in which I have been able to make the registration form using the "CreateUserWizard" control. But my question is, how do I link this up to the MS SQL database? In Expression Web 2 (in which I have been working), I can place a SqlDataSource control into the page and it connects to the database successfully -- I know this because using the GridView control successfully allows me to edit and view entries in the desired table -- but I need the registration form and login form (which is another control) to be connected to the MS SQL database.

So basically my question is: how can I bind the user account system to the desired database? :/

I have been looking on Google and doing some research and it's gotten me about this far but I really don't know where to turn next.

Your help would be very much appreciated.

Thank you!

dt

[Rohdekill]

insert user registration into SQL:

ASP.NET Backend example:

Dim strSQL As String
		Dim sqlCmd As New SqlCommand
		Try
			strSQL = "Enroll"
			sqlCmd.Connection = New SqlConnection(ConnectionString)
			sqlCmd.CommandType = CommandType.StoredProcedure
			sqlCmd.CommandText = strSQL
			sqlCmd.Parameters.Add(New SqlParameter("@Callsign", SqlDbType.NVarChar, 50, ParameterDirection.Input))
			sqlCmd.Parameters.Add(New SqlParameter("@Pass", SqlDbType.NVarChar, 50, ParameterDirection.Input))
			sqlCmd.Parameters.Add(New SqlParameter("@Fullname", SqlDbType.NVarChar, 50, ParameterDirection.Input))
			sqlCmd.Parameters.Add(New SqlParameter("@Email", SqlDbType.NVarChar, 50, ParameterDirection.Input))
			sqlCmd.Parameters.Add(New SqlParameter("@Question", SqlDbType.NVarChar, 50, ParameterDirection.Input))
			sqlCmd.Parameters.Add(New SqlParameter("@Answer", SqlDbType.NVarChar, 50, ParameterDirection.Input))
			sqlCmd.Parameters.Add(New SqlParameter("@Joined", SqlDbType.SmallDateTime, ParameterDirection.Input))
			sqlCmd.Parameters("@Callsign").Value = Trim(txtUser.Text)
			sqlCmd.Parameters("@Pass").Value = Trim(txtpass.Text)
			sqlCmd.Parameters("@FullName").Value = Trim(TxtFname.Text) + " " + Trim(TxtLname.Text)
			sqlCmd.Parameters("@Email").Value = Trim(Txtemail.Text)
			sqlCmd.Parameters("@Question").Value = Trim(txtquestion.Text)
			sqlCmd.Parameters("@Answer").Value = Trim(txtanswer.Text)
			sqlCmd.Parameters("@Joined").Value = Now()
			sqlCmd.Connection.Open()
			sqlCmd.ExecuteNonQuery()
			sqlCmd.Connection.Close()
		Catch ex As Exception
			If sqlCmd.Connection.State = ConnectionState.Open Then
				sqlCmd.Connection.Close()
			End If
		End Try

SQL Stored Procedure:

ALTER PROCEDURE [dbo].[Enroll] 
	-- Add the parameters for the stored procedure here
@Callsign as nvarchar(50),
@Pass as nvarchar(50),
@FullName as nvarchar(50),
@Email as nvarchar(50),	
@Question as nvarchar(50),
@Answer as nvarchar(50),
@Joined as datetime
AS
BEGIN
SET NOCOUNT ON;
insert into [User] (CallSign, Pass, FullName, Email, Question, Answer, Joined)
values (@Callsign, @Pass, @FullName, @Email, @Question, @Answer, @Joined)
END

[dt]

How do I put that VB-ish code you posted into the ASP.NET container so it is interpreted properly? Do I put the Dim [. . .] End Try code into a <% %> block on the page? I'm new to ASP so I don't really know how to implement it. :/ do I need to specify at the top of the page's code that I'm using that VB (is it VB? VB.NET? it looks like VB to me) as a language for the code?

Thanks :pinch: Sorry for my newb-ness.

[Pc_Madness]

How do I put that VB-ish code you posted into the ASP.NET container so it is interpreted properly? Do I put the Dim [. . .] End Try code into a <% %> block on the page? I'm new to ASP so I don't really know how to implement it. :/ do I need to specify at the top of the page's code that I'm using that VB (is it VB? VB.NET? it looks like VB to me) as a language for the code?

Thanks :pinch: Sorry for my newb-ness.

You need to put it in the Code behind. When you make a page in Visual Studio, it'll give you an option to put code in a seperate file. Then draw a submit button (an ASP one) in design view, double click on it, and you'll goto the code behind, where you can use the above code. You don't need to use stored procedures either.

tis in C#, but you get the general idea. (transaction is optional)

string sQuery = "INSERT INTO MY TABLE (@Value1)";

SqlCommand MyCommand = new SqlCommand(sQuery, conn, transaction??);

MyCommand.Parameters.Add(new SqlParameter("@Value1", variable));

MyCommand.ExecuteNonQuery();

Edited August 4, 2008 by Pc_Madness

[Rohdekill]

Madness is correct, you don't need a stored procedure. I did it that way so it would be easier for you to follow.

In ASP.NET, you'll rarely use <% %> entries on the front end.

[Antaris Veteran]

Madness is correct, you don't need a stored procedure. I did it that way so it would be easier for you to follow.

Depends how much you want to abstract different logical layers of your application/site

In ASP.NET, you'll rarely use <% %> entries on the front end.

It does still amaze me how many people still perfere the single file model, I personally prefer the CodeBehind format, but a lot of people still do it.

[Josh]

I've written apps both ways, and even nTier style development is by far the most organized you can go, sometimes it has seemed like overkill. Also keep in mind, I design AND develop in Dreamweaver. I don't use VS.NET. So to use codebehind takes a few extra seconds, and sometimes it just doesn't seem worth it.

The same goes for Sprocs. Totally makes sense, and for large apps there's no questions about using them. But at times they're overkill, IMHO. But to each his own.

[Rohdekill]

It's best to use code behind in a team development environment. Most of the projects I do at work involve someone else. So as one is in the designer end, the other can code behind. As long as item names (labels, buttons, etc.) are defined before hand, it usually goes pretty smooth.