Solved

Connection Problem.

Posted on 2007-11-16
15
299 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

15 Experts available now in Live!

Get 1:1 Help Now