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

Have you tried the Alter Table command yet ¿

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

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 ?

Your tables must be linked

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

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

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.

Quote:

---

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 ?

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?

Hi,

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

in foxpro, the commands used are

HTML Code:

---


use invent

index on item_no to itmno

---

The equivalent i tried to use in vb.net is

HTML Code:

---


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

HTML Code:

---


"One or more errors occurred during processing of command

---

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?

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

Try this:

Code:

---

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

---

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

---

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.

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

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