Transact SQL :: Field Not Update Correctly - Adding Extra Duplicated Row
Oct 8, 2015
I have the following querry :
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
[Code] ....
It produced the following result
Based on my querry when n=2, it should be set to 0, why it cannot be set ?
if I added the value 2 to the list of values as below :
CROSS APPLY (Values(0),(1),(2)) d(n)
Then the value n=2 is updated but it adds extra duplicated rows which is not part of my real
View 11 Replies
ADVERTISEMENT
Jun 13, 2014
I have a simple query like:
select max(PTR_DATE)
from MPR_portfolio_transactions
group by PTR_SYMBOL
and this is working fine, but if I add an extra column with another field like:
select max(PTR_DATE) , PTR_SHAREBALANCE
from MPR_portfolio_transactions
group by PTR_SYMBOL
Then I get an error message like:
Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How can I get round to this?
View 6 Replies
View Related
Sep 4, 2014
The FirstName and LastName values being passed in are in cyrillic text.
How do I add the N prefix in the update statement in order to store the text correctly.
I've tried FirstName = N@Firstname
or FirstName = '''N''' + @FirstName + '''
[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),
[code].....
View 8 Replies
View Related
May 25, 2008
All- Please advise how to configure a gridview so that when introducing non-editable fields to the select statement, the edit function doesn't crash with a "Procedure or function (UpdateCommand) has too many arguments specified."
The scenario: Update works find when I have a gridview based on the following SPs:
SELECT:
1 ALTER PROCEDURE dbo.H2SelectCommand
2 AS
3 SET NOCOUNT ON;
4 SELECT headcount_id, person_id, act_session_id, no_answer
5 FROM headcount
6
INSERT:
1 ALTER PROCEDURE dbo.H2UpdateCommand
2 (
3 @person_id smallint,
4 @act_session_id smallint,
5 @no_answer bit,
6 @Original_headcount_id smallint,
7 @headcount_id smallint
8 )
9 AS
10 SET NOCOUNT OFF;
11 UPDATE [headcount] SET [person_id] = @person_id, [act_session_id] = @act_session_id, [no_answer] = @no_answer WHERE (([headcount_id] = @headcount_id));
12
HOWEVER, when I alter the select command so that it includes a column (person.person_name) from a parent directory like this:
1 ALTER PROCEDURE dbo.H2SelectCommand
2 AS
3 SET NOCOUNT ON;
4 SELECT headcount_id, person.person_name, headcount.person_id, act_session_id, no_answer
5 FROM headcount
6
7 INNER JOIN person
8 ON headcount.person_id = person.person_id
...and then proceed to do an Edit/Update operation, I get the pesky "Procedure or function H2UpdateCommand has too many arguments specified" message.
The only obvious thing I could think to try was to configure the new column in the gridview to be Read Only, but this didn't help. Do I have to alter the Update SP to somehow account for the new field, even though the warning already says that there's too many arguments in it?
Any ideas on how to fix this error would be appreciated!
-Kurt
View 3 Replies
View Related
Dec 20, 2007
(6th attempt to post this on the forum - problems with the forum?)
Hello
Assume that you have SQL Server 2005 Standard Edition installed but only the Database Services and you patched it with SP2. Now assume that you need to install all the other components like Notification Services and Reporting Services, what are the correct steps to follow?
I tried this once this failed miserably and ended up in completely having to uninstall SQL Server 2005 and completely reinstalling it. Luckily for me this was a demo machine.
See:
http://gabriel.lozano-moran.name/blog/PermaLink,guid,6a81a3a9-45d3-4221-8b16-ba4e4863949c.aspx
Thanks
Gabriel
View 3 Replies
View Related
Feb 25, 2008
Hi, Apologies in advance if you get confused by reading this.... I am trying to create an additional step in the Create Wizard User Control that is provided by ASP.net. The additional step that I want to add will come after a user will create their username, password, email etc. The information which I want to save in the extra step are details such as firstname, lastname, address, height, weight etc. (I am creating an online weight management system for dieticians).When I run through the application, the username, password etc save perfectly to the database, but nothing happens with the other "personal information". There are no errors thrown so I don't know where the problem is coming from.I have included the code below as I have it:The code behind the Register.aspx file is as follows: <asp:SqlDataSource ID="InsertExtraInfo" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" InsertCommand="INSERT INTO [aspnet_UserInformation] ([first_name], [surname], [address1], [address2], [city], [country], [number], [height], [weight]) VALUES (@txtFirstName, @txtSurname, @txtAddress1, @txtAddress2, @txtCity, @txtCountry, @txtNumber, @txtHeight, @txtWeight)" ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString1.ProviderName %>"> <InsertParameters> <asp:ControlParameter Name="txtFirstName" Type="String" ControlID="txtFirstName" PropertyName="Text" /> <asp:ControlParameter Name="txtSurname" Type="String" ControlID="txtSurname" PropertyName="Text" /> <asp:ControlParameter Name="txtAddress1" Type="String" ControlID="txtAddress1" PropertyName="Text" /> <asp:ControlParameter Name="txtAddress2" Type="String" ControlID="txtAddress2" PropertyName="Text" /> <asp:ControlParameter Name="txtCity" Type="String" ControlID="txtCity" PropertyName="Text" /> <asp:ControlParameter Name="txtCountry" Type="String" ControlID="txtCountry" PropertyName="Text" /> <asp:ControlParameter Name="txtNumber" Type="String" ControlID="txtNumber" PropertyName="Text" /> <asp:ControlParameter Name="txtHeight" Type="String" ControlID="txtHeight" PropertyName="Text" /> <asp:ControlParameter Name="txtWeight" Type="String" ControlID="txtWeight" PropertyName="Text" /> </InsertParameters> </asp:SqlDataSource>
Then the code I have behind the Register.aspx.cs page is: protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e) { TextBox UserName = (TextBox)CreateUserWizard1.FindControl("UserName"); SqlDataSource DataSource = (SqlDataSource)CreateUserWizard1.FindControl("InsertExtraInfo"); MembershipUser User = Membership.GetUser(UserName.Text); //object UserGUID = User.ProviderUserKey; DataSource.InsertParameters.Add("UserId", UserGUID.ToString()); DataSource.Insert(); } I know there is a problem with the code on the aspx.cs page but I cant figure it out. I need the username / password information to relate to the personal details information. I know I have to create a foreign key in the asp_UserInformation table that will link to the username in the asp_Membership (where all the username / password info is stored) Any help will do, I'm almost in tears here!!!The Spud
View 1 Replies
View Related
Dec 4, 2007
Example:
pcstamp account description
ADM05111059718,307000109 641001 Vencimentos
ADM05081136070,356981897 641001 Vencimentos
ADM05081136281,231965274 641012 Outras Remunerações
ADM05111059718,369000120 641012 Outras Remunerações
i this case the attributes that are equal in the table are account and description
how can i query them doing a select that returns only the attributes with the same value?
I need to delete one of the duplicated attributes of the field, i can't have in this table values matching same account and same description...
View 6 Replies
View Related
Sep 9, 2015
Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies
View Related
May 28, 2015
I want to update a field and in this field insert a increment count, for example:
When I make, "Select * from Users order by User" displays:
User1 | NULL
User1 | NULL
User1 | NULL
User2 | NULL
User2 | NULL
and I want to do this:
User1 | 1
User1 | 2
User1 | 3
User2 | 1
User2 | 2
how to do this?
View 7 Replies
View Related
Feb 22, 2008
Hello all
I have a ssis component that begins like below. I recently added a new customproperty to the package and needed a way to detect when an upgrade from an old version was needed. As per microsofts directions I tried to use the currentversion property for this. This seemed to work fine until I noticed that newly added component to the dataflow keep starting at version 0 and going thru an upgrade making an duplicate of the new property. As below the new version has currentversion = 2 as a property. So why is this ignored. Or how do i make a component start out with the proper version so no upgrades happen?
Code Snippet
namespace itworkz.ssis.CustomComponents
{
[DtsPipelineComponent(
DisplayName = "ODS Tool v2",
Description = "proccess data for the operational data store",
ComponentType = ComponentType.Transform,
CurrentVersion = 2,
UITypeName = "itworkz.ssis.CustomComponents.OdsTool2UI, OdsTool2, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3276bb0510a3c305"
)]
public class OdsTool2 : PipelineComponent
{
Hope someone can explain this
View 1 Replies
View Related
Feb 16, 2006
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies
View Related
Mar 9, 2006
When I enter a data in a table, SQL Server automatically completes the data with blanks up to length of column.
this happens in a web form also in Management Studiıo,
Whan am I doing wrong ?
does Collation have anything with it ?
SQL Server 2005 / Developer Edition
View 2 Replies
View Related
Sep 14, 2001
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). What's going on here? Am I losing records somewhere?
Also, this is a cold fusion application. Thanks!!
View 1 Replies
View Related
Sep 17, 2001
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). There is absolutely no deletion of any kind going on. (i.e. My application (cold fusion) has no code whatsoever that is deleting records.) What's going on here? Am I losing records somewhere?
View 1 Replies
View Related
Oct 13, 2015
I get a column in hours in a table and when I try to convert it to months and days its giving me incorrect results.
My timehours column in my table has hours recorded timehours value is 5832 and I get results as 11 months and 27 days.
This query is giving me wrong result -select months=(timehours %365)/30,days= (timehours % 365)%30
View 13 Replies
View Related
Oct 3, 2015
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
View 14 Replies
View Related
Nov 17, 2015
I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)
I want to update table1 and set field2Update as somevalue where table2.Flag <> 0
View 4 Replies
View Related
Mar 22, 2001
I'm using a performance monitor counter with an alert to catch merge replication conflicts. As you might have seen in my earlier psting this alert keeps running even if all conflicts have been resolved. After almost 2 days searching I found that in the table master..sysperfinfo there all the PM counters are stored still has the value 4 for conflicts/sec. When I manually create a new conflict this value becomes 5 which is nonsense since it's not the number of conflicts per second but the total number of conflicts since I started the server. If I check in PM it shows the correct value, whih of course is zero 99% of the time. Has anybody ever experienced the same kind of problem??
Or does anybody know a way of resetting the values in sysperfinfo. Even after allowing direct updates to systemtables I still can't modify the table.
Thanks in advance
Markus
View 1 Replies
View Related
Aug 24, 2006
Hi,
I have a concern about adding a new field to a table with image field - which is huge.
Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field?
Previously this happened to me, and what was advised to put all the big fields at the end of the table.
Thanks.
View 1 Replies
View Related
Feb 29, 2008
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
was just curious.
-dk
View 3 Replies
View Related
Oct 22, 2007
I have the following query in an ExecuteSQL Task:
Insert Into Table2
Select * From Table1 Where Column1Val = '4'
As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.
Why is the above query failing with
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
View 4 Replies
View Related
Aug 25, 2015
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
View 8 Replies
View Related
Aug 28, 2015
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8 *Correct
1234567-9 -> 1234567- *Wrong
123456789 -> 123456789 *Correct
1234567#9 -> 1234567# *Wrong
1234567a9 -> 1234567a9 *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.
View 3 Replies
View Related
Apr 18, 2006
I need to add a field to a query which i have created in SQL Server, i have done this in Access no problem and it works fine but when icopy the SQL code from Access to SQL it just falls over. This is my code in Access...
SELECT AIMPDTA_CDADDR.CDCUST, AIMPDTA_CDADDR.CDADDC, AIMPDTA_CDADDR.CDNAME, AIMPDTA_CDADDR.CDADD1, AIMPDTA_CDADDR.CDADD2, AIMPDTA_CDADDR.CDADD3, [CDCUST] & [CDADDC] AS Expr1
FROM AIMPDTA_CDADDR LEFT JOIN AIMPDTA_ASAREA ON AIMPDTA_CDADDR.CDNAME = AIMPDTA_ASAREA.ASNAME
WHERE (((AIMPDTA_CDADDR.CDADDC)<>" "));
Any suggestions would be much appreciated.
Thanks
View 2 Replies
View Related
Nov 11, 2013
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
View 4 Replies
View Related
Nov 30, 2015
I insert/update the figure information in this table with daily figures - ie if no record exists insert new name and figure, if figure exists, update figure.
I have been asked to add logic in the insert/update SP to add a fee of 0.25% to any daily figure that results in the total value in the base table being over 1000.
For example: Base table value is 900 before update ----- Daily figure is 200 so 900 + 200 = 1100 after update of base data. New logic dictates that 0.25% must be added to 100 of this daily figure, as 100 brings it up to 1000 and the other 100 (which makes the 200) takes it over the 1000 threshold. 100 + 0.25% = 0.25 ------- Total value to add to base table = 100 + 100 + 0.25 = 200.25.i am keen to avoid WHILE loops and cursors..
View 4 Replies
View Related
May 18, 2015
I am trying to create the following in SQL automatically.
Step needs to double itself each time. How would I write that one out ? Yes I would have the first number as 1.
ID
Step
1
1
2
2
3
4
4
8
5
16
6
32
7
64
8
128
9
256
10
512
View 6 Replies
View Related
Apr 16, 2015
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
View 9 Replies
View Related
Aug 30, 2000
I am trying to add a field on a view which should be query-able.
Please what is the best way to go about it. HELP PLEASE!!!
View 1 Replies
View Related
Mar 19, 2003
Hi,
I have this problem: I'm creating a weblinked database using Access and ASP. The problem is that the fields in the table won't be always the same. E.g. sometimes there will be need for an extra field, but after a while it may not be necessary anymore. Users should be able to add a field to the table (and if possible delete one later on)
I don't whether it is possible to use sql to add a field to an accessdbase. Or is there another way to solve this problem?
thanks for your reply,
yours sincerely,
Dimitri De Vos
View 2 Replies
View Related
Apr 24, 2002
how do i add a new field and insert a value into it?
I'm importing data from another table into a new one, but my source table has a char field and the target filed is (int) can I add a new field to the source file and add a value to it? and how?
I have in my source table a field call companyName that contains text and the target table has a companyId which is an INT.
If I create a field call companyId and add a value of '1' depending on the value of CompanyName...
This is what i want ot do
if CompanyName = LA then insert 1 into new filed companyID
How can I do that? I have to do this for 100 or more tables. Can I write a script that will do that for me?
does it make sence?
View 2 Replies
View Related
May 30, 2008
Have a table with the following data.
Table1
Ord_no inv_no amt Account freefield3
25 125 100.00 1444
25 125 10.00 3555 Rebate
25 125 10.00 3555 Rebate
After the records are inserted I need to add the amt's where freefield3 equals rebate then subtract that sum from the amt where account = 1444. So my final result would be to have 80.00 in the amt field where account equals 1444
View 4 Replies
View Related
Aug 20, 2015
Tried a few ways and I can ge this to work at the end in then WHEN part. Just struggling to put this together to be accepted as a CASE WHEN statement, probably missing the obvious.
Case when Postcode like '%[abcdefghijklmnopqrstuvwxyz%]' then 'Lowercase Postcode' else 'Postcode OK' end as [DQPostcode]
collate Latin1_General_CS_AS
Simple terms looking for all instances of Lowercase characters in the Postcode field
View 5 Replies
View Related
