DataSource from a remote SQL Server

Hi huji,

see a sample at http://www.vb-helper.com/howto_net_datagrid.html

Cheers!

OK. Here is my code:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strConnString As String
        strConnString = "Data Source=localhost;" & _
            "Initial Catalog=workshop;User Id=workshop;Password=workshop;"
        Dim SQLConnection As New SqlClient.SqlConnection(strConnString)
        SQLConnection.Open()
        Dim daLogin As New SqlDataAdapter("select username,password " & _
            "from tblWorkshopAdmins", SQLConnection)
        daLogin.TableMappings.Add("Table", "tblWorkshopAdmins")
        daLogin.Fill(dsLogin)
        dgvTest.DataSource = dsLogin
        dgvTest.Refresh()
        SQLConnection.Close()
    End Sub

The code provided on VB Helper had a line like this:

' Bind the DataGrid control to the Contacts DataTable.
dgContacts.SetDataBinding(m_DataSet, "Contacts")

However, in .Net 2.0, there is no SetDataBinding method available for DataGridView objects. I used the DataSource property instead, but the DataGridView doesn't show the data, although no error occurs.

Please advice

Still, clicking on the button doesn't make any data to show in the datagridview..!

I found the bug: I had set the Datasource of the DGV to dsLogin, I changed it back to none, and it is working now. Thanks for your helping me. Please follow me in my next question:

https://www.experts-exchange.com/questions/22049571/DataGridView-appearance.html

emoreau,
I think I'm stock with some parts of data manipulation. Would you please send me a VB.Net project code, which simply reads data from a table (say tblPeople, which has two columns, name and surname) of a database over TCP/IP connection, and updates that database when the content  of the gridview is updated by the user?

start by doing it with a local database. it will be the same thing for a remote database (over TCP/IP) execpts that configuration and firewall are a lot trickier to go through.

The problem I have now: I'm using Express Edition, and it doesn't come with remote database wizards. I build a project which works correctly with the local database. Then I try to change its connection string to a remote one, I fail! Second, I try to use the methods described in the vbhelper article, but I'm no good in understanding the roles of different data objects like TableAdapter etc, so I fail to make it work too!
I'm trying to sort out a way to follow the project on a computer with Standard or Professional edition of VS installed, however, I know that Express can compile the code generated with other versions (its compiler is the same, but it doesn't have some wizards and menus.) So I thought your sending a complete code to me could be of some help for now.

You arleady have such a thing. If you have an application that runs against a local database it will run against a remote database if this database is available. Can you connect to your database using SQL Server management studio over TCP/IP? If not, your application won't be able!

I can connect to the database correctly! I can't handle updating the changed tables.

Here is a piece of code I've derived from vbhelper sample. I get an error when I change the contents of a row, move to the next row, and close the form. The error message is:

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

I found out it means that I have to set the INSERT command for the TableAdapter object. I do this when I work with a local file database.

We have a line like this:

data_adapter = New SqlDataAdapter(SELECT_STRING, CONNECT_STRING)

And the error message sends, data_adapter cannot guess the correct UPDATE or INSERT command, by looking at the SELECT_STRING command which is a SELECT command.

I don't know how to fix this.

Code:

    Private Const SELECT_STRING As String = _
        "SELECT FName, LName FROM tblTeachers"
    'Private Const UPDATE_STRING As String = _
    '    "UPDATE tblTeachers SET FName='' AND LName=''"
    Private Const CONNECT_STRING As String = _
        "Data Source=localhost;Initial Catalog=xxx;User Id=xxx;Password=xxx;"

    ' The DataSet that holds the data.
    Private m_DataSet As DataSet

    ' Load the data.
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim data_adapter As SqlDataAdapter

        ' Create the SqlDataAdapter.
        data_adapter = New SqlDataAdapter(SELECT_STRING, CONNECT_STRING)

        ' Map Table to Contacts.
        data_adapter.TableMappings.Add("Table", "tblTeachers")

        ' Fill the DataSet.
        m_DataSet = New DataSet()
        data_adapter.Fill(m_DataSet)

        ' Bind the DataGrid control to the Contacts DataTable.
        ' Changed by Jim Rogers (jrogers@antaresnet.com)
        dgContacts.SetDataBinding(m_DataSet, "tblTeachers")
        'dgContacts.DataSource = m_DataSet.Tables("Contacts")
    End Sub

    ' Save any changes to the data.
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If m_DataSet.HasChanges() Then
            MsgBox("Yes")
            Dim data_adapter As SqlDataAdapter
            Dim command_builder As SqlCommandBuilder

            ' Create the DataAdapter.
            data_adapter = New SqlDataAdapter(SELECT_STRING, CONNECT_STRING)

            'Dim UpdateCommand As New System.Data.SqlClient.SqlCommand(UPDATE_STRING)
            'data_adapter.UpdateCommand = UpdateCommand

            ' Map Table to Contacts.
            data_adapter.TableMappings.Add("Table", "tblTeachers")
            Dim a As New SqlDataAdapter(

            ' Make the CommandBuilder generate the
            ' insert, update, and delete commands.
            command_builder = New SqlCommandBuilder(data_adapter)

            ' Uncomment this code to see the INSERT,
            ' UPDATE, and DELETE commands.
            'Debug.WriteLine("*** INSERT ***")
            'Debug.WriteLine(command_builder.GetInsertCommand.CommandText)
            'Debug.WriteLine("*** UPDATE ***")
            'Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)
            'Debug.WriteLine("*** DELETE ***")
            'Debug.WriteLine(command_builder.GetDeleteCommand.CommandText)

            ' Save the changes.
            data_adapter.Update(m_DataSet)
        End If
    End Sub

I later found out that the error was returned because the SELECT command I used didn't include a primary key column, and it should. (You cannot update a table without the primary key.) And the code below works (for people who may reach here later) :



Imports System.Data.SqlClient

Public Class Form1

    Private Const SELECT_STRING As String = _
        "SELECT ID, FName, LName FROM tblTeachers"

    Private Const CONNECT_STRING As String = _
        "Data Source=localhost;Initial Catalog=xxx;User Id=xxx;Password=xxx;"

    ' The DataSet that holds the data.
    Private m_DataSet As DataSet

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

        Me.dgContacts.DataMember = ""
        Me.dgContacts.Dock = System.Windows.Forms.DockStyle.Fill
        Me.dgContacts.Name = "dgContacts"
        Me.dgContacts.Size = New System.Drawing.Size(416, 205)
        Me.dgContacts.TabIndex = 0

        Dim data_adapter As SqlDataAdapter

        ' Create the SqlDataAdapter.
        data_adapter = New SqlDataAdapter(SELECT_STRING, CONNECT_STRING)

        ' Map Table to Contacts.
        data_adapter.TableMappings.Add("Table", "tblTeachers")

        ' Fill the DataSet.
        m_DataSet = New DataSet()
        data_adapter.Fill(m_DataSet)

        dgContacts.DataSource = m_DataSet.Tables("tblTeachers")
    End Sub

    ' Save any changes to the data.
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If m_DataSet.HasChanges() Or Not m_DataSet.HasChanges() Then
            Dim data_adapter As SqlDataAdapter
            Dim command_builder As SqlCommandBuilder

            ' Create the DataAdapter.
            data_adapter = New SqlDataAdapter(SELECT_STRING, CONNECT_STRING)

            'Dim UpdateCommand As New System.Data.SqlClient.SqlCommand(UPDATE_STRING)
            'data_adapter.UpdateCommand = UpdateCommand

            ' Map Table to Contacts.
            data_adapter.TableMappings.Add("Table", "tblTeachers")


            ' Make the CommandBuilder generate the
            ' insert, update, and delete commands.
            command_builder = New SqlCommandBuilder(data_adapter)

            ' Uncomment this code to see the INSERT,
            ' UPDATE, and DELETE commands.
            'Debug.WriteLine("*** INSERT ***")
            'Debug.WriteLine(command_builder.GetInsertCommand.CommandText)
            'Debug.WriteLine("*** UPDATE ***")
            'Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)
            'Debug.WriteLine("*** DELETE ***")
            'Debug.WriteLine(command_builder.GetDeleteCommand.CommandText)

            ' Save the changes.
            data_adapter.Update(m_DataSet)
        End If
    End Sub
End Class


Thanks for your support, emoreau

glad you found it!

Please help me here:
https://www.experts-exchange.com/questions/22051905/grid-view-and-unicode.html