Can't Make This Update Work - Advice??

Feb 26, 2008

Hi - I'm in a situation with a very large table, and trying to run an update that, any way I've approached it so far, seems to be taking unnacceptably long to run. Table has about 20 million rows, looks something like this:

ID - int, identity
Type - varchar(50)
PurchaseNumber - varchar(50)
SalesAmount - Money

ID Type PurchaseNumber SalesAmount
1 A 3834AA38384 20.32$
2 B 3834AA38384 11837.32$
3 C 3834AA38384 666.32$
4 C 887DF88U01H 23423.32$
5 A 887DF88U01H 12.32$
6 B OI83999FH28 4747.1$
7 D 38438495985 9384.6
8 E 02939DDJJWI 22.22$
9 F 07939SDFDF2 33.33$

The goal of the update is to make the [Type] uniform across [PurchaseNumbers], according to the max sales amount. For each PurchaseNumber a, set the type = the type of the row that has the MAX salesAmount. If there is only one entry for PurchaseNumber, leave the type alone. Expected update after completion would look like this:

ID Type PurchaseNumber SalesAmount
1 B 3834AA38384 20.32$
2 B 3834AA38384 11837.32$
3 B 3834AA38384 666.32$
4 C 887DF88U01H 23423.32$
5 C 887DF88U01H 12.32$
6 B OI83999FH28 4747.1$
7 D 38438495985 9384.6
8 E 02939DDJJWI 22.22$
9 F 07939SDFDF2 33.33$

I got this out of a warehouse, and it definitely isn't normalized well. Was considering breaking down into a better model, but I'm not yet sure if that would make the update easier.

I've been approaching this with sub-queries (finding all the PurchaseNumbers with more then one entry, then the max sales purchase of that purchase Number, then the type of that purchase number and sales amount to update all of that purchase number) but this not only ends up a little messy, but also very slow.

The only other detail that may be important is that out of the 20 million total rows, about 19.5 million purchaseNumbers are unique. So, really, there are only about 500k rows I actually have to update.

I've thought of a few ways to make this work, but none of them seem fast and wanted to see if anyone had a pointer. Thanks!

View 5 Replies


ADVERTISEMENT

Need Advice On Best Way To Make Dbase Record Updates

Jul 23, 2005

Approximately once a month, a client of ours sends us a bunch ofcomma-delimited text files which I have to clean up and then importinto their MS SQL database. All last week, I was using a Cold Fusionscript to upload the cleaned up files and then import the records theycontained into the database, though obviously, the process tookfriggin' forever, and could have been done 500x quicker had I done itdirectly on the server. My SQL knowledge is somewhat limited, however,so I had no choice but to stick to what I know, which is Cold Fusionprogramming.In the process of cleaning up some of these comma-delimited text files,I inadvertently messed up some of the 10-digit zip codes, by applyingthe wrong Excel formula to the ZIP columns. These records were importedinto the database with obviously incorrect zip codes (ie: singledigit). So now, I have to find the best and quickest way possible tocompare these records in the database (that have the single digit zipcodes) with the unmodified data, and to update the zip codes with thecorrect data.I've had no luck setting up a TEXT file as an ODBC datasource, -- soI've ruled that out completely. I've also managed to import theunmodified data into an Access database, and to set it up as a ColdFusion datasource. But it seems this 2nd road I've been traveling downis not the ideal approach either.My question is, -- assuming that I'll be able to import the recordsfrom the Access database into their own table on the SQL server, -- howshould I go about the process of updating these records that have theincorrect zip codes?Here is the specific logic I would need to employ:* Here is a list of records, each of which contains an incorrect1-digit zip code (Database A / Table A)* Here is a much longer list of records (which contains all of therecords from Database A / Table A + thousands more), each of whichcontains a correct 5-digit zip code (Database B / Table B)* Compare both lists of records and run the following query/update:When a record in Database A / Table A has matching "name", "address1",and "address2" values as a record in Database B / Table B -- update therecord in Database B / Table B with the zip code from the matchingrecord in Database A / Table A.Would anyone care to write a sample query for me that I could rundirectly on the SQL server, or at least give me some pointers?The specific field names are as follows:name,address1,address2,city,state,zipThanks in advance!- yvan

View 5 Replies View Related

I Need To Make A Minor Change To A SQL Server Script That Is In Production. Need Advice So I Don't Screw Something Up.

Mar 1, 2007

Hello. I am not very strong with SQL server. But I know enough to get my way around. The reason I am asking forhelp is that I need to change a SQL server script that resides on one of the production database servers of thecompany I work for. I just need to add two new lines to the stored procedure.(See the lines that are bolded.) Thesevalues are [MO_FAX],[MO_EMAIL].
Can someone tell me if there is a best way of going about doing this? Can I just simply open up SQL server and quickly make the change? The big issue here is that this script is used in production. So I am just a little worriedabout screwing something up. Anyway, I would appreciate some good advice on this?
 
 
/* Returns all data given the region and country */ALTER  PROCEDURE [dbo].[GetAllInfomration]
AS SELECT   [COUNTRY],  [Company_NAME],   [CompanyAddress],   [CompanyPhone],
  [MO_FAX]  [MO_EMAIL]
  [CompanyFax],
 FROM [InfoLookup].[dbo].[Company_Contact]  RETURN

View 5 Replies View Related

How Do I Make It Work?

Jan 30, 2008

I have an OLAP database which is required to update daily. First of all back track a bit about this OLAP database, It is built from an OLAP data source consists of a dozen "Dimension" tables and half a dozen "Fact" tables and they are set up in a normalized relationship. This 20 or so OLAP tables are created from a single "Production" data warehouse de-normalized table. By the way the database engine, SSAS, SSRS and SSIS are installed in one virtual server using Sql 2005 SP2.

Here are the steps I update the OLAP database manually.

At Sql database engine


Drop the existing data warehouse table at the virtual server

Copy the up-todate data warehouse table from a remote server and paste to the database at the virtual server

Drop all the OLAP Fact tables

Drop all the OLAP Dimension tables

Create the Dimension Tables using the Sql script I saved when first created

Create the Fact Tables using the Sql script I saved when first created

Populate the data to the Dimension Tables from the data warehouse table using the Sql script I saved when first created

Populate the data to the Fact tables from the data warehouse table using the Sql script I saved when first created

Assign indexs and constrains to the Dimension Tables

Assign indexs and constrains to the Fact Tables
At Sql Server Analysis Services


Process the 15 Dimensions

Process the 2 OLAP cube
I want to automate the whole process using SSIS. I am new to SSIS and I need some direction on how to make it work?

Thanks

View 1 Replies View Related

Can't Seem To Make This Query Work

Jul 24, 2006

Hi,
I have a query thatI need to make into one query for the sake of an application reading only one cursor.
 
Here's the syntax:
select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrowinner join a10 g on x.escrow = g.escrow  where k.ftype = 'S' group by x.amount, g.officer) As New,a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrowinner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer
 
The error message i'm recieving is the following:
 
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
 
Any ideas? any suggestions would be great.
 
Thanks

View 3 Replies View Related

SQL Select - How To Make This One Work?

Aug 15, 2006

Select *From HSRSeasonWHERESeasonID='1'HSRID=(Select HSRID FROM HSR WHERE HotelID='1' AND ServiceID='1' AND RoomID='1')

View 1 Replies View Related

I Can't Make SqlBulkCopy Work

Feb 7, 2008

I have a scenario whereby I'd like to insert multiple rows into a table on a SQL server database as efficiently and easily as possible.
After some research, it looked like .NET 3.0's SqlBulkCopy class would do what I want. I've tried to set something up, but it's not working. It's not even throwing an error. The code executes but it simply hasn't done the insert by the end of it!
 My table structure is simple. The name of the table is LPSTUnavailableDate. It has just two columns, one of them an auto-populated ID field:


LPSTUnavailableDateId, INT, PRIMARY KEY, IDENTITY(1,1)

View 1 Replies View Related

How To Make Two Separate Query Work Together

Oct 15, 2014

I am currently stuck on how to make this 2 separate query work together, both work as i want them to individually, please note the syntax is according to a application i use that uses mysql to manipulate columns in an imported csv file.

CONCAT('at-',
REPLACE([CSV_COL(18)],'http://www.homebuy.co.uk/product.php/','')
)
removes last character i.e. /
SUBSTRING([CSV_COL(18)], 1, CHAR_LENGTH([CSV_COL(18)]) - 1)

basically i need these 2 to work together to give me an output like this

at-09fd8903

from the this. URL...url above minus the "" as i said both work on there own, but not together.

View 1 Replies View Related

SQL Parameter And Wildcards, How To Make It Work?

Oct 22, 2007



Hello, I have what should be a very simple problem, but I cant solve it.
I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!)

Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example:


DECLARE @Dv_id nchar(15)

SET @Drv_id = '%'



SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id



SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE '%'

OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows.
The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between:
WHERE Drv_id LIKE @Drv_id
and
WHERE Drv_id LIKE '%'
?
The wildcard statement '%' is supposed match everything, correct??
It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL.

Is there any way around this?

View 2 Replies View Related

Error 26 - Followed The Guidelines, Still Unable To Make It Work

Nov 7, 2007



Good Morning, I've been searching through all the tutorials and questions, have tried many things. I am still getting "[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]"

as an error. This is what I've got:

I€™m using SQL Server Express 2005, installed with the default settings. Had not touched this program for anything until I started to follow directions to fix Error 26. Visual Web Developer Express ASP.NET is what I€™ve used to build the webpage. I was using the ASP.NET web configuration to add users to the database, which is set to use the provider ASPSqlServerProvider.
SQL Server 2005 Surface Area Configuration
Database Engine Remote Connections €“ set to €śLocal and Remote Connections €“ Using TCP/IP Only€?
SQL Server Browser is Enabled and Running
- is set to Active, under Built In Account €“ Network Service
I have created Windows firewall exceptions for:
sqlservr.exe
sqlbrowser.exe
udp port 1434
SQL Server Configuration Manager
both SQL Server and SQL Server Browser are running.
Under SQL Server 2005 Network Configuration
Shared Memory and TCP/IP are enabled only.

SQL Native Client Configuration
Shared Memory 1
TCP/IP 2
Named Pipes 3
all enabled

I read through the post at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but as I'm new to SqlServer I do not know how to check on the first three items.

I'm getting extremely frustrated, and would just like the login portion of this website to start working before my boot ends up through the computer. Please help, lol, thank you.

View 9 Replies View Related

Transact SQL :: How To Make Procedure Work For Multiple Values In Parameter

Jun 7, 2015

Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do

exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for
exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'.
USE [merck_acronyms]
GO

[code]....

View 2 Replies View Related

Advice On The Best Way To Update A Database

Jun 17, 2007

Hi,Apologies if this has already been asked, but I couldnt find a thread that asked exactly what I wanted.Im making an administration panel for a site where you can change various settings, options, and categories that data can fall into. When editing, adding or deleting a record i COULD make a trip to the database every time, but this feels very inefficient as I understand that establishing the connection is usually the biggest performance hit when querying a database.An alternative plan is for me to simply record the changes made in the panel and have a "save" button. When this is clicked, ONE database connection would be opened and all the data would be saved/updated/deleted as necessary. However, this would involve several "for" loops while the connection is open.The question is; which method would you recommend and why? And does having several "for" loops while the connection is open nullify the advantage gained by only opening one connection?Any advice would be very much appreciated. Thank you  

View 2 Replies View Related

Need Advice On Update Statement

Aug 28, 2006

I have a simple update statement that set sthe student's password to their id number:

update studemo set passwd = ident

ident is not part of the primary key but there is a unique, non-clustered index using that column

This has been running for over a hour on a table with 25K rows. Am I doing something basically wrong here?

View 10 Replies View Related

Can’t Make Vs2008pro, Sql2005dev, Sqlexpress All Work Together Via F5run And Vista Localhost At Same Time??

May 16, 2008

Clean vista install,
Clean full office 2007plus install
Clean visual studio 2008pro install
Many failed sql 2005dev installed, did SKUUPGRADE-1
Now have office2007 Smallbiz, VS2008pro, SqlExpress and sql 2005dev installed.
Note outlook 2007 error on first run after sql2005dev.
Seams to have broken and created a new sql connection, don€™t think its the original.
but that€™s not the current issue !
web admin page error, could not connect to db
I did regsql.exe from the net dir and the web admin work and the db is created for roles and such in vs 2008 via web admin page and mssql.
Managed to create db in apps data folder and modify connection in server explorer.
Added table to aspx page from apps folder and it runs via f5, but errors under localhost.
€śCannot open user default database. Login failed.€?
€śLogin failed for user 'NT AUTHORITYNETWORK SERVICE'€?
Have seen some reference to iis permissions, but not sure what to change?
Is it a file level permission, a virtual permission
Works in vs2008 using f5 run!
http://localhost:50115/vs2008/
Can work with apps folder db file in vs 2008 server explorer
Had to modify db connection back to sqlexpress in server explorer
Can work with db in sql management studio, have three local instances;
Pc
Pcsqlexpress
Pcsmallbiz
Can display db data in aspx page via f5 , but not localhost unless vs2008 is not running!!!
Have not been able to make vs2008, Sql2005dev, sqlexpress all work together using iis in vista via localhost and vs2008 f5 at same time??
Can€™t do anything with db in solutions explorer, but can work in server explorer if I modify connection back to sqlexpress
Can€™t display data aspx page via localhost
http://localhost/vs2008/
Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITYNETWORK SERVICE'
Localhost works in iis via virtual directory if I remove db grid in page or vs is not running
Could someone test and tell how to use all of these together?
What is going to happen if I get it to work local and then try to upload to shared host?
<connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Server=wabpc;Database=VS2008test;Integrated Security=true" providerName="System.Data.SqlClient" />
<add connectionString="Server=SQLEXPRESS;Database=Database.mdf;Integrated Security=true" name="sqlexpress" />

</connectionStrings>
If I close vs2008 and the management studio, localhost works and displays the db info!!
Open vs2008, modify connection back to sqlexpress and the aspx page breaks via localhost
Close vs2008 and local host works
Open vs2008 and get a error 25 in server explorer connection string invalid, but f5 works after the offline clears.
I change db connection back to sqlexpress to work with db in server explorer!
That breaks localhost, but f5 works

Any ideas?
Need more Tylenol

View 9 Replies View Related

The Fastest Way To Perform An Update ... Advice Needed :)

Nov 12, 2006

Hi all,

I have a situation where my Visual C# application presents a number of fields. In order to update a student object, I wish to call a stored proc. 1 or more fields can be updated... And If one is left null, then I don't want to update it, but instead I want to keep the old value.

I am really wondering if I am approaching this the right way. The following stored proc does what I want it to do, however I'm thinking there may be a faster way...

Here it is:

-- Update a student, by ID.

DROP PROCEDURE p_UpdateStudent

CREATE PROCEDURE p_UpdateStudent

@ID INT,

@NewFName VARCHAR(25),

@NewOName VARCHAR(25),

@NewLName VARCHAR(25),

@NewDOB DATETIME,

@NewENumber VARCHAR(10),

@NewContactAID INT,

@NewContactBID INT

AS

BEGIN

SET NOCOUNT ON;

-- DECLARE THE OLD VALUES

DECLARE @FName AS VARCHAR(25)

DECLARE @OName AS VARCHAR(25)

DECLARE @LName AS VARCHAR(25)

DECLARE @DOB AS DATETIME

DECLARE @ENumber AS VARCHAR(10)

DECLARE @ContactAID AS INT

DECLARE @ContactBID AS INT

-- Get all of the old values

SELECT @FName = FName FROM TBL_Student WHERE ID = 10000

SELECT @OName = OName FROM TBL_Student WHERE ID = 10000

SELECT @LName = LName FROM TBL_Student WHERE ID = 10000

SELECT @DOB = DOB FROM TBL_Student WHERE ID = 10000

SELECT @ENumber = ENumber FROM TBL_Student WHERE ID = 10000

SELECT @ContactAID = ContactAID FROM TBL_Student WHERE ID = 10000

SELECT @ContactBID = ContactBID FROM TBL_Student WHERE ID = 10000



-- USE ISNULL to set all of the new parameters to the provided values only if they are not null

-- Keep the old ones otherwise.

SET @NewFName = ISNULL(@NewFName, @FName)

SET @NewOName = ISNULL(@NewOName, @OName)

SET @NewLName = ISNULL(@NewLName, @LName)

SET @NewDOB = ISNULL(@NewDOB, @DOB)

SET @NewENumber = ISNULL(@NewENumber, @ENumber)

SET @NewContactAID = ISNULL(@NewContactAID, @ContactAID)

SET @NewContactBID = ISNULL(@NewContactBID, @ContactBID)

-- Do the update

UPDATE TBL_Student

SET FName = @NewFName,

OName = @NewOName,

LName = @NewLName,

DOB = @NewDOB,

ENumber = @NewENumber,

ContactAID = @NewContactAID,

ContactBID = @NewContactBID

WHERE

ID = @ID

END

GO

So yeah it works. But As you can see I wish to keep an old copy of the values to perform checks pre update....

Is there any faster way, or am I on the right track? I need a pro's advice :) (before i write all of my procs!!)

Thanks all.



Chris





View 7 Replies View Related

How To Make Update Relation?

Mar 1, 2007

I have two tables: TableX & TableY

there is two similar fields (Size) with the same datatype, I want to make a relation in a way if I change the value in (TableX.Size) the same value will be applied to (TableY.Size).

How to?

View 4 Replies View Related

Update SubQuery(two Tables), Pls Advice Wats Wrong With The Query. 1 Hole Day I Spent Still Cant Figure

Oct 27, 2006

Table A, Table B. need to update table A balance field from table b sum of amount

UPDATE CUSTOMERS SET BALANCE=(SELECT SUM(AMOUNT) FROM PAYMENT,CUSTOMERS

WHERE CUSTOMERS.ID=PAYMENT.ID GROUP BY PAYMENT.ID)





Msg 512, Level 16, State 1, Line 25

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

View 4 Replies View Related

How To Make Update Not Change Case

Apr 15, 2015

This is on the uniqueidentifier column. Both tables are on the same database and have the same COLLATE Latin1_General_CI_AS.

But my plain update and insert results are all upper case.

Then when I tried to force by using

Insert (cols1, cols2) Values (source.col1, source.col2) COLLATE Latin1_General_CI_AS

and

Update set target.col1=source.col1, target.col2=source.col2 target.col1=source.col1 COLLATE Latin1_General_CI_AS

I got

Msg 447, Level 16, State 0, Line 32

Expression type uniqueidentifier is invalid for COLLATE clause.

View 5 Replies View Related

UPDATE/INSERT To Make One-to-Many Table Become One-to-One

Jul 23, 2005

I have a scenario where two tables are in a One-to-Many relationshipand I need to move the data from the Many table to the One table sothat it becomes a One-to-One relationship.I need to salvage the records from the many table and without goinginto detail, one of the reasons I can't do the opposite asthere are records in the ONE table that I need to keep even if theydon't have any child records in the MANY table.Below I created the code to create the sample tables:1- tblProducts is the ONE side table2- tblProductDetails is the MANY side table3- tblProductsResult is the RESULT I expect to get after runningsome T-SQL code4- tblProductComponents is another MANY side table to tblProducts5- tblProductComponentsResult is the RESULT I expect to get...Some of the points to consider:6- Normally all UniqueID columns are to be IDENTITY. Forthis sample i am entering the UniqueID values myself.7- I don't want to create new tables like tblProductsResultand tblProductComponentsResult. I want to update the real tables.I have created the tblxxxResult tables only for this post.8- The goal is to update the name of the Product by giving it thename of the first matching Name from tblProductDetails.9- If there are more than one entry in tblProductDetails for eachProduct, then I need to create new Products inheriting the originalProduct's information including its child records from tblProductComponents.If you run the code and open the tables it will be much clearerto visually see what I want to achieve.CREATE DATABASE MyTestDBGOUSE MyTestDBGOCREATE TABLE [dbo].[tblProducts] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProducts VALUES (1, 'ABC', 55)INSERT INTO tblProducts VALUES (2, 'DEF', 66)INSERT INTO tblProducts VALUES (3, 'GHI', 77)INSERT INTO tblProducts VALUES (4, 'JKL', 88)CREATE TABLE [dbo].[tblProductDetails] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[ProductID] int) ON [PRIMARY]GOINSERT INTO tblProductDetails VALUES (1, 'ABC1', 1)INSERT INTO tblProductDetails VALUES (2, 'DEF', 2)INSERT INTO tblProductDetails VALUES (3, 'GHI', 3)INSERT INTO tblProductDetails VALUES (4, 'GHI2', 3)INSERT INTO tblProductDetails VALUES (5, 'GHI3', 3)INSERT INTO tblProductDetails VALUES (6, 'JKL2', 4)INSERT INTO tblProductDetails VALUES (7, 'JKL', 4)INSERT INTO tblProductDetails VALUES (8, 'JKL3', 4)INSERT INTO tblProductDetails VALUES (9, 'JKL4', 4)CREATE TABLE [dbo].[tblProductComponents] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponents VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponents VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponents VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponents VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponents VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponents VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponents VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponents VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponents VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponents VALUES (10, 4, 'JKLb')CREATE TABLE [dbo].[tblProductComponentsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponentsResult VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponentsResult VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponentsResult VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponentsResult VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponentsResult VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponentsResult VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponentsResult VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponentsResult VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (10, 4, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (11, 5, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (12, 6, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (13, 7, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (14, 7, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (15, 8, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (16, 8, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (17, 9, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (18, 9, 'JKLb')CREATE TABLE [dbo].[tblProductsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProductsResult VALUES (1, 'ABC1', 55)INSERT INTO tblProductsResult VALUES (2, 'DEF', 66)INSERT INTO tblProductsResult VALUES (3, 'GHI', 77)INSERT INTO tblProductsResult VALUES (4, 'JKL', 88)INSERT INTO tblProductsResult VALUES (5, 'GHI2', 77)INSERT INTO tblProductsResult VALUES (6, 'GHI3', 77)INSERT INTO tblProductsResult VALUES (7, 'JKL2', 88)INSERT INTO tblProductsResult VALUES (8, 'JKL3', 88)INSERT INTO tblProductsResult VALUES (9, 'JKL4', 88)I appreciate your assistance on this.Thank you very much

View 6 Replies View Related

Ways To Make This Work: Several Selectable Related Record For One Main Record.

Apr 6, 2007

Hey all!



Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.

To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.

On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.



I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.



Thanks in advance,

Johan

View 5 Replies View Related

How To Make Automated Update On The Previous Record...

May 9, 2008

Hello...i have a table that record all the reading meter....so when i change one of the reader meter data...in logical it will automatically change the normalized fields...

Reading teble
-------------------
-id[PK]
-meter
-normalized
-date
when i insert new record..i just insert data about date and meter...an normalized is automated calculate using my function..the problem is..when i have data more than one...when i try insert or update or delete data...it nee to be automatically calculate back the normalized..i know this is needed the temperory table and then reinsert back..how can i solve this problem???

View 1 Replies View Related

Make A Copy Of SQL DB And Update/change Stored Procedures

Oct 10, 2006

Can I make a copy of my development database DEV on same SQL SERVER machine, rename it to TEST and stored procedures to be updated automatically for statements likeUPDATE [DEV].[dbo].[Company]SET [company_name] = @company_nameto becomeUPDATE [TEST].[dbo].[Company]SET [company_name] = @company_namein order not to edit each individual stored procedure for updating it ?

View 2 Replies View Related

Mssql 2005. How To Make Update Stored Procedure ?

May 1, 2008

Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile(  @user_id uniqueidentifier,  @user_firstname nvarchar(50),  @user_lastname nvarchar(50),  @user_birth nvarchar(20),   @user_gender nvarchar(20)  )
AS 
 UPDATE user_profile    SET         user_firstname = @user_firstname,     user_lastname = @user_lastname,     user_birth = @user_birth,     user_gender =  @user_gender  WHERE user_id = @user_id  RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ? 
 

View 2 Replies View Related

Update Does Not Work Well

Sep 29, 2006

Hello,I created a formview in a web page. The data are in a sql server express database.With this form, I can to create a new data, I delete it but I can't to modify the data.The app_data folder is ready to write data; the datakeynames element in formview web control declared. I replace the automated query created by VS 2005 by a strored procedure to see if the problem solved.The problem is the same with an update query or a update stored procedure...Have you an idea, please.Than you for your help.Regards.

View 1 Replies View Related

Update Does Not Work

Jan 9, 2007

Im working with a detailsview and when I try to edit something and then update, the changes are not saved.
I have 2 tables ("[etpi.admin].Ocorrencias" and "[etpi.admin].SMS") that store the data that Im trying to change. Since Im having problems with the name of tables, Im coding it manually, using SQL server management studio and VWD.
I believe my code can be wrong (Im new to vwd and C# world), so here it is:
UpdateCommand="UPDATE [etpi.admin].Ocorrencias SET [Status_Ocor] = @Status_Ocor, [Percentual] = @Percentual, [IDPriori] = @IDPriori, [Abertura] = @Abertura, [IDTecRes] = @IDTecRes, [Area] = @Area, [CodEquip] = @CodEquip, [Descricao] = @Descricao, [Destinatario] = @Destinatario, [Data_Implanta] = @Data_Implanta WHERE [etpi.admin].Ocorrencias.IDOcorre = @IDOcorre
UPDATE [etpi.admin].SMS SET [idSMS] = @idSMSWHERE [etpi.admin].SMS.IDOcorre = @IDOcorre"> 
<UpdateParameters><asp:Parameter Name="idSMS" Type="Int32" /><asp:Parameter Name="Status_Ocor" Type="String" /><asp:Parameter Name="Percentual" Type="Int32" /><asp:Parameter Name="IDPriori" Type="Int32" /><asp:Parameter Name="Abertura" Type="DateTime" /><asp:Parameter Name="IDTecRes" Type="Int32" /><asp:Parameter Name="Area" Type="String" /><asp:Parameter Name="CodEquip" Type="Int32" /><asp:Parameter Name="Descricao" Type="String" /><asp:Parameter Name="Destinatario" Type="String" /><asp:Parameter Name="Data_Implanta" Type="DateTime" /><asp:Parameter Name="IDOcorre" Type="Int32" /></UpdateParameters>
Thx.

View 9 Replies View Related

UPDATE DOES NOT WORK!!

Sep 10, 2007

hi all, i have created a gridview with the select,delete and insert commands working properly. but the update command does not work. when i edit a column and click the update button, it generates no errors but does not save the changes. it just brings back the original values. i dont know wats missing. can anyone help me?
this is part of my code:UpdateCommand="UPDATE [test101] SET Surname=@Surname, Names=@Names,Registration=@Registration,[Course code]=@Course_code,Grade=@Grade WHERE ID=@ID1 "<UpdateParameters>                <asp:Parameter Name="Surname" Type=String />                <asp:Parameter Name="Names" Type=String />                <asp:Parameter Name="Registration" Type=String />                <asp:Parameter Name="Course_code" Type=String />                <asp:Parameter Name="Grade"  Type=Int32 />                <asp:Parameter Name="ID1" Type=Int32 />            </UpdateParameters>

View 4 Replies View Related

Update Does Not Work!

Feb 11, 2008

I need to remove all white space i a column in order to succefully convert to int, but it does not seem to work in just this table.

I cannot understand why! I have done this a thousand times before!

I use:
update table1 set col1 = replace(col1, ' ', '')

datatype is varchar(10) not null

View 7 Replies View Related

Update Database Won't Work

Oct 13, 2006

Hey Guys,

Could some help me out an tell me why this wont run.

I think I got an error at the bottom


USE InfoPathBudget
GO
UPDATE TOSS
SET ytdexpenses = SUM(monthlyExpenses)
SET ytdBudgeted = SUM(monthlyBudgeted)
SET ytdCapitalExpenses = SUM(monthlyCapitalExpenses)
SET ytdCapitalBudgeted = SUM(monthlyCapitalBudgeted)


I got this when I ran it
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.

View 1 Replies View Related

How Does UPDATE Statement Work?

May 11, 2007

Could someone tell where I can find out if it's true that duringUPDFATE SQL Serve deletes data from table, and then inserts new one.Thanks-A

View 4 Replies View Related

SqlDataSource Update Doesn't Work When Using Parameter

Feb 3, 2007

Hi all:
I have a list of items (actually a relation in which a user has selected an item, along with a rating for the item) in an Access database table, connected to my app with a SqlDataSource and bound to a repeater.  The repeater displays the items to the user along with a dropdown box to show the rating, and allow the user to update it.  The page connects and displays correctly.
My problem is that when the user submits the page and I iterate through the repeater items to update each rating, the updates are not being completed in the database.  The update works if I hard-code a value for the rating into the query itself, but not when using an updateparameter (pTaskRating below).  In other words if I replace pTaskRating with '5', all the correct records will be found and have their ratings updated to 5.  That means that the mySurveyId and pTaskId(DefaultValue) parameters have to be working, because the right records are found, but I can't seem to update records based on the DefaultValue of the pTaskRating parameter, even though I can verify that the DefaultValue is correct by placing a watch on it.  It seems that my problem must be in my use of that particular parameter in the query, either in properties of the parameter or in the value assigned to it.  I am extremely frustrated - any ideas would be greatly, greatly appreciated.  Thanks!
Bruck
The table I'm pulling from and updating looks like this:
SURVEY_ID (Text 50), TASK_ID (Long Int), RATING_ID (Long Int)
Here's my ASPX for the main data source:
<asp:SqlDataSource ID="sqlTaskSelections" runat="server" ConnectionString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source="abc.mdb";Persist Security Info=True;Jet OLEDB:Database Password=xyz' ProviderName="System.Data.OleDb" SelectCommand="SELECT [SURVEY_ID], [TASK_ID], [RATING_ID] FROM [TBL_TASK_SELECTION] WHERE [SURVEY_ID] = mySurveyId" UpdateCommand="UPDATE [TBL_TASK_SELECTION] SET [RATING_ID] = pTaskRating WHERE ([SURVEY_ID] = mySurveyId) AND ([TASK_ID] = pTaskId)">
<UpdateParameters>

<asp:SessionParameter Name="mySurveyId" SessionField="SurveyId" DefaultValue="" /><asp:Parameter Name="pTaskId" DefaultValue="" /><asp:Parameter Name="pTaskRating" DefaultValue="" />
</UpdateParameters>
And here's the repeater (the Task ID and Rating are stored in hidden fields for easy access later):
<asp:Repeater ID="rptTaskSelections" runat="server">

<HeaderTemplate><table border="0"></HeaderTemplate>

<ItemTemplate>

<tr class="abctr"><td class="normal"><asp:DropDownList ID="cbRatings" runat="server"></asp:DropDownList><asp:HiddenField ID="hTaskId" Runat="server" Visible="false" Value='<%# Eval("TASK_ID") %>' /><asp:HiddenField ID="hRating" Runat="server" Visible="false" Value='<%# Eval("RATING_ID") %>' /> <%# Eval("TASK_ID") %></td></tr>
</ItemTemplate>

<FooterTemplate></td></tr></table></FooterTemplate>
</asp:Repeater>
And here's the page load and submit VB:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not Page.IsPostBack Then


'BIND / LOAD RATINGS TO DROPDOWN BOXES HEREDim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentRating As HiddenFieldrptTaskSelections.DataSource = sqlTaskSelectionsrptTaskSelections.DataBind()


For i = 0 To rptTaskSelections.Items.Count - 1



cbCurrentRating = rptTaskSelections.Items(i).FindControl("cbRatings")hCurrentRating = rptTaskSelections.Items(i).FindControl("hRating")



cbCurrentRating.DataSource = sqlRatingscbCurrentRating.DataTextField = "RATING"cbCurrentRating.DataValueField = "ID"cbCurrentRating.DataBind()cbCurrentRating.SelectedValue = hCurrentRating.Value


Next

End If
End Sub
Protected Sub btnSubmitRateTasks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmitRateTasks.Click

'UPDATE RATINGS HERE

Dim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentTaskId As HiddenField

For i = 0 To rptTaskSelections.Items.Count - 1


cbCurrentRating = rptTaskSelections.Items(i).FindControl("cbRatings")hCurrentTaskId = rptTaskSelections.Items(i).FindControl("hTaskId")


sqlTaskSelections.UpdateParameters.Item("pTaskId").DefaultValue = hCurrentTaskId.ValuesqlTaskSelections.UpdateParameters.Item("pTaskRating").DefaultValue = cbCurrentRating.SelectedValue
sqlTaskSelections.Update()

Next

Response.Redirect("nextpage.aspx")
End Sub
 

View 3 Replies View Related

Need Help Trying To Work With Forms Control To Update, Insert, And Etc...

Aug 14, 2007

Hello,
I am trying to use the forms view control to do a simple web app. My issue is, I cant get the connection to automatically generate insert, update and delete statements. I tried to do this manually but I always get an error. I have read that I need all the keys selected--this still didnt work.  I have about 6 tables picked in my view, and if I pick one of them then this advanced feature works. I have the primary keys selected.
 here is my saved view. this is in SQL 2000.
SELECT     TOP 100 PERCENT dbo.ADDRESS.EMAIL, dbo.ADDRESS.FIRST_NAME AS [first name], dbo.ADDRESS.LAST_NAME AS [last name],                       dbo.ADDRESS.STATE, dbo.ADDRESS.TEL1 AS phone, dbo.INVOICES.QUOTE_NO AS [SALES QUOTE], dbo.CUST.NAME AS Company,                       dbo.ITEMS.ITEMNO AS [Course Number], dbo.ITEMS.DESCRIPT AS S_Descript, dbo.ADDRESS.JOB_TITLE AS [Job Title],                       dbo.TRAINING_SCHEDULE.[MONTH], dbo.TRAINING_SCHEDULE.S_DATE, dbo.TRAINING_SCHEDULE.END_DATE,                       dbo.TRAINING_SCHEDULE.IS_CONFIRMED, dbo.TRAINING_SCHEDULE.IS_PAID, dbo.TRAINING_SCHEDULE.CUST_CODE AS Account,                       dbo.TRAINING_SCHEDULE.SCHEDULE_ID, dbo.CUST.CUST_CODE, dbo.INVOICES.INVOICES_ID, dbo.X_INVOIC.X_INVOICE_ID,                       dbo.ADDRESS.ADDR_CODEFROM         dbo.TRAINING_SCHEDULE INNER JOIN                      dbo.CUST ON dbo.TRAINING_SCHEDULE.CUST_CODE = dbo.CUST.CUST_CODE RIGHT OUTER JOIN                      dbo.X_INVOIC RIGHT OUTER JOIN                      dbo.INVOICES ON dbo.X_INVOIC.ORDER_NO = dbo.INVOICES.DOC_NO LEFT OUTER JOIN                      dbo.ADDRESS ON dbo.INVOICES.CUST_CODE = dbo.ADDRESS.CUST_CODE LEFT OUTER JOIN                      dbo.ITEMS ON dbo.ITEMS.ITEMNO = dbo.X_INVOIC.ITEM_CODE ON dbo.CUST.CUST_CODE = dbo.ADDRESS.CUST_CODEWHERE     (dbo.X_INVOIC.ITEM_CODE LIKE 'FOT-%') AND (dbo.X_INVOIC.STATUS = 7) AND (dbo.ADDRESS.TYPE IN (4, 5, 6)) AND (dbo.ADDRESS.EMAIL <> '') AND                       (dbo.ADDRESS.COUNTRY = 'UNITED STATES') AND (dbo.ITEMS.CATEGORY = 'TRAININGCLASSES') AND                       (dbo.TRAINING_SCHEDULE.CUST_CODE = 'joe')ORDER BY dbo.ADDRESS.STATE
I am new to this so I am not sure what to include.
thanks,
yellier

View 1 Replies View Related

Trying To Work Out Why My Code Wont Update Or Write To The DB

Jan 7, 2008

hi there, i have been wrestling with this for quite a while, as in my other post http://forums.asp.net/t/1194975.aspx, what someone advised me was to put in try catch blocks ot see whats going on, problem is i have never really done it whit this kinda thing before, and i was wondering if someone could point me in the right direction.
 
For example where would i put the try catch block in here, to show me if its not working public int getLocationID(int ProductID, int StockLoc)
{
// Gets the LocationID (Shelf ID?) for the stock column and product id
// passed
// The SQL will look Something like: string strSQL;
strSQL = "SELECT " + " location" + StockLoc + " " + "FROM " + " tbl_stock_part_multi_location " + "WHERE " + " stock_id = " + ProductID;string sConnectionString = "Data Source=xxxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxxx";
SqlConnection objConnGetLocationID = new SqlConnection(sConnectionString);SqlCommand sqlCmdGetLocationID = new SqlCommand(strSQL, objConnGetLocationID);
objConnGetLocationID.Open();int intLocation = Convert.ToInt32(sqlCmdGetLocationID.ExecuteScalar());
return intLocation;
}

View 6 Replies View Related

Update NULL Values Doesn't Work

Oct 2, 2007

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...

What's happen? Have I to call an exorcist???

Thanks in advance for any help!
Nicola

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved