Updating Rows in a DataSet

[Cornstarch]

I updated my data into the appropriate columns in the Datarow by building a DataAdapter, a CommandBuilder object, and retrieved the Update command object. After that, I submitted the Update statement via the Update method of the DataAdapter...it worked fine except for one thing.
It can't save the data into the table unless I provide a unique number to the table's primary key in my code. The table's PK already ha a built in number generator (it gives a unique number to the PK column whenever a row is added). I can manually insert a row into the table w/o giving a number to the PK column at the SQL prompt but I can't do it from the VB.NET code.
Here is the error message:

"An unhandled exception of type 'System.Data.NoNullAllowedException' occurred in system.data.dll Additional information: Column 'OWNER_ID' does not allow nulls."

Thank you in advance for your help.
Ann

 

[Cornstarch]

Please ignore this message. The code now works fine. Ann

 

[Gibgob]

I am having the same problem as you previously had. It seems that you fixed it. Could you let me know how?

Many thanks

Mark

 

[Cornstarch]

Mark:

The message I posted was meant for inserting and
not for updating a record. I did not have a problem
with updating a record because the primary key will
not change if a record is updated/modified.

I temporarily solve the insertion problem by getting
around my code...but it's not a method I would recommend
because it's not clean and does not work in a certain case.

Ann

 

[Schweiger]

Mark:

Did you try to supply any number for the PK? To me it worked, the error message didn't show up and the supplied value was ignored...

Schweiger

 

[JohnEfford]

Assuming your primary key is an identity column you do should not supply to it SQL server as it will be generated for you. But you do need to return the value to program so that your user can perform updates on the new record.

here is how I typically perform this function

SQL:

Create Procedure InsertSomthing
    @SomthingID int output --Identity column automatically produced by the server
    @SomthingInfo as varchar(25)
as 
Begin Tran
    Insert into Somthing (SomethingInfo) Values (@SomthingInfo) --Do the isert
Set SomthingID as @@identity  --Get last Identity used
Commit Tran

VB.Net

Const ConnectionString = "Your Connection Info"
Private DaSomthing As New SqlDataAdapter()
Private SQLCon as new SQLConnection(ConnectionString)
Private DTSomthing as New DataTable


Private DtSomthing_Init
‘Setup the DtSomthing table in code
DtSomthing = New DataTable()
        With DtSomthing.Columns
            .Add(“SomthingID”)
            .Add(“SomthingInfo”)
        End With
End Sub

Private Sub DASomthing_Init
'Set up Da Adapter commands
'Insert Command
DASomthing.InsertCommand = New SqlCommand("InsertSomthing",SQLCon)

With DASomthing.InsertCommand
 .CommandType = CommandType.StoredProcedure
           
'Set up the paramters
            .Parameters.Add(“SomthingID”, SqlDbType.Int)
            .Parameters(“SomthingID”).SourceColumn =  “SomthingID”
.Item(“SomthingID”).Direction = ParameterDirection.Output

            .Parameters.Add(“SomthingInfo”, SqlDbType.VarChar)
            .Parameters(“SomthingInfo”).SourceColumn = “SomthingInfo”
End With 
End Sub

‘Now to insert record
‘Begin the insert
	Dim NewRow As DataRow = DtArea.NewRow()
      DtArea.Rows.Add(NewRow)

‘End insert
      NewRow(“SomthingInfo”) = "A value"  ‘ 
      DtArea.AcceptChanges()
      DaArea.Update(DtArea)

The new SomethingID will now be in the data table.

Regards John

 

[Gibgob]

Thank you all for your replies.

With a little more research, I discovered it was a problem with the Sybase ASE OleDb 12.5 driver. The ident thing works under the 12.0 driver!!!!

I now have extra code to reread from the database on every insert so I can apply the new identity value - and this is only run for 12.5 drivers. It's inefficient but there are few inserts to this table so it's not too bad.

If only we used SQL Server then I could use a native .NET driver. Uh well - dream on....

Mark

 

[Cornstarch]

Here is a short cut to go about getting a PK from a
table w/o using stored procedure:

Dim cn As SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand()
Dim PK As Integer

cn = New SqlClient.SqlConnection("Data Source=......&quot //Your connection string here

cn.Open()

cmd.CommandText = "Select max(Customer_id) from Customer"
cmd.Connection = cn

PK = cmd.ExecuteScalar //Return the largest PK from the Customer table

PK += 1 //Add 1 to the PK, your next PK

cmd.Dispose()
cn.Close()

Ann

 

[rohitt]

What if I want this pk to be genearted from my dataset.

datatable.select(max(columnname))

I tried this but it gives exception..is something rong wid the syntax..??

-----------------------------------
Its nice to be important, but it's more important to be nice.