Identity problems(hihi) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identity problems(hihi)

Author

Topic

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-01 : 10:12:34

It`s not a laughting matter anyway,but I couldn`t resist.
I have a fully working database,trying to connect it to a Detailview in VWD.I have now tried three times to make the table,set it to Identity and refreshed everytime,and the table has been empty,just setup the Primarykey and added the data after I have set up Identity and refreshed,and refreshed again when the data is inserted(Just in case).

And than I have made a Detailview in VWD:
<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AutoGenerateRows="False" DataKeyNames="SalgID" DataSourceID="SqlDataSource2">
<Fields>
<asp:CommandField ButtonType="Button" DeleteText="Slett" NewText="Lagre"
ShowDeleteButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>

And my insert code is:
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:BCDConnectionString13 %>"
SelectCommand="SELECT * FROM [Salg]"
InsertCommand="INSERT INTO Salg(SalgID, Dato, Antall) VALUES ('Dato', 'Antall', 'Omsetning')"></asp:SqlDataSource>

But everytime I try to do the query,I get Identity_off.
Please help me,I have allmost given up.

And maybe the code should be INSERT INTO Salg(Dato, Antall, Omsetning) VALUES ('Dato', 'Antall', 'Omsetning') since those are the fields I want to insert into?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-01 : 10:20:58

Could you post your table DDL?

Jack Vamvas
--------------------
http://www.ITjobfeed.com (IT jobs)

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-01 : 11:14:53

Sorry,what`s DDL?

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-01 : 11:21:41

If DDL is Data Definition Language it`s SQL and ASP.NET in VB.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 11:44:07

He wants to see the create statement of your table.

No, you're never too old to Yak'n'Roll if you're too young to die.

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-01 : 12:40:58

Where do I find the codebehind in VWD?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 12:43:42

Sorry but I don't know what is VWD...

No, you're never too old to Yak'n'Roll if you're too young to die.

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-01 : 12:50:46

Visual Web Developer,I have also set the database up in SQL Server.

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-01 : 12:55:01

Sorry,I found the code in SQL Server:

USE [BCD]
GO
/****** Object: Table [dbo].[Salg] Script Date: 01/01/2010 18:52:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Salg](
[SalgID] [int] IDENTITY(1,1) NOT NULL,
[Dato] [int] NULL,
[Antall] [int] NULL,
[Omsetning] [int] NULL,
CONSTRAINT [PK_Salg] PRIMARY KEY CLUSTERED
(
[SalgID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 13:03:52

Your posted insert statement:
INSERT INTO Salg(SalgID, Dato, Antall) VALUES ('Dato', 'Antall', 'Omsetning')
is wrong.
1. SalgID is an identity and you should not insert anything.
2. All other columns are INT so you cannot insert strings like 'Antall' and so on.

About 1.: INSERT INTO Salg(~~SalgID,~~ Dato, Antall) VALUES (...)
About 2.: INSERT INTO Salg(~~SalgID,~~ Dato, Antall) VALUES (47,299) / but I don't know which values are needed...

No, you're never too old to Yak'n'Roll if you're too young to die.

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-01 : 14:14:05

Thank you :)SalgID is PK(which is set to Identity with Increase +1).
But I get an error when I put up the new code,Conversion failed when converting the varchar value 'Dato' to data type int.

Insert code is now:
INSERT INTO Salg(Dato, Antall, Omsetning) VALUES ('Dato', 'Antall', 'Omsetning')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 15:46:40

quote:
2. All other columns are INT so you cannot insert strings like 'Antall' and so on.

No, you're never too old to Yak'n'Roll if you're too young to die.

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-01 : 21:14:23

Thanks,what kind of datatypes should they be?They are all numbers.
Dato is date,like 22052009.Antall is amount.Omsetning is profit.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-01 : 21:38:20

Make Dato a datetime data type then. For Antall and Omsetning perhaps you want money data type.

Then: INSERT INTO Salg(Dato, Antall, Omsetning) VALUES ('05222009', 19.95, 10.25)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-02 : 07:38:50

Thanks guys for beeing patient with a newbie like me.
I tried to change Dato to Datetime,but I got an error when I tried to save:
'Salg' table
- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Does that mean that Dato has to be empty before I change it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 07:41:21

it means you already have some data in Dato which cant be converted to valid datetime format. what does below return?

select Dato FROm Salg WHERE ISDATE(Dato)=0

lars_toreh
Starting Member

29 Posts

Posted - 2010-01-02 : 08:16:09

Thank you,it`s just a simple table,so I just redo it :)
Should date look like this 23/04/2009 or 23-04-2009?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-02 : 08:19:35

Best way to insert a date value like 23.04.2009 is: '20090423'

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 08:23:13