none
How to navigate records (sql express)

    Question

  •  

    Hi

    how do i navigate in records ?

    for example, after connecting to a remote sql server, i use a DataGridView to see the records.

    if i want to edit a record a new form open with some text boxes. here i fill the new data.

    data is updated in the table but allways in the first line of record. if i select/click on a diferent line on the datagridview,

    it allways editing the first line, first record.

    in vb 6 i could navigade the recordset and point to a record position end edit it.

    how can i do somthing like this in vb express in code ?

    Thursday, November 01, 2007 1:25 PM

All replies

  • Hi ,you can use cureencymanager to navigate records,please check following sample.

    Code Block

    Imports System.Data

    Imports System.Data.SqlClient

     

    Public Class Form1

        Private myCurrencyManager As CurrencyManager

        Private bs As New BindingSource

        Private da As SqlDataAdapter

        Private ds As New DataSet

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Dim conn As New SqlConnection("Server=(Local);" & "DataBase=Northwind; Integrated Security=SSPI")

            Dim sqlstr As String = "SELECT * FROM Employees"

            da = New SqlDataAdapter(sqlstr, conn)

            da.Fill(ds)

            bs.DataSource = ds.Tables(0)

            Me.DataGridView1.DataSource = bs

            Me.DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        End Sub

        Private Sub Button1_Click(ByVal sender As System.Object, _

        ByVal e As System.EventArgs) Handles Button1.Click

     

            If bs.Position > 0 Then

                Dim obj1 As Object() = Me.ds.Tables(0).Rows(Me.bs.Position).ItemArray

                Dim obj2 As Object() = Me.ds.Tables(0).Rows(Me.bs.Position - 1).ItemArray

                Me.ds.Tables(0).Rows(Me.bs.Position - 1).ItemArray = obj1

                Me.ds.Tables(0).Rows(Me.bs.Position).ItemArray = obj2

                bs.MovePrevious()

            Else

                MessageBox.Show("you are on the top")

            End If

        End Sub

     

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            If bs.Position < bs.Count - 1 Then

                Dim obj1 As Object() = Me.ds.Tables(0).Rows(Me.bs.Position).ItemArray

                Dim obj2 As Object() = Me.ds.Tables(0).Rows(Me.bs.Position + 1).ItemArray

                Me.ds.Tables(0).Rows(Me.bs.Position + 1).ItemArray = obj1

                Me.ds.Tables(0).Rows(Me.bs.Position).ItemArray = obj2

                bs.MoveNext()

            Else

                MessageBox.Show("you are at the button")

     

            End If

     

        End Sub

     

     

        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

            Dim cmb As New SqlCommandBuilder(da)

            Try

                da.Update(ds.Tables(0))

                MessageBox.Show("dd")

            Catch ex As Exception

     

            End Try

     

        End Sub

    End Class

     

     

     

    If you have any further questions, feel free to let me know.

    Best Regards

    Regards

    Gavin

     

    Monday, November 05, 2007 7:17 AM
  •  

    i have tried your code (i have changed VAR names acording to my project).

    i  get an error massage (see below).

     

    only if i remove this line code:

    Code Block
    bs.DataSource = ds.Tables(0)

     

    my project continue to run but the navigation keys wont 'navigate'.

    by pressing them i get a massage that it has reached to the top / buttom.

     

     

    --error massage---

     

    Code Block

    System.IndexOutOfRangeException was unhandled
      Message="Cannot find table 0."
      Source="System.Data"
      StackTrace:
           at System.Data.DataTableCollection.get_Item(Int32 index)
           at Connect_To_SQLEXPRESS_Server.Form1.Form1_Load(Object sender, EventArgs e) in C:\Documents and Settings\sysadmin\My Documents\Visual Studio 2005\Projects\Connect_To_SQLEXPRESS_Server\Connect_To_SQLEXPRESS_Server\Form1.vb:line 21
           at System.Windows.Forms.Form.OnLoad(EventArgs e)
           at System.Windows.Forms.Form.OnCreateControl()
           at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
           at System.Windows.Forms.Control.CreateControl()
           at System.Windows.Forms.Control.WmShowWindow(Message& m)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
           at System.Windows.Forms.ContainerControl.WndProc(Message& m)
           at System.Windows.Forms.Form.WmShowWindow(Message& m)
           at System.Windows.Forms.Form.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.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
           at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Control.Show()
           at Connect_To_SQLEXPRESS_Server.frmLogIn.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\sysadmin\My Documents\Visual Studio 2005\Projects\Connect_To_SQLEXPRESS_Server\Connect_To_SQLEXPRESS_Server\frmLogIn.vb:line 15
           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 Connect_To_SQLEXPRESS_Server.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()

     

     

    Monday, November 05, 2007 8:59 AM
  • What I’m trying to do is, point on the DataGridView and then Import the row cells data to some Textboxes for editing.

    So I wrote this line for indicating and select the row for editing:

    Code Block

    sql = "DELETE FROM Contacts WHERE ContactID=" & Me.DataGridView1.CurrentCellAddress.Y

     

     

    This code works fine as long as the data in column ' ContactID' are sorted and the same as the DataGridView index numbering. for example, when I’m pointing on a row on the DataGridView I get the row index number (row number 3) and then clicking on the Delete button (that runs the above code) (or add button...), the Textboxes will be filled with data as long as in row(3) cell under column of ContactID the number is 3 also. I configured that cell in the sql server as automatic numbering.

    I want to enter as ContactID a unique number for example: 200711051600 (year+month+day+houer+min).

    if ContactID cell has a unique number then the above code won't do good.

     

    I need to now on what row I am pointing and then take that row and fill the text box and then send back to the sql server without depending on the 'Where' sql command.

     

     

    Monday, November 05, 2007 2:27 PM