-
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 ?
-
September 10th, 2009, 02:47 AM
#10
Re: INDEX a table in VB.NET
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?
-
September 13th, 2009, 12:50 AM
#11
Re: INDEX a table in VB.NET
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
-
September 13th, 2009, 02:36 PM
#12
Re: INDEX a table in VB.NET
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?
-
November 23rd, 2009, 02:31 AM
#13
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
#14
Re: INDEX a table in VB.NET
Try this:
Code:
fcn.Execute("INDEX on sales.dbf (BRAND)")
-
November 24th, 2009, 12:14 AM
#15
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
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
|