Solved

DataSource from a remote SQL Server

Posted on 2006-11-05
13
343 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.
0
Comment
Question by:huji
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 17876486
Hi huji,

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

Cheers!
0
 
LVL 14

Author Comment

by:huji
ID: 17876555
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
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 400 total points
ID: 17876606
huji,

Change
> dgvTest.DataSource = dsLogin

for:
dgvTest.DataSource = dsLogin.Tables(0)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Author Comment

by:huji
ID: 17876700
Still, clicking on the button doesn't make any data to show in the datagridview..!
0
 
LVL 14

Author Comment

by:huji
ID: 17876720
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
0
 
LVL 14

Author Comment

by:huji
ID: 17888394
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?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 17888705
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.
0
 
LVL 14

Author Comment

by:huji
ID: 17888876
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.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 17888988
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!
0
 
LVL 14

Author Comment

by:huji
ID: 17889079
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
0
 
LVL 14

Author Comment

by:huji
ID: 17889271
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
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 17889307
glad you found it!
0
 
LVL 14

Author Comment

by:huji
ID: 17890329
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question