T-SQL (SS2K8) :: Query Regarding Update Data Using Application
Apr 7, 2014
SELECT ContactID,FirstName,MiddleName,LastName,Description FROM Contact
Contact table contains 4 columns as explained. in the application there is one tab called Contact where it displays Above information and description is non editable.Now the new requirement has come user can update the description information and save information in a new column say 'Description1'. that means new column needs to be added in the db and also necessary changes needs to be done at the application side
For ex :SELECT ContactID,FirstName,MiddleName,LastName,Description,Description1 FROM Contact
Now when user views the contact table it should display description info by default from 'description' table.If he edits he should see edited data from 'description1' table.the logic should if updated data is there display that data from 'description1' table other wise display from 'description' table
i have a database for an application, which has some particular fields in some table. i want to make sure that data update in those fields should be only thru the application, and no one (even dba) should be able to update data in those fields thru sql tools.
this is to make sure that no body is updating the field except the users thru the application. as these fields contain infor about "approval" of documents.
finding one set of data within another set of data for update,so if within column 1 it finds the results from another select on another table, it updates a different column with the result.
Example
SELECT Title from H..Import_Table returns 'PRN - Concord' 'PRN - San Jose' 'SLP - PRN - San Jose' 'San Jose - PT - PRN'
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
I have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255) SET @myvendedor = '87,30' print @myvendedor SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor) Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6 Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to numeric.
I have some code (C#) that runs an SQL update query that sets thevalue of a column to what the user passes. So, this causes an errorwhen anything the user passes in has a ' character in it. I'm surethere's other characters that'll break it too. So, I was wondering,how do I get around this? Is there some commonly accepted regexpattern that will make the value safe to run in an SQL query? How canI take care of any values that need to be escaped?I'm not using any fancy ado.net objects:string sql= [whatever the user passes in]SqlConnection connection = newSqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());connection.Open();SqlCommand command = connection.CreateCommand();command.CommandType = CommandType.Text;command.CommandText = sql;try{int result = command.ExecuteNonQuery();if (result != 1){Response.StatusCode = 500;Response.Write("The file has been uploaded, but wecould not update the DB");Response.End();}}catch (InvalidOperationException){Response.Clear();Response.Write("error");Response.StatusCode = 500;Response.End();}connection.Close();
I'm a bit of a newby to creating update queries so I was hoping those moreexperienced could help with what should be a simple query. I do know tocreate backups and test on a test database first.I need to copy an items' cost from location 'CA' to the cost in location'OH' when the cost in 'CA' is not zeroTable name is ITEMSLocation column is LOCCost column is COSTHere's the Select statement from Enterprise Manager's Open Table, Query toolSELECT loc AS Expr1, cost AS Expr2FROM ITEMSWHERE (loc = 'CA') AND (cost <> 0)I just can't seem to get this into a working Update query.Thanks in advance...
I have created a table in a database for a football that I want to update as the season progresses. Is there any way I can add the new data to the old data in my columns through SQL rather than searching for the old data, doing the maths in my head and doing a simple update query? I know it doesn't save much extra time but I can get awful lazy when it comes to extra maths!
Example would be:
Player Minutes Adam 287
Adam has played 287 minutes prior to the most recent match in which he played 67 more. I would like to know if its possible to do an update where I can just simply add 67 onto the existing numbers with one query rather than find Adam's minutes, use a calculator, do a table update.
I am working to create a phone list that will contain Last Name, First Name, and Phone Number sorted by last name. For printing purposes I would like to have three columns of data instead of the standard of one column.
Is it possible to create a query to present data in three columns showing the data side by side?
in my original database have a column which is for "path" ,the record in this column is like → �mms://192.12.34.56/2/1/kbe-1a1.wmv】 this kind of column is about 1202,045 .. I don't think is a easy job to update by person.. it may work but have to do same job 1202,045 times..
I have to change � mms://192.12.34.56/2/1/kbe-1a1.wav】 to � mms://202.11.34.56/2/1/kbe-1a1.wav】 I tried to find the reference book and internet . can't find out the answer for this problem. can you help? or maybe is it a impossible job? thanks
i created a asp.net application using sql server. it retrieve data from a sql server and shows it in the form. it uses textbox control to show it. i wanna update it or add a new item in database. i wrote the code for it under update and add buttons. when i click on the update button after changing a data in textbox, the page is getting refreshed nad comign back to initial state without getting updated. but when i change the textbox1.text with a string in the code, its getting updated with string without any problem. even same problem for addition too, am using a dataadapeter and dataset for this. when i assign a string to the datset columns, its getting updated well but wheni assign it with textbox.text.tostring, nothing is happenig to database. can anyone help me in this issue, why the data was not sent to the datbase and the page is getting refreshed.. plz need help.. its urgent
we have a new requirement that calls for also (in addition to RS's automatic directory updates) updating an organizational application database with metatdata about instances of scheduled reports, or alternatively having our app query RS's metadata for such info.
So, let's say we have a report called Sales Detail, and it runs at 8am every day of the week for client A thru the scheduling mechanism that comes with RS. Each time it runs to completion, an entry would have to be made in our organizational database representing a new run (instance) of the report under the client for whom it was run, in this case A.
Forgetting for the moment potential issues with a reporting system interfering with the transactional, or vice versa, can datasets in RS also do updates?...or can the scheduling mechanism in RS be extended to also log info to other places?...or is the metadata kept by RS (about instances of reports) easy to get to and decipher, and possibly be extended to record for which client this particular instance was run?
Apologies if this question is similar to one posted under "Click once deployment" but I got no replies and I REALLY need to know the answer to my problem!
I am using VB on VS 2005 Express and SQL Server 2005 Express.
My application is nearing the stage where I can start to use real data (from the end user) for testing purposes. Once I am as happy as I can be, I will install the application on the user's machine and he will use the application for a few weeks, with real data, before giving me a list of problems/suggested improvements etc.
I will then need to make the changes and re-install the application on his machine without losing all the changes he has made to the DB in those weeks.
Presumably, there must be a way of updating the executables without making changes to the DB on the client machine.
This is a very serious issue for me as the data will be around 2,000 records each of over 60 fields and the raw data will come from a variety of sources including paper so the data inputting will be a massive task that I do not want to repeat!
Also, if the application were ever to be sold, then other users would want to apply updates to the software whilst keeping their own data secure.
I have tried re-publishing and installing the application after setting the Publish Status of the DB files to "Exclude" but the newly installed application fails to connect to the old version of the DB.
I would very much appreciate any help in this matter as I hope I haven't just wasted several months of coding!
I created a little test application with SQL server. After everything was tested successfully I builded a setup file with Installshield. I installed the setup file on a different mashine. Everything runs without problems, but when I make entries to the database and I reopen the application no entry is saved. I am new to SQL server.
With cte_table (columna) As (select a from cust) Update columna Set a = 'hello'
Question: would a in cust be updated even though the update is to the CTE column named columna? Or would there be an error where the column name must match?
I had run a stored procedure in my server that update statistics against all user defined tables in my database (MSSQL 7.0).
Since then I am getting errors in my ASP application where I am reffering to adovbs.inc.
Here is an example of errors I get.
Microsoft VBScript runtime error '800a0411'
Name redefined: 'adOpenForwardOnly'
/Essai/adovbs.inc, line 14
Below is the stored procedure I have run against the database.
Can anybody help tank you guys.
CREATE PROCEDURE update_all_stats AS /* This PROCEDURE will run UPDATE STATISTICS against ALL user-defined tables within this database. */ DECLARE @tablename varchar(30) DECLARE @tablename_header varchar(75) DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @tablename WHILE (@@fetch_status <> -1)
BEGIN IF (@@fetch_status <> -2)
BEGIN SELECT @tablename_header = "Updating " + RTRIM(UPPER(@tablename)) PRINT @tablename_header EXEC ("UPDATE STATISTICS " + @tablename ) END FETCH NEXT FROM tnames_cursor INTO @tablename END PRINT " " PRINT " " SELECT @tablename_header = "************* NO MORE TABLES" + " *************" PRINT @tablename_header PRINT " " PRINT "Statistics have been updated FOR ALL tables." DEALLOCATE tnames_cursor
Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:
Sample data of the field: Rec1: Sally Morgan 201-555-1212 Rec2: 555-4040 John Smith Rec3: Jane Houstin 201-555-6452 ext1223
Desired result: Rec1: Sally Morgan 999-999-9999 Rec2: 999-9999 John Smith Rec3: Jane Houstin 999-999-9999 ext9999
There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.
since ten years perhpas the society has a database with some table (person, country, and so on)since last years , the project manager decides to create a new database with some new design because before some information was save in one table well, the problem is when we pass all application to the new model we've notice that some id are not the same !
sample :
table Language old model ------------------------- id frenchDescription english description ---------------------------------------- 3 Anglais English ..... ......
table Language new model
id frenchDescription english description ----------------------------------------- 5 Anglais English ..... .....
Alright , you can understand that the new model must be the same id => 3
I currently have a AFTER UPDATE trigger that looks at a specific column being updated. It works great, but I am now needing to also capture the field data when it is initially created as well. Do I need to build a sperate FOR INSERT trigger to capture the initial column data when inserted, or is it possible to build it into my current AFTER UPDATE trigger?
Here is my current trigger: CREATE TRIGGER [dbo].[xResponsible_By] ON [dbo].[PARTS_REQUESTOR] AFTER UPDATE
Table A - data example Idno Entity Flag 1 1 y 2 1 n 3 1 y 4 1 y
Table B - data example Idno Entity 1 1 2 1 3 1
Table C - data example Idno Entity 4 1
I want to update the value flag in table A.Update flag from n to y if idno A in table B and and idno A not in table C, so from table A above, first record and 3rd record remain unchange but 2nd record, flag will be changed from n to y. 4th record where idno A not in table b and in Table C so flag will change from y to n. is it possible, or do i need to use the "fetch" method to compare record by record.
DECLARE @id VARCHAR(10) DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR SELECT [ServersList] AS 'ID' FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
