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.

Attachments Image182.png 23.42 KB

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.)

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)

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

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.

Edit:

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

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!

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! :)

@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)

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!

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.

Edited 2 Years Ago by xuexue

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

and for your query:

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

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)

Edited 2 Years Ago by cgeier

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! :)

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#

Edited 2 Years Ago by cgeier

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.

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

Edited 2 Years Ago by 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

Edited 2 Years Ago by cgeier

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.

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.

Attachments Image184.png 20.76 KB

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

Edited 2 Years Ago by 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

7b081373635bc3fabb6eb0b89e564ed6

After connecting:
8789ab94d254ec8cfba4dcd31a80f3e1

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

d697a118b59fbcd8570607f1dcfbe573

To quit clpplus:
SQL> quit

Edited 2 Years Ago by cgeier

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.

654a71da0e6e9a15694bcc33b24b9535

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

6f52d23b4339a2022b39c68c6bb22344

Thank you so much! :)

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! :)

This question has already been answered. Start a new discussion instead.