[RESOLVED] INDEX a table in VB.NET
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: [RESOLVED] INDEX a table in VB.NET

  1. #1
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

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

  2. #2
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,091

    Re: INDEX a table in VB.NET

    Have you tried the Alter Table command yet ¿

  3. #3
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

    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.

  4. #4
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

    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 ?

  5. #5
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    Re: INDEX a table in VB.NET

    Your tables must be linked
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  6. #6
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

    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.

  7. #7
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    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
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  8. #8
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

    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.

  9. #9
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

    Re: INDEX a table in VB.NET

    Quote Originally Posted by dglienna View Post
    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 ?

  10. #10
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

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

  11. #11
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

    Question 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

  12. #12
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  13. #13
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

    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

  14. #14
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    Re: INDEX a table in VB.NET

    Try this:

    Code:
    fcn.Execute("INDEX  on sales.dbf (BRAND)")
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  15. #15
    Join Date
    Jul 2009
    Location
    Kuwait
    Posts
    170

    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

Page 1 of 2 12 LastLast

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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center