Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Connection Problem.

Posted on 2007-11-16
15
Medium Priority
?
315 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

916 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