Quick Question

[WJProctor]

HI, im new to SQL Server, and trying to learn about it all the time. Two questions i wanted to ask. The first been how do i create a boolean field, like a yes/no one in access?? And the second was does SQL Server manage multiply users on the same record, or as i have done before should i have a field which is when the record was last updated to check against to see if other users have edited the field before me. Hope this makes sense?/

JP

 

[hmscott]

The first question is pretty straightforward. The equivalent to the MS Access Yes/No data type is the SQL "Bit" data type. Be aware however, that Access does some behind-the-scenes magic with Yes/No. In reality, Yes is stored as a -1 (negative one) and No is stored as a 0 (zero). The Bit data type will only accept 1 and 0 (no negative numbers).

As for your second question, I'm still trying to understand what you are looking for. SQL will automatically apply locks to a row that is being updated to prevent two users from updating a record at precisely the same time. However, I think you are want to apply some business logic on top of SQL's inherent concurrency logic. Business logic must always be handled in code (or you might be able to use a trigger here).

Regards,

hmscott

 

[WJProctor]

Cheers, that makes sense to me, i understand what you mean. Is there anyway of creating an autonumber by the way, like in access??

 

[nigelrivett]

Checking whether a record is usually done with a timestamp.
Read the timestamp and check it with the update - if it is not the same then the record has been changed.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[hmscott]

To create a column like Access' Autonumber, you use
IDENTITY(SEED, INCREMENT)

For example, try:
CREATE MyTable (
ID INT IDENTITY(1, 1) NOT NULL,
Data VARCHAR(20) NULL
) ON PRIMARY
GO

INSERT MyTable (Data)
SELECT 'Record1' UNION
SELECT 'Record2' UNION
SELECT 'Record3'
GO

 

[TheBugSlayer]

Use a bit or integer field with a value of 1/0 or -1/0 for Yes/No fields.

Read up on sql server locks. Basically the type of lock you define in the transaction controls how and how many users acces a a record, set of records or even a whole table. In the default form more than one user have access to the same record.

 

[WJProctor]

Thanx for all your help so far. I wondered if i could ask one for question. Im trying to set up the connection string for VB.net to connect to my database, but it keeps turning up errors. What should the connection string be for a db called "EBMDB", with a Username of "EBMAdmin" and a Password "Password". I cant seem to get it right.

Thanks again for your help.

Regards

JP