Enterprise Manager and Query Analyser Hang-up

When I use the Query Analyser, I can view the data in one table. But if I
insert a row to the table, the Query Analyser hangs up. If I want to drop an
index from that table using the Enterprise Manager, it hangs up too. All the
other tables work fine. How can I solve it? Thanks.Joe
Drop/Create INDEX requres page lock and hence nobody is available to use the
table till transaction will be commited. In fact Iwould do that by EM
because it has lots of lacks.
When you insert a row into the table check out that the table does not have
a trigger which may hangs up the application.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FE0761E8-C157-4A45-BDB9-C7A4FDD63424@.microsoft.com...
> When I use the Query Analyser, I can view the data in one table. But if I
> insert a row to the table, the Query Analyser hangs up. If I want to drop
an
> index from that table using the Enterprise Manager, it hangs up too. All
the
> other tables work fine. How can I solve it? Thanks.

Entering Unique data into a column

Is there a better to insure an INSERT will only add a new row only if the
data being added to a particular column is unique, besides doing a SELECT
before doing the INSERT?
SELECT X from table where X='NewData'
ExecuteReader and test for number of Rows
if Zero then do the INSERT
Thank you...
BruceINSERT INTO YourTable (x, ...)
SELECT 'NewData', ...
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE x = 'NewData')
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ecidneFpqq8UaW7cRVn-pA@.giganews.com...
> INSERT INTO YourTable (x, ...)
> SELECT 'NewData', ...
> WHERE NOT EXISTS
> (SELECT *
> FROM YourTable
> WHERE x = 'NewData')
> --
> David Portas
> SQL Server MVP
> --
>
Thanks.....
Bruce|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ecidneFpqq8UaW7cRVn-pA@.giganews.com...
> INSERT INTO YourTable (x, ...)
> SELECT 'NewData', ...
> WHERE NOT EXISTS
> (SELECT *
> FROM YourTable
> WHERE x = 'NewData')
> --
> David Portas
> SQL Server MVP
> --
>
Okay, What am I doing wrong? The following didn't work.. The INSERT happened
even though the value of 'Dentist' was in the table in the columne
AddressType.
INSERT INTO AddressType(AddressType) Values ('Dentist')
SELECT 'Dentist'
WHERE NOT EXISTS
(SELECT *
FROM AddressType
WHERE AddressType = 'Dentist')
Now, my table, AddressType, had only two columns, PrimaryKey (with
autoincrement) and AddressType.
Thanks...
Bruce|||You added the VALUES clause, which means it's no longer an INSERT... SELECT.
Try:
INSERT INTO AddressType (AddressType)
SELECT 'Dentist'
WHERE NOT EXISTS
(SELECT *
FROM AddressType
WHERE AddressType = 'Dentist')
BTW I assume AddressType is declared unique. Always declare unique columns,
don't just rely on the INSERT.
David Portas
SQL Server MVP
--|||
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:L8udnQJ32Z_Hm2ncRVn-rA@.giganews.com...
> You added the VALUES clause, which means it's no longer an INSERT...
SELECT.
> Try:
> INSERT INTO AddressType (AddressType)
> SELECT 'Dentist'
> WHERE NOT EXISTS
> (SELECT *
> FROM AddressType
> WHERE AddressType = 'Dentist')
> BTW I assume AddressType is declared unique. Always declare unique
columns,
> don't just rely on the INSERT.
> --
> David Portas
> SQL Server MVP
> --
>
This works. Thanks. And no, the column is not declared unique. I forgot
about that attribute, but will add it...
Thanks again...
Bruce

entering data into sql database

hi

I am working on webforms i have to insert system date into sql database when a button is clicked.

I have have 3 fields on the webform and a submit button.

I have to insert the date along with the other fileds into the sql database

when I am trying to insert date using getdate() it is getting inserted but not into the same row as that of other fields.

Can you plzzz help me with the code for inserting system data into the database into the same row as that of other fields

myCommand2 =New SqlCommand("Insert into Items(Requestdate) values (getdate()) where Hospital= '" & DropDownList2.SelectedItem.Text & "' ", myConnection)

ra = myCommand2.ExecuteNonQuery()

myConnection.Close()

THE ABOVE CODE GIVES AN SYNTAX ERROR NEAR "WHERE"

please help

you can also post me ur replies onurs_forever_tanya2001@.yahoo.co.in

Thanks bye

You can't specify awhere clause on an insert statement as the whole point of an insert is to add a record (i.e. it doesn't already exist). If you want to update and existing record, you will have to use anupdatestatement.

|||

by using update statement will i be able to insert new record then?

because i have to take system date while inserting a new record...

please help

|||

tanya2001:

by using update statement will i be able to insert new record then?

No. If you want to insert a record you have to use an insert statement (but remove your where clause). If you want to update an existing record, use an update statement.

|||

Hey tanya ,

Thanks for your question .

let me descrive some essesntioal stuff for you first.

Insert Statement , we use this statement when we wantadd new record to our table , it means that record doesn't exist and we are going to add that , so in this case we wont usingwhere on this state ment becuase there is no any rows and we want to add that.

Update Statement , we use this statement when we already we have one or more records in our table and we want just change some of their value we use update statement and we use where to specify which rows we are going to change.

In you solution , if you are going to Insert New value to table that doesn't not exist at all use Insert Statement, if you are going to change some value in your database use update.

|||

thanks for your answer but i want to insert system date as well with the new record in the database

so how can i insert the system date as well as the new record by entering a single submit button

its actually like i have 3 fields in a webform so i have to insert those three fields as well as system date should be inserted when submit is clicked

please help

|||

thanks all i got the answer