[RESOLVED] INDEX a table in VB.NET

[makdu]

Hi,
I want to create an index on my table. I used the following command

 
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?

[HanneSThEGreaT]

Have you tried the Alter Table command yet ¿

[makdu]

While using Alter command

    cmd = New OleDbCommand(" ALTER TABLE INVENT ADD INDEX (ITEM_NO)", cn)
  cmd.ExecuteNonQuery()

I am getting sytax error.

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

[makdu]

Also I am not using create index in the statrting .
My current code looks like

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 ?

[dglienna]

Your tables must be linked

[makdu]

The table is linked and not actuall in Access.I am accessing the linked table in Access and not the actuall table in FoxPro.

[dglienna]

Don't think you can do that. You'd have to read each one separately, and then match on the key

[makdu]

in

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.

[makdu]

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 ?

[makdu]

Let me make it more clear what my problem is
1. I have an MS ACCESS database with some foxpro tables linked to it.
2. I want to use/create index for the foxpro table from vb.net
3. Is there a way i can use the foxpro index from vb.net/MS ACCESS so that my search function will be faster?

[makdu]

Hi,

I want to achieve the same result in vb.net which is obtained from foxpro.

in foxpro, the commands used are

use invent

index on item_no to itmno 

The equivalent i tried to use in vb.net is

cn = New System.Data.OleDb.OleDbConnection("Provider=VFPOLEDB.1;Data Source=D:\atitest;Persist Security Info=False")

cn.Open()

Dim Query1 As String = "use INVENT"

Dim Query2 As String = "index on item tag itemindx"

Dim Cmd1 As OleDbCommand = New OleDbCommand(Query1, cn)

Dim Cmd2 As OleDbCommand = New OleDbCommand(Query2, cn)
 

Cmd1.ExecuteNonQuery()

Cmd2.ExecuteNonQuery()

I am getting exception in the second non query

"One or more errors occurred during processing of command

[dglienna]

because it isn't valid SQL code. that's the only thing that you can use, which is why you can't do that. maybe foxpro lets you create a macro, or stored procedure that accepts variables?

[makdu]

Using VFPOLEDB connectivity also, i tried to achieve the indexing . Here is the 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

[dglienna]

Try this:

fcn.Execute("INDEX  on sales.dbf (BRAND)")

[makdu]

I got the following Exception

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