Identity Issue ???

Hi Peeps,

I have had to add an Identity column to my sqlce table because it complains of no primary key when doing a delete row. The problem is how do I handle the identity in sqlCE? I know how to do it in OLEDB!

Any ideas please?

Jiggy!

What do you mean by 'handle'?

I assume the Identity column will be used as an autonumber, right?

Yes mate, I have wrote a routine which gets the MAX of my autonumber so I add one to it and assign that to my auto number.

in order to delete columns in a table you need some value that distinguishes each row from each other. therefore a primary key.
you can use a identity field where you provide the starting number and the incremental amount (100,1) means starts at 100 and increments by 1, there is no need to create a function for it

hope it answers what you are looking for

Hi Mate,

I created an identity field which started at 1 and incremented by one but it did not seem to work. I know in VB.NET you have to do SELECT @@ on the row update to get the next ID. In the end I changed the identity column to an int as primary key and used my function (Get MAX) to get the next ID.

Cheers for your help,

Jiggy!

What doesn't work?

You just need to do an insert, with a list excluding the identity field, and the identity field will sort itself out

Ah, I was using the command builder which will probably also work but in my select sql I used * which would have included the identity field. I will try without the identity field!

Still cant get this to work properly. If I delete a row and then insert one I get a concurrency issue. Here is my save code:-

Code:

---

            sqlDRStoreContact = sqlDSStoreContacts.Tables(0).NewRow
            sqlDRStoreContact("SC_ST_STORECODE") = cboStore.SelectedValue
            sqlDRStoreContact("SC_JT_CODE") = cboJobTitle.SelectedValue.ToString
            sqlDRStoreContact("SC_JT_NAME") = cboJobTitle.Text.ToString
            sqlDRStoreContact("SC_NAME") = txtContactName.Text.ToString
            sqlDRStoreContact("SC_RECORD_STATUS") = "NEW RECORD"

            sqlDSStoreContacts.Tables(0).Rows.Add(sqlDRStoreContact)
            sqlCBStoreContacts = New SqlCeCommandBuilder(sqlDAStoreContacts)
            sqlDAStoreContacts.Update(sqlDSStoreContacts, "SC_STORE_CONTACTS")

---

And here is my delete code:-

Code:

---

                sqlDSStoreContacts.Tables(0).Rows(dgContacts.CurrentRowIndex).Delete()
                sqlCBStoreContacts = New SqlCeCommandBuilder(sqlDAStoreContacts)
                sqlDAStoreContacts.Update(sqlDSStoreContacts, "SC_STORE_CONTACTS")

---

Thanks for any help,

Jiggy!

The thing is if I don't include the identity field it works on the delete but the update fails and if I dont include it the delete fails. How do I get them both to work????

OK I have used the insert command but this inserts it direct into the database which is great but I have a datagrid with a dataset bound to it and it does not show it there. Any ideas please

Jiggy!

Hi,
not without some explanation of the code, or seeing the code :)

Pete

My code is above mate. I think I know what is happening; if I do a new row that works fine and the identity number is stored in the database but I don't think it is returning to the dataset so when I delete that record I get a concurrency error. Here is the insert I tried which puts it into the database fine but not in my dataset so the bound grid looks like it hasn't worked.

Code:

---

            'Dim sqlInsertRow As SqlCeCommand = CeData.CreateCommand()
            'sSQL = "INSERT INTO SC_STORE_CONTACTS(SC_ST_STORECODE, SC_JT_CODE, SC_JT_NAME, "
            'sSQL = sSQL & "SC_NAME, SC_RECORD_STATUS) VALUES('" & cboStore.SelectedValue & "', "
            'sSQL = sSQL & "'" & cboJobTitle.SelectedValue.ToString & "', "
            'sSQL = sSQL & "'" & cboJobTitle.Text.ToString & "', "
            'sSQL = sSQL & "'" & txtContactName.Text & "', "
            'sSQL = sSQL & "'NEW RECORD')"
            'sqlInsertRow.CommandText = sSQL
            'sqlInsertRow.ExecuteNonQuery()

---

Thanks for getting back to me mate,

Jiggy!

Hi,
I am getting lost to what your problem is now. You asked about a datagrid not showing your record, and then show the insert code.

What exactly is your problem now please?

Thanks

If I use :-

Code:

---

sqlDSStoreContacts.Tables(0).Rows.Add(sqlDRStoreContact)
            sqlCBStoreContacts = New SqlCeCommandBuilder(sqlDAStoreContacts)
            sqlDAStoreContacts.Update(sqlDSStoreContacts, "SC_STORE_CONTACTS")

---

and then delete a that row I get a concurrency error so someone mentioned using the INSERT INTO method. The problem with that is it inserts it directly into the database and not my bound dataset.