Solved

Connection Problem.

Posted on 2007-11-16
15
305 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 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