Solved

Connection Problem.

Posted on 2007-11-16
15
308 Views
Last Modified: 2013-11-26
HI,

Could somebody help what's wrong with below code:

Imports System.Data.OleDb

Dim strSQLServerName As String = "192.168.1.2"
Dim strSQLCatalog As String = "DATA"

dim strConnection as string = "Provider=SQLOLEDB.1;User Id=sa;Persist Security Info=False;Initial Catalog=" & strSQLCatalog & ";Data Source=" & strSQLServerName

Dim conApp As New OleDbConnection(strConnection)

Dim daTable As New SqlDataAdapter(strSqlCommand, strConnection)
conApp.Open()

I used to do it using vb6 with the string connection, and it is okay.
I use sql server 7

Please help.

Thank you.



0
Comment
Question by:emi_sastra
[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
  • 7
  • 4
  • 3
15 Comments
 
LVL 5

Expert Comment

by:pavaneeshkumar
ID: 20299090
can u provide the error generated when the connection faild
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20299134
1) The data adapter internally handles the connection, so you don't need to create an external connection object.

2) You should use the System.Data.SqlClient with SQL Server

3) The SQL Server client assumes the provider, so you can't specify it in the connection string.

4) It is generally accepted to store the connection string into an app.config file or settings.

5) Suggested changes:

Imports System.Data.SqlClient
Imports System.Configuration

...

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("Name").ConnectionString

        Dim table As New DataTable()
        Dim commandText As String = "Select * From Table1"
        Using adapter As New SqlDataAdapter(commandText, connectionString)
            adapter.Fill(table)
        End Using

6) Memory management with .NET is handled by the garbage collector, and it runs when the system dictates.  One way to control when an object is destroyed is to implement the IDisposable interface.  The SqlDataAdapter implements the Dispose method.

7) Using blocks are language extension that will implicitly call the Dispose method for any object the implements IDisposable, without having to write the code yourself.

Bob
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20299225
The error is "Key not supported : 'provider'.

Dim connectionString As String = ConfigurationManager.ConnectionStrings("Name").ConnectionString

What is in the "Name"?

Thank you.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 20299337
Here is an example of using the app.config file to store the connection string:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>  
  <connectionStrings>      
      <add name="MyCnString" connectionString="Data Source=legolas;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>  
  </connectionStrings>
</configuration>

For this example, the name is 'MyCnString'.  This value can be anything that you wish that is meaningful for the connection.  You can have multiple connection string entries in the <connectionStrings> section.  

I was trying to say before that when specifying the connection string for SQL Server, you should remove the Provider= portion, since that SqlClient doesn't support anything other than the SQL Server provider.

Bob
0
 
LVL 5

Expert Comment

by:pavaneeshkumar
ID: 20299393
use this
Dim strSQLServerName As String = "192.168.1.2"
 
Dim strSQLCatalog As String = "DATA"
 
 
dim strConnection as string = "User Id=sa;Persist Security Info=False;Initial Catalog=" & strSQLCatalog & ";Data Source=" & strSQLServerName
 
 
Dim conApp As New OleDbConnection(strConnection)
 
 
Dim daTable As New SqlDataAdapter(strSqlCommand, strConnection)
 
try
{
	conApp.Open()
 
}
catch(SqlException excp)
{
	//do some thing with error
}

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20299428
Dim connectionString As String = ConfigurationManager.ConnectionStrings(strConnection).ConnectionString

Name 'ConfigurationManager' is not declared.      

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20299472
Error
 An OLE DB Provider was not specified in the connection string. An example would be , 'Provider=SQLOLEDB'

Thank you.
0
 
LVL 5

Expert Comment

by:pavaneeshkumar
ID: 20299591
conApp Should be SqlConnection not a OleDbConnection
import System.Data.SqlClient;
 
 
dim conApp as SqlConnection;
Dim strSQLServerName As String = "192.168.1.2"
 
 
Dim strSQLCatalog As String = "DATA"
 
 
 
dim strConnection as string = "User Id=sa;Persist Security Info=False;Initial Catalog=" & strSQLCatalog & ";Data Source=" & strSQLServerName
 
 
Dim conApp As New OleDbConnection(strConnection)
 
 
Dim daTable As New SqlDataAdapter(strSqlCommand, strConnection)
 
 
 
set conApp=conApp=new SqlConnection(strConnection);
 
try
 
{
 
        conApp.Open()
 
 
}
 
catch(SqlException excp)
 
{
    
    //do some thing with error
 
}

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20299661
Imports System.Data.OleDb

 Private Sub btnGetRemoteData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetRemoteData.Click
        Dim dsTable As DataSet

        Dim strSQLServerName As String = "192.168.1.2"
        Dim strSQLCatalog As String = "DATA"

        strConnection = "User Id=sa;Persist Security Info=False;Initial Catalog=" & strSQLCatalog & ";Data Source=" & strSQLServerName

        strTableName = "TMMERK"
        strFieldsName = "*"

        Try
            dsTable = OpenData(strFieldsName, strTableName, "", "FullName", strConnection)
            DataGridView1.DataSource = dsTable.Tables(strTableName)

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

Public Function OpenData(ByVal strFields As String, _
                             ByVal strFileName As String, _
                             ByVal strCriteria As String, _
                             ByVal strOrderBy As String, _
                             ByVal strConnection As String) As DataSet

        Dim conApp As New OleDbConnection(strConnection)

        Dim dsTable As New DataSet()
        Dim strSqlCommand As String

        strSqlCommand = "SELECT " & strFields & " FROM " & strFileName

        If Trim(strCriteria) <> "" Then
            strSqlCommand = strSqlCommand & strCriteria
        End If

        If Trim(strOrderBy) <> "" Then
            strSqlCommand = strSqlCommand & " ORDER BY " & strOrderBy
        End If

        Try
            Dim daTable As New SqlDataAdapter(strSqlCommand, strConnection)

            conApp.Open()

            daTable.Fill(dsTable, strFileName)
            conApp.Close()

        Catch ex As Exception
            conApp.Close()
            Throw ex

        End Try

        Return dsTable

    End Function
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20300131
I love it when I give advice and none of it was heeded.  I hope you find a solution to your problem.

Bob
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20302441
I don't understand what you meant?

The code I provide to you still have an error at connection.
An OLE DB Provider was not specified in the connection string. An example would be , 'Provider=SQLOLEDB'
 
The same as :

I try to use your code :
Dim conApp As New OleDbConnection(strConnection)

 An OLE DB Provider was not specified in the connection string. An example would be , 'Provider=SQLOLEDB'

Thank you.
      
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20302534
Previous comment:

conApp Should be SqlConnection not a OleDbConnection

At last,

I just change :  OleDbConnection to SqlConnection  then the connection established.

Thank you.

One problem using configuration:

Att app.config:
<configuration>
  <connectionStrings>
    <add name="MyCnString" connectionString="Data Source=LOCALHOST;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
Dim connectionString As String = ConfigurationManager.ConnectionStrings(strConnection).ConnectionString

The error is "Name 'ConfigurationManager' is not declared".      

What's wrong with the above code?

Thank you.
0
 
LVL 5

Accepted Solution

by:
pavaneeshkumar earned 500 total points
ID: 20373471
which version of dot net frame work you are using.

as per my understanding ConfigurationManager is available with framework version 3 & higher.

for version lower than 3 you need to use onfigurationSettings.AppSettings

Change in configuration file is also required.



//App.config
//___________________________
<configuration>
	<appSettings>
		<add key="MyCnString" value="Data Source=LOCALHOST;Initial Catalog=Northwind;Integrated Security=True"/>
	</appSettings>
</configuration>
 
//____________________________________________________________
 
//     VB Code
//____________________________________________________________
Imports System.Data.SqlClient
Imports System.Configuration
Dim connectionString As String = System.Configuration.ConfigurationSettings.AppSettings["MyCnString"].ToString

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20552940
I am sorry, I missed this question.
Thank you.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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