i have an ASP .net application with sql server database, and i am wondering if you can give your ideas on how
to troubleshoot the following issue:
i can insert / update / delete from any table, but i have one page that uses transactions
to insert into different tables. the c# code is correct and very simple, but it fails.
i used the sql profiler
to see how my app interacts with the DB, especially that the app is using stored procedures, i can catch the exec procedure statement and run it manually from SSMS and it works fine, but the same stored procedure fails from the application!!! which lead me
to think that issue is coming from the user account and settings, i am no expert in sql server and wondering if anyone can explain how
to verify the required settings for user account. thanks
EDIT:
in web.config here is how i reference my connection
<connectionStrings>
<add name="Conn" connectionString="Data Source=localhost;Initial Catalog=myDB;Persist
Security Info=True;User ID=DbUser;Password=password1254_3" providerName="System.Data.SqlClient">
</connectionstring>
EDIT:
i will try
to describe the process here:
1- i begin a transaction
2- i call a stored proc
to insert (which succeeds) and return the scope identity ( that will be used in the next step)
3- i call another stored procedure
to insert some more info + scope identity from step 2, which is a foreign key here
4- i get error, foreign key violation
5- transaction rolled
back, now tables empty again...
thanks
