[RESOLVED] Access db problem (VB 2008 ex)

[_ivo_]

Hello!

I need help again, please.

I have contacts db made in Access 2007. In win form app I connect this database and I have form with grid view.

When I insert this database (drag & drop) from data sources i get my "DataGridView" table. Now I can see and edit all of my entries.

But I can't save

This is the error I get when I click on automatically generated save button :



And this is the text from this error:

See the end of this message for details on invoking 
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Data.OleDb.OleDbException: An UPDATE or DELETE query cannot contain a multi-valued field.
   at CRM.WEContactsDataSetTableAdapters.TableAdapterManager.UpdateAll(WEContactsDataSet dataSet) in C:\Documents and Settings\ivo\My Documents\Visual Studio 2008\Projects\CRM\CRM\WEContactsDataSet.Designer.vb:line 2464
   at CRM.pregled_klijenata_grid_Form.ContactsBindingNavigatorSaveItem_Click(Object sender, EventArgs e) in C:\Documents and Settings\ivo\My Documents\Visual Studio 2008\Projects\CRM\CRM\Ostale forme\pregled_klijenata_grid_Form.vb:line 13
   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)


************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3607 (GDR.050727-3600)
    CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
CRM
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Documents%20and%20Settings/ivo/My%20Documents/Visual%20Studio%202008/Projects/CRM/CRM/bin/Release/CRM.exe
----------------------------------------
Microsoft.VisualBasic
    Assembly Version: 8.0.0.0
    Win32 Version: 8.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.VisualBasic/8.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3082 (QFE.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Runtime.Remoting
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Runtime.Remoting/2.0.0.0__b77a5c561934e089/System.Runtime.Remoting.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Data.DataSetExtensions
    Assembly Version: 3.5.0.0
    Win32 Version: 3.5.30729.1 built by: SP
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Data.DataSetExtensions/3.5.0.0__b77a5c561934e089/System.Data.DataSetExtensions.dll
----------------------------------------
System.Core
    Assembly Version: 3.5.0.0
    Win32 Version: 3.5.30729.1 built by: SP
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Core/3.5.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

My question is : What I have to do to be able to save this

Thanks!

Regards,
Ivo

[jmcilhinney]

Obviously there's an issue with an UPDATE or DELETE statement, so what are the UPDATE and/or DELETE statements that are being executed?

[_ivo_]

Obviously there's an issue with an UPDATE or DELETE statement, so what are the UPDATE and/or DELETE statements that are being executed?

I'm really not sure.
Here is image what I get when playing the app in VB

SCREEN SHOT

Regards,
Ivo

[_ivo_]

Now I have made one other test app with same thing and it works

Only different is that db is local on my computer

db in my first app is on server in my office - the app is designed to work only in my company...

Maybe this is the problem? But I can't see why
Because if connection to db is problematic (because it is on server) then I wouldn't even get my grid view filled, right...

Regards,
Ivo

[jmcilhinney]

Open your DataSet in the designer and look at the UpdateCommand and DeleteCommand properties of your TableAdapters. That's where the offending SQL code is found.

[_ivo_]

Open your DataSet in the designer and look at the UpdateCommand and DeleteCommand properties of your TableAdapters. That's where the offending SQL code is found.

I have opened this in design ("Edit DataSet with Designer") but I can't find UPDATE or DELETE command anywhere

This I really can't understand. Other app with db works w/o problems and with this one I have a problem

[jmcilhinney]

Where are properties normally found? In the Properties window.

[_ivo_]

Where are properties normally found? In the Properties window.

yes, I know this
Here is ss of tableadapter property, maybe this will help

[_ivo_]

my bad!!
i have find it now!!

[jmcilhinney]

That's not the properties of the TableAdapter. As it says at the top of the Properties window, that's the properties of the Fill query of the TableAdapter.

[_ivo_]

That's not the properties of the TableAdapter. As it says at the top of the Properties window, that's the properties of the Fill query of the TableAdapter.

I have saw it after posting
This is the property of TableAdapter and I can't see what's whrong

[jmcilhinney]

As I can only see a portion of the SQL code for the DeleteCommand and UpdateCommand, neither can I.

[_ivo_]

This is delete command

DELETE FROM `Contacts` WHERE (((? = 1 AND `number` IS NULL) OR (`number` = ?)) AND (`ID` = ?) AND ((? = 1 AND `Company` IS NULL) OR (`Company` = ?)) AND ((? = 1 AND `Last Name` IS NULL) OR (`Last Name` = ?)) AND ((? = 1 AND `First Name` IS NULL) OR (`First Name` = ?)) AND ((? = 1 AND `E-mail Address` IS NULL) OR (`E-mail Address` = ?)) AND ((? = 1 AND `Job Title` IS NULL) OR (`Job Title` = ?)) AND ((? = 1 AND `Business Phone` IS NULL) OR (`Business Phone` = ?)) AND ((? = 1 AND `Home Phone` IS NULL) OR (`Home Phone` = ?)) AND ((? = 1 AND `Mobile Phone` IS NULL) OR (`Mobile Phone` = ?)) AND ((? = 1 AND `Fax Number` IS NULL) OR (`Fax Number` = ?)) AND ((? = 1 AND `Address` IS NULL) OR (`Address` = ?)) AND ((? = 1 AND `City` IS NULL) OR (`City` = ?)) AND ((? = 1 AND `State/Province` IS NULL) OR (`State/Province` = ?)) AND ((? = 1 AND `ZIP/Postal Code` IS NULL) OR (`ZIP/Postal Code` = ?)) AND ((? = 1 AND `Country/Region` IS NULL) OR (`Country/Region` = ?)) AND ((? = 1 AND `Category` IS NULL) OR (`Category` = ?)))

And this is update command

UPDATE `Contacts` SET `number` = ?, `Company` = ?, `Last Name` = ?, `First Name` = ?, `E-mail Address` = ?, `Job Title` = ?, `Business Phone` = ?, `Home Phone` = ?, `Mobile Phone` = ?, `Fax Number` = ?, `Address` = ?, `City` = ?, `State/Province` = ?, `ZIP/Postal Code` = ?, `Country/Region` = ?, `Web Page` = ?, `Notes` = ?, `Attachments` = ?, `Category` = ? WHERE (((? = 1 AND `number` IS NULL) OR (`number` = ?)) AND (`ID` = ?) AND ((? = 1 AND `Company` IS NULL) OR (`Company` = ?)) AND ((? = 1 AND `Last Name` IS NULL) OR (`Last Name` = ?)) AND ((? = 1 AND `First Name` IS NULL) OR (`First Name` = ?)) AND ((? = 1 AND `E-mail Address` IS NULL) OR (`E-mail Address` = ?)) AND ((? = 1 AND `Job Title` IS NULL) OR (`Job Title` = ?)) AND ((? = 1 AND `Business Phone` IS NULL) OR (`Business Phone` = ?)) AND ((? = 1 AND `Home Phone` IS NULL) OR (`Home Phone` = ?)) AND ((? = 1 AND `Mobile Phone` IS NULL) OR (`Mobile Phone` = ?)) AND ((? = 1 AND `Fax Number` IS NULL) OR (`Fax Number` = ?)) AND ((? = 1 AND `Address` IS NULL) OR (`Address` = ?)) AND ((? = 1 AND `City` IS NULL) OR (`City` = ?)) AND ((? = 1 AND `State/Province` IS NULL) OR (`State/Province` = ?)) AND ((? = 1 AND `ZIP/Postal Code` IS NULL) OR (`ZIP/Postal Code` = ?)) AND ((? = 1 AND `Country/Region` IS NULL) OR (`Country/Region` = ?)) AND ((? = 1 AND `Category` IS NULL) OR (`Category` = ?)))

[jmcilhinney]

What are the data types of each of those columns? I'm guessing that the issue is that those statements are using optimistic concurrency (hence the long WHERE clause) and one of the columns is a type that's not allowed in a WHERE clause (maybe 'varbinary').

[_ivo_]

Are you thinking about db itself?

About this?
Could this be a issue? In this other db that is working all columns are set as text...

[_ivo_]

What are the data types of each of those columns? I'm guessing that the issue is that those statements are using optimistic concurrency (hence the long WHERE clause) and one of the columns is a type that's not allowed in a WHERE clause (maybe 'varbinary').

+ rep for this post

This was the issue...
I have changed some fields to text and deleted some I don't need and now it is working!