We help IT Professionals succeed at work.

DataSource from a remote SQL Server

huji
huji asked
on
Medium Priority
458 Views
Last Modified: 2008-03-17
I want to create a datasource which connects to a remote SQL server. I can connect to that server with the following code:

        Dim strConnectionString As String
        strConnectionString = "Data Source=xxx.xxx.xxx.xxx;Initial Catalog=xxx;User Id=xxx;Password=xxx;"
        Dim SQLConnection As New SqlClient.SqlConnection(strConnectionString)
        SQLConnection.Open()
        SQLConnection.Close()

However, I don't know how to create a Data Source to use with datagrids, etc.
Comment
Watch Question

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
Hi huji,

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

Cheers!

Author

Commented:
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
Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
huji,

Change
> dgvTest.DataSource = dsLogin

for:
dgvTest.DataSource = dsLogin.Tables(0)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

Author

Commented:
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:

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_22049571.html

Author

Commented:
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?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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.

Author

Commented:
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.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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!

Author

Commented:
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

Author

Commented:
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
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
glad you found it!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.