Solved

DataSource from a remote SQL Server

Posted on 2006-11-05
13
338 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
  • 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
Industry Leaders: 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Import a txt file into a DataGridView and TextBox 20 63
Optimize the query 5 50
Open a link in vb.net 2 25
ModalPopup  question 22 37
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

713 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