updating sql db table, getting blanks in column data



running a test (via the executute option within vs 2008 pro) -- provides the following feedback:

Running [BackOffice].[CopyNewLeadtoDIP] ( @Title_ID = 1, @FirstNameApp1 = test, @LastNameApp1 = test, @TelHomeApp1 = 123, @TelWorkApp1 = 123, @TelMobileApp1 = 123, @EmailApp1 = test, @Address1Current = test, @Address2Current = test, @Address3Current = test, @TownCurrent = 1, @CountyCurrent = 1, @PostCodeCurrent = test, @CountryCurrent = 1, @AssignedStaffMember = 45, @MasterAccount_ID = 260934 ).

(1 row(s) affected)
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [BackOffice].[CopyNewLeadtoDIP].


where am I going wrong.

your time and efforts with this enquiry are much apprieated
ALTER PROCEDURE BackOffice.CopyNewLeadtoDIP
 
@Title_ID						int,
@FirstNameApp1					nvarchar(50),
@LastNameApp1					nvarchar(50),
@TelHomeApp1					nvarchar(50),
@TelWorkApp1					nvarchar(50),
@TelMobileApp1					nvarchar(50),
@EmailApp1						nvarchar(50),
@Address1Current				nvarchar(50),
@Address2Current				nvarchar(50),
@Address3Current				nvarchar(50),
@TownCurrent					int,
@CountyCurrent					int,
@PostCodeCurrent				nvarchar(10),
@CountryCurrent					int,
@AssignedStaffMember			int,
@MasterAccount_ID				int
 
AS
BEGIN
 
UPDATE DIPApplication SET TitleApp1 = s.Title_ID,
					      FirstNameApp1 = s.FirstName,
					      LastNameApp1 = s.LastName,
					      TelHomeApp1 = s.LandlineHome,
					      TelWorkApp1 = s.LandlineWork,
					      TelMobileApp1 = s.Mobile,
					      EmailApp1 = s.EmailAddress,
					      Address1Current = s.Address1,
					      Address2Current = s.Address2,
					      Address3Current = s.Address3,
					      TownCurrent = s.Town_ID,
					      CountyCurrent = s.County_ID,
					      PostCodeCurrent = s.PostCode,
					      CountryCurrent = s.Country_ID,
					      AssignedStaffMember = s.AssignedStaffMember
 
				   FROM DIPApplication t
					
				   INNER JOIN MasterAccounts s ON s.MasterAccount_ID = t.MasterAccount_ID
									     
				   WHERE t.MasterAccount_ID = @MasterAccount_ID
									  
END

Open in new window

amillyardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
>>>>getting blanks in column data

Can you please brief your problem a bit more clear?
0
Ashish PatelCommented:
Also what i see is why you are passing all those parameters when you are not using them in the procedure.
0
amillyardAuthor Commented:
the db cell has literally a 'blank' -- normally when the cell has not been used or updated yet -- there is a 'null' entry visible -- whereas when this stored procedure operates the data entry then looks blank (i.e. no text at all)
0
Get expert help—faster!

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

Gururaj BadamCommented:
What's your expectation. The execution result says that it has updated 1 row.

I didn't understand the problem here.
0
Gururaj BadamCommented:
The first you should do is set a primary key on this table if already not present. This helps to trap insertion of null values into the table
0
Aneesh RetnakaranDatabase AdministratorCommented:
AS
BEGIN
set nocount on ------------------  add this line in order to eliminate "the no of rows modified part"
 
UPDATE DIPApplication SET TitleApp1 = s.Title_ID,
                                              FirstNameApp1 = s.FirstName,
                                              LastNameApp1 = s.LastName,
                                              TelHomeApp1 = s.LandlineHome,
                                              TelWorkApp1 = s.LandlineWork,
                                              TelMobileApp1 = s.Mobile,
                                              EmailApp1 = s.EmailAddress,
                                              Address1Current = s.Address1,
                                              Address2Current = s.Address2,
                                              Address3Current = s.Address3,
                                              TownCurrent = s.Town_ID,
                                              CountyCurrent = s.County_ID,
                                              PostCodeCurrent = s.PostCode,
                                              CountryCurrent = s.Country_ID,
                                              AssignedStaffMember = s.AssignedStaffMember
 
                                   FROM DIPApplication t
                                       
                                   INNER JOIN MasterAccounts s ON s.MasterAccount_ID = t.MasterAccount_ID
                                                                             
                                   WHERE t.MasterAccount_ID = @MasterAccount_ID
                                                                         
END




@RETURN_VALUE = 0 - means ur sp executed successfully
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amillyardAuthor Commented:
Novice_Novice:

my expectation is 1 of 2

1: using the 'execute' mode within vs -- I should at teh very least get the initial variables (manually entered in order to get the stored procedure to operate)

2: used in the live environment -- should be updating with other content from other db table
0
amillyardAuthor Commented:
asvforce:

I thoguht I had to pass those variables across -- my objective is to 'copy' a few columns from table a to table b  -- the record hook / identifier is the MasterAccount_ID which is consistent in both tables

should I have writtent the script differnetly?
0
amillyardAuthor Commented:
Novice_Novice:  confirming that all tables within db have a primary key set, normally use   tablename_ID  format for the primary key
0
amillyardAuthor Commented:
Further clarity:

MasterAccounts table has some basic infomation in it.

As DIPApplication table gets created -- this tables needs to copy a few columns from MasterAccounts table.
0
Gururaj BadamCommented:
your're consistently saying that you want to copy few columns, few questions

1. Are you sure you meant columns only or you meant to say rows?
2. If columns, what values will the remaining columns will contain?
0
amillyardAuthor Commented:
Novice_Novice:  apologies -- row  (been up all night working)
0
Gururaj BadamCommented:
I'm not sure whether this will resolve your problem but you can give a try

ALTER PROCEDURE BackOffice.CopyNewLeadtoDIP
 
@Title_ID                                               int,
@FirstNameApp1                                  nvarchar(50),
@LastNameApp1                                   nvarchar(50),
@TelHomeApp1                                    nvarchar(50),
@TelWorkApp1                                    nvarchar(50),
@TelMobileApp1                                  nvarchar(50),
@EmailApp1                                              nvarchar(50),
@Address1Current                                nvarchar(50),
@Address2Current                                nvarchar(50),
@Address3Current                                nvarchar(50),
@TownCurrent                                    int,
@CountyCurrent                                  int,
@PostCodeCurrent                                nvarchar(10),
@CountryCurrent                                 int,
@AssignedStaffMember                    int,
@MasterAccount_ID                               int
 
AS
BEGIN
 
UPDATE DIPApplication SET TitleApp1 = ISNULL(@Title_ID, s.Title_ID),
                          FirstNameApp1 = ISNULL(@FirstNameApp1, s.FirstName),
                          LastNameApp1 = ISNULL(@LastNameApp1, s.LastName),
                          TelHomeApp1 = ISNULL(@TelHomApp1, s.LandlineHome),
                          TelWorkApp1 = ISNULL(@TelworkApp1, s.LandlineWork),
                          TelMobileApp1 = ISNULL(@TelMobileApp1, s.Mobile),
                          EmailApp1 = ISNULL(@EmailApp1, s.EmailAddress),
                          Address1Current = ISNULL(@Address1Current, s.Address1),
                          Address2Current = ISNULL(@Address2Current, s.Address2),
                          Address3Current = ISNULL(@Address2Current, s.Address3),
                          TownCurrent = ISNULL(@TownCurrent, s.Town_ID),
                          CountyCurrent = ISNULL(@CountyCurrent, s.County_ID),
                          PostCodeCurrent = ISNULL(@PostCodeCurrent, s.PostCode),
                          CountryCurrent = ISNULL(@CountryCurrent, s.Country_ID),
                          AssignedStaffMember = ISNULL(@AssignedAccount_ID, s.AssignedStaffMember)
 
                 FROM DIPApplication t
                                       
                 INNER JOIN MasterAccounts s ON s.MasterAccount_ID = t.MasterAccount_ID
                                                                             
                 WHERE t.MasterAccount_ID = @MasterAccount_ID
                                                                         
END
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.