Greetings!
My name is Kathleen and I am from Indiana. I have taken a few courses with C# that included Visual Studio however most topics were using MS ACCESS instead of Oracle databases so I am struggling with the tight-time frame learning curve. I thought the code would work mostly the same for both but I was very much wrong.
I made a working C# .NET Visual Studio application that successfully reads and writes to a MS ACCESS database but I need to convert to read and write to an Oracle server and database. I have setup everything to MS ACCESS using data sources, table adapters, and the query wizards. I have now converted most everything to Oracle. The application can connect and read data the Oracle database fields into the Windows form application successfully. I have ported my previous ACCESS SQL queries to Oracle syntax (ex: LIKE is case-sensitive). Now I am stuck hitting bricks with the Update command. Just a reminder this code was working perfectly writing to MS ACCESS.
Here is a Little Background Information:
My Visual Studio Information
Microsoft Visual Studio Professional 2013 Version 12.0.30723.00 Update 3
Microsoft .NET Framework Version 4.5.51209
Oracle Developer Tools for Visual Studio 12.1.0.2.0
SQL Server Data Tools 12.0.30919.1
Microsoft SQL Server Data Tools
32-bit drivers for connection to Oracle (we cannot change to 64-bit)
Here is the code where I update my database
//Add the row to the database
operatorDataDataSet.tblChangeRequests.AddtblChangeRequestsRow(newRow);
// Update the siteTableAdapter Object
tblChangeRequestsTableAdapter.Update(newRow);
The newRow object has all the required fields that the record needs in the correct data types
Example: newRow.DEVICE_KEY = txtDEVICE_KEY.Text;
This is string
The Structure of tblChangeRequests (my main table) in the Visual Studio application looks like this with the data types
STRING DEVICE_KEY
STRING GRID_ID
STRING DEVICE_NAME
STRING DEVICE_DESCRIPTION
STRING FIRST_FEEDER
STRING SECOND_FEEDER
STRING RTU_NAME
STRING RTU_PORT
STRING EFD_OBJECT_ID
STRING DEVICE_TYPE
STRING AOR
STRING FIRST_FEEDER_SUB
STRING SECOND_FEEDER_SUB
STRING NORMAL_STATE
STRING GRID_ID_U
STRING FIRST_FEEDER_U
STRING SECOND_FEEDER_U
STRING EFD_OBJECT_ID_U
STRING DEVICE_TYPE_U
STRING DEVICE_DESCRIPTION_U
STRING AOR_U
STRING FIRST_FEEDER_SUB_U
STRING SECOND_FEEDER_SUB_U
STRING NORMAL_STATE_U
STRING REQUESTER_ID
STRING DEVICE_KEY_U
STRING RTU_NAME_U
STRING RTU_PORT_U
STRING DEVICE_NAME_U
STRING COMMENTS_U
DATETIME REQUEST_DATE
STRING ADD_RECORD_U
STRING MODIFY_RECORD_U
STRING DELETE_RECORD_U
The Oracle Administrator made the corresponding Oracle table “tblChangeRequests” look like this:
Structure of ORACLE database
MODIFY_RECORD_U VARCHAR2(5)
ADD_RECORD_U VARCHAR2(5)
DEVICE_KEY VARCHAR2(100)
DEVICE_TYPE VARCHAR2(100)
RTU_NAME VARCHAR2(100)
GRID_ID VARCHAR2(100)
FIRST_FEEDER VARCHAR2(100)
SECOND_FEEDER VARCHAR2(100)
RTU_PORT VARCHAR2(100)
FIRST_FEEDER_SUB VARCHAR2(100)
SECOND_FEEDER_SUB VARCHAR2(100)
NORMAL_STATE VARCHAR2(100)
EFD_OBJECT_ID VARCHAR2(100)
DEVICE_DESCRIPTION VARCHAR2(100)
DEVICE_NAME VARCHAR2(100)
AOR VARCHAR2(100)
DEVICE_KEY_U VARCHAR2(100)
DEVICE_TYPE_U VARCHAR2(100)
RTU_NAME_U VARCHAR2(100)
GRID_ID_U VARCHAR2(100)
FIRST_FEEDER_U VARCHAR2(100)
SECOND_FEEDER_U VARCHAR2(100)
RTU_PORT_U VARCHAR2(100)
FIRST_FEEDER_SUB_U VARCHAR2(100)
SECOND_FEEDER_SUB_U VARCHAR2(100)
NORMAL_STATE_U VARCHAR2(100)
EFD_OBJECT_ID_U VARCHAR2(100)
DEVICE_DESCRIPTION_U VARCHAR2(100)
DEVICE_NAME_U VARCHAR2(100)
AOR_U VARCHAR2(100)
REQUEST_DATE DATE
REQUESTER_ID VARCHAR2(100)
COMMENTS_U VARCHAR2(500)
DELETE_RECORD_U VARCHAR2(5)
Remember my Visual Studio application can view all the data through the tableadapter and in the form through a navigator. No issues with reading.
I have access to view the Oracle server and database but remember I am a newbie and the administrator really needs to be told how to model this database and guessed based on how my data appeared in a sample Excel file. I’m not sure if the Exception is caused by the incorrect design in Oracle or my code.
When I call the Update method it returns an Exception and the details are below:
Exception Details
- System.FormatException was unhandled
HResult=-2146233033
Message=Failed to convert parameter value from a String to a Byte.
Source=System.Data
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows)
at RequestDMSChangesTool.OperatorDataDataSetTableAdapters.tblChangeRequestsTableAdapter.Update(DataRow dataRow) in c:\Users\e176993\Desktop\VERSION_DEVELOPER_ONLY\CODESET\RequestDMSChangesTool\RequestDMSChangesTool\OperatorDataDataSet.Designer.cs:line 7139
at RequestDMSChangesTool.RequestForm.addRecord() in c:\Users\e176993\Desktop\VERSION_DEVELOPER_ONLY\CODESET\RequestDMSChangesTool\RequestDMSChangesTool\RequestForm.cs:line 839
at RequestDMSChangesTool.RequestForm.btnSubmitChangeRequest_Click(Object sender, EventArgs e) in c:\Users\e176993\Desktop\VERSION_DEVELOPER_ONLY\CODESET\RequestDMSChangesTool\RequestDMSChangesTool\RequestForm.cs:line 756
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.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.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at RequestDMSChangesTool.Program.Main() in c:\Users\e176993\Desktop\VERSION_DEVELOPER_ONLY\CODESET\RequestDMSChangesTool\RequestDMSChangesTool\Program.cs:line 24
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: System.FormatException
HResult=-2146233033
Message=Input string was not in a correct format.
Source=mscorlib
StackTrace:
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.Byte.Parse(String s, NumberStyles style, NumberFormatInfo info)
at System.String.System.IConvertible.ToByte(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Data.OracleClient.OracleParameter.CoerceValue(Object value, MetaType destinationType)
InnerException:
I really wish this would be easier to debug/figure out. Is there a technique to make better sense of this?
Does anyone have comments or suggestions on what may be causing the exception and how to address with solutions?
Thank you very much for your time and kind consideration,
-Kathleen