Increase number by 1

Hello all,
I have, what i think, is a unique problem that i'm hoping some of you can help me on.

I need to create a record number that is incremented by 1 whenever someone adds a new record to the database. For example, records numbering 1,2,3 are in the database. When the users adds a new record, SQL takes the last recordno, 3 in this case, and adds 1 to it thus producing 4.

Also, i need to have the ability to replace deleted record numbers with new ones. Using the example above, say a user deletes record number 2. Whenever someone adds a new record, sql would see the missing number and assign the new record that number.

I hope i'm making sense here. Does anyone have any ideas about this? Any articles on the web that someone could point me to?

Thanks.
Richard M.hi richard,
i guess you need to do this by coding urself. u can use the feature in Sql server to increment the number by one but i don't think its possible to replace the deleted number.

so the better solution will be to add the incrementing number programatically. first declare int data type in sql server and assign 1 for the first record. for new records, check whether any number is missing and try to add new into that.

for eg, if you have 10 records, then no of 10th record should be 10 else some record is deleted. so u can use loop to check which number is missing.

i hope u can do the coding.

Incorrect syntax near 'sp_cursorclose'

I get this error message when using JDBC and JSP to access a sqlserver 2000 database. Trying to insert a record. Insert works perfectly when run from query analyzer. Fails with above error message when inserting from web app.
Any ideas?
Amy Thropp wrote:

> I get this error message when using JDBC and JSP to access a sqlserver 2000 database. Trying to insert a record. Insert works perfectly when run from query analyzer. Fails with above error message when inserting from web app.
> Any ideas?
Show the actual jdbc code you're running and also the whole stacktrace of the
exception.
thanks
Joe
|||inserting lock with {INSERT INTO record_locks (type, record_id, session_id, user_id, timestamp) VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1, getdate())}
connection: jdbc:JSQLConnect://tsps5.bha.biancohopkins.com:1433/database=conversion_db/sa barfed on update {INSERT INTO record_locks (type, record_id, session_id, user_id, timestamp) VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1, getdate())}
, error: com.jnetdirect.jsql.u: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.
com.jnetdirect.jsql.u: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.
at com.jnetdirect.jsql.at.a(Unknown Source)
at com.jnetdirect.jsql.ae.f(Unknown Source)
at com.jnetdirect.jsql.ae.new(Unknown Source)
at com.jnetdirect.jsql.ae.for(Unknown Source)
at com.jnetdirect.jsql.l.execute(Unknown Source)
at com.jnetdirect.jsql.ae.else(Unknown Source)
at com.jnetdirect.jsql.ae.executeQuery(Unknown Source)
at TestLock.main(TestLock.java:28)
"Joe Weinstein" wrote:

>
> Amy Thropp wrote:
>
> Show the actual jdbc code you're running and also the whole stacktrace of the
> exception.
> thanks
> Joe
>
|||here's the code. The other posting had the stacktrace messages
public static void main( String[] args)
{
String query =
"INSERT INTO record_locks (type, record_id, session_id, " +
"user_id) " +
"VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1)";
try {
Class.forName( "com.jnetdirect.jsql.JSQLDriver");
Connection conn = DriverManager.getConnection( DB, USER, PASSWD);
Statement stmt =
conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
print( "inserting lock with {" + query + "}");
ResultSet rs = stmt.executeQuery( query);
print( "and got back from update");
} catch (Exception e) {
print( "connection: " + DB + "/" + USER + " barfed on update {" +
query + "}, error: " + e.toString());
e.printStackTrace();
}
return;
}
"Joe Weinstein" wrote:

>
> Amy Thropp wrote:
>
> Show the actual jdbc code you're running and also the whole stacktrace of the
> exception.
> thanks
> Joe
>
|||Ok.
The problem is that you're doing an insert (not a query), and then calling
executeQuery() instead of executeUpdate().
Try this:
String insert =
"INSERT INTO record_locks (type, record_id, session_id, " +
"user_id) " +
"VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1)";
Class.forName( "com.jnetdirect.jsql.JSQLDriver");
Connection conn = DriverManager.getConnection( DB, USER, PASSWD);
Statement stmt = conn.createStatement();
print( "inserting lock with {" + insert + "}");
stmt.executeUpdate(insert);
Joe Weinstein at BEA
Amy Thropp wrote:
[vbcol=seagreen]
> here's the code. The other posting had the stacktrace messages
> public static void main( String[] args)
> {
> String query =
> "INSERT INTO record_locks (type, record_id, session_id, " +
> "user_id) " +
> "VALUES( 'epss', 1006, 'B77384E6BF824A351B8434967F99C7BF', 1)";
> try {
> Class.forName( "com.jnetdirect.jsql.JSQLDriver");
> Connection conn = DriverManager.getConnection( DB, USER, PASSWD);
> Statement stmt =
> conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
> print( "inserting lock with {" + query + "}");
> ResultSet rs = stmt.executeQuery( query);
> print( "and got back from update");
> } catch (Exception e) {
> print( "connection: " + DB + "/" + USER + " barfed on update {" +
> query + "}, error: " + e.toString());
> e.printStackTrace();
> }
> return;
> }
>
> "Joe Weinstein" wrote:
>

Incorrect syntax near my_stored_procedure

I have a stored procedure (sp) named 'ins_MemberPayment'. This procedure creates a record of a member's payment in the MemberPayments table. The sp looks like this:
___
CREATE PROCEDURE ins_MemberPayment
(@.MemberId VarChar(10),
@.CCNum Char(4),
@.Amount smallmoney)
AS

INSERT INTO MemberPayments
(MemberId, PaymentDate, CCNum, Amount)
VALUES
(@.MemberId, GetDate(), @.CCNum, @.Amount)
GO
--

The ASP.NET page looks like this:
___
Dim UserName As String = txtUserName.Text 'hotrodjimmy73
Dim CreditCard As String = Trim(txtCreditCard.Text) '5464655458776221
Dim Amount As String = "1.00"

Dim cnn As New SqlConnection(Application("SQLConnectionString"))
Dim trans As SqlTransaction
Dim cmd2 As New SqlCommand(dbo() & "ins_MemberPayment", cnn, trans)
cmd.CommandType = CommandType.StoredProcedure

With cmd2.Parameters
.Add("@.MemberID", UserName)
.Add("@.CCNum", Right(CreditCard, 4))
.Add("@.Amount", Convert.ToDecimal(Amount))
End With

cnn.Open()
cmd2.ExecuteNonQuery()
cnn.Close()
--
When I run the page, I get the error:
___
Incorrect syntax near 'ins_MemberPayment'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'ins_MemberPayment'.

Source Error:

Line 261: cmd2.ExecuteNonQuery()
--

Does anybody see the error? I'm blind to it after looking for a couple hours now.In this line

Dim cmd2 As New SqlCommand(dbo() & "ins_MemberPayment", cnn, trans)

what is the purpose of the dbo() function call?

Terri|||What does dbo() evaluate to? Does it translate to "dbo."?

Try this alternative syntax and see if it works.

Dim cm as SqlCommand = New SqlCommand()
cm.Connection= cnn
cm.CommandType= CommandType.StoredProcedure
cm.CommandText= "ins_MemberPayment"
cm.Transaction = trans

|||Oh, sorry. I should have simplified that for the purpose of posting to the forum. The dbo function call simply returns a conditional string: "dbo." if the page is running in it's production environment, and "" if it is running on my local development machine.

It's not relevant for the question at hand.|||I didn't want to "lead the witness" by originally saying that I have a hunch that the problem has something to do with smallmoney and string conversion. But now that I've tested this in Query Analyzer, I'm pretty sure this is the case.

I can get the procedure to work when passing it a value of 12 or 12.00, but as soon as I put single quotes around it, I get the error:

Implicit conversion from data type varchar to smallmoney is not allowed. Use the CONVERT function to run this query.

If I hope to accomplish the conversion in ASP.NET before sending the value to SQL Server, what do I need to convert it too? Int16, Int32, Double?

What's the "RIGHT" data type to convert to?|||The right .NET Framework data type is SQLMoney. (Here's across reference map of SQL Server and .NET Framework data types).

I suggest adding your parameters as such:

With cmd2.Parameters
 .Add("@.MemberID", SqlDbType.Varchar, 10, UserName)
 .Add("@.CCNum", SqlDbType.Char,4,Right(CreditCard, 4))
 .Add("@.Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))
End With

Terri|||Using your code I get:

Value of type 'System.Data.SqlTypes.SqlMoney' cannot be converted to 'String'.|||Going back to your originally supplied code, change this:

cmd.CommandType = CommandType.StoredProcedure

to this:

cmd2.CommandType = CommandType.StoredProcedure

Terri|||Oops. Thanks for the fresh pair of eyes. That did one good thing for me; It allowed me to get more accurate errors returned.

Now it works as:

.Add("@.Amount", SqlTypes.SqlMoney.Parse(Amount))

But not as:

.Add("@.Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))

Hmm. Strange. Oh, well. It works for my purposes. THANKS!!|||I normally don't add my parameters that way, so I am sure I provided you with syntactical errors :-(

But I am glad you got it working now!

Terri

Incorrect group sum problem

Hi all,
I have encountered a very strange problem in RS2005. In my report I
show real estate transactions. When I have more than one record with
the same TR_ID (transaction id) I need to popolate nulls into certain
money fields like AGC_amt and some other fields. These amounts are the
same in all transactions with the same TR_ID and should be treated as
one value not a repeating value. Let's say $1,000 in AGC_amt should
remain $1,000 even for every transaction with the same TR_ID it
repeats. I conditionally place nulls into those texboxes that have the
same TR_ID in the previous record so only the first record has the
value.
=iif(Fields!TR_id.Value = Previous(Fields!TR_id.Value), nothing,
Fields!AGC_amt.Value)
I need to sum up all these values in my group footer. What I have in my
report is the amount equal to total as if I did not place nulls! It is
easy to show in example:
tr_id AGC_am
----
12345 $1,000
same tr_id I place null here although it is $1,000 in dataset
same tr_id also null for the same reason
group sum: $3,000 (instead of $1,000 what I expect!)
formula for the sum in group footer:
=Sum(Fields!AGC_amt.Value)
What is wrong here? I tried to place zeros in place of nulls and get
the same incorrect number, i.e. I have $1,000, $0 and $0 totaling to
$3,000 in group footer!!!
I am going insane!
Please, help!
Thanks,
StanNever mind, everyone! My mistake. There was another field in my dataset
I was supposed to use in my group totals. I just did not know about it.
Stan
suslikovich wrote:
> Hi all,
> I have encountered a very strange problem in RS2005. In my report I
> show real estate transactions. When I have more than one record with
> the same TR_ID (transaction id) I need to popolate nulls into certain
> money fields like AGC_amt and some other fields. These amounts are the
> same in all transactions with the same TR_ID and should be treated as
> one value not a repeating value. Let's say $1,000 in AGC_amt should
> remain $1,000 even for every transaction with the same TR_ID it
> repeats. I conditionally place nulls into those texboxes that have the
> same TR_ID in the previous record so only the first record has the
> value.
> =iif(Fields!TR_id.Value = Previous(Fields!TR_id.Value), nothing,
> Fields!AGC_amt.Value)
> I need to sum up all these values in my group footer. What I have in my
> report is the amount equal to total as if I did not place nulls! It is
> easy to show in example:
> tr_id AGC_am
> ----
> 12345 $1,000
> same tr_id I place null here although it is $1,000 in dataset
> same tr_id also null for the same reason
> group sum: $3,000 (instead of $1,000 what I expect!)
> formula for the sum in group footer:
> =Sum(Fields!AGC_amt.Value)
> What is wrong here? I tried to place zeros in place of nulls and get
> the same incorrect number, i.e. I have $1,000, $0 and $0 totaling to
> $3,000 in group footer!!!
> I am going insane!
> Please, help!
> Thanks,
> Stan

Inconsistent Search Results

When searching for this street using this parameter:
'L''Avenue De La Reine Elizabeth'
I get a result ( The record contains 'L''Avenue De La Reine Elizabeth The
Second',)
but When I try searching for the whole thing, it won't return any rows, nor
will it for
'L''Avenue De La Reine Elizabeth The'.
Is there a word limit of some sort on full text search?
Can anyone throw some light on this?
Thanks In advance
Warren
Warren,
Could you post the full output of the following SQL script as it is helpful
info in troubleshooting SQL FTS issues?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Are you using CONTAINS or FREETEXT when you searched for 'L''Avenue De La
Reine Elizabeth The Second'? Could you post the exact SQL query that you
were using?
Thanks,
John
"WalrusWal" <WalrusWal@.discussions.microsoft.com> wrote in message
news:D6020947-4BD9-4C0E-A7B2-9B157213E242@.microsoft.com...
> When searching for this street using this parameter:
> 'L''Avenue De La Reine Elizabeth'
> I get a result ( The record contains 'L''Avenue De La Reine Elizabeth The
> Second',)
> but When I try searching for the whole thing, it won't return any rows,
nor
> will it for
> 'L''Avenue De La Reine Elizabeth The'.
> Is there a word limit of some sort on full text search?
> Can anyone throw some light on this?
> Thanks In advance
> Warren
>
|||Thanks for replying, John - here is the stuff you asked for, though I can't
send you the SQL query itself, though it isd interesting to Note that if I
restrict the search to 5 words, it will search & find the record(s). If any
more than that, it does not bother searching!
------
us_english
(1 row(s) affected)
-----------
-
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
(1 row(s) affected)
name minimum maximum config_value
run_value
-- -- -- --
default full-text language 0 2147483647 1033 1033
ftcatid NAME
PATH
STATUS NUMBER_FULLTEXT_TABLES
------
-----------
- -- --
5 Address Data
C:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA
0 4
6 Cat_Desc
C:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA
0 0
(2 row(s) affected)
TABLE_OWNER
TABLE_NAME
FULLTEXT_KEY_INDEX_NAME
FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
------
------
------
-- --
------
dbo
building
PK_building
1
1 Address Data
dbo
DeliveryPoint
PK_DeliveryPoint
1
1 Address Data
dbo
parish
PK_parish
1
1 Address Data
dbo
road
PK_road
1
1 Address Data
(4 row(s) affected)
TABLE_OWNER
TABLE_ID TABLE_NAME
FULLTEXT_COLUMN_NAME
FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME
FULLTEXT_BLOBTP_COLID FULLTEXT_LANGUAGE
------
------
------
------
-- --
dbo
373576369 building
name
2 NULL
NULL 1033
dbo
741577680 DeliveryPoint
Business
2 NULL
NULL 1033
dbo
741577680 DeliveryPoint
SubElementDesc
3 NULL
NULL 1033
dbo
741577680 DeliveryPoint
LocationOnStreet
5 NULL
NULL 1033
dbo
741577680 DeliveryPoint
NameOfTerrace
6 NULL
NULL 1033
dbo
741577680 DeliveryPoint
Locality
8 NULL
NULL 1033
dbo
741577680 DeliveryPoint
PostCode
10 NULL
NULL 1033
dbo
533576939 parish
name
2 NULL
NULL 1033
dbo
709577566 road
name
2 NULL
NULL 1033
(9 row(s) affected)
Name
Owner
Type Created_datetime
------
------
road
dbo
user table 2004-05-06
10:14:12.887
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
------
id
int
no 4 10
0 no (n/a)
(n/a) NULL
name
varchar
no 80
no no
no SQL_Latin1_General_CP1_CI_AS
parishid
int
no 4 10
0 no (n/a)
(n/a) NULL
usrn
float
no 8 53
NULL yes (n/a)
(n/a) NULL
root
varchar
no 80
yes no
no SQL_Latin1_General_CP1_CI_AS
SortOrder
varchar
no 80
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
--- --
id
1
1 0
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
index_name
index_description
index_keys
------
---------
-----------
-
PK_road
clustered, unique,
primary key located on PRIMARY
id
constraint_type
constraint_name
delete_action
update_action status_enabled status_for_replication constraint_keys
-------
------
-- -- -- --
-----------
-
FOREIGN KEY
FK_road_parish
No Action No Action
Enabled Is_For_Replication parishid
REFERENCES APMS.dbo.parish (id)
PRIMARY KEY (clustered)
PK_road
(n/a) (n/a) (n/a)
(n/a) id
Table is referenced by foreign key
-----------
-
APMS.dbo.DeliveryPoint: FK_DeliveryPoint_road
APMS.dbo.parentRoad: FK_parentRoad_road
APMS.dbo.parentRoad: FK_parentRoad_road1
APMS.dbo.roadAlias: FK_roadAlias_road
No views with schema binding reference this table.
"John Kane" wrote:

> Warren,
> Could you post the full output of the following SQL script as it is helpful
> info in troubleshooting SQL FTS issues?
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Are you using CONTAINS or FREETEXT when you searched for 'L''Avenue De La
> Reine Elizabeth The Second'? Could you post the exact SQL query that you
> were using?
> Thanks,
> John
>
> "WalrusWal" <WalrusWal@.discussions.microsoft.com> wrote in message
> news:D6020947-4BD9-4C0E-A7B2-9B157213E242@.microsoft.com...
> nor
>
>
|||Sorry John - here is the relevenat bit of the SQL Statement - @.searchStreet
is the parameter in question...
where (
contains (d.*, @.searchtext1)
or d.business like @.Business+'%'
or contains( b.*, @.searchtext1)
or b.name like @.Business+'%'
or contains( r1.*, @.searchtext1)
or r1.name like @.Business+'%'
or contains( r2.*, @.searchtext1)
or d.NameOfTerrace like @.Business+'%'
or d.postcode like @.Business+'%'
or d.locality like @.searchtext2+'%')
and contains(r2.* ,@.searchstreet) and contains(p.*,@.searchTown)
"John Kane" wrote:

> Warren,
> Could you post the full output of the following SQL script as it is helpful
> info in troubleshooting SQL FTS issues?
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Are you using CONTAINS or FREETEXT when you searched for 'L''Avenue De La
> Reine Elizabeth The Second'? Could you post the exact SQL query that you
> were using?
> Thanks,
> John
>
> "WalrusWal" <WalrusWal@.discussions.microsoft.com> wrote in message
> news:D6020947-4BD9-4C0E-A7B2-9B157213E242@.microsoft.com...
> nor
>
>
|||Warren,
Thank you for the info! You're using SQL Server 2000 SP3 on Windows Server
2003 (Win2003) with US_english (1033) both the default language as well as
the "Language for Word Breaker" for your FT-enable varchar columns.
Since you're using Win2003 and it's new wordbreaker dll (LangWrbk.dll), the
string "L''Avenue De La Reine Elizabeth The Second" (without the double
quotes) breaks as follows:
Original text: 'L''Avenue De La Reine Elizabeth The Second'
IWordSink::PutWord: cwcSrcLen 9, cwcSrcPos 0, cwc 9, 'L''Avenue'
IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 10, cwc 2, 'De'
IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 13, cwc 2, 'La'
IWordSink::PutWord: cwcSrcLen 5, cwcSrcPos 16, cwc 5, 'Reine'
IWordSink::PutWord: cwcSrcLen 9, cwcSrcPos 22, cwc 9, 'Elizabeth'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 32, cwc 3, 'The'
IWordSink::PutWord: cwcSrcLen 6, cwcSrcPos 36, cwc 6, 'Second'
When you pass the above string into the @.searchstreet variable are you also
passing in double quotes (" ")? This would indicate to SQL Server 2000 that
this is a phrase query. Additionally, if you search only on "Avenue De La
Reine Elizabeth The Second" (with the double quotes) do you get a hit on
this? Note, there is no word limit of any sort on full text search queries,
but there are issues with punctuation characters (such as single quotes that
are escaped) that depend upon the OS-supplied wordbreaker dll.
Thanks,
John
"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:C6D8715A-9C48-4CA5-87B2-3C551A77859D@.microsoft.com...
> Sorry John - here is the relevenat bit of the SQL Statement -
@.searchStreet[vbcol=seagreen]
> is the parameter in question...
> where (
> contains (d.*, @.searchtext1)
> or d.business like @.Business+'%'
> or contains( b.*, @.searchtext1)
> or b.name like @.Business+'%'
> or contains( r1.*, @.searchtext1)
> or r1.name like @.Business+'%'
> or contains( r2.*, @.searchtext1)
> or d.NameOfTerrace like @.Business+'%'
> or d.postcode like @.Business+'%'
> or d.locality like @.searchtext2+'%')
> and contains(r2.* ,@.searchstreet) and contains(p.*,@.searchTown)
> "John Kane" wrote:
helpful[vbcol=seagreen]
La[vbcol=seagreen]
The[vbcol=seagreen]
rows,[vbcol=seagreen]

Inactive record

Hi just wondering if there is a way to have one record in a table be read
only, were an application can not remove it and have all other records act
normally? I am currently handling this in .net code but would prefer to
have it built into the table.
thanks.
--
Paul G
Software engineer.No there is now row level security based on SQL Server 2000.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BD1A992D-4723-48EA-82C1-7CF94A102258@.microsoft.com...
> Hi just wondering if there is a way to have one record in a table be read
> only, were an application can not remove it and have all other records act
> normally? I am currently handling this in .net code but would prefer to
> have it built into the table.
> thanks.
> --
> Paul G
> Software engineer.|||Sorry, i mean : No there is no row level security based on SQL Server 2000.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:eNTxiwzSFHA.612@.TK2MSFTNGP12.phx.gbl...
> No there is now row level security based on SQL Server 2000.
> HTH, Jens Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
> "Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BD1A992D-4723-48EA-82C1-7CF94A102258@.microsoft.com...
>|||ok thanks for the information.
"Jens Sü?meyer" wrote:

> No there is now row level security based on SQL Server 2000.
> HTH, Jens Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
> "Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BD1A992D-4723-48EA-82C1-7CF94A102258@.microsoft.com...
>
>|||On Wed, 27 Apr 2005 07:54:06 -0700, Paul wrote:

>Hi just wondering if there is a way to have one record in a table be read
>only, were an application can not remove it and have all other records act
>normally? I am currently handling this in .net code but would prefer to
>have it built into the table.
>thanks.
Hi Paul,
You can use a trigger:
CREATE TRIGGER DontTouchThisRow
ON MyTable AFTER UPDATE, DELETE
AS
IF EXISTS (SELECT *
FROM deleted
WHERE KeyCol = 1) -- Column to be protected
BEGIN
RAISERROR ('Row 1 may not be changed or removed', 16, 1)
ROLLBACK TRANSACTION
END
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

In VB Express 2005, I cant save any record in database of sql server.

I was using the msdn tutorial regarding sql database for vb2005 express. In that tutorial, I followed the steps as listed. But the problem is that the save button in my application during run time, it does not work. Furthermore, the code for for saving a record during run time does not work as well.

I have reinstall sql 2005 server and vb2005 express but of no use.

I have winxp with system of pentium 4.

What I should do to solve this dilemma.

Faisal.

You may want to check with this group of people:

http://www.tutorialized.com/tutorials/Visual-Basic/Database-Related/1

Buck Woody

In VB Express 2005, I cant save any record in database of sql server.

I was using the msdn tutorial regarding sql database for vb2005 express. In that tutorial, I followed the steps as listed. But the problem is that the save button in my application during run time, it does not work. Furthermore, the code for for saving a record during run time does not work as well.

I have reinstall sql 2005 server and vb2005 express but of no use.

I have winxp with system of pentium 4.

What I should do to solve this dilemma.

Faisal.

You may want to check with this group of people:

http://www.tutorialized.com/tutorials/Visual-Basic/Database-Related/1

Buck Woody

In SQL SERVER 2005, how can I get the ID of the record I just insert to table?

In SQL SERVER 2005, how can I get the ID of the record I just insert to table?

I defined a table MyTable, and I insert a record into the table using the SQL below

Insert into MyTable (Name) values ("User Name")

You know the field ID is IDENTITY, so it can not be in Insert SQL, and SQL SERVER will pass a value to it automatically.
How can I know the ID of the record I just insert to table?

CREATE TABLE [dbo].[MyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Take a look at @.@.IDENTITY - it returns the identity value for the row just added.|||Thanks!|||

There are a number of ways, the easiest of which is SELECT SCOPE_IDENTITY().

If you are using a text command, this works well:

Dim conn as new sqlconnection("{Your connection string}")

conn.open

dim cmd as new sqlcommand("SET NOCOUNT ON INSERT INTO MyTable(Name) VALUES (@.Name) SET NOCOUNT OFF SELECT SCOPE_IDENTITY()",conn)

cmd.parameters.add("@.Name",sqldbtype.varchar)

cmd.parameters("@.Name").value={Whatever}

dim MyID as integer=cmd.executescaler

conn.close

Try not to use @.@.IDENTITY unless you fully understand what the difference is between @.@.IDENTITY and SCOPE_IDENTITY(), in which case, you'll almost never want @.@.IDENTITY anyhow.