[2005] identity fields don't take values and don't take nulls in an insert?!?!

it looks like there really is a problem with my insert. QuizID is an identity column that increments automatically so I'm uploading a table in this style:

VB Code:

strSQL = "INSERT INTO tblQuiz(Description,QuizCreated) VALUES (@Description, @QuizCreated)"
            Adapter.InsertCommand = New SqlClient.SqlCommand(strSQL, Connection)
            Adapter.InsertCommand.CommandType = CommandType.Text
            Adapter.InsertCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 50, "Description").SourceVersion = DataRowVersion.Current
            Adapter.InsertCommand.Parameters.Add("@QuizCreated", SqlDbType.DateTime, 8, "QuizCreated").SourceVersion = DataRowVersion.Current
            Adapter.Update(tblQuiz)

with item(1) in that table object being null because our instructor said "never try to set an identity field, the database will do it for you" Well the database is being an ass today because it says "cannot inser the value NULL into column 'QuizID', table [longfilename I'm not typing]; column does not allow nulls. INSERT fails. The statement has been terminated.

So how exactly do I get this table added to the table in the database then?

Normally you don't leave identity fields null in a DataTable either. You just don't insert the value they contain into the database. First of all you have to have create your QuizID column as an identity when you created the database. The error message you're getting sounds to me like you haven't done that. See the attached screen shot.

Secondly, you would normally create the corresponding DataColumn as an identity column too. You set its AutoIncrement property to True and the AutoIncrementSeed and AutoIncrementStep to match the database. Now when you add a row to your DataTable it will automatically generate an identity value and assign it to the QuizID field. It may or may not be the same value as eventually gets generated by the database but that doesn't matter. You have a unique value that you can use as a primary key for the time being.

When you actually update the database with the new data you don't include the identity column in the INSERT statement. The database will generate a real value for the identity column of each new row. You then refresh your DataTable if required, which can be done automatically if you've set up your DataAdapter correctly, and the generated value is now included in your local data.

Well it is set correctly as a 1 +1incrementing identity in the database which is good (our teacher did leave like 3 huge errors in the database though cuz he's eeeeeeevil :( ) and the way I generate the table in the first place is with this command:

VB Code:

strSQL = "select * from tblQuiz where QuizID = -1"
        Adapter.SelectCommand = New SqlClient.SqlCommand(strSQL, Connection)
        Adapter.Fill(tblQuiz)

which returns an empty table with all the columns being the correct datatype (pretty smart huh! :bigyello: ) then create new rows in that empty table from what's in the csv file. So you're saying those settings didn't stick for the table that statement passed back so I'd have to do this:

VB Code:

Adapter.Fill(tblQuiz)
        tblQuiz.Columns(0).AutoIncrementSeed = 1
        tblQuiz.Columns(0).AutoIncrementStep = 1
        tblQuiz.Columns(0).AutoIncrement = True
        tblQuiz.Columns(0).Unique = True

I'm surprised there was no tblQuiz.Columns(0).isIdentity property

Or should I just not select the identity fields in the first place so when I upload the table it will only be a table containing the 2 other columns?

omfg I just realised that no matter how I solve the problem, the data that matches Answers to Questions will be gone because each answer record links to a QuestionID which is also an identity and the amount of answers for each question varies from 2 to 5. So there's literally no way to do this. I swear I'm gonna fill my teacher's office with rotten bananas for even including some [Removed by Mod] requirement like this to the project.

Man, you really do fly off the handle at the slightest provocation don't you? You should keep even implied cursing to a minimum. This is a very common scenario and your teacher hasn't set you anything impossible or even unusual.

Like I said, once you save the new records to database you refresh your DataTable with the autogenerated values and any related tables will automatically use the new values.

Now, I'm not sure exactly what's going on at your end but as long as you have set the properties of the column in the database properly I can't see any way that a null value could be inserted into your column.

Also, those folks at Microsoft are way ahead of you:

VB Code:

strSQL = "select * from tblQuiz"
        Adapter.SelectCommand = New SqlClient.SqlCommand(strSQL, Connection)
        Adapter.FillSchema(tblQuiz)

well that's a handy solution with, as I showed, only half the needed backup methods in case it doesn't work. What if there's a database that doesn't support that method? Well anyway, I was still completely correct in saying it's impossible. The Answer data is imported like this:

20,Bubba Franks,1,False,
20,Brett Favre,2,True,
20,Donald Driver,3,False,
20,Nobody,4,False,
21,black,1,True,
21,red,2,False,
21,purple,3,False,

where that first number is the QuesID from the Question table. That column is an identity so it gets reset one way or another as a different set of values. Let's say the questions table is imported and gets 42 and 43 assigned as their new QuesID's. There's no way to match up the questions to it now. Originally I asked for the highest QuesID back then found the difference between the old and the new ID for each question in the test, added that amount to the QuesID on each answer and they matched. Can't do that now though because the original QuesID gets wiped. And if you're thinking to split the answers into multiple tables, one for each set with the same QuesID, I'd either have to hard code an add function for every table created that way because otherwise I wouldn't be able to add them all in realtime because that goes back to the getting the max QuesID problem again. So yeah, it's impossible. Even if I'm missing something...well...I'll get back to you on how many people in my class have the whole thing completely working.
As for my irritability, see my last post. This is bound to be the yearly day from hell no matter what happens during it, this is just making it worse. Severe drug withdrawal + 10 hours of programming + 5 hours of sleep = irritable Desolator :eek2:
P.S. now I get to start modifying an entire DB2 database for a project due tomorrow morning on an AS400 (green screen and no mouse = evil :cry: ) using SQL commands that apparently don't work on any other DBMS. Of course, if you'd be so kind as to blow up my school, I'd be fine tomorrow (minus the continued withdrawal)

Quote:

---

Originally Posted by Desolator144

well that's a handy solution with, as I showed, only half the needed backup methods in case it doesn't work. What if there's a database that doesn't support that method?

---

It's still not impossible. If you have answers to questions 20 and 21 then you add questions with IDs 20 and 21 to your Question table and the answers to your Answer table. Your DataSet would require a dataRelation that propagates updates. Now, after you insert the Question data you refresh you DataTable and it gets updated with the ID values generated by the database. The DataRelation propagates the changes to the Answer DataTable and those records now have question IDs matching the values in the database. You insert the Answer records and all is well with the world.

so I add both tables locally to a dataset with questions as table 0 answers as table 1 and pass that to my database operations class (aka the databasemonkey) and do something like adapter.update(TheDataset.tables(0)) and it will automatically update the answers table as long as I have the relationship setup correctly? (speaking of that, how do you add a relationship object and configure it to do that?) btw I was correct in saying it was impossible to do because impossible to do [in this project] was implied and if I'm the one doing the project and I didn't know how to do that then it is impossible. But hopefully now it's not.