then I try it like this : exec proc_add_comp 'new answer','by me',1
result is : (1 row(s) affected)
but when I look in the db, nothing was changed, comp_answer still has its old value.. comp_answer is nvarchar type column..isnt add operation allowed in update statement? thanks...
I have a pretty complex SQL statement that looks like this: SELECT aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact, SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username) AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM aspnet_Employers LEFT OUTER JOIN aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null". Why does it do this? What would i change to prevent this from happening?
I am trying to create package something like that..
1- New Customer table as OleDB source component 2- Lookup component - checks customer id with Dimension_Customer table 3- And if same customer exist : I have to update couple fields on Dimension_Customer table 4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination but How can I update the existing ones? I have tried to use oledb command but somehow it didnt work my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
can insert statement works fine in case when function
for example
case when condition1=true then (first insert statement based on some condition) when condition2=true then (second insert statement based on some other condition) end
WHERE (student_id LIKE '%') AND (register_id LIKE '%') AND (register_group LIKE '%') AND
week_no = Case @Week when null Then '#' Else @Week End
the idea is if they don't enter a number it would bring up all records. This works in mangaement studio but c# brings up the error sql server doesn't support udt on excecution of the sql. This is sql server 2000. Any ideas why this doesn't work and how to fix it.
I have a page that is supposed to add a year to a record when it loads. The problem is that it adds 2 or three years instead. Here is the page_load event:Sub page_load(sender as object, e as eventargs) Try Dim connection As SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim command As SqlCommand = new SqlCommand("Updateexpiredate", connection) command.CommandType = CommandType.StoredProcedure
Dim param0 As SqlParameter = new SqlParameter("@memberid",SqlDbType.Int) param0.Direction = ParameterDirection.Input param0.Value = memberid command.Parameters.Add(param0)
connection.Open() command.ExecuteNonQuery() connection.Close() myerror.Text = "Thank You! Your account was updated" Catch ex As Exception myerror.Text = ex.Message End Try End SubAnd here is the SPROC:CREATE PROCEDURE Updateexpiredate ( @memberid int ) AS UPDATE members SET expiredate=(dateadd(year,1,expiredate)) <--I also tried expiredate=(dateadd(month,12,expiredate)) with the same results WHERE memberID = @memberID GO
This statement failsupdate ded_temp aset a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)With this error:Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'a'.But this statement:select * from ded_temp awhere a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)Runs without error:Why? and How should I change the first statement to run my update. Thisstatement of course works fine in Oracle. :)tksken.
Hello to all, i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me. I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418: declare @IDM int; declare @IDO varchar(8000); set @IDM = 3418; set @IDO = '3430' select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 )) but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM)) I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks I use MS SQL 2005 Server Management Stadio Express Thanks a million and Best regards Sha
I'm attempting to extract some yearly average figures from our DB. I've written a SELECT statement in SQL Server MS which returns exactly what I need:
SELECT YEAR, CAllSource, AVG(CallTotal) AS [Average calls per day]
FROM (SELECT COUNT(CallID) AS CallTotal, DATEPART(YEAR, Recvddate) AS Year, CASE WHEN CallSource IN ('Auto Ticket', 'Email')
THEN 'Email' WHEN CallSource IN ('Phone') THEN 'Phone' ELSE 'Other' END AS CallSource
FROM Calllog where
DATEPART(MONTH, Recvddate) = 3
AND DATEPART(dw, RecvdDate) NOT IN ('7', '1')
GROUP BY RecvdDATE, callsource) as sub
GROUP BY YEAR, CallSource
ORDER BY YEAR, CallSource
The problem is that when I attempt to use this in SSRS I get the following error:
"sub.Year is not a recognised DATEPART Option".
Now as you can see, "sub.Year" is not even mentioned in the expression. However I noticed that when running the query, SSRS automatically adds "sub." before the YEAR in the section highlighted in yellow above. a) Why is it doing this, and b) does anyone know of a workaround/fix?
I have an SQL statement that, when run through SQL Server management studio works fine. However, when I run it on my ASP page, it doesn’t update the data! I have tried both as a stored procedure and as a simple commandText update statement. All I do is simly update the value of a column based on another column – nothing particularly complex: update customer set dateChanged = System.DateTime.Now.ToString("dd-MMM-yyyy"), CURRSTAT = 'Active',custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END,cust_Changing_To = NULLFROM customers_v WHERE CURRSTAT = 'Changing'
As you can see, nothing that complex. The line that is causing the problem is the case: custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END all it does is if another nullable integer column is not null, sets it to the value of that column, else it retains its existing value. Like I say, this works in Management studio, but I cannot get it to execute programatticaly from an asp page. No exceptions are being thrown, it just doesn’t update the data. Any ideas? Thanks
I have 2 Gridviews and a DetailsView for each GridView. The first Gridview and DetailsView work fine and I can Insert, Delete and Update the DetailsView just fine. However the second Gridview/DetailsView will only let me Insert but not Delete or Update. When I click on the "Delete" button it just ignores me. If I do an "Edit", when I try to click on the "Update" button it is ignored again and I have to click on "Cancel". I don't get any error messages...
a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key of a datarow you had just inserted into the database.
After a long set of trial and error i came up with the following:
- add the tablemappings of a table - call the dataadapte.fillschema method
then after inserting a new row into the database the primary key gets filled in automatically!
now thing is
i was hoping to duplicate this in sql server
but it doesn't seem to work at all
so after i insert a row into my datatable and update it the row is in the database but in vb the datarow primary key is not filled in! anyone have an idea?
prefereabely one that does not resort to stored procedures with return parameters etc
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?
I have been fighting this problem for several days now.
All I need to do is run a query against a table on mssql 2005 server and update a table (only update) in Oracle
Here are some of the things I have tried:
1) from a forum tip, I set up a dataflow: OLE DB Source (MS SQL) -> Derived Column -> OLE DB Command (Oracle)
- I get "Error at Data Flow Task [OLE DB Command [2850]]: Columns "NyNamn" and "id" cannot convert between unicode and non-unicode string data types."
2) I have tried a Source - OLE DB Source (MS SQL) -> Derived Column -> OLEDB Desination (Oracles OLEDB)
- Error when trying to preview: TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task [OLE DB Destination [2953]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF. Error at Data Flow Task [OLE DB Destination [2953]]: Opening a rowset for ""APPINV2"."OWNERS"" failed. Check that the object exists in the database.
------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
3) I have tried a Source - OLE DB Source (MS SQL) -> Derived Column -> OLEDB Desination (MicrosoftsOLEDB for Oracle)
- Error when trying to preview: TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task [OLE DB Destination [2953]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Unspecified error". An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Data type is not supported.". Error at Data Flow Task [OLE DB Destination [2953]]: Opening a rowset for ""APPINV2"."OWNERS"" failed. Check that the object exists in the database.
------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
I have full rights on the oracle database with the account used, i can connect with toad with no problems.
To me, this should be a simple task. has anyone got any advice for me ?
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows. Here is my logic - 1. Create Ole DB source to RemoteSERVER - using SELECT stmt 2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output). 3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows 4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE; But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
Hi.. I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc.. IF Exists( SELECT * FROM PlanEligibility WHERE PlanId = @PlanId ) BEGIN UPDATE PlanEligibility SET LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS ELSE @rsLengthOfService END, EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, EntryDates = @EntryDates, EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0 When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM, LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END, EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, OtherEmployeeExclusions = @OtherEmployeeExclusions WHERE PlanId = @PlanId END ELSE BEGIN INSERT INTO PlanEligibility ( PlanId, LengthOfService, EligibilityAge, EntryDates, EligiDifferentRequirementsMatch, LengthOfServiceMatch, EligibilityAgeMatch, OtherEmployeeExclusions ) VALUES ( @PlanId, Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS ELSE @rsLengthOfService END,--@rsLengthOfService, CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge, @EntryDates, Case When @PD_EmployeeContribution = 0 Then 0 When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END, CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch, @OtherEmployeeExclusions ) END Any help will be appreciated.. Regards, Karen
We have a rather large environment and have just a couple of boxes out there that we are getting cannot begin distributed transaction on inserts and updates but works fine on selects. Inserts and updates work fine outside the begin tran / commit so it's definitely DTC
We have checked the configuration on and the source box is set to No authentication required same for destination.
We have: Verified credentials running the service, changed them, same problem. Uninstalled and re-installed MSDTC per Microsoft instructions.
Have run all the tools for checking DTC DTCPing etc and followed those procedures which typically in the past has resolved any DTC issues. Other than swapping out the offending pc for a new one we are at a loss.
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
Hi everybody,I am using SQL Server 2005. I have a table which currently has only 1 record. I am unable to update any field for this particular record and SQL server is timing out and giving an error message saying No row was updated. I created another record in the table and tried to update the fields in the new record without any problem. I am unable to update any field only for the 1 record in the table using my application, query window sql statement as well as directly changing the in the database.Can anybody please help me.thanks in advance,Murthy here
