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.
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.)
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.
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 saysSQLException 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
Installing IBM Data Server drivers and clients
The following is for C#, but can be easily translated to VB .NET:
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!
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
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.
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:
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)
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:
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:
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:
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
ADODB connection string from this post: Click Here
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:
Additional info:
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
After installation, DB2 Command Line Processor Plus can be found in:
(for Win 7):
* Start
* All Programs
* IBM DB2...
* DB2 Command Line Processor Plus
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.
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=>
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:
Method 2:
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):
or
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
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
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! :)
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! :)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.