Hi! I 'd like to update the database..I 'd like to update the same field, the first update would set all to 'No' and the second update woyuld set specific records to 'Yes':
1) "Update tblDept SET IsTop ='No'
2) "Update tblDept SET IsTop = 'Yes' WHERE id = 200 "
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
Hello. I know, you think I'm totally clueless, and you're right. SQL is the most confusing thing for me but I must learn it. So, I am running a query to find out the name of the person who sold the most stoves with the least color purchased. I have ran that query and it works . My problem is that I have to add to that query another column that counts the number of stoves sold, instead of having them displayed one by one. My query is as follows:
SELECT EMPLOYEE.Name,STOVE.Color FROM INV_LINE_ITEM, STOVE, EMPLOYEE, INVOICE WHERE INV_LINE_ITEM.FK_Stovenbr=STOVE.SerialNumber AND INV_LINE_ITEM.FK_InvoiceNbr=INVOICE.InvoiceNbr AND INVOICE.FK_EmpID=EMPLOYEE.EmpID AND STOVE.Color IN (SELECT CAST(MIN(Color) AS CHAR(5)) 'Min Color' FROM STOVE) AND EMPLOYEE.Name IN (Select CAST(MAX(Name) AS CHAR(15)) 'Employee Name' FROM EMPLOYEE) ORDER BY EMPLOYEE.Name, STOVE.Color
I have two tables linked with the colunm name systemId and I need to get all the systems responding to the condition (t1.software & 0000000001) = 1 and if they have any contacts I would like to see them. Here is what I have for the moment however my querry do not return the system info if no contact exist and that is wrong.
select customers.customerId,customers.systemId,customers.country from customers,contacts where (customers.software & 0000000001) = 1 and customers.SystemId = contacts.SystemId order by customerid
I have created a small querry and for some reason my container (datalist) is not reconizing the company name This is also the first time that i have created a querry with two select statements in the sproc.... Does it make a difference if there is two select statements in a querry?? When populating a datalist?Not sure if i need to seperate the two statements some kind of way.. Is this ok to use with datalist? Erik.. ALTER PROCEDURE Get_Information_For_Datalist_Global_ @IDProduct AS INT, @IDCompany AS INT AS SELECT Product.ProductName, Product.ProductID,Product.ProductImage, Product.ProductDescription, SprocsTable.* FROM Product INNER JOIN SprocsTable ON Product.ProductID = SprocsTable.ProductID WHERE Product.ProductID = @IDProduct SELECT Company.CompanyID, Company.CompanyName FROM COMPANY WHERE COMPANY.COMPANYID = @IDCompany
hi. i want to have a text box and command button. i will be entering the clients id in the text box and press the command button to search the clients record. this will be search query. i have done that previously in vb6. but dont know what query to write in vb08. Any help. thks.
There are 3 servers in our group which the Local Enterprise Manager Tool is giving us an error when we try to view local DB table data by right-clicking on the table and selecting return all rows... we get this error I thought this was a permissions error but as far as I know we haven't played with the permissions. This seems to be a common ailment here... does anyone have a fix for the EM tool.
An unexpected error happened during this operation. [Query] - Query Designer encountered a Query error: Unspecified error
I have two database on same server. I want write a querry, witch qering the data from 1st database, than create a new table on the 2nd database. But the in clause after the into clause permanently generate error.
Here is the query€™s little part: Select €¦.. into Backup in €™Test.mdb€™ from €¦
I try wrote other ways in €™Test€™ in €™dbo.Test€™ in dbo.Test but none of them work
its only work, when i not want pass for the second database
Hi,How to run a DTS from query analyzer using some t-sql commands??? Is it possible?? What is the command to run a DTS from QA???and What are the possible ways of running a DTS?? from how many types we can execute a DTS like 2 of it i know from EM directly and from the help of Schedule jobs.
I have 2 SQL store procedure which are suppose to return similar number of data. For an unknown reason one of the procedure is returning lasse data than the other. in order to dig out the reason for that I need to compare the two set of data return by both procedure.
one by one is a huge woek has I have more than 1000 records.
So my question is that what is the easy way to extract data which has been forget or miss ?
For example
my procA return 500 rows my procB return 400 rows
I would like to identify the 100 records which are in ProcA results but not in ProcB results In other words extr^ct the difference between A and B
hi. i have created a database with a table called customer. hav a field which displays the date. how can i add a query on the date picker so that when i choose a date on the calendar i can view the customers details. in other display the record by choosing a particular date.
i lost my laptop driver's cd,right now i am using windows XP professional.and laptop is of compaq v3000. so from where i get my laptops driver,can you help me out .?
Need a bit of help here. In one table i have my production data on some wheels. Each time the wheel is touched, it gets smaller. Then in another table I have the primary data of that wheel, ie price vendor, make model and scrap diameter.
What I'd like to figure out is, In one month, what was the wheel size to start off, what is the end size to give me a total size used during that month. If i could get that, then I think I would be able to figure out what the price cost for useage of that wheel was for the month.
Hi... I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc... ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE @Count int, @Sproc varchar(50), @Status varchar(200), @TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.' UPDATE Statements..Plan SET PlanName = PlanName1, Description = PlanName2 FROM Statements..Plan cp JOIN ( SELECT DISTINCT PlanId, PlanName1, PlanName2 FROM Census ) c ON cp.CPlanId = c.PlanId WHERE cp.ClientId = @ClientId AND ( IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'') OR IsNull(cp.Description,'') <> IsNull(c.PlanName2,'') )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.' END ELSE BEGIN SET @Status = 'No records were updated in Plan.' END
SET @Status = 'Adding plan information to Plan table.' INSERT INTO Statements..Plan ( ClientId, ClientPlanId, UserId, PlanName, Description ) SELECT DISTINCT @ClientId, CPlanId, @UserId, PlanName1, PlanName2 FROM Census WHERE PlanId NOT IN ( SELECT DISTINCT CPlanId FROM Statements..Plan WHERE ClientId = @ClientId AND ClientPlanId IS NOT NULL )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.' END ELSE BEGIN SET @Status = 'No information was added Plan.' END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Hi.every one. I have a problem when I use this querry select replace(Title,'.','')AS Title1, ID from tbl_Sim Where Title1 LIKE '%377%'
And here is my record Title : 0988.613.775 ID : 2
and here is the error: Invalid column name 'Title1'. Can any one show me how to run this querry, I want to TRIM the 'Title' before it can be compared with another condition I am a beginner. Thanks alots!
Select dbo.fn_EnGiftId(116) AS 'EnGiftId', dbo.fn_DeGiftId(''+ EnGiftId +'') AS 'DeGiftId' But the above querry giving me the following error When i am executing the querry:
Code Snippet
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'EnGiftId'. The scenario is I have created two functions 1st is for encryption of GiftId and 2nd is for Decryption of the Encrypted Giftid. But in the same select querry i want to use the name of column which i have given to the encrypted data in the selct querry i want to use that same column name in the decryption function uin the same querry.
When i am executing the below querry it gives me the required output. So Why not above querry.
Code Snippet
Select dbo.fn_EnGiftId(116) AS 'EnGiftId', dbo.fn_DeGiftId(''+ dbo.fn_EnGiftId(116) +'') AS 'DeGiftId' i d't want to write the encryption function again and again. Is theer any way to avoid this.?????
I linked my Active Directory catalog on my local machine as linked server. Next I querry this server, and it works pretty.
I have problem in similar situation when I linked AD as linked server on other machine and when I querry AD it yields error:
Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query " SELECT cn, mail, sn, co, distinguishedName, displayName FROM 'LDAP://DC=<domain>,DC=com' WHERE objectClass = 'user' AND objectCategory = 'Person' " for execution against OLE DB provider "ADSDSOObject" for linked server "(null)".
I am new to ASP.NET and met a problem about access a database on a Web Server (www.myserver.com). My meaning here that I created a .sql from my localhost to test, but when to upload to my web server on the internet, an error occured: "System.Data.SqlClient.SqlException: SQL Server does not exist or access denied". Anyone here can help me to solve my problem. Waiting for your reply soon. Thanks
I'm creating my first report. I do "new project" > "report server project". Thereafter I create my shared datasource. Thereafter I try to add a new report. It ask to enter my querry.
I've built up my querry previously with the querry analyser in SSMS, and there it runs quite fast.
But when i copy-paste the querry in the reporting designer, and i choose to preview it, it takes more then 20 minutes.
however, my querry it quite big, but i've written querries ten times larger in my life. (it's about 50lines: 10 querries, union-ed together and in each one i have 4 innerjoins.... all tables are even almost empty, so it's not that indexes are missing or whatever)...
is it known that in the report designer querries are much more slower????
Hello. Hope you are all well and having a good day. I've got a problem i would like you to help me out with. My company has got a client who sells meat and my senior tech lead has customized the cart to fit their needs and have created a despatch table with a "simple asp form" that allows them to scan their products in with a wi-fi scanner.
The despatch table has the following fields: 1. OrderID 2. Product 3. Quantit 4. sellbydate 5. traceabilitycode and 6. Weight Question 1: how do i create a form in asp that allow a user to view the order no, product and quantity celibate,traceabilitybycode and Weight after scanning the details into the simple form created? Question 2: How can i create an asp form that allows users to search for the Order that populates the orderid, Product, quantity? Question 3: How can i create an asp for that allows users to search for Product with orderid,traceabilitycode and quantity? Question 4: How can i create an asp form that allows users to Trace: Orderid, traceabilitycode, sellbydate and Quantity?
Please find below the source code of both the: despatchLotsRowsSimpleForm.asp that allow them to scan details into the table and the despatchLotsExec.asp:
The despatchExec.asp Page <% ' WebShop 3.0x Shopping Cart ' Developed using ASP ' August-2002 ' Email(E-Mail address blocked: See forum rules) for further information ' (URL address blocked: See forum rules) ' Details: add e-mail to newsletter list %>
i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...
One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.
But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.
I have a web form that collects details on books (as an example), and in that form is a checkboxlist that displays an entry for each potential author in the database (as an example).
The user can obviously tick as many authors as they want to represent Authors of the book. The ticked entries form the entries in the BooksToAuthors table which only has BookID and AuthorID columns.
I have a number of questions:
How do I take what is in the CheckBoxList to the database and how does this relate to Stored Procedures?
Do I fill the checkbox selections into an Array? How do I get these 'many items' to a Stored Procedure that runs a transaction to put the book in and then the many rows in AuthorsToBooks.
What is being passed? Can you pass an array or something to a stored procedure?
As far as performance goes should I avoid using "IN" with update statements?Example:update table_nameset x = 5where y IN (select z from table_name1 where a = b and c = d)If this is terribly inefficient what are the alternatives?Thanks...
I have 3 queries and I need to see if there is a way to combine them since they do the same thing, or if there is a more efficient way I am missing. I run the query below on Table A to find the product first for TableA.Model=1, then for Model=2 and then Model=3. The reason I split it into three queries is I need Model=1 Customers only, then Model=2 only if there isn't a Model=1 Customer, etc.
UPDATE Table1 INNER JOIN TableA ON Table1.Product = TableA.Product SET Table1.Customer = [TableA].[Customer] WHERE (((TableA.Model)="1") AND ((Table1.Customer) Is Null));