Write Conflict - cascading combo boxes ADP

[AMH]

Hi everyone,


I'm building a client/server DB with Access as the front end and SQL
Server as the backend. I have a subform in which I'm using cascading
combo boxes, however my design seems to be failing! I've found
several examples, and have used them.


The cascading does work, but everytime I insert a new record on the
subform, I
get a "Write Conflict" error, asking me to either undo, copy to
clipboard, or drop changes. Regardless of what I pick, the record is
entered anyway. It seems that it's trying to save the data twice in
one go.... Deleting and updating any of the existing records does not
cause this problem - only inserts.

Here is the code behind the 1st combo box:

______________________________________
Private Sub rm_code_AfterUpdate()


On Error Resume Next
gin.RowSource = "Select distinct batchingred.gin " & _
"FROM batchingred " & _
"WHERE batchingred.rm_code = '" & rm_code.Value & "' " & _
"ORDER BY batchingred.gin;"
End Sub
______________________________________
______________________________________

As I said, the cascade works fine, just not the addition of a new
record. "batchingred" is a stored view, as the form needs to access
two tables at once to display the combo values. I have set the form to

access all data from this view. Data is only updated to one of the
base tables in this view.

Hope someone can help.

 

[Sylvain Lafontaine]

Yes, it's quite possible that ADP might try to save your record twice. As
it use optimistic locking, the second call will fail because the null values
used to validate the first call are no longer valid when the second call is
made.

Using the SQL-Server Profiler will quickly give you a confirmation of this.

I think that ADP has the very bad habit of requerying all other controls and
the subform as well each time you change the record source of a single
control with a Select statement.

A possible solution for you would be to replace the Select statement with a
stored procedure or with an Exec call to a stored procedure, something like:

Me!MySubForm.Form!MyCombBox.RowSource = "EXEC MyStoredProcedure 1, 2, 3 "

You can also try making an EXEC on the Select statement. With ADP, we
never know what's work and what don't.

 

[AMH]

Hi Sylvain,

I replaced the Select statement in the VBA code with a stored
procedure:

SELECT DISTINCT gin
FROM dbo.batchingred2
WHERE (rm_code = @rm)
ORDER BY gin

in the stored procedure designer in Access. I ran the procedure,
entered a value for @rm and it worked fine.

Then I replaced the VBA code with the following on the "rm_code"
control on the actual subform:

__________________________________________
Private Sub rm_code_AfterUpdate()

On Error Resume Next
gin.RowSource = "EXEC spGIN Me![rm_code].Value"

End Sub
_______________________________________________________

I also tried a few variations on this code. I get the following error
message: "Invalid SQL statement. Check the server filter on the form
or record source."

Apologies, my skills at coding are particularly poor.
Any suggestions on improving it?

 

[Sylvain Lafontaine]

You must replace Me![rm_code].Value inside the string with the real value
before making the assignment:

gin.RowSource = "EXEC spGIN " & Me![rm_code].Value

Make sure that you don't forget any blank space. Finally, I will repeat my
suggestion that you try the SQL-Server Profiler to see what's really
happening. You might have a suprise sometimes, especially with ADP.

 

[AMH]

I tried that statement too, but no change, and my dependent combo shows
no values at all. Still get the Invalid SQL statement error. Here is
the data from SQL profiler:
__________________________________________________________________________________________________________

SELECT "batchingred2"."batch_id", "batchingred2"."gin",
"batchingred2"."total_qty_g", "batchingred2"."rm_code" FROM
"batchingred2" //This is the opening of the subform

SET ROWCOUNT 0
EXEC sp_MShelpcolumns N'batch_ingredients', NULL, N'id', 1
EXEC sp_MShelpcolumns N'inventory', NULL, N'id', 1
SET NO_BROWSETABLE OFF
EXEC sp_MShelpcolumns N'dbo.batch_ingredients' , NULL, 'id', 1
EXEC sp_MShelpcolumns N'dbo.inventory' , NULL, 'id', 1

SELECT DISTINCT rm_code FROM batchingred2 //This is getting
the rm_code values from the batchingred2 view for the controlling combo
box.
EXEC spGIN SF-1155
//This is selection of "SF-1155" value from the rm_code combo box.

SET FMTONLY ON select "batch_id","gin","total_qty_g" from
"INSIGHT"."dbo"."batch_ingredients" SET FMTONLY OFF //??
SET NO_BROWSETABLE ON
declare @P1 int //??
set @P1=76 //??

exec sp_prepare @P1 output, N'@P1 int,@P2 char(9),@P3 numeric(6,1)',
N'INSERT INTO "INSIGHT"."dbo"."batch_ingredients"("batch_id", "gin",
"total_qty_g") VALUES(@P1,@P2,@P3)', 1
//Prepare for insert of data to the base table of the view

select @P1
exec sp_execute 76, 12345, '345 ', 10.0
//These are the values entered for the remaining fields in the subform

exec sp_executesql N'UPDATE "INSIGHT"."dbo"."inventory" SET
"rm_code"=@P1 WHERE "rm_code" IS NULL AND "gin"=@P2', N'@P1
varchar(7),@P2 varchar(1)', 'SF-1155', ''
//not sure why this is being done - i don't want the inventory table
updated with a new value.

exec sp_executesql N'SELECT * FROM ( SELECT "batchingred2"."batch_id",
"batchingred2"."gin", "batchingred2"."total_qty_g",
"batchingred2"."rm_code" FROM "batchingred2" ) AS DRVD_TBL WHERE
"batch_id" = @P1 AND "gin" = @P2', N'@P1 int,@P2 varchar(9)', 12345,
'345 '

//This statement occurs another 2 times....why?

exec sp_executesql N'SELECT * FROM ( SELECT "batchingred2"."batch_id",
"batchingred2"."gin", "batchingred2"."total_qty_g",
"batchingred2"."rm_code" FROM "batchingred2" ) AS DRVD_TBL WHERE
"batch_id" = @P1 AND "gin" = @P2', N'@P1 int,@P2 varchar(9)', 12345,
'345 '

exec sp_executesql N'SELECT * FROM ( SELECT "batchingred2"."batch_id",
"batchingred2"."gin", "batchingred2"."total_qty_g",
"batchingred2"."rm_code" FROM "batchingred2" ) AS DRVD_TBL WHERE
"batch_id" = @P1 AND "gin" = @P2', N'@P1 int,@P2 varchar(9)', 12345,
'345 '

exec sp_unprepare 76


Never used profiler before......I'm assuming my problem is coming from
the fact that rm_code is trying to update in the inventory table (which
I don't want it to do, and from the fact that the last statement is
running three times - although I don't understand why this statement
appears.

Thank you so much for your assistance. I'm new to SQL Server, but
willing to learn.

 

[AMH]

Also, I can't see where it is linking @rm to rm_code for the
controlling combo box...

 

[Sylvain Lafontaine]

If the value of Me![rm_code].Value a numeric value or a string value?

If it's a string, then you must enclose it between quote. (Single quote I
think, but you may try with double quote.)

From the line « EXEC spGIN SF-1155 », I conclude that you may have forgot to
put the required quote around SF-115.

 

[Sylvain Lafontaine]

Also, I told you that ADP has the bad habit of repeating multiple times some
SELECT queries; it's hard to give you an explanation.

 

[AMH]

no luck so far...

 

[AMH]

I tried it with quotation marks, but it doesn't seem to make any
difference. I think the problem is because it's trying to update both
base tables of the view, rather than just one of them. If I go into
the view and update from there, it gives the same message, so the
cascading combo boxes seem to make no difference. Is there any way
around this?

 

[Sylvain Lafontaine]

Personally, I don't use views, only stored procedures; so in your case it's
a good idea to try by making a direct join query without using this view.
Maybe something will change.

Did you use the VIEW_METADATA parameter when creating this view?

 

[AMH]

I did try the same query using a stored procedure, with no luck either.

I am not using the VIEW_METADATA parameter - should I?

 

[AMH]

Sylvain,

I have tried to tidy it up as much as possible (and narrow down my
profiler trace to where the issue might lie).

Here is the trace:

SET FMTONLY ON select "batch_id","gin","total_qty_g" from
"batch_ingredients" SET FMTONLY OFF

SET NO_BROWSETABLE ON

declare @P1 int
set @P1=6

exec sp_prepare @P1 output, N'@P1 int,@P2 char(9),@P3 numeric(6,1)',
N'INSERT INTO "batch_ingredients"("batch_id", "gin", "total_qty_g")
VALUES(@P1,@P2,@P3)' 1 select @P1

exec sp_execute 6, 6, '345 ', 65.0

exec sp_executesql N'UPDATE "INSIGHT".."inventory" SET "rm_code"=@P1
WHERE "rm_code" IS NULL AND "gin"=@P2', N'@P1 varchar(7),@P2
varchar(1)', 'SF-1155', ''

exec sp_executesql N'SELECT * FROM (SELECT batch_ingredients.batch_id,
batch_ingredients.gin, batch_ingredients.total_qty_g, inventory.rm_code
FROM batch_ingredients INNER JOIN inventory ON batch_ingredients.gin =
inventory.gin) AS DRVD_TBL WHERE "batch_id" = @P1 AND "gin" = @P2',
N'@P1 int,@P2 varchar(9)', 6, '345 '

exec sp_executesql N'SELECT * FROM (SELECT batch_ingredients.batch_id,
batch_ingredients.gin, batch_ingredients.total_qty_g, inventory.rm_code
FROM batch_ingredients INNER JOIN inventory ON batch_ingredients.gin =
inventory.gin) AS DRVD_TBL WHERE "batch_id" = @P1 AND "gin" = @P2',
N'@P1 int,@P2 varchar(9)', 6, '345
___________________________________

From what I can see, one of the last three statements is causing the

issue. Firstly, the INSERT statement on the "inventory" table is
confusing me. I don't want this table updated, hence I've set the
unique table property of the form to "batch_ingredients" not
"inventory". Is there anything else I can do to prevent this from
happening?

Then, the SELECT statement is occurring twice, but I'm not sure why.

I am not using the VIEW_METADATA parameter, and no Resync Command (what
is this?)...

Any further thoughts?

 

[Sylvain Lafontaine]

I don't know the schema of your tables, so I cannot tell you if setting the
Unique Table property to batch_ingredients instead of "inventory" is a good
thing or not. (However, you must be cautious when setting this property.)

One of the biggest problems with ADP is that you don't have any direct
control on the updating procedure; leaving no easy wat to correct for any
failure. A possibility to circumvent this would be to create a View with an
INSTEAD OF trigger; however, I never tried it personally so I cannot tell
you if this will work for you or not.

Another possibility would be to add a TimeStamp field to your tables; maybe
it will help, maybe not.

Finally, if I were you, I would consider the possibility of forgetting about
ADP and use a MDB file with linked tables instead. It will be much more
easier to get something that will work with linked tables than with ADP.