Updating one record in SQL Server

[token]

Time for another dumb question....from me.

I have this web application where you submit a form on the web, and it will be stored in an SQL Server database. The form will then be viewed by retrieving that info from the database then possibly editted and approved by an authority. My problem is, how do I update that one record after displaying it to the authority. The form has well over 40 fields.

What is the simplest way to do this?


Thanks again.

[Magiaus]

Does the record have a primary key? Is it a record in one table or is it spread across multi tables?

ALTER PROCEDURE dbo.Contacts_SetContactByContactGuid
(
	@ContactGuid uniqueidentifier,
	@EmailAddress varchar(300),
	@IsActive bit,
	@Type varchar(300),
	@Password varchar(300),
	@FirstName varchar(50),
	@MiddleName varchar(50),
	@LastName varchar(50),
	@PhoneNumber varchar(50),
	@Gender bit,
	@Birthday datetime,
	@FaxNumber varchar(50),
	@MobileNumber varchar(50),
	@WebURL varchar(1000)
)
AS
	UPDATE    Contacts
	SET              EmailAddress = @EmailAddress, IsActive = @IsActive, Password = @Password, FirstName = @FirstName, MiddleName = @MiddleName, 
	                      LastName = @LastName, PhoneNumber = @PhoneNumber, FaxNumber = @FaxNumber, MobileNumber = @MobileNumber, WebURL = @WebURL, 
	                      Gender = @Gender, Birthday = @Birthday, Type = @Type
	WHERE     (Guid = @ContactGuid)
	RETURN

You'll notice the where statement based on the primary key Contacts.Guid. I usually fire an Statement like this in a button after doing any validation/field checking I need to do. If you have SQL Manager or Access you use the GUI to build this type of staement quite simply.

[token]

Thanks again Magiaus, you're always to the rescue.

The table does have a primary key and all the info is in one table.
So from what you left me here, that is a stored procedure in SQL, and you just call that from the button.

What does the setting of the parameters look like on the vb.net side?

[Magiaus]

That's cause I can't find a job and I'm bored lol. http://planet-source-code.com/vb/scr...1698&lngWId=10 that is some code I wrote a few years ago it's in VB.Net and it helps with stored procedures. I honestly haven't used VB.Net in so long I can't remember the syntax well enough to give a good example but that should help.

And here is a C# example

public void Save()
{
    if(!Helpers.Common.IsGuidNull(_key))
    {//update
        Helpers.SqlProcedure.ExecuteNonQuery
            ("Contacts_SetContactByContactGuid",
            new System.Data.SqlClient.SqlParameter[]{
                                                        new System.Data.SqlClient.SqlParameter("@ContactGuid", _key),
                                                        new System.Data.SqlClient.SqlParameter("@EmailAddress", _email),            
                                                        new System.Data.SqlClient.SqlParameter("@IsActive", _isActive),            
                                                        new System.Data.SqlClient.SqlParameter("@Type", _type),                
                                                        new System.Data.SqlClient.SqlParameter("@Password", _password),                
                                                        new System.Data.SqlClient.SqlParameter("@FirstName", _firstName),                
                                                        new System.Data.SqlClient.SqlParameter("@MiddleName", _middleName),                
                                                        new System.Data.SqlClient.SqlParameter("@LastName", _lastName),    
                                                        new System.Data.SqlClient.SqlParameter("@Birthday", _birthday),
                                                        new System.Data.SqlClient.SqlParameter("@Gender", _gender),                
                                                        new System.Data.SqlClient.SqlParameter("@PhoneNumber", _phoneNumber),                
                                                        new System.Data.SqlClient.SqlParameter("@FaxNumber", _faxNumber),                
                                                        new System.Data.SqlClient.SqlParameter("@WebURL", _webURL),                
                                                        new System.Data.SqlClient.SqlParameter("@MobileNumber", _mobileNumber)},        
            base.ConnectionString);
    }
    else
    {//insert
        System.Data.SqlClient.SqlParameter pKey = new System.Data.SqlClient.SqlParameter();
        pKey.SqlDbType = System.Data.SqlDbType.UniqueIdentifier;
        pKey.Direction = System.Data.ParameterDirection.Output;
        pKey.ParameterName = "@ContactGuid";
        pKey.IsNullable = false;
        
        Helpers.SqlProcedure.ExecuteNonQuery
            ("Contacts_AddContact",
            new System.Data.SqlClient.SqlParameter[]{
                                                        pKey,
                                                        new System.Data.SqlClient.SqlParameter("@EmailAddress", _email),            
                                                        new System.Data.SqlClient.SqlParameter("@IsActive", _isActive),            
                                                        new System.Data.SqlClient.SqlParameter("@Password", _password),                
                                                        new System.Data.SqlClient.SqlParameter("@Type", _type),                
                                                        new System.Data.SqlClient.SqlParameter("@FirstName", _firstName),                
                                                        new System.Data.SqlClient.SqlParameter("@MiddleName", _middleName),                
                                                        new System.Data.SqlClient.SqlParameter("@LastName", _lastName),    
                                                        new System.Data.SqlClient.SqlParameter("@Birthday", _birthday),
                                                        new System.Data.SqlClient.SqlParameter("@Gender", _gender),                
                                                        new System.Data.SqlClient.SqlParameter("@PhoneNumber", _phoneNumber),                
                                                        new System.Data.SqlClient.SqlParameter("@FaxNumber", _faxNumber),                
                                                        new System.Data.SqlClient.SqlParameter("@WebURL", _webURL),                
                                                        new System.Data.SqlClient.SqlParameter("@MobileNumber", _mobileNumber)},        
            base.ConnectionString);
        _key = new System.Data.SqlTypes.SqlGuid(pKey.Value.ToString());
        //System.Diagnostics.Debug.WriteLine(pKey.Value.ToString());
    }
} 

The way you create Paramaters is basicly the same in VB.Net it's just in VB syntax.

[token]

Thanks that helps a great deal, I kinda had an idea that that's what I would ahve to do, I was just hoping for a simpler solution....with less typing. But oh well I had to do all that typing to insert it, i can do it for the update as well.

Your no job and boredom really works out for me, you've answered almost all my posts, and as far as i can tell almost everyone elses, on this forum.

Thanks again and in advance when you get my next post.

[Magiaus]

To bad I can't get a job helping answer questions lol

[mendhak]

If posting on VBF was our job, we'd be sneaking out right now to go and sit in a windowless cubicle.

[Magiaus]

I find out next week if I am going to get the job I want. Everybody rub Rabbit's feet and stuff for me.