Solved

DataSource from a remote SQL Server

Posted on 2006-11-05
13
329 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 69

Expert Comment

by:Éric Moreau
Comment Utility
Hi huji,

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

Cheers!
0
 
LVL 14

Author Comment

by:huji
Comment Utility
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 69

Accepted Solution

by:
Éric Moreau earned 400 total points
Comment Utility
huji,

Change
> dgvTest.DataSource = dsLogin

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

Author Comment

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

Author Comment

by:huji
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
glad you found it!
0
 
LVL 14

Author Comment

by:huji
Comment Utility
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now