[RESOLVED] Access db problem (VB 2008 ex)

Printable View

May 5th, 2010, 07:27 AM
_ivo_

[RESOLVED] Access db problem (VB 2008 ex)

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 :sick:

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

http://img338.imageshack.us/img338/5292/errorre.jpg

And this is the text from this error:

HTML Code:

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 :thumb:

Thanks!

Regards,
Ivo
May 5th, 2010, 09:01 AM
jmcilhinney

Re: Access db problem (VB 2008 ex)

Obviously there's an issue with an UPDATE or DELETE statement, so what are the UPDATE and/or DELETE statements that are being executed?
May 5th, 2010, 09:14 AM
_ivo_

Re: Access db problem (VB 2008 ex)

Quote:

Originally Posted by 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?

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

SCREEN SHOT

Regards,
Ivo
May 5th, 2010, 01:34 PM
_ivo_

Re: Access db problem (VB 2008 ex)

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

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 :sick:
Because if connection to db is problematic (because it is on server) then I wouldn't even get my grid view filled, right... :afrog:

Regards,
Ivo
May 5th, 2010, 07:36 PM
jmcilhinney

Re: Access db problem (VB 2008 ex)

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.
May 6th, 2010, 02:01 AM
_ivo_

Re: Access db problem (VB 2008 ex)

Quote:

Originally Posted by 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.

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

This I really can't understand. Other app with db works w/o problems and with this one I have a problem :sick:
May 6th, 2010, 02:12 AM
jmcilhinney

Re: Access db problem (VB 2008 ex)

Where are properties normally found? In the Properties window.
May 6th, 2010, 02:25 AM
_ivo_

Re: Access db problem (VB 2008 ex)

Quote:

Originally Posted by jmcilhinney

Where are properties normally found? In the Properties window.

:bigyello::bigyello: yes, I know this :sick::sick:
Here is ss of tableadapter property, maybe this will help :eek:

http://img46.imageshack.us/img46/9165/propi.jpg
May 6th, 2010, 02:26 AM
_ivo_

Re: Access db problem (VB 2008 ex)

my bad!!
i have find it now!!
May 6th, 2010, 02:27 AM
jmcilhinney

Re: Access db problem (VB 2008 ex)

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.
May 6th, 2010, 02:43 AM
_ivo_

Re: Access db problem (VB 2008 ex)

Quote:

Originally Posted by 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.

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

http://img16.imageshack.us/img16/2590/props.jpg
May 6th, 2010, 02:46 AM
jmcilhinney

Re: Access db problem (VB 2008 ex)

As I can only see a portion of the SQL code for the DeleteCommand and UpdateCommand, neither can I.
May 6th, 2010, 03:15 AM
_ivo_

Re: Access db problem (VB 2008 ex)

This is delete command

Code:

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

Code:

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` = ?)))
May 6th, 2010, 03:26 AM
jmcilhinney

Re: Access db problem (VB 2008 ex)

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').
May 6th, 2010, 03:37 AM
_ivo_

Re: Access db problem (VB 2008 ex)

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...

http://img130.imageshack.us/img130/534/tttye.jpg
May 6th, 2010, 04:26 AM
_ivo_

Re: Access db problem (VB 2008 ex)

Quote:

Originally Posted by 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').

+ rep for this post

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

:thumb: