Showing posts with label record. Show all posts
Showing posts with label record. Show all posts

Friday, March 30, 2012

ID for New record

Hi,
How can I insert a new record in sql table and retrun the ID of that reocrd using a stored procedure?
Thanks,From Books Online:

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.@.IDENTITY AS 'Identity'

In stored proc:

create procedure sp_Test

@.outputvalue int output
as

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.outputvalue = @.@.IDENTITY|||Thanks DMWCincy.|||Watch out fot @.@.IDENTITY, better use SCOPE_IDENTITY() besuase @.@.IDENTITY will return the last increment for the table, not necessarily the one that was generated by you transaction.

Monday, March 26, 2012

I won't know where in the string "5679" will be, but...

If it is in that textbox (in the details row), I have to display a
drill down row underneith that record.
Any help is appreciated.
TrintWhat do you want to display there ? I detailed row exists for that row where
the '5679' exist you add a new group ti the table, for this etailed group
you can then handle the toggle to tell the report that it only has to toggle
or to be visible if the textbox.Value has a specific value.
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"trint" <trinity.smith@.gmail.com> schrieb im Newsbeitrag
news:1113850634.959940.237640@.o13g2000cwo.googlegroups.com...
> If it is in that textbox (in the details row), I have to display a
> drill down row underneith that record.
> Any help is appreciated.
> Trint
>|||Ok,
This is what I don't understand. The hidden rows with downline dealers
only has '5678'. But the visible row that needs a drill down might
have in the dealerlineageid (which is a field not to be displayed)
'5678' and 2754 and 10 other dealer numbers (multiple dealer numbers).
How would I know if 5678 is in there as a value? If it has multiple
dealer numbers separated by commas? Wouldn't the expression for the
"hidden" value need to be a substring query? How would I do that?
Thanks,
Trint

Friday, March 23, 2012

I want write a Trigger that make a 3nd table that contain all the record

Hi
I want to write Trigger tha do this:
I have 2 table

main table & sub main table that have data like this
for example I have Bank (code 001) in main table and visa(code 0001) & mastercard(code 0002) in submain table.

or I have BMW(code 101) in main table and X5(code 0001) & X3(code 0001) in sub main table.

I want write a Trigger that make a 3nd table that contain all the record of that two table like this:

NAME CODE
Bank 001
visa card 0010001
master card 0010002

BMW 101
X5 1010001
X3 1010002
..........................................

Trigger MUST make code for any record ( main code * 1000 + submain code ) that is unic. and record name.

1) Set a foreign key in the "submain" table to reference the unique 3-digit codes in the main table.

2) Write an INSERT tigger on the "submain" table that inserts a JOIN of main+submain into the 3rd table.

Something like what you see below. You could also use a computed column as explained in the other post.

Thanks

set nocount on
go

use tempdb
go

create table main(
the_name char(6) not null primary key
, the_main_code char(3) not null constraint main_unique unique
)
go

insert into main values ('Banks', '001')
insert into main values ('Goods', '101')
go

create table submain(
the_name varchar(20) not null primary key
, the_submain_code char(4) not null
, the_main_code char(3) foreign key references main(the_main_code)
)
go

create table combined (
the_main_name char(6)
, the_submain_name varchar(20)
, the_combined_code char(7)
)
go

create trigger make_combined on submain
for insert
as
insert into combined
select main.the_name, inserted.the_name, inserted.the_main_code + inserted.the_submain_code
from main join inserted
on main.the_main_code = inserted.the_main_code
go


insert into submain (the_name, the_submain_code, the_main_code) values ('visa card', '0001', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('master card', '0002', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X5', '0001', '101')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X3', '0002', '101')
go

select * from combined order by the_combined_code
go


drop table submain
go
drop table main
go
drop table combined
go

I want to select the SECOND newest record in a table,....is this possible?

Hi!
I want to do a query against a SQL DB and by sorting a datetime field, I want to get the second newest record in the table, not the newest.
Can I do that?
/Johan Ch

You could do it like this:
SELECT TOP 1
*
FROM
(SELECT TOP 2 * FROM myTable Order by myDateTime DESC) AS A
ORDER BY
myDateTime ASC

Wednesday, March 21, 2012

I Want to Lock One Record

Please help! I have a VB application used by multiple users. MS SQL 2000 backend.

When a user opens a record for editing, it want to mark the record as locked so that if other users try to open this record, it will not be available.

Currently, I'm using a programmatic method that basically sets a field 'lock" to 1 if the record is in use and 0 if not in use. But this has the side affact of locking the record if the user reboots their workstation. In addition, I don't believe that this is the best method and approach to achieve my goal.

Thanks very much for your help in advance. All ideas are welcome.

crownlogI think u can make use of SQL Server's locking mechanism.
example:
U can put a special feild as a semphemore.
when a user opens a record,the program begin a transaction and update the field to 1 immdiately.When the user press save button in front-program,the transaction will commit.When the locker workstation reboots,the transaction will auto-released right now,and the other waiting program will get the control immdiately.|||enhydraboy,

Thanks very much. Might you have more details or an example in using MSSQL2K locking mech.

Regards
crownlog|||Nobody?

This is a bad design. It's the path that leads to...the dark side..

You should desgin the table so that it has an update datetime column.

Every time a row is modified, update that column.

If you need to know if the row was modified, check that column again and compare the datetimes...if they are the same, good to go...if they're different, show the user the changes...you could even build something to allow them to merge the 2...then apply the update...

The my own opinion (MOO)|||crownlog ... listen to Brett's advice. What if your user, after locking the row, goes to lunch or home for the weekend or Aruba for the week or Australia after embezzling $1M? best to not lock the row until ready to update!|||should read...

"best to let sql server manage ALL of your locking"|||Actually, the issue is that I need to open a record and keep it opened the entire time it is being edited by user1 - could be minutes. I need to keep all the other users from trying to open it while it is being edited by User1. I only need that record locked during the edit.

What's the best way to do it?|||Why is a bad design?
Is A timestamp field good?
What I can confirm is that is not what the crownlog requests.|||Actually, the issue is that I need to open a record and keep it opened the entire time it is being edited by user1 - could be minutes. I need to keep all the other users from trying to open it while it is being edited by User1. I only need that record locked during the edit.

What's the best way to do it?

Let me tell my solution in detail,but u must suffer my bad english first.
Everybody knows that RDBMS uses locking to keep consistency of updating and reading during cocurrent sessions.
So when we program in VB/VC/Delphi,we needn't care anything because sqlserver engine will do it better.

crownlog's case is that he want find a solution that will synchronize transaction between different clients(according to per application).
So we must establish a synchronization rules,we need a center-locking control.
There are many solution u can choose:
1 Unique Application server,u must program by yourself
2 Let SQL Server became a center-locking control server,because database locking control is very perfect.What u need do is understanding it and making use of it.

About "best to let sql server manage ALL of your locking",I haven't read the book.But I know if I unstand sql server well,I am not fraid to using it and I well let sql server serve for me saftly.|||crownlog ... listen to Brett's advice. What if your user, after locking the row, goes to lunch or home for the weekend or Aruba for the week or Australia after embezzling $1M? best to not lock the row until ready to update!
It's the problem crownlog must think it over.
How to realize his requests in UI function.
Using locking or using timestamp,U will not avoid the problem that when one body go home for sleeping without quit his appliction,the others must wait he come back to finish his work.
Maybe u must need administrator to force one to go out.So It's not the problem of techniques.|||Maybe a combination of the two. A binary field to determine whether the record is being currently used, and a time limit of say 10 minutes, so another field with a timestamp. Assuming all the systems are using a level 2 time clock server. You could have a script running in the background to clear out any locked fields and set it to 0 if the time has lapsed comparative to the timestamp. Also setting a timer on the page of your application, and maybe a timestamp refresh button. It would force the user to pay attention to what they are doing. If I'm not getting my idea across, just drop me an e-mail, and I'll try to get some sort of flowchart together to explain it better.

Monday, March 19, 2012

i want a certain field to be sequentially increased

Hi,
I want to setup one of the fields in a table so it increments
sequentially(int data type). i.e the first record should be record 1
and the second one should be 2 and so on. This field will also be the
key field. I am new to SQL and don't know how to do this.
I am using SQL server 2000.
Thanks for the help in advance.

-S>> I want to setup one of the fields [sic] in a table so it increments
sequentially(INTEGER data type). i.e the first record [sic] should be
record 1 [sic] and the second one should be 2 and so on. This field
[sic]will also be the key field [sic]. I am new to SQL and don't know
how to do this. <<

Very new. Rows are not records; columns are not fields; keys are not
physical record numbers. You missed the most basic concept of the
RDBMS model. A key is a subset of the attributes (columns) which are
unique and not null for each entity (row) in the table. It is never a
"magic number for everything" generated by the computer which has no
meaning in the reality you are trying to model.

You actually have to think and work hard to design a database. Get a
book on data modeling and read it.|||Take a look at IDENTITY (Property) in "SQL Server Books Online".

"Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
news:a3007893.0311071034.54617b6b@.posting.google.c om...
> Hi,
> I want to setup one of the fields in a table so it increments
> sequentially(int data type). i.e the first record should be record 1
> and the second one should be 2 and so on. This field will also be the
> key field. I am new to SQL and don't know how to do this.
> I am using SQL server 2000.
> Thanks for the help in advance.
> -S|||thanks for the 40. I hear SQL FOR SMARTIES is pretty good. Any thoughts??

joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0311071358.4db9e6b8@.posting.google.com>...
> >> I want to setup one of the fields [sic] in a table so it increments
> sequentially(INTEGER data type). i.e the first record [sic] should be
> record 1 [sic] and the second one should be 2 and so on. This field
> [sic]will also be the key field [sic]. I am new to SQL and don't know
> how to do this. <<
> Very new. Rows are not records; columns are not fields; keys are not
> physical record numbers. You missed the most basic concept of the
> RDBMS model. A key is a subset of the attributes (columns) which are
> unique and not null for each entity (row) in the table. It is never a
> "magic number for everything" generated by the computer which has no
> meaning in the reality you are trying to model.
> You actually have to think and work hard to design a database. Get a
> book on data modeling and read it.|||>> thanks for the 40. I hear SQL FOR SMARTIES is pretty good. Any
thoughts?? <<

I like it a lot -- especially when the royalty check arrives :)

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dont listen to Celko - his is too pr0 :p

go with the identity - but beware some of its behaviour, and protect
it carefully, it is not terribly robust and can totally shaft your
data relationships if you use it and let it get damaged.

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vqofb5e8rl3k97@.corp.supernews.com>...
> Take a look at IDENTITY (Property) in "SQL Server Books Online".
> "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > Hi,
> > I want to setup one of the fields in a table so it increments
> > sequentially(int data type). i.e the first record should be record 1
> > and the second one should be 2 and so on. This field will also be the
> > key field. I am new to SQL and don't know how to do this.
> > I am using SQL server 2000.
> > Thanks for the help in advance.
> > -S|||Wang,

I do agree with you. These are all features that help programmers to develop
more efficient applications. It all depends on how good you are, if you are
a terrible programmer then it doesn't matter whether you use IDENTITY or
not, you will end up with a piece of crap anyway. A professionals is someone
who can take advantage of these features and use them wisely to help
developing their creative ideas without making a mess.

Shervin

"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0311110220.539cf94d@.posting.google.c om...
> Dont listen to Celko - his is too pr0 :p
> go with the identity - but beware some of its behaviour, and protect
> it carefully, it is not terribly robust and can totally shaft your
> data relationships if you use it and let it get damaged.
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:<vqofb5e8rl3k97@.corp.supernews.com>...
> > Take a look at IDENTITY (Property) in "SQL Server Books Online".
> > "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> > news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > > Hi,
> > > I want to setup one of the fields in a table so it increments
> > > sequentially(int data type). i.e the first record should be record 1
> > > and the second one should be 2 and so on. This field will also be the
> > > key field. I am new to SQL and don't know how to do this.
> > > I am using SQL server 2000.
> > > Thanks for the help in advance.
> > > > -S|||how would you protect the behavior of IDENTITY?
Shervin and wangkhar thanks for the help.
Celko, Don't expect any royalty from this beginner!!

Wangkhar@.yahoo.com (WangKhar) wrote in message news:<bb269444.0311110220.539cf94d@.posting.google.com>...
> Dont listen to Celko - his is too pr0 :p
> go with the identity - but beware some of its behaviour, and protect
> it carefully, it is not terribly robust and can totally shaft your
> data relationships if you use it and let it get damaged.
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vqofb5e8rl3k97@.corp.supernews.com>...
> > Take a look at IDENTITY (Property) in "SQL Server Books Online".
> > "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> > news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > > Hi,
> > > I want to setup one of the fields in a table so it increments
> > > sequentially(int data type). i.e the first record should be record 1
> > > and the second one should be 2 and so on. This field will also be the
> > > key field. I am new to SQL and don't know how to do this.
> > > I am using SQL server 2000.
> > > Thanks for the help in advance.
> > > > -S|||>> Celko, Don't expect any royalty from this beginner! <<

What I used to get from beginners was consulting gigs, to clean up the
mess they made when their company has problems. Those jobs pay MUCH
better than royalties :)

Using IDENTITY as a primary key is a sign that there is no data model,
only an imitation of a sequential file system. Since this "magic,
all-purpose, one-size-fits-all" pseudo-identifier exists only as a
result of the physical state of a particular piece of hardware at a
particular time as read by the current release of a particular database
product, how do you verify that an entity has such a number in the
reality you are modeling?

You will see newbies who design tables like this:

CREATE Drivers
(driver_id IDENTITY (1,1) NOT NULL PRIMARY KEY,
ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin));

Now input data and submit the same row a thousand times, a million
times. Your data integrity is trashed. The natural key was this:

CREATE Drivers
(ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin),
PRIMARY KEY (ssn, vin));

If you want to enforce a rule that a car can have one driver:

CREATE Drivers
(ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL PRIMARY KEY
REFERENCES Motorpool(vin));

Now you are REALLY thinking about relations and keys instead of 1950's
sequential record numbering. Go further and add DRI:

CREATE Drivers
(ssn CHAR(9) NOT NULL
REFERENCES Personnel(ssn)
ON DELETE CASACADE
ON UPDATE CASACADE,
vin CHAR(17) NOT NULL PRIMARY KEY
REFERENCES Motorpool(vin)
ON DELETE CASACADE
ON UPDATE CASACADE,
PRIMARY KEY (ssn, vin));

Adding an IDENTITY column to either of these tables as a candidate key
would be dangerously redundant; one query uses the IDENTITY and another
uses the real key, and like a man with two watches, you are never sure
what time it is.

Researching the CHECK constraints you need for a VIN or SSN will take a
few days -- but newbies only want "quick and magic answers that solve
all the problems" and do not bother doing the real work.

Finally, an appeal to authority, with a quote from Dr. Codd: "..Database
users may cause the system to generate or delete a surrogate, but they
have no control over its value, nor is its value ever displayed to them
..."(Dr. Codd in ACM TODS, pp 409-410) and Codd, E. (1979), Extending
the database relational model to capture more meaning. ACM Transactions
on Database Systems, 4(4). pp. 397-434.

This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means never
used in queries, DRI or anything else that a user does.

Codd also wrote the following:

"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two companies
merge, the two employee databases might be combined with the result that
some or all of the serial numbers might be changed.).

(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.

(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it has
ceased to have one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] - is
to introduce entity domains which contain system-assigned surrogates.
Database users may cause the system to generate or delete a surrogate,
but they have no control over its value, nor is its value ever displayed
to them...." (Codd in ACM TODS, pp 409-410).

References

Codd, E. (1979), Extending the database relational model to capture more
meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||It's not always consecutive. So if it's important to you just forget
IDENTITY.
Identity is a means just like many other things in SQL, if you chose the
right tool for your problem you won't regret it. It's not a silver bullet
but it will help you if you use it in the right place.

Good luck,
Shervin

"Sumanth Suri" <dangerousminds_17@.yahoo.com> wrote in message
news:2e121621.0311111136.5c401fd@.posting.google.co m...
> how would you protect the behavior of IDENTITY?
> Shervin and wangkhar thanks for the help.
> Celko, Don't expect any royalty from this beginner!!
> Wangkhar@.yahoo.com (WangKhar) wrote in message
news:<bb269444.0311110220.539cf94d@.posting.google.com>...
> > Dont listen to Celko - his is too pr0 :p
> > go with the identity - but beware some of its behaviour, and protect
> > it carefully, it is not terribly robust and can totally shaft your
> > data relationships if you use it and let it get damaged.
> > "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:<vqofb5e8rl3k97@.corp.supernews.com>...
> > > Take a look at IDENTITY (Property) in "SQL Server Books Online".
> > > > "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> > > news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > > > Hi,
> > > > I want to setup one of the fields in a table so it increments
> > > > sequentially(int data type). i.e the first record should be record 1
> > > > and the second one should be 2 and so on. This field will also be
the
> > > > key field. I am new to SQL and don't know how to do this.
> > > > I am using SQL server 2000.
> > > > Thanks for the help in advance.
> > > > > > -S

Monday, March 12, 2012

I ran a update quey by mistake

With out any BEGIN Transaction statement , i ran a update query.

I had to update just one record using the "Where field='abc'"

But i happened to miss that. Is there any chance of recovery? can i use the transaction logs to bring back the records before the update?

A quick answer is appreciated.

Sahel

Hi Sahel.

No way to get it back directly using native Sql tools/operations without performing some restore type operations.

Best way may be, if you have a standard backup structure in place, take a log backup immediately, then restore a copy of your database side-by-side with the existing db (using a different db name), and restore logs up to the point in time right before you ran the update statement (see the STOPAT option of the RESTORE LOG statement), then update the existing table with the values that exist in the table(s) from the restored database.

There are some 3rd party applications that you could consider researching and using, although these aren't a supported solution generally speaking, so be sure to clarify/understand that before using them (Lumigent Log Explorer is one for example).

HTH

Sunday, February 19, 2012

I need help with an insert clause, please help

I am trying to write a complex insert statement and not really sure how to get started.

First I am building an application to a record label to add artist, songs, pictures, video, and music to their site.

Thanks to asp.net 2.0 building the application interface was easy, but now I realize I have run into a brick wall and need some help.

in one application, called "add artist album"

in this application, you, the website administrator, are adding information to two different tables (artist (holds all info about the artist), album (holds all the info about artist album))

I have no clue how to really start writing an insert statement for this.

Basically I want to be able to insert the album data (artistid (foreign key), album name and release date) and also have a drop down listbox, which is connected to the database by the sqldatasource connector to allow the administrator to choose an artist from the artist table so that when they add the albem and release date they, the administrator, can choose which artist in the drop down list box to associate with the album and release date data that goes in the album table. That association is made with the artistid because it is a foreign key to the artist table but how do I insert the chosen artistid number into that foreign key field for the album table?


Please can someone point me in the right direction.

I am thinking that the insert statement will be something like and insert with nested select statement and inner join included but i haven't a clue how to do this.

My table DDL

Create table artist (artistid int prmrykey, artistname varchar (100), bio varchar(100))

Create table album (albumid int prmrykey, albumname varchar (100), releasedate date, artistid int foreignkey)

My DML for the insert statement so far:

ALTER PROCEDURE sp_AddArtistAlbum
@.AlbumName varchar(50),
@.ReleasedDate datetime,
@.ArtistID int
AS

SET NOCOUNT ON

DECLARE@.AlbumIDINTINSERT INTOtb_Album

(AlbumName, ReleasedDate, ArtistID)

VALUES(@.AlbumName, @.ReleasedDate,selectArtistIDfromtb_ArtistwhereArtistID = (ArtistID from sqldatasource from the drop down listbox) )

Like I said know I am supposed to have a select subquery statement that is nested to really make this thing work but I do not know where to start, can someone please help me.

I hope I have provided enough information.

My expected results are to insert data from into the album table and have that data associated with an artist chosen in the drop down box.

Please Help!!!!!!!!!!!!!!

ALTER PROCEDUREsp_AddArtistAlbum@.AlbumNamevarchar(50),@.ReleasedDatedatetime,@.ArtistIDintASSET NOCOUNT ONDECLARE @.AlbumIDINTINSERT INTO tb_Album(AlbumName, ReleasedDate, ArtistID)VALUES (@.AlbumName, @.ReleasedDate, @.ArtistID)

The ArtistID is the SelectedValue from your DropDownList (althought the Artist Name appears in the dropdown). That's all you need to insert. It's there and available without any additional messing about.

If you look at the logic of your proposed SQL, you will see that it says "I have the ArtistID from the dropdown, Now I want to select the ArtistID from the Artist table that matches the ID I already have". Bit of a nonsense when you analyse it like that...Big Smile

I need help please! My Exception Handling is not working.

I need help please...

I am using exception handling and Transaction handling combined.
The code is written to delete a record from 2 different table.
The purpose of using exception and transaction handling combined is tomake sure that the record is deleted in BOTH table. If there is errorwhile deleting a record from one of the tables, it will rollback thedatabase to the original and will stop deleting any record.

However, The Transaction handling is not working. When error occurswhile deleting a record on table A, the code still delete a record fromone table B. The transaction.RollBack() doesn't work. This is my code:

Try
Conn= NewSqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Conn.Open()
Trans = sConn.BeginTransaction

For Each row In GridView1.Rows
Dim sql As String = "SELECT * FROM Member
Using Cmd As New SqlCommand(sql, Conn, Trans)
SqlDataSource1.DeleteParameters("MemberID").DefaultValue =DropDownListMember.SelectedValue
SqlDataSource1.Delete()
SqlDataSource2.DeleteParameters("CarID").DefaultValue =DropDownListCar.SelectedValue
SqlDataSource2.Delete()
End Using
End If
Next
sTrans.Commit()
Label1.Text = "deleted."
Catch ex As Exception
If sTrans IsNot Nothing Then
sTrans.Rollback()
End If
Label1.Text = "error"
Return
Finally
If sConn IsNot Nothing Then
sConn.Close()
End If
End Try

soo much thanks in advanced!!!got my question answered!

I found it myself by trying change the codes. thanks all! (?)