Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

dataadapter update - extra parameter

I am currently working on windows application and using dblayer for all db related tasks. We use dataadapter for update. Basically the select/insert/update/delete command have a stored procedure of their own and the update works without any problem.

For insert and update, I would like to send extra parameters to the stored procedure. Those columns does not exist in the table. So I changed the underlying stored procedure and as well as in the corresponding dataadapter insert/update commands. But when I call the dataadapter.update, it errors out with a message that the extra columns are not passed.

Any suggestions/recommendations?

Thanks in advance

Tuesday, March 23, 2004

Post an example of the stored procedure and the code for your data adapter.  Excerpts will be fine.

Wednesday, March 24, 2004

Ok I solved the problem. Anyway, this is what I was trying to do.

Below code is sample code from PUBS which mimic my production code

Me.SqlInsertCommand1.CommandText = "[NewInsertCommand]"
        Me.SqlInsertCommand1.CommandType = System.Data.CommandType.StoredProcedure
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@phone", System.Data.SqlDbType.VarChar, 12, "phone"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@address", System.Data.SqlDbType.VarChar, 40, "address"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@city", System.Data.SqlDbType.VarChar, 20, "city"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar, 2, "state"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@zip", System.Data.SqlDbType.VarChar, 5, "zip"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@contract", System.Data.SqlDbType.Bit, 1, "contract"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@AuthorRating", System.Data.SqlDbType.NVarChar, 3, "AuthorRating"))

For the insert, I want to pass the last parameter @AuthorRating to stroed procedure. But AuthorRating is not a column in Author table. Please assume we have another table called AuthorRating where every author's rating is stored.

By passing this way, I can do 2 inserts in one db call. But the update was erroring out earlier as I did not give the "sourcecolumn name" as AuthroName. We have typed datasets for our project and so all update is done by calling dataadapter.update method.

ADO.NET truely rocks!

Thursday, March 25, 2004

*  Recent Topics

*  Fog Creek Home