"sql data source" updating

hello all,

I ran into a problem using the "sql data source updating " method. i'm using a form view with about 20 parameters that are bind to controls in the form view and 5 other parameters that i'm setting the value to in the "sql data source updating" method. Every thing updates find execept for the last 5 parameters that i'm setting the values to in the "sql data source updating" method. My store procedure(sp) updates and insert mulitple tables. For some UNKOWN reason the tables that uses the parameteres in the "sql data source updating " are inserting multple records when only 1 record should be inserted. Have anyone have this problem?

Hi,

Please check if the insert command in the stored procedure has been called several times. Also, is there any triggers fired after the insertion?

If that still doesn't work, please let us see the stored procedure and code for Updating event handler.

Also, if you're inserting a record into the database, I suppose you need to handle the SqlDataSource.Inserting event.

"Changed Context to ..." message when editing permissions w/ VBA

I have a client using a small SQL Express database with an Access front end.

I've created a form in the front end that allows a supervisor the ability to perform two very basic security operations. 1) Adding a new user, and 2) granting/removing write privileges. The code behind the form uses VBA to build some T-SQL strings and then executes them via an ADO connection.

The strings are as follows:

1) Add new login to server:
"CREATE LOGIN [NETWORK\UserName] FROM WINDOWS WITH DEFAULT_DATABASE = MyDatabase"

2) Add user to database:
"USE MyDatabase CREATE USER [NETWORK\UserName]"

3) Grant Write Permissions:
" USE MyDatabase EXEC sp_addrolemember 'db_datawriter', 'NETWORK\UserName' "

4) Remove Write Permissions:
" USE MyDatabase EXEC sp_droprolemember 'db_datawriter', 'NETWORK\UserName' "

I have full privileges over the server & database, and everything runs perfectly.

However, when the supervisor - whose login includes security admin privileges - tries this, he gets a message that says "Changed Context to MyDatabase, Error# -2147217900, 80040e14" and the code fails.

If I grant him "db_owner" status, the message does not come up and everything runs perfectly.

I'm not a security guru or anything, but I suspect that it's not the greatest idea to hand out db_owner privileges.

I ran a search, and the best I could come up with were a couple of KB articles:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;197459
http://support.microsoft.com/default.aspx?scid=KB;EN-US;143339

I'm not even sure these articles are talking about what's happening..

Any ideas?When is the last time your boss ran an office update? Or more specifically, which MDAC is he using?|||When is the last time your boss ran an office update? Or more specifically, which MDAC is he using?
I'm not at the client's location right now, so I can't give a firm answer.. but I had someone there gather the following info..

Access is version 11.6566.8132 SP2

They're running XP Pro version 2002 SP2.

Per this page: http://support.microsoft.com/kb/231943/en-us they should be on at least MDAC 2.8 SP1. However based on the release dates shown on that page, I wouldn't be suprised if they were running 2.8 SP2.

Does that help?|||Ok, went by their office yesterday. MDAC is version 2.8.1117.0.

After a little more time spent, it appears as if this is the 'informational' message passed back to ODBC. It appears that I need to figure out how to trap for it in VB. The KB articles I posted got me started, but I need some more work..

I also seem to still have a SQL issue.. In the little bit of troubleshooting I had time for, it also appears that I have a 2nd error coming in behind the "changed context.." message. If the user is not a dbowner, it says that he doesn't have permissions to run these operations.

Is this correct? Is dbowner status required to change permissions of other users?