-
September 2nd, 2009, 01:41 AM
#1
[RESOLVED] INDEX a table in VB.NET
Hi,
I want to create an index on my table. I used the following command
Code:
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\database.mdb;Persist Security Info=False")
cn.Open()
cmd = New OleDbCommand("Create INDEX itmno_idx ON INVENT (ITEM_NO)", cn)
cmd.ExecuteNonQuery()
Now I want to use this. Is there a file created with some extension when i do an Indexing? .
Is the above statment actually index the field, ITEM_NO or do i have to use ALTER TABLE command?
Currently I am getting error "Cannot execute data definition statements on linked data sources" Any idea?
Last edited by makdu; September 3rd, 2009 at 07:00 AM.
-
September 3rd, 2009, 08:17 AM
#2
Re: INDEX a table in VB.NET
Have you tried the Alter Table command yet ¿
-
September 4th, 2009, 11:24 PM
#3
Re: INDEX a table in VB.NET
While using Alter command
Code:
cmd = New OleDbCommand(" ALTER TABLE INVENT ADD INDEX (ITEM_NO)", cn)
cmd.ExecuteNonQuery()
I am getting sytax error.
Code:
System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217900
Message="Syntax error in field definition."
Source="Microsoft JET Database Engine"
StackTrace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at Indexing.indexng.index_Click(Object sender, EventArgs e) in D:\vb example\Indexing\Indexing\Form1.vb:line 18
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(Int32 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(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Indexing.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()
Where is the syntax problem???
Last edited by makdu; September 10th, 2009 at 03:01 AM.
-
September 5th, 2009, 12:32 AM
#4
Re: INDEX a table in VB.NET
Also I am not using create index in the statrting .
My current code looks like
Code:
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\database.mdb;Persist Security Info=False")
cn.Open()
cmd = New OleDbCommand("ALTER TABLE INVENT ADD INDEX (ITEM_NO)", cn)
cmd.ExecuteNonQuery()
What is the error in this ?
-
September 5th, 2009, 01:34 AM
#5
Re: INDEX a table in VB.NET
Your tables must be linked
-
September 5th, 2009, 01:45 AM
#6
Re: INDEX a table in VB.NET
The table is linked and not actuall in Access.I am accessing the linked table in Access and not the actuall table in FoxPro.
-
September 5th, 2009, 07:49 PM
#7
Re: INDEX a table in VB.NET
Don't think you can do that. You'd have to read each one separately, and then match on the key
-
September 10th, 2009, 12:14 AM
#8
Re: INDEX a table in VB.NET
in
Code:
cn = New OleDbConnection("Provider=vfpoledb;Data Source=D:\Jim\vb example\Indexing\INVENT.dbf;")
cn.Open()
cmd = New OleDbCommand("ALTER TABLE INVENT ADD INDEX (ITEM_NO)", cn)
cmd.ExecuteNonQuery()
INVENT is a foxpro table linked to a MS ACCESS data table. I am trying to add an index to that INVENT table only.
Last edited by makdu; September 10th, 2009 at 12:28 AM.
-
September 10th, 2009, 01:48 AM
#9
Re: INDEX a table in VB.NET
Originally Posted by dglienna
Don't think you can do that. You'd have to read each one separately, and then match on the key
Is there a work around if the tables are linked ?
-
November 23rd, 2009, 02:31 AM
#10
Re: INDEX a table in VB.NET
Using VFPOLEDB connectivity also, i tried to achieve the indexing . Here is the code
Code:
fcn = New ADODB.Connection
fcn.ConnectionString = "provider=VFPOLEDB.1;Data Source=" & tablefolderpath fcn.Open()
fcn.Execute("INDEX on D:\myfolder\sales.dbf (BRAND)")
But i get Command contains unrecognized phrase/keyword. message
I am trying to index on the brand field of sales.dbf table
-
November 23rd, 2009, 07:50 PM
#11
Re: INDEX a table in VB.NET
Try this:
Code:
fcn.Execute("INDEX on sales.dbf (BRAND)")
-
November 24th, 2009, 12:14 AM
#12
Re: INDEX a table in VB.NET
I got the following Exception
Code:
System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2147217900
Message="Command is missing required clause."
Source="Microsoft OLE DB Provider for Visual FoxPro"
StackTrace:
at ADODB.ConnectionClass.Execute(String CommandText, Object& RecordsAffected, Int32 Options)
at LPStats.ModMain.Main() in D:\LPStats\LPStatus\ModMain.vb:line 80
-
November 24th, 2009, 12:23 AM
#13
Re: INDEX a table in VB.NET
This is the more specific error detail
Code:
""Errors reported by ADO
(1) Error#: -2147217900
Desc. : Command is missing required clause.
Source: Microsoft OLE DB Provider for Visual FoxPro
Native Error: 221
SQL State:
Help Context: 0
Help File:
"
what is wrong with
Code:
fcn.Execute("INDEX on sale.dbf (Brand)")
one more thing to add .
I tried something like this
Code:
fcn.Execute("use sale.dbf; index on (price,brand) to prc")
This didnt throw any error or exception. But i am expecting a .cdx file named prc.cdx to be generated. But that is not happening. So i am not able to verify whether the indexing has happened or not.
Last edited by makdu; November 24th, 2009 at 12:41 AM.
-
November 26th, 2009, 02:30 AM
#14
Re: INDEX a table in VB.NET
I have made some progress
Code:
fcn = New ADODB.Connection
fcn.ConnectionString = "provider=VFPOLEDB.1;Data Source=" &tablefolderpath
fcn.Open()
fcn.Execute("Execscript('Use sale exclusive'+CHR(13)+'Index on BRAND tag idx1')")
This genearate a sale.cdx file. but I am expecting a idx1. file generation as we gave idx1 as the tag name
-
November 26th, 2009, 09:51 PM
#15
Re: INDEX a table in VB.NET
As I've said before, you'd have to USE FoxPro to create the index files. It can probably be scripted, though. The problem is that it's probably not like any other language.
Paradox 3.5 had PAL, one of the greatest languages available at the time. The Windows version lost it
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|