AutoIdent

[Gary Townsend \(Spatial Mapping Ltd.\)]

I am doing some data entry on a form which also a master/detail form. I have
2 textboxes and a dropdown box which allows you to enter the master
information for a "Route". The data for the route is fetched from a PostGres
database using NPGSQL objects for the data interface. This information is
stored in a dataset.

When i insert multiple records into this dataset i get a
"System.Data.ConstraintException: Column 'route_id' is constrained to be
unique. Value '' already exists" error I've read about the idea of setting
the incremement within the dataset to -1 so that this wouldn't be a problem.
However, this causes a problem when someone is inserting information then
into the details section of the form the master identity they end up sending
to the database is -1,-2,-3 etc...

Is there anyway to fetch the identity for each row that is inserted and
return that to the dataset i tried to create a stored procedure and return a
value and then bind that outbound parameter to the route_id column with no
success this was the code i used to try that.

RouteAdapter.InsertCommand = New
NpgsqlCommand("vts_insert_route(:a,:b,:c)", PGConnect)
RouteAdapter.InsertCommand.CommandType =
CommandType.StoredProcedure
With RouteAdapter.InsertCommand
.Parameters.Add(New NpgsqlParameter("a", DbType.String))
.Parameters.Add(New NpgsqlParameter("b", DbType.Int32))
.Parameters.Add(New NpgsqlParameter("c", DbType.String))
.Parameters.Add(New NpgsqlParameter("returnvalue",
DbType.Int32))
.Parameters(0).Direction = ParameterDirection.Input
.Parameters(1).Direction = ParameterDirection.Input
.Parameters(2).Direction = ParameterDirection.Input
.Parameters(3).Direction = ParameterDirection.ReturnValue

.Parameters(0).SourceColumn = "route_name"
.Parameters(1).SourceColumn = "school_id"
.Parameters(2).SourceColumn = "route_description"
.Parameters(3).SourceColumn = "route_id"
End With

Unfortunatley i can change the structure of the database or i would have
made the route_id a GUID.

------------------------------------
Gary Townsend
Database Developer
Spatial Mapping Ltd.
garyt[NADDASPAM]spatialmapping.com
remove [NADDASPAM] for email.

[William \(Bill\) Vaughn]

I wrote an article "Managing an Identity Crisis" some time ago. It expect
that covers what you need.
See http://www.betav.com/msdn_magazine.htm
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Gary Townsend (Spatial Mapping Ltd.)" <ga************@spatialmapping.com>
wrote in message news:zLgvf.48510$OU5.42082@clgrps13...

I am doing some data entry on a form which also a master/detail form. I
have
2 textboxes and a dropdown box which allows you to enter the master
information for a "Route". The data for the route is fetched from a
PostGres
database using NPGSQL objects for the data interface. This information is
stored in a dataset.

When i insert multiple records into this dataset i get a
"System.Data.ConstraintException: Column 'route_id' is constrained to be
unique. Value '' already exists" error I've read about the idea of
setting
the incremement within the dataset to -1 so that this wouldn't be a
problem.
However, this causes a problem when someone is inserting information then
into the details section of the form the master identity they end up
sending
to the database is -1,-2,-3 etc...

Is there anyway to fetch the identity for each row that is inserted and
return that to the dataset i tried to create a stored procedure and return
a
value and then bind that outbound parameter to the route_id column with no
success this was the code i used to try that.

RouteAdapter.InsertCommand = New
NpgsqlCommand("vts_insert_route(:a,:b,:c)", PGConnect)
RouteAdapter.InsertCommand.CommandType =
CommandType.StoredProcedure
With RouteAdapter.InsertCommand
.Parameters.Add(New NpgsqlParameter("a", DbType.String))
.Parameters.Add(New NpgsqlParameter("b", DbType.Int32))
.Parameters.Add(New NpgsqlParameter("c", DbType.String))
.Parameters.Add(New NpgsqlParameter("returnvalue",
DbType.Int32))
.Parameters(0).Direction = ParameterDirection.Input
.Parameters(1).Direction = ParameterDirection.Input
.Parameters(2).Direction = ParameterDirection.Input
.Parameters(3).Direction = ParameterDirection.ReturnValue

.Parameters(0).SourceColumn = "route_name"
.Parameters(1).SourceColumn = "school_id"
.Parameters(2).SourceColumn = "route_description"
.Parameters(3).SourceColumn = "route_id"
End With

Unfortunatley i can change the structure of the database or i would have
made the route_id a GUID.

------------------------------------
Gary Townsend
Database Developer
Spatial Mapping Ltd.
garyt[NADDASPAM]spatialmapping.com
remove [NADDASPAM] for email.