declaration of connection string using ado

[xuexue]

Hi!

My database is in AS/400 DB2 hence, I am using SQL.
Here's my line of code for opening a connection.

Dim str As String = "User ID=EDITH;Password=EDITH;Data Source=10.0.1.11;Initial Catalog=ictms;Integrated Security=True"
Dim conn As SqlConnection = New SqlConnection(str)

conn.Open()

Now, I wanted to execute a delete query, however, I already get errors in the conn.Open() area.
Please check the attached file for the error.

[jeffcogswell]

SQL Server is touchy about the names. Depending on how it's configured you may or may not be able to just use an IP address like you're using in the connection string. When I'm faced with this, I first try to make sure I can connect to the server using SQL Management Studio. First try it with the IP address like in your connection string. If it doesn't connect, you'll need to check the actual name of the server and try that. Then if Management Studio can connect, you know you're good, and you can paste the name back into your connection string.

Also, just a thought as I look again at your connection string. It looks like you have both integrated security (which refers to logging in with your existing Windows account), as well as a username/password. I'll need to check, but I think you shouldn't have both. (I might be wrong there, though.)

[cgeier]

The poster stated she is using DB2, not SQL Server.

SqlConnection Class
Represents an open connection to a SQL Server database

Use ODBC or OleDB.

Connection Strings

[xuexue]

Hi Sir's! cgeier is right! I am connecting to an AS/400 Server which uses DB2 as its database. Now, I am using ADO.net to connect to the database in ASP.NET.

    Dim str As String = "User ID=EDITH;Password=EDITH;Data Source=10.0.1.11;Initial Catalog=ictms"
    Dim conn As SqlConnection = New SqlConnection(str)
    conn.Open()

The problem is that in conn.open() it says
SQLException was unhandled by user code

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

[newbie26]

hi. try to check your web config. You can add your connection string there:

<connectionStrings>
    <clear/>

    <add name="ConnectionString" connectionString="Provider=SQLOLEDB;Data Source=10.0.1.11;Persist Security Info=True;Password=EDITH;User ID=EDITH;Initial Catalog=ictms" providerName="System.Data.OleDb"/>

</connectionStrings>

then call this connection in your form:

Dim SqlConnection As New Connection
        Dim sConSql As String = SqlConnection.GetSqlConnectionString
        Dim conSql As New SqlConnection(sConSql)

    conSql.open()

Hope this helps

[newbie26]

I just forgot... Add a class for opening your connection in each form:

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Configuration

Public Class Connection

    Public Function GetSqlConnectionString() As String

        Dim sConSql As String = ConfigurationManager.ConnectionStrings("conStrSQL").ConnectionString
        If String.IsNullOrEmpty(sConSql) Then
            Throw New Exception("Connection String not available in configuration file.")
        Else
            Return sConSql
        End If

    End Function

End Class

you can name this as Connection.vb

then import this in your form:

Imports MyProject.Connection

then open the connection just like I posted above.

[newbie26]

Edit:

Dim sConSql As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

[cgeier]

Installing IBM Data Server drivers and clients

The following is for C#, but can be easily translated to VB .NET:

Connection to DB2 from .NET

[xuexue]

hi newbie26, i see that you used OLEDB for the connection. But I am trying to do it using ADO? Is it still the same?

Thanks!

[xuexue]

hi! i just saw this on our Web.config, now I would like to know, how can i use it to declare a connection on my .vb page.

What i will do is to have a delete query.

  <connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
      providerName="System.Data.SqlClient" />
    <add name="ConnectionString" connectionString="DataSource=S068F8CP;DefaultCollection=ictms;Naming=SQL"
      providerName="IBM.Data.DB2.iSeries" />
  </connectionStrings>

Thanks! :)

[newbie26]

@xuexue..you can add your own connection string in your web.config:

<add name="conStr" connectionString="Provider=SQLOLEDB;Data Source=10.0.1.11;Persist Security Info=True;Password=EDITH;User ID=EDITH;Initial Catalog=ictms" providerName="System.Data.OleDb"/>

just change your provider to what it is..

then as I have posted above, add a module for your connection.

Public Function GetSqlConnectionString() As String

    Dim sConSql As String = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
    If String.IsNullOrEmpty(sConSql) Then
        Throw New Exception("Connection String not available in configuration file.")
    Else
        Return sConSql
    End If

End Function

and now, you can call that in your form:

Imports MyProject.Connection

Dim SqlConnection As New Connection
    Dim sConSql As String = SqlConnection.GetSqlConnectionString
    Dim conSql As New SqlConnection(sConSql)

[xuexue]

Hi! The code I sent it is the one that we will use. The problem is that how to use on .vb file. How to make a simple query for select/delete.

Thansk!

[newbie26]

so, first thing is that you cannot connect to the server?

try this site:

http://www.codeproject.com/Questions/335874/A-network-related-or-instance-specific-error-occurt

[xuexue]

Hi! As I said, we do not connect to an SQL Server. We connect to our AS400 Server which stores the DB2 Database.

Also, my problem is how to make a connection declaration on the .vb file so that I can code the query for delete. How to open a connection in the .vb file.

[newbie26]

hi. the link i posted is about the error you posted before which i thought would help. my apology if it didn't.. :'(

anyway, have you tried to edit your code like this:

Dim cn As DB2Connection = New DB2Connection("Database=mydatabse;UserID=yourid; Password=youpassword;Server=AS400")
cn.Open

[newbie26]

and for your query:

Dim cmd As DB2Command = New DB2Command("SELECT * from myTable", cn)
Dim count As Integer = Convert.ToInt32(cmd.ExecuteScalar)

[newbie26]

check this link:

http://www.codeproject.com/Articles/4870/Connect-to-DB-from-Microsoft-NET

[cgeier]

Go to IBM Fix Central website:
Fix Central

Click "Find Product" tab
In "Product Selector", enter IBM Data Server Client Packages
For "Installed Version", select 10.5.*
For "Platform", select Windows
Click Continue
Select Browse for fixes radio button
Click Continue

Download and install fix pack that says "IBM Database Add-Ins for Visual Studio"

May also need to download/install "IBM Data Server Driver Package (Windows/x86-32 32bit) V10.5 Fix Pack 3" for 32-bit OS. Or "IBM Data Server Driver Package (Windows/x86-64 64bit) V10.5 Fix Pack 3"

Add Reference:

• Click "Project" (in menu)
• Select "Add Reference"
• Select "Add Reference"
• Select ".NET"
• Select "IBM.Data.DB2"
• Click "OK"

Add Imports IBM.Data.DB2

Try the following:

        Dim connectStr As String
        Dim sqlText As String
        Dim conn As New DB2Connection

        connectStr = "Database=Sample;Server=127.0.0.1:50000;Uid=db2admin;Pwd=yourpass;"


        conn = New DB2Connection(connectStr)
        conn.Open()


        sqlText = "Select * from SchemaName.Employee"

        Dim cmd As New DB2Command(sqlText, conn)

Where "127.0.0.1" is your IP address, and "50000" is your port number, and "SchemaName" is your desired schema name (ex: user1)

[xuexue]

hi @cgeier, the code works already! The problem is that in the conn.open, error occurs.

ERROR [08001] [IBM] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "10.0.1.11".  Communication function detecting the error: "connect".  Protocol specific error code(s): "10061", "*", "*".  SQLSTATE=08001

Thanks! :)

[cgeier]

Here are multiple ways of retrieving data from DB2. They were tested using the express version of DB2.

See previous post on installing appropriate packages and adding reference to "IBM.Data.DB2" and imports statement: Imports IBM.Data.DB2

In the following examples, I will use the following:

• Schema name: HR
• Database name: Sample
• IP Address: 127.0.0.1
• Port: 50000

Version 1 & version 2 are preferred methods.

Version 1: (using DB2Connection and DB2DataAdapter)

• Add Imports IBM.Data.DB2

  Private Sub readDataFromDB2NETv1()
      Dim connectStr As String
      Dim sqlText As String
      Dim conn As New DB2Connection
  
      'without TrustedContext
      connectStr = "Database=Sample;Server=127.0.0.1:50000;Uid=db2admin;Pwd=nopass;"
      'connectStr = "Database=Sample;Server=127.0.0.1:50000;UserID=db2admin;Password=nopass;"
  
      'if TrustedContext has been set up
      'connectStr = "Database=Sample;Server=127.0.0.1:50000;UserID=db2admin;Password=nopass;TrustedContextSystemUserID=masteruser;TrustedContextSystemPassword=masterpassword"
  
  
      Try
  
          conn = New DB2Connection(connectStr)
          conn.Open()
  
          sqlText = "Select * from HR.Employee"
  
          Dim da As New DB2DataAdapter(sqlText, conn)
  
          Dim rowsRetrieved As Integer
          Dim output As String = String.Empty
  
          Dim dt As DataTable = New DataTable()
  
          'fill DataTable
          rowsRetrieved = da.Fill(dt)
  
          'close connection
          conn.Close()
  
          For Each row As DataRow In dt.Rows
              Dim col0 As String = row(0).ToString()
              Dim col1 As String = row(1).ToString()
              Dim col2 As String = row(2).ToString()
  
              output += col0 & " " & col1 & " " & col2
              output += System.Environment.NewLine
          Next
  
          MessageBox.Show(output)
  
      Catch ex As DB2Exception
          MessageBox.Show(ex.Message)
      Catch ex As Exception
          MessageBox.Show(ex.Message)
      End Try
  End Sub
  

Version 2 (using DB2Connection and DB2DataReader):

• Add Imports IBM.Data.DB2

  Private Sub readDataFromDB2NETv2()
      Dim connectStr As String
      Dim sqlText As String
      Dim conn As New DB2Connection
  
      'without TrustedContext
      'connectStr = "Database=Sample;Server=127.0.0.1:50000;Uid=db2admin;Pwd=nopass;"
      connectStr = "Database=Sample;Server=127.0.0.1:50000;UserID=db2admin;Password=nopass;"
  
      'if TrustedContext has been set up
      'connectStr = "Database=Sample;Server=127.0.0.1:50000;UserID=db2admin;Password=nopass;TrustedContextSystemUserID=masteruser;TrustedContextSystemPassword=masterpassword"
  
      Try
          conn = New DB2Connection(connectStr)
          conn.Open()
  
          sqlText = "Select * from HR.Employee"
  
          Dim output As String = String.Empty
  
          Dim cmd As New DB2Command(sqlText, conn)
          Dim reader As DB2DataReader
          reader = cmd.ExecuteReader
  
          output = String.Empty
          While (reader.Read())
              output += reader(0) & " " & reader(1) & reader(2)
              output += System.Environment.NewLine
          End While
  
          'close connection
          conn.Close()
  
          MessageBox.Show(output)
  
      Catch ex As DB2Exception
          MessageBox.Show(ex.Message)
      Catch ex As Exception
          MessageBox.Show(ex.Message)
      End Try
  End Sub
  

Version 3 (OleDB):

• Add Imports System.Data.OleDb

  Private Sub readDataFromDB2UsingOleDB()
  
      Dim conn As OleDbConnection
      Dim connectStr As String
  
      Try
  
          connectStr = "Provider=IBMDADB2;Database=Sample;Hostname=127.0.0.1;Schema=HR;Protocol=TCPIP;Port=50000;Uid=db2admin;Pwd=nopass;"
  
          conn = New OleDbConnection(connectStr)
          conn.Open()
  
          Dim sqlText As String = "Select * from HR.Employee"
  
          Dim da As New OleDbDataAdapter(sqlText, conn)
  
          Dim rowsRetrieved As Integer
          Dim output As String = String.Empty
  
          Dim dt As DataTable = New DataTable()
  
          'fill DataTable
          rowsRetrieved = da.Fill(dt)
  
          'close connection
          conn.Close()
  
          For Each row As DataRow In dt.Rows
              Dim col0 As String = row(0).ToString()
              Dim col1 As String = row(1).ToString()
              Dim col2 As String = row(2).ToString()
  
              output += col0 & " " & col1 & " " & col2
              output += System.Environment.NewLine
          Next
  
          MessageBox.Show(output)
  
          Console.WriteLine(output)
      Catch ex As OleDb.OleDbException
          MessageBox.Show(ex.Message)
      Catch ex As Exception
          MessageBox.Show(ex.Message)
      End Try
  
  End Sub
  

Version 4 (using ADODB and OleDbDataAdapter):

Add Reference for ADODB:

• Click "Project" (in menu)
• Select "Add Reference"
• Click "COM" tab

• Select "Microsoft ActiveX Data Objects x.x Library"

• Add Imports ADODB

• Add Imports System.Data.OleDb

  Private Sub readDataFromDB2UsingADODBv2()
  
      Dim connectStr As String = "Provider=IBMDADB2;Database=Sample;Hostname=127.0.0.1;Schema=HR;Protocol=TCPIP;Port=50000;Uid=db2admin;Pwd=nopass;"
  
      Dim adoConn As New ADODB.Connection()
  
      Try
  
          adoConn.ConnectionString = connectStr
          adoConn.Open()
  
          Dim sqlText As String = "Select * from HR.Employee"
  
          Dim rs As New ADODB.Recordset()
  
          rs.Open(sqlText, adoConn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 0)
  
          Dim da As OleDbDataAdapter = New OleDbDataAdapter()
  
          Dim rowsRetrieved As Integer
          Dim output As String = String.Empty
  
          'Dim ds As DataSet = New DataSet()
  
          'fill DataSet
          'rowsRetrieved = da.Fill(ds, rs, "Employee")
  
          'For Each row As DataRow In ds.Tables("Employee").Rows
          'Dim col0 As String = row(0).ToString()
          'Dim col1 As String = row(1).ToString()
          'Dim col2 As String = row(2).ToString()
  
          'output += col0 & " " & col1 & " " & col2
          'output += System.Environment.NewLine
          'Next
  
          'MessageBox.Show(output)
  
          Dim dt As DataTable = New DataTable()
  
          'fill DataTable
          rowsRetrieved = da.Fill(dt, rs)
  
          'close connection
          rs.Close()
          adoConn.Close()
  
          For Each row As DataRow In dt.Rows
              Dim col0 As String = row(0).ToString()
              Dim col1 As String = row(1).ToString()
              Dim col2 As String = row(2).ToString()
  
              output += col0 & " " & col1 & " " & col2
              output += System.Environment.NewLine
          Next
  
          MessageBox.Show(output)
  
      Catch ex As OleDbException
          MessageBox.Show(ex.Message)
      Catch ex As Exception
          MessageBox.Show(ex.Message)
      End Try
  End Sub
  

Version 5 (ADODB):

Add Reference for ADODB:

• Click "Project" (in menu)
• Select "Add Reference"
• Click "COM" tab

• Select "Microsoft ActiveX Data Objects x.x Library"

• Add Imports ADODB

  Private Sub readDataFromDB2UsingADODBv1()
  
      Dim connectStr As String = "Provider=IBMDADB2;Database=Sample;Hostname=127.0.0.1;Schema=HR;Protocol=TCPIP;Port=50000;Uid=db2admin;Pwd=nopass;"
  
      Dim adoConn As New ADODB.Connection()
  
      Try
  
          adoConn.ConnectionString = connectStr
          adoConn.Open()
  
          Dim sqlText As String = "Select * from HR.Employee"
  
          Dim rs As New ADODB.Recordset()
          rs.Open(sqlText, adoConn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 0)
  
          Dim output As String = String.Empty
  
          While (rs.EOF = False)
              If (rs.Fields(0).Value = Nothing) Then
                  output += System.Environment.NewLine
              Else
                  output += rs.Fields(0).Value & " " & rs.Fields(1).Value & " " & rs.Fields(2).Value
                  output += System.Environment.NewLine
              End If
  
              'move to next record
              rs.MoveNext()
          End While
  
          'close connection
          rs.Close()
          adoConn.Close()
  
          MessageBox.Show(output)
  
      Catch ex As Exception
          MessageBox.Show(ex.Message)
      End Try
  End Sub
  

Version 6 (ODBC):

• Add Imports System.Data.Odbc

  Private Sub readDataFromDB2UsingOdbc()
      Dim conn As OdbcConnection
      Dim connectStr As String
  
  
      Try
          connectStr = "Driver={IBM DB2 ODBC Driver}; HostName=127.0.0.1;Port=50000;Protocol=TCPIP; Database=SAMPLE; Uid=db2admin; Pwd=nopass;"
  
          conn = New OdbcConnection(connectStr)
          conn.Open()
  
          Dim sqlText As String = "Select * from HR.Employee"
  
          Dim da As New OdbcDataAdapter(sqlText, conn)
  
          Dim rowsRetrieved As Integer
          Dim output As String = String.Empty
  
          Dim dt As DataTable = New DataTable()
  
          'fill DataTable
          rowsRetrieved = da.Fill(dt)
  
          'close connection
          conn.Close()
  
          For Each row As DataRow In dt.Rows
              Dim col0 As String = row(0).ToString()
              Dim col1 As String = row(1).ToString()
              Dim col2 As String = row(2).ToString()
  
              output += col0 & " " & col1 & " " & col2
              output += System.Environment.NewLine
          Next
  
          MessageBox.Show(output)
  
      Catch ex As Odbc.OdbcException
          MessageBox.Show(ex.Message)
      Catch ex As Exception
          MessageBox.Show(ex.Message)
      End Try
  End Sub
  

Resources:
Developing ADO.NET and OLE DB Applications

IBM DB2 8 for Linux, UNIX, and Windows documentation

• Click "Developing" (left menu)
• Click "Database Applications" (left menu)
• Click "Programming Applications" (left menu)
• Click "ADO.NET, OLE DB, and ODBC"
  See topics under each link.

ADODB connection string from this post: Click Here

DB2 Connection Strings in Ado.Net

Connecting to a DB2 database using C#

[xuexue]

Hi! This is my simple code just to test if it will work.

Imports IBM.Data.DB2

Dim str As String
        Dim sql As String
        Dim conn As New DB2Connection

        str = "Database=ictms;Server=10.0.1.11:50000;Uid=edith;Pwd=edith;"
        conn = New DB2Connection(str)
        conn.Open()

        sql = "Select * from ictms.import"

        Dim cmd As New DB2Command(sql, conn)

Error occurs on theconn.Open() part.

I tried using telnet 10.0.1.111 50000 it says, could not connect, connection failed.

I tried to create a new rule on the Windows Firewall Security Advanced Settings, opening TCP Port 50000 but still it does not work.

[cgeier]

Use command line processor (CLP) from IBM Data Server Runtime Client or Command line process plus (CLPP) from IBM Data Server Driver Package

See also:

IBM Knowledge Center

• Click "Database fundamentals"
• Click "Installing"
• Click "Installing IBM Data Server drivers and clients"

Additional info:

• Click "Developing code for accessing and managing data"
• Click "Database applications"
  See desired links (ADO.NET, OLE DB, etc...)

To connect in DB2 Command Line Processor Plus:

SQL> connect user1/'YourPassword'@127.0.0.1:50000/sample

where:
user1 = you user name
YourPassword = your password
127.0.0.1 = your IP address
50000 = port number
sample = database name

[cgeier]

After installation, DB2 Command Line Processor Plus can be found in:

(for Win 7):
* Start
* All Programs
* IBM DB2...
* DB2 Command Line Processor Plus

[xuexue]

hi sir! I've installed the two softwares I needed, but I cannot find this IBM DB2 folder in the All Programs but all the installation were successful.

[xuexue]

Hi! I've already installed DB2 Command Line Processor but the command line states db2=> how can i shift to sql? I've tried inputting SQL> connect user1/'YourPassword'@127.0.0.1:50000/sample but error.

[cgeier]

That syntax is for DB2 Command Line Processor Plus. You are trying to use it in DB Command Line Processor. They are two different tools.

When using DB2 Command Line Processor Plus you will see: SQL>

When using DB2 Command Line Processor you will see: db2=>

[cgeier]

I think that you may need to use Command Line Processor Plus (CLPP) from IBM Data Server Driver Package.

To download and install:
IBM Support Fix Central

Method 1:

• Click "Select product" tab
• For "Product Group", select "Information Management"
• For "Select from Information Management", select "IBM Data Server Client Packages"
• For "Installed Version", select "10.5.*"
• For "Platform", select "Windows"
• Click "Continue"
• Click "Continue" (Browse for fixes radio button should be checked by default)

Method 2:

• Click "Find product" tab
• For "Product selector", ente "Information Management"
• For "Product selector " enter "IBM Data Server Client Packages"
• For "Installed Version", select "10.5.*"
• For "Platform", select "Windows"
• Click "Continue"
• Click "Continue" (Browse for fixes radio button should be checked by default)

Download and install "IBM Data Server Driver Package":

For 32-bit:
Download and install "IBM Data Server Driver Package (Windows/x86-32 32 bit) V10.5 Fix Pack 3

For 64-bit:
Download and install "IBM Data Server Driver Package (Windows/x86-64 64 bit) V10.5 Fix Pack 3

After installation, DB2 Command Line Processor Plus can be found in:

(for Win 7):

• Start
• All Programs
• IBM DB2 DB2COPY1 (or IBM DB2...))
• DB2 Command Line Processor Plus

or

• Start
• All Programs
• Accessories
• Command Prompt
• Type: clpplus

Note: clpplus runs "C:\Program Files\IBM\SQLLIB\BIN\clpplus.bat" (for 32-bit)

Then:

SQL> connect user1/'YourPassword'@127.0.0.1:50000/sample

where
user1 = you user name
YourPassword = your password
127.0.0.1 = your IP address
50000 = port number
sample = database name

After connecting:

Run a query on HR.Employee:
SQL> select count(*) from HR.Employee;

To quit clpplus:
SQL> quit

[cgeier]

To find your port number:

How to identify DB2 port number

Using DB2 Control Center:
How to find the DB2 Connection Port Number on Windows

Locating the DB2 connection port number on UNIX

[xuexue]

Hi! I'm sorry I overlooked the DB2 Command Line Processor Plus. I already inputted the instructions you told me but still I encountered errors. Kindly check what I am doing wrong.

Then I tried to get the port number using get dbm cfg but this is only what I get.

Thank you so much! :)

[xuexue]

Hi Sir! The code for DB2 really didn't work for me :(

But I tried this one since where using AS400 for DB2 iSeries.

Imports IBM.Data.DB2
Imports IBM.Data.DB2.iSeries

Dim str, sql, sqlUpdate As String
Dim conn As New iDB2Connection
Dim cmd, cmdUpdate As New iDB2Command
Dim count As Integer

'Opening of iDB2Connection
str = "Datasource=10.0.1.11;UserID=edith;password=edith;"
conn = New iDB2Connection(str)
conn.Open()

'SQL Query
sql = "SELECT COUNT(*) AS count FROM ictms.import"
cmd = New iDB2Command(sql, conn)

'SQL Query Execution
count = Convert.ToInt32(cmd.ExecuteScalar)

sqlUpdate = "UPDATE ictms.import SET document_number=123456 WHERE recid=11186"
cmdUpdate = New iDB2Command(sqlUpdate, conn)

conn.Close()

THe code for counting the records works as I've checked it already. Thanks so much! :)