Change Identity value

I changed a column on an existing table to be a Identity column but when I
try to insert a record I get an error that the id already exists. Is there a
way for me to change the value so identity returns a higher #?Are you passing the identity value?
AMB
"Joe" wrote:

> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>
>|||You could use identity_insert to insert a row with the highest value
and then turn it off. Any inserts after that will increment sequentially.
ie.
create table test ( id int identity (1,1), col varchar(10) )
insert into test (col) values ('row1')
insert into test (col) values ('row2')
insert into test (col) values ('row3')
set identity_insert test on
insert into test (id, col) values (10,'row4')
set identity_insert test off
insert into test (col) values ('row5')
select * from test
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:O5mJERHEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>|||Joe,
Look at the DBCC CHECKIDENT command in the Books Online. If your table
containing the identity column was called jobs,
DBCC CHECKIDENT (jobs, RESEED, 30)
would force the new value to 30.
On Fri, 11 Feb 2005 14:59:57 -0500, "Joe"
<J_no_spam@._no_spam_Fishinbrain.com> wrote:

>I changed a column on an existing table to be a Identity column but when I
>try to insert a record I get an error that the id already exists. Is there
a
>way for me to change the value so identity returns a higher #?
>

Change default rowlock on SQL Server

Hello!

Does anyone know if there is a setting on MS SQL server to tell it to always lock a row for updating when attemping to change a record? I have a program that does a select statement and the user can then choose which records to edit. Once they select to edit a record, I need the sql server to prevent other users from editing that record. I know I can use the 'Updlock' hint but was hoping there was just a default setting on the server I could change. Thank you!In SQL2K row locking is default, if more locks required, this could escalate to page lock or full table lock. In SQL2K there is no way to change this behavior except using hints or isolation level, you can play also with indexes to obtain key locks and with cursor modes.

HTH,
OBRP|||Also refer thru books online for LOCKS topic which has been explained.

CeWriteRecordProps failling with a empty FILETIME field..

Hi All, Does anyone have already made an application that writes some data into the EDB Pocket database ? I′m trying to insert a record with a empty FILETIME field and I′m getting the Error 87 (INVALID_PARAMETER), I′ve tested with some date in the field and the record is added sucessfull, it seems that the database doesn′t accept anymore empty FILETIME fields? Is it true? I didn't find anything in the docs.

thanx in advance.

The CEPROPVAL is OEM dependent. Have you verified the behavior on other devices too? If that remains the same, I need to investigate further.|||

Hi Thiago,

Are you using CeWriteRecordProps (EDB)? If so, then yes you would get error if you are passing empty FILETIME field. Somehow, this is missed in documentation. We have a bug for that, and I would check the status.

Please pass non-empty FILETIME to get yourself unblocked. Sorry for the pain created.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

Hi Laxmi,

Thank you very much for your reply, I′m passing now a non-empty FILETIME and it's working very well, now I'm stuck in another function, a similar problem with the CeSeekDatabaseEx funcion, especially when used with the CEOID, BEGINNING, LAST or CURRENT flags.

In the documentation, MSDN says that isn't necessary to open the database with a sort order when seeking with these flags, but if I do this, get the same ERROR 87 (INVALID_PARAMETER).

The function only works (find a record sucessfully) when I open the database specifing a sort order.

Do you know something about this issue?

In the documentation is:

The ERROR_INVALID_PARAMETER may be returned in the following situations:

x. The database was opened without a specified sort order, and dwSeekType is
a value other than the following:
CEDB_SEEK_CEOID
CEDB_SEEK_BEGINNING
CEDB_SEEK_CURRENT
CEDB_SEEK_END.

All other seek types require the use of a sort order.

link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wcedata5/html/wce50lrfCeSeekDatabaseExEDB.asp

Thanx in advance,
Thiago

CeWriteRecordProps failling with a empty FILETIME field..

Hi All, Does anyone have already made an application that writes some data into the EDB Pocket database ? I′m trying to insert a record with a empty FILETIME field and I′m getting the Error 87 (INVALID_PARAMETER), I′ve tested with some date in the field and the record is added sucessfull, it seems that the database doesn′t accept anymore empty FILETIME fields? Is it true? I didn't find anything in the docs.

thanx in advance.

The CEPROPVAL is OEM dependent. Have you verified the behavior on other devices too? If that remains the same, I need to investigate further.|||

Hi Thiago,

Are you using CeWriteRecordProps (EDB)? If so, then yes you would get error if you are passing empty FILETIME field. Somehow, this is missed in documentation. We have a bug for that, and I would check the status.

Please pass non-empty FILETIME to get yourself unblocked. Sorry for the pain created.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

Hi Laxmi,

Thank you very much for your reply, I′m passing now a non-empty FILETIME and it's working very well, now I'm stuck in another function, a similar problem with the CeSeekDatabaseEx funcion, especially when used with the CEOID, BEGINNING, LAST or CURRENT flags.

In the documentation, MSDN says that isn't necessary to open the database with a sort order when seeking with these flags, but if I do this, get the same ERROR 87 (INVALID_PARAMETER).

The function only works (find a record sucessfully) when I open the database specifing a sort order.

Do you know something about this issue?

In the documentation is:

The ERROR_INVALID_PARAMETER may be returned in the following situations:

x. The database was opened without a specified sort order, and dwSeekType is
a value other than the following:
CEDB_SEEK_CEOID
CEDB_SEEK_BEGINNING
CEDB_SEEK_CURRENT
CEDB_SEEK_END.

All other seek types require the use of a sort order.

link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wcedata5/html/wce50lrfCeSeekDatabaseExEDB.asp

Thanx in advance,
Thiago

Cerating and updating new record using ADO

Hi, i want to create new record in remote accsess db using ADO. I'm using this code:

VB:
------------------------
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open "Provider=MS Remote;Remote Server=http://www.xyz.com;" & "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\Inetpub\wwwroot\biblio.mdb;"
rs.Open "SELECT * From USERS", cnn, adOpenKeyset, adLockOptimistic, adCmdText

rs.AddNew
rs("Name") = "Jon"
rs("PhoneNR") = "00375221"
rs.Update
------------------------

error apears when i updating recrodset:

'-21474671259' operation must use an updateable query

Why ??Originally posted by fizikas
Hi, i want to create new record in remote accsess db using ADO. I'm using this code:

VB:
------------------------
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open "Provider=MS Remote;Remote Server=http://www.xyz.com;" & "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\Inetpub\wwwroot\biblio.mdb;"
rs.Open "SELECT * From USERS", cnn, adOpenKeyset, adLockOptimistic, adCmdText

rs.AddNew
rs("Name") = "Jon"
rs("PhoneNR") = "00375221"
rs.Update
------------------------

error apears when i updating recrodset:

'-21474671259' operation must use an updateable query

Why ??

see the following article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;175168

Categorise Bar Chart

How to create a bar chart by setting the static category myself?

I mean, for example, I have a dataset which record the number of coins different people have.

I would like to draw a bar chart which shows 3 bars with the following 3 different categories:

1) n < 5,

2) n >= 5 and n <10,

3) n>=10.

where n is the number of coins.

How can this be expressed in the categories grouping?

Thanks in advance.

Sorry, this is currently not directly supported through chart groupings. You would need to write the query (or add a calculated field on the dataset) so that you get this categorization in the data and then use those fields in the chart.

-- Robert

Catch Duplicate records

I have a table that I am inserting records to via VB6 using an ADO command.
If I try to insert a duplicate record I get the error message
-2147217873 Access Primary Key violation etc
This error doesn't jump to my error handler so how can I catch it? is there
something I can do from with SQL? or am I missing something in VB
ThanksThe best way to handle the error is to prevent it from happening in the firs
t
place. That can be done by, for example, checking to see if the value exists
before you try to insert it.
You can certainly catch this error in VB and I would propose that is the bes
t
place to do it.
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:%239gwgADTFHA.3544@.TK2MSFTNGP10.phx.gbl...
>I have a table that I am inserting records to via VB6 using an ADO command.
> If I try to insert a duplicate record I get the error message
> -2147217873 Access Primary Key violation etc
> This error doesn't jump to my error handler so how can I catch it? is the
re
> something I can do from with SQL? or am I missing something in VB
> Thanks
>|||If u use stored procedure
u can return value from it
e.g
....
.....
...
INSERT INTO ....
......
RETURN @.@.ERROR
in VB u must check the return value if return value != 0
then an error occuerd. In case everything ok zero will returnd
Message posted via http://www.webservertalk.com|||Thanks, I have tried this
declare @.Stockcode char(30)
declare @.Barcode char(20)
declare @.Qty int
set @.Stockcode = '123456'
set @.Barcode = '1234567890123'
set @.Qty = 12
INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
RETURN @.@.ERROR
GO
but I get the error
A RETURN statement with a return value cannot be used in this context.
What am I doing wrong?
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
> If u use stored procedure
> u can return value from it
> e.g
> ....
> .....
> ...
> INSERT INTO ....
> ......
> RETURN @.@.ERROR
>
> in VB u must check the return value if return value != 0
> then an error occuerd. In case everything ok zero will returnd
> --
> Message posted via http://www.webservertalk.com|||Again, prevent the error from happening by checking for the value. Given tha
t
you did not specifiy the PK I'll use the Force and guess:
Create Table dbo.OuterBarCodes
(
StockCode Char(30)
, BarCode Char(20)
, Quantity Int
, Constraint PK_OuterBarCodes Primary Key (StockCode, BarCode)
)
If Not Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
If you really want to know whether the value exists then do this:
If Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Begin
Raiserror('Duplicate StockCode and BarCode', 16, 1)
Return
End
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:ODBIBhDTFHA.560@.TK2MSFTNGP10.phx.gbl...
> Thanks, I have tried this
> declare @.Stockcode char(30)
> declare @.Barcode char(20)
> declare @.Qty int
> set @.Stockcode = '123456'
> set @.Barcode = '1234567890123'
> set @.Qty = 12
>
> INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
> RETURN @.@.ERROR
> GO
> but I get the error
> A RETURN statement with a return value cannot be used in this context.
> What am I doing wrong?
> "E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
> news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
>

Catch a trigger from the Grid Events

Hi,

I have one problem when I try to update one record in the data base.

Such error arises when I update a table and a trigger raises an error, I cant catch it within the Row_Updating event command and I dont know how to get it within the Row_Updated event, because the exception happes before that command.

Also, I tried in the sqldatasource updating event but I got no results.

Any idea?

thanks in advance.

Modify your procedure to catch any errors and return a message:http://technet.microsoft.com/en-us/library/ms175976.aspx

cast not valid

Hello:
I want to make a query that return all record that don't have one valid
cast, something like this:
select * from table where cast(field as bigint) is valid
how can I do that or some variant?. the real problem is to import some table
in a dts but when I try to convert the str to bigint, raise one execption,
that I want to jump and eliminate this record but continue, instead the dts
stop.
Best regards,
Owen.Owen wrote:
> Hello:
> I want to make a query that return all record that don't have one
> valid cast, something like this:
> select * from table where cast(field as bigint) is valid
> how can I do that or some variant?. the real problem is to import
> some table in a dts but when I try to convert the str to bigint,
> raise one execption, that I want to jump and eliminate this record
> but continue, instead the dts stop.
> Best regards,
> Owen.
CAST is not a BOOLEAN function. That is, it does not return whether a
value _can_ be converted from one type to another. It explicitly tries
to convert and throws an exception if a failure occurs. You could use
the ISNUMERIC() function or roll your own integer check function or use
the one here:
http://www.aspfaq.com/show.asp?id=2390
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SELECT *
FROM Table
WHERE (x NOT LIKE '%[^0-9]%'
AND LEN(x) BETWEEN 1 AND 18)
OR x IS NULL
David Portas
SQL Server MVP
--