A weired error about HTTP 500 !

Question

A weired error about HTTP 500 !

Alick Wang 306

I created a web user asp.net 8.0 mvc without ef.

When I modify the stucture of oracle datebase table ,such as add a field ,then the web related to the table report a error about HTTP 500 . But after I restart the iis,it works well without doing anything else!

3 answers

Your answer

Answer 1

Bruce (SqlWork.com) 82,321 Volunteer Moderator

As we can not see your code, we have no idea why it behaves this way. I’d assume you have some init code that needs to run on a schema change.

Answer 2

Alick Wang 306

.net 8.0 with Dependency Injection

In Program.cs

builder.Services.AddScoped<DAL.Account.IAccount,DAL.Account.Account>();

In Account.cs

public class Account : IAccount

{

private readonly IOptions<DAL._DBConnStr> _dbconnStr;

public Account(IOptions<DAL._DBConnStr> dbconnStr)

{

_dbconnStr = dbconnStr;

}

public DataTable GetRoles()

{

using (OracleConnection conn = new OracleConnection(_dbconnStr.Value.myConnectionString))

{

DataTable dt = new DataTable();

OracleCommand selectCmd = new OracleCommand("select * from roles order by sortindex", conn);

OracleDataAdapter da = new OracleDataAdapter();

da.SelectCommand = selectCmd;

da.Fill(dt);

return dt;

}

In AccountController

IAccount AccountDA;

public AccountController(IAccount accountDA)

{

AccountDA = accountDA;

}

public IActionResult Login(string ua)

{

DataTable dt = AccountDA.GetUserAccountByUser(ua);

}

Anonymous

2024-04-18T02:40:29.7533333+00:00

Hi @Alick Wang,

Suppose you have a roles table that corresponds to a Role entity class. You can create a RoleDto class that is used for all queries and data interactions. This way, no matter how the roles table changes (such as adding or removing fields), we can keep the rest of the application stable by adjusting the Role-to-RoleDto mapping.

So I think use Data Transfer Objects (DTOs) is a good idea.

Best Regards

Jason
Alick Wang 306 Reputation points

2024-04-20T13:47:52.46+00:00

It's not this kind of problem!

Only add a field in the database without changing anything else,the web should not be affected ,but occur a HTTP 500 error.
MotoX80 37,246 Reputation points

2024-04-20T16:48:49.4133333+00:00

Check the http error logs in C:\Windows\System32\LogFiles\HTTPERR for additional error information.

Also check the IIS logs and the Application and System event logs for errors.

It is impossible to diagnose your problem based on "Getting a 500 error". There should be sub codes and an error description.

https://www.bing.com/search?pglt=41&q=how+to+troubleshoot+iis+500+error

Configure IIS to send detailed errors back to the browser.

https://www.bing.com/search?q=iis+errors+to+browser

You don't have a try/catch in your code, so yes, it's going to blow up if an error occurs. You need to find the root error.
Alick Wang 306 Reputation points

2024-05-11T08:15:19.79+00:00

Check the http error logs in C:\Windows\System32\LogFiles\HTTPERR for additional error information.

There is no error log about the problem.
AgaveJoe 30,571 Reputation points

2024-05-11T11:47:49.5966667+00:00

When I modify the stucture of oracle datebase table ,such as add a field ,then the web related to the table report a error about HTTP 500 . But after I restart the iis,it works well without doing anything else!

I see you are using SELECT * and a generic DataTable container. Given the code snippet and problem statement, I'm guessing your expectation is DDL changes should not affect a running web application.

This is a very tough use case to design when considering web applications have concurrent requests (many users). Any kind of state management is problematic because the state in the web application can be out-of-sync with the database schema.

For example, let's assume a user is populating an HTML form based on a table. While the user is entering data, a column is added to the same table. This is clearly problematic especially if the column cannot be null.

Let's look at another example. Let's assume the application stores the user data in an authentication cookie. A standard in MVC applications. If a column is added or removed from any of the related tables like roles, claim, or user then the user's cached data fields can be out-of-sync with the database schema.

At the end of the day, your design is not functioning as you expect. There is not much the community can do because the bug(s) can be anywhere within the code base. You'll need to troubleshoot your code until you can find the bug(s).
MotoX80 37,246 Reputation points

2024-05-11T11:48:01.2566667+00:00

Did you configure IIS to send detailed errors back to the browser?

Did you add a try/catch to the code and display the exception details?

https://4sysops.com/archives/enable-detailed-iis-errors/

https://4sysops.com/archives/iis-failed-request-tracing/

https://4sysops.com/archives/debugging-an-iis-500-internal-server-error/

Answer 3

to:MotoX80

In the windows logs->applications

---> Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00942:

at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

at Oracle.ManagedDataAccess.Client.OracleDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

MotoX80 37,246 Reputation points

2024-05-17T15:52:24.6566667+00:00

https://www.databasestar.com/ora-00942/

https://stackoverflow.com/questions/16129912/sql-error-ora-00942-table-or-view-does-not-exist

https://www.bing.com/search?q=ORA-00942+OracleDataAdapter

Instead of doing a "select *", maybe do a select of the specific columns that that page needs.

Sounds like something is being cached by the Oracle client. Do any of your pages save a connection in a .Net application variable?

Maybe something on the Oracle documentation site might help you.

https://docs.oracle.com/en/database/oracle/oracle-database/19/odpnt/InstallManagedConfig.html#GUID-A6BC4979-C78F-4DE6-B6B2-7EE12AD5749D

Connection pooling offers ClearPool and ClearAllPools methods. That might be something to investigate.

https://docs.oracle.com/en/database/oracle/oracle-database/19/odpnt/featConnecting.html#GUID-DEE5B959-0BC8-42A7-962D-697A38BA5DA8

How often do you modify the structure of a table? If you are doing this in your test environment, then notify your IIS support team and have them just recycle IIS. In production, you should do this in a maintenance timeframe window where updates are scheduled. Schedule an IIS reset or server reboot to implement your changes.

You might have better luck asking this question in an Oracle forum since this is an Oracle error.

@AgaveJoe any thoughts?

Share via

A weired error about HTTP 500 !

3 answers

Your answer