StrataFrame Forum » StrataFrame Application Framework - V1 » Issues » Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Kari Paukku — Thu, 20 Jul 2006 08:34:04 GMT

Hi,

when retrieving data from Oracle database a VARCHAR2 field being NULL gives an expections. When there is text in the field then the select works ok.

What might be the problem here and how can it be solved?

TIA,

Kari

ps. I did find some other post related to null values put not quite like this. Also, when can we expect the next release with the DevExpress wrapper among other enhancements :-)

Picture attached

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

StrataFrame Team — Thu, 20 Jul 2006 08:34:04 GMT

I have it working, Kari, but I have to get the security hooks to the point that the framework is stable before I can release it. We finished the security editors yesterday, so I believe I'll have the update posted by the end of the weekend. Sorry for the delay, but the security and these fixes will be worth it.

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Kari Paukku — Thu, 20 Jul 2006 03:20:37 GMT

Hi,

any news on the fix for Oracle? I today (20-JUL-06) downloaded the SF Installation package for DevEx 6.15 and tested the Oracle update. The " AND AND AND " problem is still open, I quess...?

Cheers,

Kari

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

StrataFrame Team — Tue, 27 Jun 2006 12:41:43 GMT

It's in the works, and I'm running the changes through the unit tests to iron out any other issues.

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Kari Paukku — Tue, 27 Jun 2006 12:18:40 GMT

Hi,

any news relating the Oracle update probkem...?

Cheers,

Kari

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Trent L. Taylor — Sun, 11 Jun 2006 09:22:41 GMT

Kari,

I am answering this from the floor of Tech-Ed and we will be out of the office all week. We will get your problem resolve first thing when we get back. Sorry for any trouble.

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Kari Paukku — Sat, 10 Jun 2006 14:17:25 GMT

Hi,

just downloaded the latest (8.6.2006) version of SF but the problem updating Oracle database still seems to be there....

Any estimate when the final version of 1.4.1 will be available?

Cheers

Kari

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

StrataFrame Team — Thu, 01 Jun 2006 11:55:58 GMT

Well, Kari, that's what I would call a bug :Whistling:

I'll get it fixed and post you an update ASAP. Thanks for the help. I'll have to take a look at my unit tests and figure out why that one wasn't picked up...

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Kari Paukku — Thu, 01 Jun 2006 11:42:54 GMT

Hi Ben,

and thanks for all the help. The new SF showed nicely what was the problem. As I read it, SF adds a couple of extra "AND"'s into the update's where clause:

UPDATE "RESYS"."EMP" SET "EMP_NAME" = :EMP_NAME WHERE "EMP_NO" = :EMP_NO AND AND AND (("EMP_NAME" = :EMP_NAME_ORG) OR ((:EMP_NAME_ORG IS NULL) AND ("EMP_NAME" IS NULL)))

Here is the SQL for the table

===================================================
-- Table EMP

CREATE TABLE emp
(
emp_no NUMBER(10) NOT NULL,
emp_name VARCHAR2(50)
);

-- Constraints for EMP

ALTER TABLE emp
ADD CONSTRAINT pk_emp PRIMARY KEY (emp_no);

INSERT INTO EMP (EMP_NO, EMP_NAME) VALUES (1,'Bart Simpson');
=====================================================

Attached is the full debug.html file in PDF format. What I did was 1) retrieve the data => ok, 2) update the EMP_NAME to 'Bart Simpson testing update' => error. I'm using Oracle 10gR2 running on a WinXP machine.

Hope this helps,

Kari

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

StrataFrame Team — Wed, 31 May 2006 17:13:12 GMT

OK, Kari, here's the maintenance install. Just let me know what you find. You'll have to pull the setup exe out of the zip file... the forum won't let you upload executables.

Thanks :)

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

StrataFrame Team — Wed, 31 May 2006 16:58:13 GMT

Well, Kari, I'm not sure why the debug isn't working, but I'm building you a maintenance release right now that has some additional debugging information. You can install this one and just leave it installed, don't worry about going back to the pre-release.

Once you get it installed, it should give you more information in the debug file. Also, if you can get the version of Oracle that you're running, the table structure (screenshot or CREATE TABLE script would be fine), and the .Designer.vb file for the business object and post those with the output from the debug file, it will go a long way towards helping me to reproduce the error.

Let me know :) Thanks

P.S. I'll post the maintenance install here in a second.

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Kari Paukku — Wed, 31 May 2006 16:41:56 GMT

Hi,

what might be the problem here: with the new relaese I still get the same ORA-00936 error mentioned in my previous post. Setting the debug on only shows the select statement - still the error appears when trying to save the changes.

Currently I'm testing this with the simplest possible query - just a couple of columns in the table. With SF samples everything is ok. Might the problem be with the SQL and Oracle? Unfortunately I didn't manage to find the right place to debug the application so that I could see the UPDATE statement.

Any ideas how to resolve this...?

Thank you for all your help,
Kari

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

StrataFrame Team — Fri, 26 May 2006 13:03:21 GMT

The next release will be out before I leave the office today (4 hours or so to go) :)

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

StrataFrame Team — Fri, 26 May 2006 12:59:40 GMT

We updated the debug mode in the version that I'm building to allow for the debugging of INSERT, UPDATE, and DELETE commands. Before this version, the debug mode on the DbDataSourceItem class only showed SELECT commands. So, once we get the new build to you, you can call SetDebugOn() on the DbDataSourceItem and you'll be able to see the command that is being sent to the database, and that will give us a clue.

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Kari Paukku — Fri, 26 May 2006 12:30:13 GMT

Hi,

one additional problem: when I try to update the WWW-field I get "ORA-00936 Missing Expression" error.

So this is an Oracle error saying - I guess - that there is a problem with the update SQL-statement. What might go wrong here and is there a way to see the update statement SF is sending to the database.

Thanks again,

Kari

The error window "An error occured while saving an the data to the server"
================

BusinessLayerException
An error occurred while saving an the data to the server.
DataLayerSavingException
ORA-00936: missing expression

OracleException
ORA-00936: missing expression

Source : MicroFour StrataFrame Business

Stack Trace:
   at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
   at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
   at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
   at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
   at MicroFour.StrataFrame.Data.OracleDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler, Boolean RecreateCommand)
   at MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler)
   at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)
   at MicroFour.StrataFrame.Data.DataLayer.SaveByForm(DataTable TableToSave, Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.Business.BusinessLayer.SaveByForm(Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save(Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save()
   at MicroFour.StrataFrame.UI.Windows.Forms.MaintenanceFormToolStrip.cmdSave_Click(Object sender, EventArgs e)
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

Kari Paukku — Fri, 26 May 2006 11:41:14 GMT

Hi Ben,

thanks, that worked fine.

Kari

ps. Is there hope that we would have the next release available still this weekend?

RE: Conversion from type 'DBNull' to type 'String' is not valid. (Oracle10gR2)

StrataFrame Team — Fri, 26 May 2006 11:00:27 GMT

You will need to go through the BOMapper and customize your "WWW" field so that it will return an alternate value on NULL. Within the BOMapper, select the business object, then select the WWW field. Right-click the field and select Customize from the context menu. In the customization dialog, select "Return Alternate On Null" as the null value handling type, and then set the return value to either "Nothing" or "String.Empty" (without the quotes) and the field will return the appropriate value when a NULL value exists in the database.

As for the DevExpress wrapper, I'm building the install right now.