Need Advice On Updates
Mar 15, 2008
Hi, I just started learning ASP.NET this week and have watched a mountain of videos from this website which has helped me alot
However I have been stuck on a problem for 2 days now.
I have created an SQL database with the Following 2 tables:
USERS COMPUTERS
Userid Computerid
firstname Userid
secondname Manufacturer
Model
I have made a relationship between the 2 tables.
I then created a dataset with the following query:
SELECT COMPUTERS.Computer_ID, COMPUTERS.Manufacturer, COMPUTERS.Model, USERS.First_Name, USERS.Last_Name FROM COMPUTERS INNER JOIN USERS ON COMPUTERS.User_ID = USERS.User_ID
I however do not get the option in my grid view when i output this data, to UPDATE.
The best i have found from google is that i need to use subqueries and not innerjoins but i just cant seem to get my head around them, please help as i feel my head may just explode if i think about this or try any more ways to get this to work :D
View 5 Replies
ADVERTISEMENT
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
Dec 19, 2007
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?
View 4 Replies
View Related
Oct 30, 2007
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...
Regards
Karen
View 5 Replies
View Related
Feb 5, 2004
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?
View 4 Replies
View Related
Jun 19, 2007
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...
View 3 Replies
View Related
Sep 20, 2014
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));
View 3 Replies
View Related
May 10, 2007
okay, so i have about 37 different updates i need to do to a table that is rather large (71million) and has no indexes. i know it's gunna table scan, and honestly, i'm not really worried about that. my question is, is there a way up squeeze all of these updates into one?
here is what i was going to do, but each one will take about an hour to run... (here are 5 of the 37 updates, but they are all basically the same concept)
update t1 set books_music='' from mailorder t1 where books_music is null
update t1 set Car_Buff='' from mailorder t1 where Car_Buff is null
update t1 set Childrens_Items_Buyers='' from mailorder t1 where Childrens_Items_Buyers is null
update t1 set Computer='' from mailorder t1 where Computer is null
update t1 set Crafts_Sewing='' from mailorder t1 where Crafts_Sewing is null
in FoxSlow (foxpro) i could just do something like this:
do while !eof()
replace books_music with '' for books_music=null
replace car_buff with '' for car_buff=null
skip
enddo
View 9 Replies
View Related
Feb 17, 2008
I have an SQL 2005 STD server, full install, that we use to run SQL and reporting services. When I run Microsoft Update, it shows updates for Visual Studio 2005 SP1 and MS Office 2003 SP3. The server does have MS Office 2003 web components installed, installed as part of the initial SQL server install, but not the MS Office suite software. It also has loaded the reporting services version of Visual studio 2005 that installs with reporting services, but not the entire version of Visual Studio 2005. Should I install these service pack updates? Is there any benifit?
View 3 Replies
View Related
Sep 21, 2007
Hello
Which is better and faster?? and WHY????Writing Select Statement with joins in Stored procedure, or creating view and calling it from stored procedure (select * from view)..
View 4 Replies
View Related
Jan 13, 2004
Hi
Dont laugh but...
I am not entirely ignorant to web technologies, and best practices but i am having a bit of a planning dylema.
My company has a well established SQL 2000 database with windows application which has been created by myself, what i am planning on doing is creating a web site, using asp.net and publishing some of the information, so that our clients may use it, and stop pestering us on the phone. what i would like to know is what would be the best way forward, obviously i don't want to show them all our information, and don't want to put 5Gb worth of data onto a ISP website. What would you suggest i do?
Thanks in advance
Brad
View 1 Replies
View Related
Jun 8, 2004
DECLARE @returnDay int
SELECT @returnDay = DatePart(day,GetDate())
If @returnDay = 8
BEGIN
select * from Hospitals left join Units ON Units.HospitalID = Hospitals.HospitalID where Units.HospitalID is null
RETURN
END
this is just a part of the procedure I am trying to create, I am getting hospitals that haven't submitted any data and wish to send them an email.
on the other hand I have two tables that have all the data for emailing to hospitals but are not linked to tables giving the list of hospitals
I have been advised to create a cursor(easier said then done) that will go through my list of records that need to receive an email
nothing going very well with that at the moment.
so I was hoping to see if somebody has any other suggestions for me.....
View 1 Replies
View Related
Apr 19, 2004
Hi,
I am trying to solve this procedure.
Let me try to explain it...I am getting DEGREEID from one of the SELECT query . I want to OUTPUT (ie , COUNT) from procedure,the number of departments with the degreeid, got from the above query.
With below procedure, Since an employee can have multiple DEGREEID , the cursor is giving OUPTUT ie, COUNT for the LAST Degreeid. Eventhough the previous DEGREEID dont have any DEPARTMENT...but only for the LAST DEGREEID...!
How can I solve this..... whether I can solve this with CURSOR or I have to use someother way...Please advice me !
DATA
-------
DEGREE_EARNED
---------------------
EMPID DEGREEID
------ ------------
201 12
201 3
201 250
202 3
202 10
203 17
DEPARTMENT
---------------
DEPID DEGREEID
------ ------------
10 1 12
111 250
111 12
121 3
121 12
121 250
------------------------------------
--------------------------------------------------------------------
DECLARE @vchid int
DECLARE testcursor CURSOR FOR
SELECT degree_id
FROM degree_earned WHERE emp_id= @empid
OPEN testcursor
FETCH NEXT FROM testcursor INTO @vchid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
Select @outresult = COUNT(*)
from
department
where degree_id = @vchid
FETCH NEXT FROM testcursor INTO @vchid
END
--------------------------------------------------------------------
View 4 Replies
View Related
Mar 15, 2006
I tried to run a SQL script and i get the following message
quote:Error Message = Native SQL Error Code
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ''.
SELECT name, id, description FROM products WHERE id=4INSERT INTO ''admin_login'' (''login_id'', ''login_name'', ''password'', ''details'') VALUES (252,''neo2'',''newpas5'',''NA'')--
Error Code : 350
F:InetpubParexeldefault.ihtml
iSQL dbname="Parexel" ALIAS="prod" SQL="SELECT name, id, description FROM products WHERE id=4INSERT INTO 'admin_login' ('login_id', 'login_name', 'password', 'details') VALUES (252,'neo2','newpas5','NA')--"
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ''.SELECT name, id, description FROM products WHERE id=4INSERT INTO ''admin_login'' (''login_id'', ''login_name'', ''password'', ''details'') VALUES (252,''neo2'',''newpas5'',''NA'')--
Im new to SQL and i dont know what im doing
Also what does this mean
quote:iSQL dbname="admin" ALIAS="prod" SQL="SELECT name, id, description
What do SQL databases do and what would i need to do to enter data in to SQL such as a email address or cant that be done?
I think dbname is the database name ALIAS is that the table name?
im just trying to enter data in to my database database and i have no clue what im doing.
Iv read guides online but i get the above error 350
D
View 6 Replies
View Related
Jul 18, 2006
Hi all,
My first request for help here even if I read this site quite often and got tons of usueful information. Thanx all
I have an application (VB 6) calling store procedure on a SQLServer 2000 DB. One of the table gives me headache. I wrote a simple store procedure to insert a record into that table. If I call the store procedure from query manager it works perfect but if I call it from VB it looks like to work (return from the execute fine) but then I query the table for that record and it'll just take time and then return time out. I have to stop the VB application and then query it again then it'll return no record.
I suspect the table being locked somehow but I check inside the VB app code and that's the only place the table is called. Further more I have hundred of store procedures being used that way and they're all ok. I indexed the table , no use either...
I am just short of ideas how to debug this...I'll need your advice :)
thank you
View 9 Replies
View Related
Feb 6, 2006
Okay, given my newness to SQL, and the complexity of this query, I thought I'drun this by you for your opinion:SELECT DISTINCT a.WeekEnding, c.lastdate, c.numlate, b.totaldateFROM Accomplishment a LEFT OUTER JOIN(SELECT weekending, COUNT(weekending) AStotaldateFROM AccomplishmentWHERE (EmployeeID = 50)GROUP BY weekending) b ON a.WeekEnding =b.weekending LEFT OUTER JOIN(SELECT weekending, MAX(entrydate) ASlastdate, COUNT(weekending) AS numlateFROM accomplishmentWHERE employeeid = 50 AND entrydate >weekendingGROUP BY weekending) c ON a.WeekEnding =c.weekendingORDER BY a.WeekEndingWhat I'm trying to do is for each pay period find which ones the employeesubmitted a timesheet and which they were late (and if they were late, howmany of them). However, the query takes a good 5 seconds, and it seemsremoving the "entrydate > weekending" clause speeds things up to almostinstant, however it does ruin the count that I really want. No idea whythat makes such a difference..
View 4 Replies
View Related
Oct 28, 2006
hellooo
I have an Accounting system(vb.net 2003, SQL server 2000), every new year data is cleared, but i may use some data from previous years (such as liabilities)??
whats the best way to that ???
-Shall I create programmatically a new clone DB every year (new DB with same structure as previous year)
OR
-Shall I add a "year field" for tables in DB????
knowing that data will keep growing every year??????
whats the best solution, knowing that i dont want the end user of my application to do anything manually, such as creating DB ......
Thank you
View 14 Replies
View Related
Jul 11, 2007
I have a couple of files that I have that are comma seperated, and am looking for the best way to take those files, but them in a temp location to run some sql up against, and than update or insert a production sql database based on a SP i have written that takes a number of variable. I have played around with using the recordset destination and defining all the variables and than using a for each loop but seem to be stuck. Somewhat new to the whole programming field, and to be honest, seems a little intimidating with the little I know of the programming side. Just looking for some thoughts.
View 1 Replies
View Related
Aug 3, 2007
Hello,
I am new to the CE OS, SQL CE and mobile computing in general. I have been developing database applications using MS products ever since Windows 3.11 and Visual Basic 1.0. I have used MS Access and SQL Server since they were first released. But, I have never worked with Windows CE or any of the mobile OSs or with SQL CE or SQL Mobile.
We now have a project that requires us to develop a database application on handheld devices using the CE 4.2 and CE 5.0 OSs.We will be using CF 2.0, VS 2005 and SQL 2005 for our development environment.
My questions are:
1. Which version of mobile SQL will allow us to best develop for both the CE 4.2 and 5.0 OSs usinf VS 2005? I have done a lot of reading online and it's pretty confusing. There are quite a number of different versions out there. It seems some work with 4.2 and some with 5.0. Is there a version that will work for both?
2. Is SQL CE 3.0 (Sql 2005 Mobile) available for other than Laptop and Tablet use? When i finaly got to the download page for this version there was verbiage there that suggested it was not available for smart devices.
3. What is a good source of info to resolve these questions? I am using the MSDN areas for CE and SQL Mobile but haven't really found what I need to get started. Any suggestions on forums, books, articles, blogs, etc... would be greatly appreciated.
I know these are very broad questions but I want to get some advice from the experienced before going to far here.
View 10 Replies
View Related
Jun 1, 2007
I just want to ask for any good advice here ragarding my project. I'm planning to create a program that will use an sql server as a database. I have 3 branches and I need the branches to be updated always when they create any transaction or changes. The sql server will be installed in a PC with Windows Server 2003 and with a public IP. the question is can I access the sql server from other branches by using the public IP of the ServerPC thru the Internet? If yes, how? if no, is there a best way to do this updating method?
Thanks!
View 5 Replies
View Related
Mar 6, 2008
Hi,
I am totally new to SQL and need to ask some basic questions.
Can anybody tell me if SQL 2005 will run ok on Windows Server 2008 32bit and/or 64bit.
Also, we are currently planning to implement OCS 2007 and we are considering running it on Windows Server 2008 32bit with SQL 2005. Does anyone know if this combination is possibleand if not what implementation should we be looking at.
Thanks for your time,
Cheers,
Ian.
View 3 Replies
View Related
Aug 9, 2007
Hi everyone,
i wonder if any one can give me some advice.
here is my situation, i have a central database server that contains all the data, running in an intranet, the client application, which is a thick client containing most of the biz logics is installed on a laptop,
i m required to develop a solution so that when the client application is disconnect to the database server (e.g. the laptop is taken away somewhere) the application can still work and when the client application gets connect to the database server again the application will be able to synchronize changes with the database and update all the changes. and i m not allow to develop a web-base application
my initial thinking is to use merge replication, however, i m conerning with the performance on the laptop if i have to install sql server on the laptop.
is there any other approach that can still achieve the requirement and have a better performance?
thanks a lot in advance.
View 1 Replies
View Related
Oct 29, 2007
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 "
Cheers!
View 4 Replies
View Related
Dec 16, 2003
I am using MSDE Release A was wondering what is the best way to find out when there are critical updates for MSDE. Is there a notification service for MSDE like there is for Windows?
View 1 Replies
View Related
Jul 11, 2000
I'm pretty new to T-SQL and have an *easy* problem, for you experts, that I can't get seem to get solved. I'd like to loop through a list of items in TABLE "Items". I then want to use that list to loop through and SUM SALES and QTY for each item from a TABLE called "Shipments". As I loop through each item, I want to UPDATE the "Items" table with the Summary data. So, logically I'd do something like this:
SELECT item_no
FROM Items
BEGIN
SELECT SUM(sales) AS Total_Sales, SUM(qty) AS Total_Qty
WHERE item_no=@item_no
UPDATE Items
SET Sales=@Total_Sales,
Qty=@Total_Qty
WHERE item_no=@item_no
END
I've tried somewhat successfully to use cursors to create my loop query, but I cannot seem to get the SELECT and UPDATE correct in the loop itself. Can anyone steer me in the right direction (or better yet, provide a solution)?
Thanks for the help,
Chris
View 2 Replies
View Related
Jan 22, 2001
I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent
View 2 Replies
View Related
Sep 29, 2000
Hi
I am using SQL Server 6.5 as my backend for a VB6 Application.
This is a OLTP kind of package that I have developed with about 600 Users.
I use BeginTran and CommitTran between my Update/Insert Queries in my VB Application.
My database has grown considerably and all of a sudden, all
the CommitTran failed. None of the records were saved.
When I stopped and restarted my SQL Server, the operation became
normal all Update/Insert queries for the same Application succeeded.
(Note that I have not stopped my Server for the last 45 days).
WHY DOES THIS HAPPEN?
HOW DO I AVOID THIS IN FUTURE...
PLEASE HELP
Thanks
Jivee
View 1 Replies
View Related
Apr 5, 1999
Is there an easy way to update a Value field
for all records in One Table from a Value Field in another?
Both Tables have the same number of records.
View 3 Replies
View Related
Nov 6, 2006
How would I check a db to see if a record exists and if it does then do an update but if it doesn't do an insert. so: Set oRS = objDB.Execute("select loginid from computers where loginid='" & WshNetwork.Computername & WshNetwork.UserName & "'")
If that returns something then do an update but if not then do the insert.
View 2 Replies
View Related
Jun 18, 2004
Hi all,
I recently inherited multiple databases for a research study. These databases use an Access front end with the tables stored on SQL Server. Currently, there is a folder for each database on a network drive. I make changes to the front ends (forms, reports etc.) in a development version of the dbs, test them, have a user test them, and then import them to the production front end.
Each user has a copy of the front end on their 'C:' drive. The previous developer put together a separate VB app that copies the changed mdb files from the network drive to their 'C:' drive. This doesn't seem like the best solution to me but I haven't come up with a better one. I would appreciate any input.
Thanks,
Monk
View 1 Replies
View Related
Jun 23, 2004
I have two sql 2000 server tables one is active and one is terminated (I inherited this db) and I was thinking of having the active table automatically update the terminated table when an employee is terminated. Access is at the front end and in the form of the active table theres a drop down text box that has the employement status(active, terminated, on leave...etc) so when the status turns into terminated i want the acitve table to send those records to the terminated table, ( the data in both tables are not exactly the same). looking into a trigger or stored procedure. This is the first time I've done this so I'm doing some reasearch on how to handle it. Any suggestions
View 14 Replies
View Related
Jun 25, 2004
well as I get further into this project of automatic updates I'm fining more and more barriers. The combo list box which indicates whether the employee is terminated or active might be a problem with sql since you cant create a Row source and a Row source type in a sql table. that combo box exsist in the properties of the form. The Row Source Type is a Value List. Shoot :(
View 4 Replies
View Related
May 25, 2004
Hello,
I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?
Thanks you
Jeff
View 2 Replies
View Related
