[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

[makdu]

This is the more specific error detail

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

fcn.Execute("INDEX on sale.dbf (Brand)")

one more thing to add .
I tried something like this
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.

[makdu]

I have made some progress

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

[dglienna]

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