Primary Keys are important to uniquely identify values in a table. A standard integer based primary key is fine for most databases.
Sometimes more than one unique identifier is implemented in a database table. This second unique identifier may be used to reconstruct records or be passed down to child tables.
In the event you do plan to implement two unique keys using an MSSQL database it can be done using triggers.
That means every time you add a new row record a GUID (e.g. 14E89334-05D0-4D1B-B85D-BFF7251AE41A ) gets created and inserted by the database to the table field automatically.This value is set to NULL in the create table script, so you are not forced to enter a GUID as the new row is added via ANM10.
e.g. ] [uniqueidentifier] NULL,In ANM10 you can leave the “required” checkbox empty based on the “NULL” setting not requiring data to be entered on row insert…
In ANM10 you can deselect the “Add” checkbox so the empty field is not visible when you Add/Insert the new record
In ANM10 you can Select the “Edit | Read Only” checkbox to ensure the GUID does NOT get modified during editing.
In ANM10 DO NOT select the GUID as the 2nd Primary Key in the Table properties checkbox.A sample MSSQL trigger to do this is shown below;
CREATE TRIGGER
_GUID_Trigger ON [Schema].[Table] AFTER INSERT AS BEGIN
UPDATE [Schema].[Table] SET [] = NEWID() FROM INSERTED
END
The following proposed method is the preferred solution as it will eliminate extra SQL code dependencies (i.e. A Trigger will NOT be required).
Please note the post subject should be amended to read “Create GUID Dynamically in MSSQL as 2nd Unique Identifier” because it is NOT being set as a Primary Key in either the database or ANM10.If your ANM10 Web Application is the only interface used to ADD new records then you can use the “Row_Inserting” event to perform this task using the following code.
Every time a new row is ADDed it will dynamically create the GUIDIt is important to note that the GUID field in your database table is set to NULL, so you do not get a “Required” error.
It is also important to note that the GUID field in your MSSQL database table can also be of TYPE ROWGUIDCOL instead of the default uniqueidentifier;
eg [] [uniqueidentifier] ROWGUIDCOL NULL, --optional
eg [] [uniqueidentifier] NULL, --preferred (less code)This method works on every Row Insert (aka ADD) event. ANM10 settings advised in the previous post still apply.// Row Inserting event
public bool Row_Inserting(OrderedDictionary rsold, ref OrderedDictionary rsnew) {
rsnew[“”] = System.Guid.NewGuid();
return true;
}
In some cases you may have added a GUID field after the table was created or the GUID field has not been populating after each edit even though the MSSQL default values (i.e. newid()).
To generate a GUID where no GUID value exists for a row it is possible to use the Row_Updating event while EDITing.
To do this a check must be done to see if a GUID value already exists. The Execute Scalar approach is used to identify ONLY the row that is being EDITed.// Row Updating event
public bool Row_Updating(OrderedDictionary rsold, ref OrderedDictionary rsnew) {
object GUID;
GUID = ew_ExecuteScalar(“SELECT FROM .
WHERE = " + Convert.ToInt32(.EditValue));
if (GUID != “”) {
//do nothing
}
//if no guid exists a guid will be added
rsnew[”"] = System.Guid.NewGuid();
return true;
}
The Row_Updating Event has been modified in this example to integrate improved logic that eliminates generation of NEW Unique GUIDs if a GUID already exists for the ChildGUID. This POST is related to the Row_Rendered Event Update described in the second post of this topic; http://www.hkvforums.com/viewtopic.php?f=45&t=32359This example has been advanced somewhat due to the need to store 2 GUIDs. One Unique and one from the Master Table.
It enables you to EDIT a child record and GUID generation occurs automatically.
It enables you to ADD a child record and GUID generation occurs automatically.
Works with both Grid Add and Grid EditIt does NOT require EDIT | Read Only checkboxes to be checked as discussed in the related topic.Warning: The Unique GUID field (in this case “OrderGUID”) will throw an error if you select the “Check Duplicate” checkbox for the GUID Field.
It should not required, especially since they are GUIDs and are supposed to be unique after all.To customise replace “OrderGUID” with your GUID field and place the code in the Row_Updating event.// Row Updating event
public bool Row_Updating(OrderedDictionary rsold, ref OrderedDictionary rsnew) {
}
// Row Updating event FINISHCODE BELOW Completes the Solution To inheriting the ParentGUID and Generating a New Unique ChildGUID if RequiredSAME FIELD Row_Rendered EVENT// Row Rendered event
public void Row_Rendered() {
if (ClientFunctionGUID.EditValue != "")
{ //Get GUID (PK) from Master Table to Populate the Child Table Field (FK)
ClientFunctionGUID.EditValue = ew_ExecuteScalar("SELECT ClientFunctionGUID FROM VCWAMC.CD0ClientFunctions WHERE ClientFunctionID=" + ClientFunctionID.CurrentValue);
ClientFunctionGUID.ReadOnly = true;
OrderGUID.ReadOnly = true;
}
else
{
ClientFunctionGUID.ReadOnly = true;
OrderGUID.ReadOnly = true;
}
The example uses ALL Server Side events and No Client Side events.
I have another post “5 Ways…GUID…” that details use of client side scripts.
Hope this helps
dont generate yoru primary key on your own. make the db do it automatically and set the field to primary key… auto increment… generating it your self and maintaining its integrity would mean yould have to pull the entire field and check the new value against all the results and if it is already there yould have to regenerate and recheck untill a unique value is genreated which would make your script really slow.