Identity Issue ???

[Jigabyte]

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!

[Hack]

What do you mean by 'handle'?

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

[Jigabyte]

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.

[Strider]

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

[Jigabyte]

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!

[petevick]

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

[Jigabyte]

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!

[Jigabyte]

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:-

            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:-

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

Thanks for any help,

Jiggy!

[Jigabyte]

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????

[Jigabyte]

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!

[petevick]

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

Pete

[Jigabyte]

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.

            '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!

[petevick]

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

[Jigabyte]

If I use :-

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.