I have faced a situation that when i try to update a page. Some values can be updated while some cannot. I try to print the executed SQL query and get the following1 "UPDATE orders SET
2 cust_id=15,po_code='PO20060610',
3 po_amt=10000.0000,
4 add_charges=0,
5 commission='eeeeeee',
6 lab_charges=0,
7 fty_dis=0,
8 pay_trm='adasds',
9 cust_dis=0,
10 trade_trm_desc='',
11 curr_rate=1,
12 ship_expense=0,
13 shipmark='eng ship mard new2',
14 sidemark='Eng Side Mark new333'
15 ,inner_box='Eng Inner Box new333',
16 confirmation='rend confirmation2',
17 contract='end contact23',
18 internal_remark='testing testing 26/6/2007 333',
19 rec_curr_rate=0,rec_amt=0,shipmark_attach='',
20 sidemark_attach='',inner_box_attach='',
21 ord_type=1,status=2,ord_confirm_code='',
22 commission_type=1,sidemark_lang='English',
23 curr_code='HKD',unit_code='PCS',
24 trade_trm='FOB Hong Kong',rec_curr='USD',
25 ord_date='2006/06/10', po_date='01/01/2007',exp_delivery_date='01/01/2007',
26 act_delivery_date='01/01/2007', pay_start_date='10/10/06',pay_end_date='10/10/06',upd_time='2007/03/15 15:41:14' WHERE ord_id=292;Set @ord_id=292;"
The fields sidemark, inner_box, internal_remark cannot update, while others can.
I think it's really strange.... since i have no idea why some can be updated while some others and the SQL seems to me is correct.
I am new to both ASP.net and this forum. I have seen some posts close to this, but none address this problem.
I have a SQL Server database on JOHN1 called 'siu_log' with a table called 'siu_log'. It has two fields: Scenarios char[20] and Machines char[20].
I have been adapting code from Build Your Own ASP.NET Website in C# & VB.NET by Zac Ruvalcaba to learn the language. Much of what you will see is his work adapted for my use.
Sub dg_Update(ByVal s As Object, ByVal e As DataGridCommandEventArgs) Dim strMachineName, strScenarioName As String Dim intResult As Integer strScenarioName = Trim(scenariosDataGrid.DataKeys(e.Item.ItemIndex)) strMachineName = CType(e.Item.FindControl("txtMachine"), TextBox).Text cmd = New SqlCommand("UPDATE siu_log SET Machines=@Machine " & _ "WHERE Scenarios=@Scenario", conn) cmd.Parameters.Add("@Machine", strMachineName) cmd.Parameters.Add("@Scenario", strScenarioName) conn.Open() intResult = cmd.ExecuteNonQuery() resultLabel.Text = "The result was " & intResult & "." conn.Close() scenariosDataGrid.EditItemIndex = -1 BindData() End Sub
The problem is the strMachineName variable always contains the previous contents of the text box -- not the new one. This makes the UPDATE query just push the old data back into the table.
I have a grid like this and when I change Designation value of Name X from PA to PAT, this change should reflect to Sathish Designation too. Changing similar values should reflect all over the grid. Like update database on change of value of similar kind.
By far I use this query to update the grid. So a where clause should be used now to update the grid to fit this scenario.
("UPDATE AppInvent_Test SET Name = @Name, Designation = @Designation, City = @City WHERE EmpID = @EmpID");
EmpID Name Designation City 21 X PA Chn 2 Sathish PA Chn 3 Shiva A Cbe 17 Venkat M Hyd 22 Y SM Cbe 18 Vignesh SA Hyd
I have a grid with checkbox, where users can select multiple rows and edit at the same time and save it to the DB. Now I have used a Footer Template with textbox in the gridview. So if I want to put similar data's for some particular rows at the same time in the grid, I select the multiple rows and try to put values in the footer template textbox and when I click on save, it saves successfully.
UPDATE QUERY: "UPDATE [Test] SET [Name]='" + Name + "',[Designation]= '" + Designation + "', [City]= '" + City + "' WHERE EmpID='" + EmpID + "'";
Now here is the challenge, but even when I enter null values in the footer template textbox it has to save with the old values of the rows and not null values. I tried it and couldn't make it happen. So anything like putting the case for each column and mentioning like if null accept the old value and not null accept new value.
Help, please. I am trying to update atable with this structre:CREATE TABLE Queue (PropID int, EffDate smalldatetime,TxnAmt int)INSERT Queue (PropID) SELECT 1INSERT Queue (PropID) SELECT 2INSERT Queue (PropID) SELECT 3....from this table...CREATE TABLE Txns (PropID int, TxnDate smalldatetime,TxnType char(1), TxnAmt int)INSERT Txns SELECT 1 '20000201', 'B', 100000INSERT Txns SELECT 1 '20020515', 'B', 110000INSERT Txns SELECT 1 '20020515', 'A', 120000INSERT Txns SELECT 1 '20020615', 'c', 130000....only certain txn types are okay, and they have an orderof preference...CREATE TABLE GoodTxnTypes (GoodTxnType char(1), Pref)INSERT GoodTxnTypes SELECT 'A', 1INSERT GoodTxnTypes SELECT 'B', 2The idea is to fill in the NULL fields in the Queue table,according to a rule -- the transaction must be the latesttransaction within a date window, it must be one of the goodtxn types, and if there are two txns on that date, choosethe txn by the preferred txn type (A is preferred over B,according to the field Pref).If the time window were 20020101 to 20030101, the txnselected to update the Queue table would be this one:INSERT Txns SELECT 1 '20020515', 'A', 120000 -- there aretwo in the time window that are type A or B; they areboth on the same day, so the 'A' is preferred.If the time window were 20000101 to 20010101, this wouldbe selected because it is the only A or B type txn inthe interval:INSERT Txns SELECT 1 '20000201', 'B', 100000I'm looking for a statement that starts...UPDATE Queue SET EffDate = ...., TxnAmt = .... (EffDate,in this table, is the same as TxnDate in the Txn table).Assume we have @FirstDate and @LastDate available.Help, please. I'm getting stuck with (a) a sub-query tofind the relevant Txn records, and (b) another sub-querywithin that to find the MAX(TxnDate) within the timewindow. Filtering the Txn records on the basis of theGoodTxnTypes table is easy, as is ordering what is returned.But I'm having trouble joining the sub-queries back to theQueue table on the basis of PropId.
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
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
instead of insert into command sqlcom.CommandText = "insert into Approve_Overtime(syainNo,ninka_nen_from,ninka_gatsu_from)values(@syainNo,@ninka_nen_from,@ninka_gatsu_from)
Hi Guys I already searched for one day and didn't find any solution. Unfortunately I can't update my dataset. It works when I remove the string "WHERE ID = ?" in the UpdateCommand but then it updates all the records. I think it must be something obvious which I just don't see:(There are two tables; tblArtists and tblCountries which are connected together with a Inner Join. Now, I need the ID from the table Artists to update the dataset. How can I get it?Here the code:....SelectCommand="SELECT tblArtists.ID AS IDArtists, tblArtists.ArtistName, tblArtists.FirstName, tblArtists.LastName, tblArtists.Address, tblArtists.PLZ, tblArtists.City, tblArtists.Region, tblArtists.Country_ID, tblArtists.MusicStyles, tblArtists.HomeDJ, tblArtists.Active, tblArtists.CDate, tblArtists.Description, tblCountries.ID AS IDCountries, tblCountries.Country, tblArtists.Producer, tblArtists.Picture, tblArtists.Homepage, tblArtists.Phonenumber, tblArtists.eMail FROM (tblCountries INNER JOIN tblArtists ON tblCountries.ID = tblArtists.Country_ID) WHERE (tblArtists.Active = ?) AND (tblArtists.HomeDJ = ?) AND (tblArtists.Producer = ?)" UpdateCommand="UPDATE tblArtists SET ArtistName = ?, FirstName = ?, LastName = ?, Address = ?, PLZ = ?, City = ?, Region = ?, Country_ID = ?, MusicStyles = ?, HomeDJ = ?, Active = ?, Description = ?, Producer = ?, Picture = ?, Homepage = ?, Phonenumber = ?, eMail = ? WHERE ID = ?"> <SelectParameters> <asp:ControlParameter ControlID="activeRadioButton" Name="Active" PropertyName="Checked" Type="Boolean"/> <asp:ControlParameter ControlID="homedjRadioButton" Name="HomeDJ" PropertyName="Checked" Type="Boolean"/> <asp:ControlParameter ControlID="producerRadioButton" Name="Producer" PropertyName="Checked" Type="Boolean"/> </SelectParameters> <UpdateParameters> <asp:Parameter Name="ArtistName" Type="String" Size="254" /> <asp:Parameter Name="FirstName" Type="String" Size="254" /> <asp:Parameter Name="LastName" Type="String" Size="254" /> <asp:Parameter Name="Address" Type="String" Size="254" /> <asp:Parameter Name="PLZ" Type="String" Size="254" /> <asp:Parameter Name="City" Type="String" Size="254" /> <asp:Parameter Name="Region" Type="String" Size="254" /> <asp:Parameter Name="Country_ID" /> <asp:Parameter Name="MusicStyles" Type="String" Size="254" /> <asp:Parameter Name="HomeDJ" Type="Boolean" /> <asp:Parameter Name="Active" Type="Boolean" /> <asp:Parameter Name="Description" Type="String" /> <asp:Parameter Name="Producer" Type="Boolean" /> <asp:Parameter Name="Picture" Type="String" Size="254" /> <asp:Parameter Name="Homepage" Type="String" Size="254" /> <asp:Parameter Name="Phonenumber" Type="String" Size="50" /> <asp:Parameter Name="eMail" Type="String" Size="50" /> <asp:Parameter Name="original_IDArtists" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> GreetingsPat
B IDIDAVALUE 1|1|12 2|1|34 3|2|17 4|3|4 5|1|22 6|3|1
I want to update A with values from b , so the value2 from a= value from b but only with latest corresponding data based on b.id so after updtate a looks like:
IDVALUE1VALUE2 1 |a |22 couse max id from B with ida=1 is 5 and so the value is 22, so A.value2=22 2 |v |17 couse max id from B with ida=2 is 3 and so the value is 17so A.value2=22 3 |c | 1 couse max id from B with ida=3 is 6 and so the value is 1 so A.value2=1 4 |g |12 couse there is no ida=4 in B
I have a table say #temp1 with coulmn Id and Description and I do have another table #temp2 with column id and Projectdescription but in #temp2 there could be more then one value against one id like the data in #temp2 is look like
id Projectdescription 1 Computer project 1 update in computer project 1 another update in computer 2 Physics project 2 another update
but #temp1 has only one accurance of id and data initially looks like id Description 1 NUll 2 NUll and I would like to update this table description from #temp2 Projectdescription column so that data in #temp1 table look like after update
id Description 1 Computer project,update in computer project, another update in computer
2 Physics project, another update
I mean I would like to have concatination form of Projectdescription in description column against specific ID I can achieve this by using UDF but i dont want to use that I just want to do it by update statement not even by using cursor
Both have an identical table called "Codes" containing billing numbers and prices.
Database B is one I use for testing, and is out of date. Is there an easy way to load the values from Table_A.codes into Table_B.codes?
I know how to do an update from one table to another within the same database, but am clueless how to do it when the data reside in separate databases.
Hi there...Is it possible to write an UPDATE or INSERT query, where the new value comesfrom an array? For example:UPDATE table_a SET column_x = [@array1]WHERE column_y = [@array2];It's a query (for argument's sake called query1) in an Access database,which I'm accessing as a stored procedure through PHP, so I would run somephp code that looked possibly like this:$array1 = array("a","b","c","d");$array2 = array(1,2,3,4);$str_sql = "exec query1 $array1, $array2";my_run_query_function($str_sql);But is it possible? The information I can find about it seems to say that ithas to be an array created inside the query, but if it's a stored procedure,this isn't possible, is it?Hope someone can help...Plankmeister.
Hi, I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one. Any Ideas on how to get around this problem?THanks
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username 2 FROM users 3 WHERE user_username LIKE '%[!-)]%';
Hello everybody, I can't perform an operation apparently very easy: set a field to a NULL value.
This is the db: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
This is the table: CREATE TABLE [ProgettoTracce] ( [ID_Progetto] [int] NOT NULL , [MisDifDef] [real] NULL , [MisDifMeas] [real] NULL , [MisDifAna] [real] NULL , [MisDifID] [real] NULL , [MisDifCV] [real] NULL ) ON [PRIMARY] GO
This is qry: UPDATE ProgettoTracce SET MisDifDef = NULL WHERE ID_Progetto = 3444
The qry has been performed with no error. Then I execute SELECT * FROM ProgettoTracce WHERE ID_Progetto = 3444 and I find the value I tried to overwrite with NULL. If I update with 0 (for example) it works. Obviously this happens on the production db, because on the development db the update with NULL works fine. No transaction is called, db options are the same on dbs...
I'm trying to update a value into a table a sum. The two tables have ID values. These ID values appear once in Table1 and multiple times in Table2. I'm currently trying to sum up the values in Table2 where the IDs are equal to Table1 and then update the value:
UPDATE [Table1] SET [Total] = (SELECT SUM([Table2].[QTY]) FROM [Table2], [Table1] WHERE [Table1].[ID] = [Table2].[ID] GROUP BY [Table1].[ID]) FROM [Table1], [Table2] WHERE [Table1].[ID] = [Table2].[ID]
I have two tables and I need to update values in them via a stored procedure. Tried too much to update but some times it update the first table only, others the second or even fail due to cannot allow duplicates. Also when it updates the WHOLE data in the table becomes the same as the new updated ones. I've now reached to this error after all these lines of codes
Cannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminated
Here is the SQL code :
ALTER procedure [dbo].[UpdateEmp] @EmpName nvarchar(100), @Nationality nvarchar(30), @Passport nvarchar(20), @ContractDate date, @HealthDate date
on insert in a new table a have to change one column before insert.
I wrote this trigger:
create trigger SUBSCR_ID_TRANSFER ON dbo.SalesOrderExtensionBase AFTER INSERTAS BEGIN SET NOCOUNT ON;DECLARE @OpportunityID uniqueidentifier;DECLARE @subscrId uniqueidentifier;declare @salesorderid uniqueidentifier;set @salesorderid = (select SalesorderID from inserted)SET @OpportunityID = (SELECT OpportunityId FROM SalesOrderBase where SalesOrderID=@salesorderid)SET @subscrId = (SELECT New_old_subscridId from OpportunityExtensionbase where OpportunityID=@OpportunityID)Update inserted set New_old_subscridId = @subscrIdENDbut SQL Rise the error "The inserted values can not be modified"
using custom tasks. My properties got saved properly the first time the task is added to the workspace. After that, changing values in the CustomUI updates the properties, but the package shows, that It doesn't need to get saved. So my properties don't get changed in the package XML. Moving my custom task on the workspace after changing properties, let the package go into "save me" state and my propertiy values get saved to XML.
So now, Im searching for a method or property I have to call or set to show the package it needs to get saved after closing my CustomUI.
Any hint?
Thanks
PS: When I change the property value in the task properties grid (lower right). The package gets notified about the need to get saved.
I am creating a website. i have completed the GUI. i have labels on the left side of the form and a text box for each of those labels on the right side of the form. i have the two dropdown boxes that you have helped me create and bind. now im stuck on how to start the code to get this all working. i know what i need it to do (pseudocode) but dont know how to write the code for it. these books and online sources, really dont say where to put things, they just give examples, not stating where to place it. I am using a SQL database called Manufacturers i would like to insert or update, but not delete records wtih this tool pixelsyndicate mentioned using the GUI to do the code for me, would love to know how to do this. any help would be awesome, or any good beginner resources would help too
I have never used triggers before and I have tried to solve one problem. If I have the column "currency" in a table and want to make sure that the entered value i valid in relation to another table that contains valid currency formats, I did it like this:
--------------------------------- CREATE TRIGGER [trigger_checkCurrency] ON [dbo].[Client] FOR INSERT, UPDATE AS declare @currency as char(50) declare @country as char(50)
declare cur cursor for SELECT currency, country FROMinserted
OPEN cur fetch cur into @currency, @country WHILE @@FETCH_STATUS = 0 BEGIN if not exists(select * from listinfoid where listname = 'currency' and listid = @currency) begin set @currency = (cast(@currency as varchar (3)) + ' is not a valid currency') CLOSE cur DEALLOCATE cur RAISERROR (@currency,16,-1) with log return end if not exists(select * from listinfoid where listname = 'country' and listid = @country) begin set @country = (cast(@country as varchar (3)) + ' is not a valid contry') CLOSE cur DEALLOCATE cur RAISERROR (@country,16,-1) with log return end else fetch cur into @currency, @country
END CLOSE cur DEALLOCATE cur update Client set currency = UPPER(currency), country = UPPER(country) ---------------------------------
I use a cursor to handle multiple rows in an update query. (SQL2000-server)
Is there an easier och better way to do this? I´m a bit unsure of this code.
Create table #test ( id int primary key, Name varchar(100) ) insert into #test values (1,'John') insert into #test values (2,'Walker')
[Code] ....
-- Query 1 : update #test set name = 'Joney' where id = 1
-- Query 2 : set rowcount 1 update #test set name = 'Joney' where id = 1 set rowcount 0
1. #test table have primary key & clustered index. 2. Obviously only one row will be available for an id. 3. In query 1, will the sql server look for matching rows even after it found 1 row? 4. Will query 2 really gains some performance?
