Question SQL Issue (hopefully)

[ggunter]

I have a small program set up to test making a connection to an Access database and running a query.

I used the wizard to connect to the database and set up the query just like in the MSDN walkthrough. I encountered no errors when setting this up.

However, when I actually run the program I receive the following error:

System.Data.ConstraintException was unhandled
  Message="Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
  Source="System.Data"
  StackTrace:
       at System.Data.DataTable.EnableConstraints()
       at System.Data.DataTable.set_EnforceConstraints(Boolean value)
       at System.Data.DataTable.EndLoadData()
       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       at query_Test_1.AIDataSetTableAdapters.tblAssocInfoTableAdapter.FillByDistinctProcessNumber(tblAssocInfoDataTable dataTable) in C:\_GG Project\_Library\z_Forum\query_Test_1\AIDataSet.Designer.vb:line 1662
       at query_Test_1.Form1.Form1_Load(Object sender, EventArgs e) in C:\_GG Project\_Library\z_Forum\query_Test_1\Form1.vb:line 11
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.Form.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ContainerControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.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.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
       at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
       at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
       at System.Windows.Forms.Control.set_Visible(Boolean value)
       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(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at query_Test_1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain.nExecuteAssembly(Assembly 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.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

The query I am trying to run is as follows:

SELECT DISTINCT tblProcessNumber.ProcessNum
FROM (tblAssocInfo LEFT OUTER JOIN tblProcessNumber ON tblAssocInfo.ProcessNum = tblProcessNumber.ProcessNum)
ORDER BY tblProcessNumber.ProcessNum

Maybe it's just me being under the weather today but I don't see anything wrong with the SQL.

This is my entire project code so far:

Dim dtLV As New query_Test_1.AIDataSet.tblAssocInfoDataTable
Dim taLV As New query_Test_1.AIDataSetTableAdapters.tblAssocInfoTableAdapter

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        taLV.FillByDistinctProcessNumber(dtLV)

End Sub

Would someone please point me in the right direction?

 

[Menthos]

You're right, there's nothing in that query that would use any constraints.

I'd check your datatable definition since thats the likely place a constraint has been put in place.

 

[ggunter]

Thank you for the quick reply Menthos. Sorry for the delay. I've been home sick since my last post.

I checked the database and couldn't find any issues with it. It's pretty basic. Doesn't even have any relations set up.

I tried setting the query up in it's own DataTable/TableAdapter. Now, it runs with no errors but doesn't return any data.

I'm confused by that because when I click the "Execute Query" button in the wizard, it returns the correct data.

Here's my project now:

Dim dtDistinct As New query_Test_1.AIDataSet.tblDistinctProcessNumDataTable
Dim taDistinct As New query_Test_1.AIDataSetTableAdapters.tblDistinctProcessNumTableAdapter

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

  taDistinct.GetData()

  MessageBox.Show(dtDistinct.Rows.Count.ToString())

End Sub

 

[ggunter]

Changed from "Return a datatable" to "Fill a datatable" and it worked!!

Thanks!!