Ad-hoc INSERT of new Identity Value

Hello,
I have a table with a primary key having IDENTITY (ID).
In case of an INSERT I want to fill another Column C1
with the new Identity value.
How can I achive this? By a Trigger maybe?
Thank you very much
JoachimTry
create table #t (c1 int not null identity(1,1),c2 char(1),c3 as c1)
insert into #t (c2) values ('a')
insert into #t (c2) values ('b')
insert into #t (c2) values ('c')
select * from #t
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim|||What would be the point of having redundant data in the table? Anyway, much
easier with a computed column than with a trigger, but this won't work if
your intention is to later update C1.
CREATE TABLE dbo.foo
(
id INT IDENTITY(1,1),
blat VARCHAR(32),
C1 AS id
);
INSERT dbo.foo(blat) SELECT 'bar';
SELECT * FROM dbo.foo;
DROP TABLE dbo.foo;
Or just use a view:
CREATE VIEW dbo.View_foo
AS
SELECT id, C1 = id
FROM dbo.foo;
But it's tough to provide a good answer if we have no idea. It's usually
better to describe the intended goal than to ask us how to accomplish the
solution you've already determined is the only way to do it...
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim

Additional INSERT, UPDATE, and DELETE Statements Disabled.

Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.
I have been looking for an answer to my problem, but unfortunately I couldn't find one. So I created a user here on www.asp.net just for making this post.

Before I continue I just want to apologies if there is another post where this question is already answered.

Please watch this Print Screen I just took: "http://www.bewarmaronsi.com/Capture.JPG "

As you can see the "INSERT, UPDATE, and DELETE Statements" are disabled, and that's exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.

The MDF file is located in the App_data folder and is called ASPNETDB.

And when I try to add custom SQL statements, it gives me Syntax error near "=". Something like that. I bought the Total Training Set1 package and it works perfect in their examples.

I just want to thank you for reading my post and I hope that you got some useful information for me.

By the way, I', from Sweden so you have to excuse me if my English is rusty.

Thanks!

PS: Can it be that I'm running windows Vista?

Try adding a primary key column to the the table and check.

If any issues, let me know.

Sri

|||

Thank you for your postsridhar.av, I don't exactly know what you mean, but I think this might be it… please look at this new print screen:

"

http://www.bewarmaronsi.com/Capture2.JPG

"

And if that's the case, it still doesnt work.

Thanks

|||Right click on the Column -->Set Primary key|||

Thank you so muchsridhar.a, it works perfect now!

I'm a designer so if you need any help in that area, dont hesitate and email me anytime you want.
From Graphics to 3D modeling and Animation.

Thanks!

Adding value in a query

I am trying to insert a value numeric + 1 in to db table but i get error when i do this

this is the code

Const SQLAsString ="INSERT INTO [PageHits] ([DefaultPage]) VALUES (@.defaultP)"

Dim myCommandAsNew Data.SqlClient.SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@.DefaultP" +"1", DefaultP.Text.Trim())

myConnection.Open()

myCommand.ExecuteNonQuery()

myConnection.Close()

The Error:

Must declare the variable '@.defaultP'.

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: Must declare the variable '@.defaultP'.

The code you wrote will send a parameter called @.DefaultP1 to the database.

Did you want to increase an existing value in the database? If so, you must use an UPDATE query.sql

Adding rows to Oracle of MsAccess from T-SQL?

Hi everyone,

I'd like to know how to update/insert rows to sources different than Microsoft.

Should I use OpenDataSource(), OpenRowSet() functions or they are deprecated with Sql25k?

Thanks in advance for your time and for your inputs,

Both OpenDataSource and OpenRowSet can be used for inserting and updating data in data sources other than SQL Server. They are both supported by SQL Server 2005. You can also use linked servers.

Please tell me if you need further information.

Best regards,
Sami Samir

|||

If you are moving millions of rows of data, I found the fastest way to move from Access or SQL Server to ORACLE was via an intermediate text file.

A data load that would have taken around 10 hours to move one table via an ODBC connection from SQL Server to ORACLE took only a few minutes via text export from SQL Server, and SQL*Loader with ORACLE. (I don't know if you consider a few hundred megabytes a large amount of data. I consider it "typical" of a single table. I had many such tables to transfer.)

Another problem I ran into was mixed-case table names that could be created in ORACLE by uploads from SQL Server. My recollection is that you need to surround the names with double-quotes, ", to get ORACLE to recognize the tables with the mixed-case names.

I don't recall trying UPDATE or INSERT statements. I always logged in to ORACLE to drop the tables that I needed to create to perform the data uploads when I used an ODBC connection for "small" tables (with only tens of thousands of rows).

I hope others here may have more direct experience with the UPDATE and INSERT issues you are contemplating.

Dan

Adding rows to Oracle of MsAccess from T-SQL?

Hi everyone,

I'd like to know how to update/insert rows to sources different than Microsoft.

Should I use OpenDataSource(), OpenRowSet() functions or they are deprecated with Sql25k?

Thanks in advance for your time and for your inputs,

Both OpenDataSource and OpenRowSet can be used for inserting and updating data in data sources other than SQL Server. They are both supported by SQL Server 2005. You can also use linked servers.

Please tell me if you need further information.

Best regards,
Sami Samir

|||

If you are moving millions of rows of data, I found the fastest way to move from Access or SQL Server to ORACLE was via an intermediate text file.

A data load that would have taken around 10 hours to move one table via an ODBC connection from SQL Server to ORACLE took only a few minutes via text export from SQL Server, and SQL*Loader with ORACLE. (I don't know if you consider a few hundred megabytes a large amount of data. I consider it "typical" of a single table. I had many such tables to transfer.)

Another problem I ran into was mixed-case table names that could be created in ORACLE by uploads from SQL Server. My recollection is that you need to surround the names with double-quotes, ", to get ORACLE to recognize the tables with the mixed-case names.

I don't recall trying UPDATE or INSERT statements. I always logged in to ORACLE to drop the tables that I needed to create to perform the data uploads when I used an ODBC connection for "small" tables (with only tens of thousands of rows).

I hope others here may have more direct experience with the UPDATE and INSERT issues you are contemplating.

Dan

Adding more tables than fit in the workspace

I have a report which needs 9 tables. I got the first three working but
when I went to insert the 4th - there was no "whitespace" for me to paste
into. Each of these sections will trigger a page break so maybe I just
overlap them on the screen? Is there a way to make the working space
bigger or some other solution? Thanks!Go to properties and select Body. Set the Size Height to what you desire.
"Don Parker" wrote:
> I have a report which needs 9 tables. I got the first three working but
> when I went to insert the 4th - there was no "whitespace" for me to paste
> into. Each of these sections will trigger a page break so maybe I just
> overlap them on the screen? Is there a way to make the working space
> bigger or some other solution? Thanks!
>
>
>|||Report Designer's layout surface can be resized by
* Dragging the right or bottom edge of the layout surface.
* Entering a new value for the body height or width using the properties
window.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> wrote in
message news:1CB6C09D-BF49-4B4F-AB7C-7CA57264CAA3@.microsoft.com...
> Go to properties and select Body. Set the Size Height to what you desire.
> "Don Parker" wrote:
>> I have a report which needs 9 tables. I got the first three working but
>> when I went to insert the 4th - there was no "whitespace" for me to paste
>> into. Each of these sections will trigger a page break so maybe I just
>> overlap them on the screen? Is there a way to make the working space
>> bigger or some other solution? Thanks!
>>
>>

Adding Leading Zeros to a char value - TSQL Question

Hi all:

I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))

SELECT @.SchoolCode_Modified AS Col1

The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:

CREATE PROCEDURE [LAF].[uspSaveLoanApplication]

...more params here

@.SchoolCode char(6),

@.BranchCode char(2),

@.PK int OUTPUT,

@.PreviousLoanApplicationStatus char(3) OUTPUT

AS

SET NOCOUNT OFF

SET @.PreviousLoanApplicationStatus = 0

IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus

END

-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting

DECLARE @.SchoolCode_Modified char(6)

DECLARE @.BranchCode_Modified char(2)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))

SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))

IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

UPDATE [LAF].[LoanApplication] SET

....more here

[SchoolCode] = @.SchoolCode_Modified,

[BranchCode] = @.BranchCode_Modified,

...more here

WHERE

[LoanApplicationID] = @.LoanApplicationID

AND [UpdatedOn] = @.LastUpdated

SELECT @.LoanApplicationID PK

SET @.PK = @.LoanApplicationID

END

ELSE

BEGIN

INSERT INTO [LAF].[LoanApplication] (

.....more here

[SchoolCode],

[BranchCode],

.....more here

) VALUES (

....more here

@.SchoolCode_Modified,

@.BranchCode_Modified,

....more here

)

SET @.PK = SCOPE_IDENTITY()

SELECT @.PK PK

END

With a quick scan, your code appears correct.

How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?

|||

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

|||

DanR1 wrote:

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

Good catch...

Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.

|||

Arnie,

Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)

So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.

Dan

|||

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = '123'

SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified

SELECT @.SchoolCode_Modified AS Col1

adding image dynamically to crystal report

hello
please could anyone help me to insert an image into crystal report if I saved the link in the database . I tried to change the type to "ole object" it works, but i don't have to change the type in my project.If you are using SQL Server then search for TextCopy.exe, you will get lot of related code.|||it is very easy in the Crystal 11.5 and later.

Place the OLE object -> Bitmap Image on the report. Right click the OLE Object -> Format Graphics -> Picture (tab) -> Graphic Location.

And enter the URL where the image file is placed.
Now the crystal Report will show the image on runtime by picking from the location mentioned.|||i'm using v8.0 , i'll download the XI version and I'll try