@@Identity.....Help Me somebody
-
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 SELECT @@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) VALUES(@ArtistFName, @ArtistLName, @ArtistBirth, @ArtistDeath, @ArtistCountry, @ArtistNationality, @ArtistMovement, @ArtistBio, @ArtistPhoto) /* Return the ID of the new artist */ SELECT @@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
-
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 SELECT @@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) VALUES(@ArtistFName, @ArtistLName, @ArtistBirth, @ArtistDeath, @ArtistCountry, @ArtistNationality, @ArtistMovement, @ArtistBio, @ArtistPhoto) /* Return the ID of the new artist */ SELECT @@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
Can you send us the table definition? It seems that the ArtistID column is not an IDENTITY column.. Could you check it? Another tip: MS does not recommend you to use the @@IDENTITY for this, because it has a nasty definition that causes some weird bugs with triggers. Always use the SCOPE_IDENTITY() function. Q261186 - Computer Randomly Plays Classical Music
-
Can you send us the table definition? It seems that the ArtistID column is not an IDENTITY column.. Could you check it? Another tip: MS does not recommend you to use the @@IDENTITY for this, because it has a nasty definition that causes some weird bugs with triggers. Always use the SCOPE_IDENTITY() function. Q261186 - Computer Randomly Plays Classical Music
Yes, the ArtistId can't be an IDENTITY column if that message appears. He should also consider using a different datatype than the all-mighty "text" for his input parameters. Morty
-
Yes, the ArtistId can't be an IDENTITY column if that message appears. He should also consider using a different datatype than the all-mighty "text" for his input parameters. Morty
Morten Abrahamsen wrote: He should also consider using a different datatype than the all-mighty "text" for his input parameters. God! I didn't even noticed that! :eek: :omg: Q261186 - Computer Randomly Plays Classical Music
-
Morten Abrahamsen wrote: He should also consider using a different datatype than the all-mighty "text" for his input parameters. God! I didn't even noticed that! :eek: :omg: Q261186 - Computer Randomly Plays Classical Music
Yeah.. and that sort of sucks because that specific type is (sometimes?) transferred differently than normal SQL statements/types in order to handle the large amount of data it MIGHT contain. (2 147 483 647 bytes to be exact) ;) Morty