@@IDENTITY... NEED HELP SOMEBODY (PLEASE)

Hi,

I really need help with a major problem I have encountered. I'm trying
to
insert information into a SQL Server 2000 database using stored
procedures.
And each table that has a Primary Key (int) won't return a value using
@IDENTITY. The error states that ArtistID doesn't except null
values
and will not insert into table. Here's the source code for the stored
procedure:

CREATE PROCEDURE ArtXchangeWeb.procManagerInsertArtist
@ArtistFName text,
@ArtistLName text,
@ArtistBirth text,
@ArtistDeath text,
@ArtistCountry text,
@ArtistNationality text,
@ArtistMovement text,
@ArtistBio text,
@ArtistPhoto varchar(255)

AS
BEGIN
INSERT INTO Artist(ArtistFName, ArtistLName, ArtistBirth, ArtistDeath,
ArtistCountry, ArtistNationality,
ArtistMovement, ArtistBio, ArtistPhoto)
VA
LUES(@ArtistFName, @ArtistLName, @ArtistBirth, @ArtistDeath,
@ArtistCountry, @ArtistNationality, @ArtistMovement, @ArtistBio,
@ArtistPhoto)
/* Return the ID of the new artist */
@IDENTITY
END
GO

If it doesn't give me the previous error it gives me a Input String not
the
right type error. Here's the code behind .vb:


If IsPostBack Then
'Save the new product to the database
With cmdManagerInsertArtist
.Parameters("@ArtistFName").Value = txtFName.Text()
.Parameters("@ArtistLName").Value = txtLName.Text()
.Parameters("@ArtistBirth").Value = txtBirth.Text()
.Parameters("@ArtistDeath").Value = txtDeath.Text()
.Parameters("@ArtistCountry").Value =
ddlCountry.SelectedItem.Text
.Parameters("@ArtistMovement").Value =
ddlMovement.SelectedItem.Text
.Parameters("@ArtistNationality").Value =
ddlNationality.SelectedItem.Text

.Parameters("@ArtistBio").Value = txtBio.Text()
.Parameters("@ArtistPhoto").Value = txtArtistImg.Text()
cnn.Open()
Session("ArtistID") = .ExecuteScalar
cnn.Close()
'And redirect to the management page
Server.Transfer("ManageArtist.aspx")
End With

If you can help me out that would be greatful. I wouldn't be a
programmer if
it wasn't for sites like yours and I've searched around for answer to
my
problem and to no avail I'm still stuck in the same spot. I hope you
can
help me.

Thanks,

Gregory Foreman

Comments

  • : The error states that ArtistID doesn't except null
    : values
    : and will not insert into table.

    It sounds like IDENTITY fields still require a value to be inserted into them. I've never used SQLServer, but I've used Informix, which has SERIAL fields. To get the next unique value, you just insert a zero into the SERIAL field. Perhaps your IDENTITY field works in a similar way?
  • Hi, Gregory
    Are You shore that this column is set as "identity column", from error message You are receiving, it is sure that this column do not allow NULL value and You are not inserting this value. To use automatic generation of values for that column, it is necessary to set that column as "identity column", therefore, to be sure check a property for identity column is it set or not, for this column (You can do it with "SQL Server Enterprise Manager".
    Damir

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories