computed column primary key does not allow nulls

[johnc83]

Hi again,

can someone point out where I am going wrong here pls..

I have a computed column (JobID) which takes the value of AutoID autonumber column and prefixes it with the letter 'A'. When I create a new record directly in SQL Server Management, it saves the record - gives the new AutoID number and populates the JobID field - no problem.

When I am trying to insert a new record using the VS tabladapter, it errors saying 'Column JobID does not allow nulls'. I can't exactly populate this myself as I don't know what the AutoID is going to be..

I'm not sure how to get around this.
Hope someone can help,

thanks John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Qamgine]

Okay, this is more of an SQL question, not VB.NET.

When you create a record in a table, the record identifier (AutoID) is actually NULL. It only gets assigned the unique ID "after" it has been saved. Seeing that JobID does not allow NULLs, the record wont "save".

Question, if you are just appending an A to every recordID (AutoID), why not just use the AutoID? For every record you enter, it will just be 1, 1A, 2, 2A, etc. It seems redundant as AutoID is Unique.

 

[johnc83]

Hi Qamgine,

I would love to have just the one column (AutoID) but I didn't think it was possible. All I have been able to do is make the AutoID column and then have computed column add the prefix.

If you could tell me how to make that column (also primary key) it would solve everything!

Just to make it clear, the column would look like this

AutoID
A1
A2
A3
A4


Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[earthandfire]

A primary key can consist of more than one column.

The values in each individual column making up the primary do not need to be unique, however the combined values do.
[tt]
AlphaField NumberField
A 1
A 2
A 3
B 1
B 2
B 3
[/tt]
As you can see, neither field is unique, but the combination of Alpha Field and NumberField is.


Hope this helps.

 

[johnc83]

Hi, thanks for the reply. Maybe I have mis-understood your concept but that is pretty much how I have it set up at the minute. I could really do with one column comprising of a prefix and an autonumber.

Im not sure if this is possible?

Thanks again

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[earthandfire]

Does it matter how many columns are in the table?

You can simply combine the fields when extracting the data.


Hope this helps.

 

[johnc83]

Hi Earthandfire,

sorry for delay in reply..
no it doesn't matter how many columns but the prefix number does need to be there in the actual column (as opposed to calculating it later).

reason for this is that I am planning to populate the table with similar data from other servers (example there will be a record with jobID as A12345 and another with B12345) without the prefix being there it would be the same number and I can't have that because it needs to be unique as it is a primary key.

needs to be a primary key for relationships reasons.

Hope that makes sense and someone can help.

Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Qamgine]

If you absolutely must do it that way then do this. You have to first add the record and then update it after it's committed.

1.) Allow the JobID to be NULL or allow dummy data like (1234).
2.) When you create the initial record, either leave JobID NULL or entered random information in there like "1". Capture the new recordID from the AutoID.
3.) After the record is added, do an immediate update on it and change your JobID to "<alpha char>" + new recordID.

 

[Sorwen]

Like Qamgine said. The reason is because an AutoID isn't created until the record has been saved/updated.

-I hate Microsoft!
-Forever and always forward.

 

[johnc83]

Hi All,

Thank you so much for your replies. I have got it to work by removing the 'DBNull' value from the default value of the column in tableadapter. This now puts a empty string in that column which then (after the autonumber has been created) convertes the empty string to A12345.

I have now ran into another problem though! :-(

Before, when I was using a normal autonumber, I would click save and it would immediately populate the AutoID textbox with the new number. Now when I do it, both the JobID (computed) and AutoID boxes are empty and I have to find out what the number by checking the database. I think I may have messed this up when I was changing stuff trying to resolve my original problem.

Im not sure how it automatically gave me the new AutoID in the first place so I don't really know where to check to fix it.

Can anyone tell me this?

Thanks again.

John



.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Kieran777]

I would leave the table adaptor in VB for that column as DBNULL (providing the column in the SQL Database allows NULL values), then I would implement the solution in SQL via a TRIGGER. You can set a TRIGGER in SQL to Update the field after the Save, when the identity value is known.

cheers Kieran777

 

[johnc83]

Hi Kieran, sorry but that won't work. My computed column is also the primary key (and needs to be the primary key for relationships) so I cannot have it NULL at any point.

Hope there is a solution to this! :-(

Thanks for the reply

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Sorwen]

Did you change the data type for that field? That will often do it. If you go into the table directly and add a record does it still AutoNumber it then?

-I hate Microsoft!
-Forever and always forward.

 

[johnc83]

Hi Sorwen,thanks for the reply.

In SQL Server I don't have an option to change the datatype of my computed column it is simply blank and the option to change it is greyed out. I could possibly ALTER column through script but what would I change it to?

If I add a new record directly in SQL, I enter all my relevant fields, leave autonumber and JobID blank and it accepts it and assigns the next autonumber and computes the jobID perfectly. I don't understand why doing it through my front end won't work!



.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Sorwen]

In SQL Server I don't have an option to change the datatype of my computed column it is simply blank and the option to change it is greyed out. I could possibly ALTER column through script but what would I change it to?

No, I meant the autonumber column, but if you are still able to manually enter a column and it still autonumbers then that wouldn't matter.

I'm not really sure what might be happening. I might have missed where you already said, but how is/was the data getting into the textbox is it bound. If so maybe the binding is messed up or bound to the wrong field now? Are all of the other text boxes showing the correct data or were those the only two? Maybe it is moving to a new record and so the fields would be blank? Just kind of throwing thoughts out.

-I hate Microsoft!
-Forever and always forward.

 

[johnc83]

I have made an adjustment which may help the situation..
I removed the computed formula from the JobID column so it is now just a varchar(50). Now that I have done that, I can create a new record and when I hit save, the AutoID textbox populates with the next integer in the DB. The JobID is simply populated with whatever I type in (xysj32k for example) so SQL simply doesn't like how I have set up that column..

If it helps, this is the formula in my computed Primary Key column

(isnull('A'+CONVERT([varchar](20),[AutoID],(0)),(0)))

Surely I am not the first person who has needed to implement this

thanks again for your time on this, really appreciated.

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Sorwen]

I think in some way you are coming back to the problem that the AutoNumber isn't created until the record is finished updating/saving. Keep this in mind I only know a little about Databases and binding. This much is certain at least when dealing with Access (Which I have to use more than any SQL databases).

When you just type anything in the one of the last things the table does is the AutoNumber. When you do a calculated field of course the calculations are the last thing that happens. Makes sense right. It could be because it has to do the calculations that it isn't passing the data back timely or something. It really sounds like something to do with the binding. I don't think bindings have a timeout or anything. Whether it is fixable I don't know. I quit messing with binding because I kept running into problems. I don't know if they were just my problems or bindings, but I just gave up.

Maybe someone who knows a little more about bindings will answer.

-I hate Microsoft!
-Forever and always forward.