Additional INSERT, UPDATE, and DELETE Statements Disabled.

Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.
I have been looking for an answer to my problem, but unfortunately I couldn't find one. So I created a user here on www.asp.net just for making this post.

Before I continue I just want to apologies if there is another post where this question is already answered.

Please watch this Print Screen I just took: "http://www.bewarmaronsi.com/Capture.JPG "

As you can see the "INSERT, UPDATE, and DELETE Statements" are disabled, and that's exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.

The MDF file is located in the App_data folder and is called ASPNETDB.

And when I try to add custom SQL statements, it gives me Syntax error near "=". Something like that. I bought the Total Training Set1 package and it works perfect in their examples.

I just want to thank you for reading my post and I hope that you got some useful information for me.

By the way, I', from Sweden so you have to excuse me if my English is rusty.

Thanks!

PS: Can it be that I'm running windows Vista?

Try adding a primary key column to the the table and check.

If any issues, let me know.

Sri

|||

Thank you for your postsridhar.av, I don't exactly know what you mean, but I think this might be it… please look at this new print screen:

"

http://www.bewarmaronsi.com/Capture2.JPG

"

And if that's the case, it still doesnt work.

Thanks

|||Right click on the Column -->Set Primary key|||

Thank you so muchsridhar.a, it works perfect now!

I'm a designer so if you need any help in that area, dont hesitate and email me anytime you want.
From Graphics to 3D modeling and Animation.

Thanks!

Adding, Deleting rows from Visual Basic 2005 Express Edition

Hi,

I'm a complete novice concerning SQL Server (Express Edition)

I'm trying to Add or Delete rows froma VB 2005 Express Function or Sub. While the program is running everything is ok. Except when restarted added records are gone and deleted records are back.

Have i missed an option during installation?

Thx,

Steven

Your installation of SQL Server 2005 Express may be operating in 'Snapshot Isolation' mode. Refer to Books Online for more details.

You may also find this series of instructional videos to be useful.

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

|||

Make sure that you did not specify the datafile for "Always copy", if you did this, the file will always be copied from scratch upon new start of the Visual Studio debug session.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1611696&SiteID=1

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Arnie,

Thx for your trouble, but i got the 'Always Copy' option wrong!

Greetings,

Steven

|||

Hi Jens,

Your answer was the correct one.

Thx,

Steven

Adding Delete to Stored Procedures

I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out

REATE PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
IF @.@.Error <> '0'
RETURN

GOI dont believe that a simple answer like
DELETE FROM EmployeeGamingLicense WHERE STATUS = 'TERMINATED'
is what you are looking at. Can u please give us some more details on what is expected?|||From the way your question is phrased, it sounds like you want to insert from tableA to tableB then if that was successful delete from tableA. If so:


declare @.err int
BEGIN TRAN
INSERT INTO TableB (col1, col2, col3, etc)
SELECT col1, col2, col3, etc)
FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

DELETE FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

COMMIT|||I am trying to delete the data from the EmployeeGamingLicense table after its been inserted into the Termination table

ALTER PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
DELETE FROM EmployeeGamingLicense o
WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
IF @.@.Error <> '0'

RETURN

GO|||inserted is a virtual table only accessable via a trigger, which does not fit with your ALTER proc.

Why do a write just to have to do a select with the "If Exists". Don't make it more complicated than necessary.

This is one of the prime examples of a transaction ... a unit of work that must succeed or fail as a unit. If the insert works and then the server crashes, you have a duplicate entry in two tables. The BEGIN TRAN ... COMMIT block ensures consistency between the two tables ... it either exists in one or the other, thanks to the COMMIT or ROLLBACK.

Adding delete statement in a dts package script to delete records

I am using a dts package to move tables from one server to another and will
like to know how I can add a delete statement that can delete records more
than a year old from a particular table in the source database before the dts
package moves the the tables to the destination database. Any help will be
appreciated
Bothe servers are running sql server 2000.Hi
You can add an Execute SQL Task and add workflow so that it completes before
the transformation. You may want to check out www.sqldts.com for more
information on using DTS or check out the content in Books Online
John
"Aboki" wrote:
> I am using a dts package to move tables from one server to another and will
> like to know how I can add a delete statement that can delete records more
> than a year old from a particular table in the source database before the dts
> package moves the the tables to the destination database. Any help will be
> appreciated
> Bothe servers are running sql server 2000.

Adding delete statement in a dts package script to delete records

I am using a dts package to move tables from one server to another and will
like to know how I can add a delete statement that can delete records more
than a year old from a particular table in the source database before the dt
s
package moves the the tables to the destination database. Any help will be
appreciated
Bothe servers are running sql server 2000.Hi
You can add an Execute SQL Task and add workflow so that it completes before
the transformation. You may want to check out www.sqldts.com for more
information on using DTS or check out the content in Books Online
John
"Aboki" wrote:

> I am using a dts package to move tables from one server to another and wil
l
> like to know how I can add a delete statement that can delete records more
> than a year old from a particular table in the source database before the
dts
> package moves the the tables to the destination database. Any help will be
> appreciated
> Bothe servers are running sql server 2000.

Adding and Updating in same query

Hi all,
I am trying to copy records from table 1 where field 1 is equal to "N" to
table 2. I also want to change field1 to "Y" after the copy or delete the
record, so that the record will not get copied again.
Can this be done?
Thank you,
George
One thing I need to mention: I am trying to accomplish this in the
Transformation
Thanks again.
George
|||> One thing I need to mention: I am trying to accomplish this in the
> Transformation
What is "the Transformation"?
|||Of course you can handle this in multiple ways, e.g. one way would be to use
Y on insert
INSERT INTO table2(field1, pk) SELECT 'Y', pk
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.pk = t2.pk
WHERE t2.pk IS NULL
Or not have a "field1" at all. Assuming you can identify any row in either
table uniquely, and properly identify a duplicate, you can easily use a
similar LEFT JOIN without needing a flag.
Or not have two tables at all. What are you trying to accomplish with two
tables that you can't accomplish with one?
If this isn't helpful, then instead of an ambiguous word problem, please
post DDL, sample data and desired results. See http://www.aspfaq.com/5006
for info.
"George" <George@.discussions.microsoft.com> wrote in message
news:CB84E118-4A8C-43BD-9A22-6C58E98D5461@.microsoft.com...
> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George
|||Hi George,
INSERT INTO TABLE2 SELECT <FIELDS> FROM TABLE1 WHERE FIELD1='N'
GO
UPDATE TABLE1 SET FIELD1='Y'
GO
Hope this will solve the problem
thanks and regards
Chandra
"George" wrote:

> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George

Adding and Updating in same query

Hi all,
I am trying to copy records from table 1 where field 1 is equal to "N" to
table 2. I also want to change field1 to "Y" after the copy or delete the
record, so that the record will not get copied again.
Can this be done?
Thank you,
GeorgeOne thing I need to mention: I am trying to accomplish this in the
Transformation
Thanks again.
George|||> One thing I need to mention: I am trying to accomplish this in the
> Transformation
What is "the Transformation"?|||Of course you can handle this in multiple ways, e.g. one way would be to use
Y on insert
INSERT INTO table2(field1, pk) SELECT 'Y', pk
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.pk = t2.pk
WHERE t2.pk IS NULL
Or not have a "field1" at all. Assuming you can identify any row in either
table uniquely, and properly identify a duplicate, you can easily use a
similar LEFT JOIN without needing a flag.
Or not have two tables at all. What are you trying to accomplish with two
tables that you can't accomplish with one?
If this isn't helpful, then instead of an ambiguous word problem, please
post DDL, sample data and desired results. See http://www.aspfaq.com/5006
for info.
"George" <George@.discussions.microsoft.com> wrote in message
news:CB84E118-4A8C-43BD-9A22-6C58E98D5461@.microsoft.com...
> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George|||Hi George,
INSERT INTO TABLE2 SELECT <FIELDS> FROM TABLE1 WHERE FIELD1='N'
GO
UPDATE TABLE1 SET FIELD1='Y'
GO
Hope this will solve the problem
thanks and regards
Chandra
"George" wrote:

> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George