Insert And Update A Table In Single Web Page
Jul 27, 2006
A problem has come up in designing a Web page to maintain a small reference table in SQL Server 2000 (9 columns, about 25 records).
I tried to design the Web page to allow INSERT and UPDATE operations using a single page that posts back to itself. The page contains a set of empty form fields for a new record, followed by a set of filled-in form field for each row in the table. The form fields for existing records are given a unique name based on the field name concatenated with the primary key value for that row.
If I set up the page to INSERT only, it works properly. But when I add the fields for existing records, the INSERT operation malfunctions. Specifically, anytime a set of existing fields for a particular column is added to the page, the INSERT will no longer work properly for that column. This is true for all fields except the primary key field. It always INSERTs correctly. I tried adding only some columns to the set of existing form fields. In that case, the INSERT operation added the correct values for the fields that were not listed in the existing records section, but failed for the others.
I am using the INSERT INTO syntax for that operation and the recordset .Update syntax for the edits. I tried using the recordset .AddNew/.Update syntax for the insert, but it exhibited the same problems. The column data types contain smallint, bit, nvarchar, and ntext types.
I know that the correct values are being put into the INSERT statement. I also tried renaming the INSERT form fields to be totally different than the names of the existing record fields. But the problem comes back no matter what.
If necessary, I can split the logic so that inserts and updates are handled by different pages. But I would like to make this work if possible. If a reader knows why SQL Server is causing this problem, any help would be greatly appreciated.
View 5 Replies
ADVERTISEMENT
Feb 14, 2008
Fit an intere table in same page without page break for save the excel export.
My table has a Group for order my dates.
I need to have the intere table in the same page, i don't care about blank space at the end of the page.
I can't use the page break beacuse i need an excel export in a unique sheet..
I have tested.. every page break..you'll have a different sheet in your excel export
I need something like this
page 1
Zone
1
2
3
4
5
6
7
7
8
9
..
page 2
Zone
1
2
3
4
5
6
7
7
8
9
..
but an unique sheet in the excel export
View 4 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Jul 23, 2005
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
View 4 Replies
View Related
Apr 4, 2014
I Want to write a single SP which inserts and updates.
CREATE PROCEDURE sp_NewPassword
@UserName nvarchar(50),
@OldPassword nvarchar(50),
@NewPassword nvarchar(50)
AS
BEGIN
INSERT INTO RGH_ChangePassword(vUserName, vNewPassword) VALUES(@UserName, @NewPassword)
UPDATE RGH_LoginMaster SET vPassword=@NewPassword WHERE vUserName=@vUserName and vPassword=@OldPassword
END
When i tried to create above sp it gives me an error saying
Msg 137, Level 15, State 2, Procedure sp_NewPassword, Line 8
Must declare the scalar variable "@vUserName".
View 1 Replies
View Related
May 8, 2008
In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net?
My old VB6 code
Dim dbData As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim strParm As String
Dim strCusNo As String
'
strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password"
dbData.Open strParm
'
strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'"
rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText
If rs1.BOF And rs1.EOF Then
rs1.AddNew
Else
End If
With rs1
!CusNo = strCusNo
.Update
End With
rs1.Close
'
Set rs1 = Nothing
dbData.Close
Set dbData = Nothing
Is there an ADO.Net equivalent?
thanks,
View 3 Replies
View Related
Mar 18, 2005
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
View 2 Replies
View Related
Jul 17, 2015
I have Table Staffsubjects with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this, But the problem is If do the update, It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
[Code] .....
View 10 Replies
View Related
Apr 11, 2008
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
View 3 Replies
View Related
Jan 15, 2008
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior.
For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this?
Thank you
View 10 Replies
View Related
Sep 25, 2002
I would like to update/insert data into a Orderhearder Table along with the related details into the corrosponding OrderDetails Tables. Can this be done using a single stored procedure or do we have to make one call to the UpdateOrderHeader Stored Procedure and loop thru all the details and call the UpdateOrderDetails Stored Procedure. How do we handle the Transactions in such a case ?
Thanks
Anurag Agarwal
View 1 Replies
View Related
Jul 17, 2015
I have Table Staffsubjects
with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this. But the problem is If do the update. It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
[Code] ....
View 8 Replies
View Related
May 16, 2008
Hi All,
In my SSRS report. I have a report which has only one page. In preview it is showing as only 1 page but when I am printing the report. I am getting two printouts with the second page as a blank.. Please help me in printing the page that contains report. Intially I used a Page header, at that it used to print the blank page with a header only. Now as I removed the header it is printing the page without header i.e Blank Page.. So please help me in prinitng a single page that has the report. It is urgent,..
Thanks
dotnetdev1
View 5 Replies
View Related
Feb 9, 2012
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
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.
Do I need to use a cursor?
View 5 Replies
View Related
Jan 18, 2008
In my web page(.Net/c#) , I want to update few fields of a database table. The code is fine. The stored procedure through which I perform the execution is fine. I'm confidently saying this is because, if I try to do the update direclty while executing the stored procedure, it gets updated. I'm facing a weird problem while trying to update the fields from my web page. When I make an update and submit it, the first time it shows the updated value in the screen. But if I try to refresh it or redisplay the data ( I guess whenever it makes a trip to the database), the old values gets populated instead of the new values. I tried debugging it, but everything seems to be working fine. I looked up the execution in SQL Profiler, everything seems to be fine. I've no clue what's going on. I've proper permissions to make an update. So there is no permission issue. Could someone tell me how to handle this issue and what might be the possible problem which makes the values not update in the database?
View 8 Replies
View Related
Oct 16, 2015
I'm trying to do the following:
update a tables value(a single column), based on a query of another database, using a common column.
Here's what I've cooked up so far:
Declare @trackingNo nvarchar (50)
Set @trackingNo =
(
select tracking_no from P21_Shipping.dbo.shipping_data t1
inner join P21.dbo.oe_hdr t2 on t1.order_no = t2.order_no
[Code] ...
print @trackingNoThe error it's returning is:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So, I'm wanting to query Shipping_data for order_No that matches the same orderNo column value in P21 Database(oe_hdr table), then update P21 oe_hdr table with "trackingNo from Shipping_data
View 8 Replies
View Related
Apr 20, 2008
need help on update from one table to another like this
this is my first table
tb_all_holiday
id
fname
Start_Date
End_Date
val_holiday
111
aaaa
15/03/2008
21/03/2008
1
222
bbbb
02/05/2008
09/05/2008
3
333
cccc
03/04/2008
15/05/2008
4
333
cccc
29/04/2008
07/07/2008
1
444
dddd
01/05/2008
02/05/2008
1
444
dddd
09/05/2008
19/08/2008
1
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
this is my second table to insert into !
i need to insert to another table like this
single row for each day from start_date TO END_DATE
check each employee add row for each day
insert all employee one after one
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
......................................................add row for each day
...............................
333 ccc 15/05/2008 4
TNX for help
View 6 Replies
View Related
Sep 25, 2014
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052
Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
View 3 Replies
View Related
Apr 20, 2008
question need help
how can i use this code below not for insert
i need it for update another table but only if exist
the link to the code in this FORUM
http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True
Adam Haines wrote:
GPS,
Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic.
Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.
Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt.
Now the code to get the results you need:
Code Snippet
declare @t table(
id int,
fname char(4),
Start_Date datetime,
End_Date datetime,
val_holiday int
)
insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 )
insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3)
insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4)
insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 )
select id, fname, cal.ISODate, val_holiday
from @t t1
inner join Calendar cal
on cal.isodate >= t1.start_date and
cal.ISODate <= t1.end_date
is possible to do it
TNX
View 4 Replies
View Related
Feb 15, 2012
I have a table JOBCODE which contains a list of codes.
I want to insert these values into table VIEWS as a list separated by spaces.
E.G.
Table Jobcodes looks like this
code
1
2
3
4
5
6
And I want table Views to look like this:
field1
1 2 3 4 5 6
How do I go about this?
View 4 Replies
View Related
Sep 24, 2015
I am have a situation to insert data from multiple sources to a single table.
i.e., multiple and concurrent insert on same table
Will it lead to dead lock at any point? is there any possibility?
How insert will work ? What is the architecture ? Any references to read?
View 1 Replies
View Related
Oct 25, 2007
I am using SQL 2000
I am getting a syntax error when I parse this sql script:
insert into Elec_Sub_Test1
values ('10-20-2007',35),
('10-21-2007',24)
What is the correct syntax to insert mutlipe rows in a single table.
View 4 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Jul 16, 2015
We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.
Below is the Table Structure.
Table Name IN_USE_FG
CUST_D 0
PROD_D 0
GEO_D 0
DATE_D 0
Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.
Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.
View 6 Replies
View Related
Oct 11, 2013
I am trying to build a DIM table using a source table that has the following setup...
CREATE TABLE [dbo].[APPL_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[APPL_STATUS_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]....
What I am trying to do is to break out the APPL_STATUS_DATE into a STATUS_START_DATE and an STATUS_END_DATE in a new table. I also need to be able to update the STATUS_END_DATE based on the previous day's date. Like so...
CREATE TABLE [dbo].[APPL_APPLICANT_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[STATUS_START_DATE] [datetime] NULL,
[STATUS_END_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]...
View 10 Replies
View Related
Mar 2, 2006
hello all
pls tell me
how can i insert, retrieve date from ASPX(vs2003) page in table in sql server 2000??
i m in trouble
pls help me
View 1 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Jan 30, 2014
I am trying to insert values in a single table with four columns from 4 different sources. is it possible to run these 4 insertions in parallel. all these insertion are independent of each other
View 3 Replies
View Related
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
May 6, 2015
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
View 7 Replies
View Related
Jan 31, 2008
I'm importing XML file into DataTable and need to Insert Data into SQL Table.
I'm not sure if its posible to take a DataTable with Data and insert into DataAdapter. From there i wanted to update SQL using TableAdapter?
Any Tips?
Thanks,
View 4 Replies
View Related
Jun 8, 2005
This should be easy for someone, but I just can't seem to find a sample to do this.....I have created a table...CREATE TABLE dbo.test ( oId int NOT NULL UNIQUE, test1 varchar(50) NOT NULL PRIMARY KEY )Now, I need to go back and simply add another column to the table such as test2 varchar(50)Not sure if the insert is the way to go and been playing around with various statements but with no luck.Suggestions?Thanks all,Zath
View 2 Replies
View Related
Mar 11, 2004
I am new to triggers and need help on the following:
I have a hourly table that inserts new rows every hour but I need to either Insert or Update the daily table with the sum of the reading from the hourly table. If a row exist in the daily table with the date of the hourly table, then I need to update this row but if it doesn't exist, I need to insert this row.
Thanks for any suggestions...
Alan
View 4 Replies
View Related