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?
 
Aneesh RetnakaranConnect With a Mentor Database 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
 
Ashish PatelConnect With a Mentor Commented:
>>>>getting blanks in column data

Can you please brief your problem a bit more clear?
0
 
Ashish PatelConnect With a Mentor Commented:
Also what i see is why you are passing all those parameters when you are not using them in the procedure.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
Gururaj BadamConnect With a Mentor Commented:
What's your expectation. The execution result says that it has updated 1 row.

I didn't understand the problem here.
0
 
Gururaj BadamConnect With a Mentor Commented:
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
 
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 BadamConnect With a Mentor Commented:
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 BadamConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.