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 ?

Answer 1

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

 

Answer 2

 

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()

 

 

Answer 3

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.