Add to Google Reader or Homepage

Errorjava.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

        In this post i am going to tell you all about the most commonly found Exception:- Data source not found and no default driver specified.This is one of the sql Exception which would be thrown when the jdbc:odbc connection is not performed correctly.First of all we must know about the two important things in any database connection

 Data source name:


 Data source name is nothing but the database name in which u would create tables to
 store the data.

Default Drivers:


There are many drivers available in your computer for making jdbc:odbc connection each for specific database.For eg Microsoft Access Driver for creating a connection with the MS Access database.Similarly for sql,mysql and sybase.

This error would occur when creating a jdbc:odbc connection for MS Access database.when i am doing a simple mini-project which makes use of  MS Access for back-end support i encountered this error.

Here is the part of the project code which involves making a database connection

Program:

 

import java.io.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
class Dem extends JFrame
{
public Dem()
{
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
this.setVisible(false);
this.setSize(400,400);
this.setTitle("ADDRESS BOOK");
bp1=new JPanel();
bp2=new JPanel();
bp1.setLayout(new GridLayout(4,4));
label1=new JLabel("NAME");
text1=new JTextField(25);
bp1.add(label1);
bp1.add(text1);
label2=new JLabel("ADDRESS");
bp1.add(label2);
text2=new JTextField(25);
bp1.add(text2);
label3=new JLabel("SEX");
bp1.add(label3);
text3=new JTextField(25);
bp1.add(text3);
label4=new JLabel("PHONE NO");
bp1.add(label4);
text4=new JTextField(25);
bp1.add(text4);
JButton back=new JButton("BACK");
bp2.add(back);
back.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e)
{
setVisible(false);
}
});
//To create database
create();

JButton submit=new JButton("SUBMIT");
bp2.add(submit);
submit.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e1)
{
insert();
}
});
JButton reset=new JButton("RESET");
reset.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e7)
{
text1.setText("");
text2.setText("");
text3.setText("");
text4.setText("");
}
});
bp2.add(reset);
add(bp1,BorderLayout.NORTH);
add(bp2,BorderLayout.SOUTH);
}
public void create() 
{
try
{

//Creating the database connection
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dsn="prodb21";
String url="jdbc:odbc:"+dsn;
Connection con=DriverManager.getConnection(url,"","");
try
{
Statement s=con.createStatement();
s.execute("create table prod21(name varchar(20),address varchar(20),sex varchar(10),phno varchar(12))");
s.close();
}
finally
{
con.close();
}
}
catch(Exception err)
{
System.out.println("Error"+err);
}
}
public void insert()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:prodb21");
try
{
PreparedStatement pstm=con.prepareStatement("insert into prod21 values(?,?,?,?)");
pstm.setString(1,text1.getText());
pstm.setString(2,text2.getText());
pstm.setString(3,text3.getText());
pstm.setString(4,text4.getText());
pstm.executeUpdate();
}
finally
{
con.close();
}
}
catch(Exception err1)
{
System.out.println("error"+err1);
}
}
 JPanel bp1,bp2;
JLabel label1,label2,label3,label4;
JTextField text1,text2,text3,text4;
}



In the above program,under the comment line Creating the database connection,i have used the data source name as Prodb21 and the table name as prod21. Lets see how i created the database connection for this part of the program. 

 

Creating a jdbc:odbc Connection:


1) Go to control panel and choose System Security->Administrative tools or simply search in the search toolbar in the control panel for the keyword ODBC.

2) In that Administrative tools window select Data Sources(ODBC).

3) Then ODBC data source administrator window will appear.In that select System DSN tab
On the right side of the window there will be a ADD button.Click it to add a driver for this database connection,then create new source window will appear.

4) In that window select Microsoft Access Window(*.mdb) and click Finish.

5) Then ODBC Microsoft Access Setup window will appear.Give the database name in the data source name field and click create.

6) In the next window select the location for the database and give the database name once again in the top left corner field and click ok.

7) Then a window will prompt a message "database is successfully created".Here few things are to be noted down.Keep the database in the same location as the program resides.

8) These above mentioned steps will create a jdbc:odbc Connection successfully.




5 comments:

Unknown said...
This comment has been removed by the author.
Unknown said...

Hello.
I've been trying to connect to a MS Excel 2007 database through JDBC. My JRE is 32-bit and OS is Windows 7 64-bit.I am running the java code through eclipse. But problem is I am unable to create a DSN using Windows\system32\odbcad32.exe because it doesn't show ODBC drivers for MS Excel at all, it's only showing drivers for MS SQL Server.

When tried to click on Configure for "MS Excel Database" (which is an already created DSN, I guess), it first shows error message : "The setup routines for the Microsoft Excel Drivers (*.xls, *.xlsx,....) ODBC Driver could not be found. Please reinstall the driver." And then another message : "Errors found! The specified DSN contains an architecture mismatch between the Driver and Application."

After reading in forum.
The odbcad32.exe in Windows\SysWOW64 does let me create a DSN for MS Excel, it shows the drivers installed properly. However, when tried to connect to that DSN through a Java program, I get the following exception :

java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains
an architecture mismatch between the Driver and Application
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at AccessTest.main(AccessTest.java:19)

What might be the problem and what do I have to do to get it working? Any help would be highly appreciated.

P.S : Please give a solution for this .I am very frustrated for this as i am searching the solution in different forums but unable to get the solution.

Ganesh said...

Hi

i haven't ever come across this error "mismatch between the architecture and driver". I am not sure whether this is gonna work but i guess it may be due to the non-compliance of 64 bit os with the 32 bit JRE. To know more about the 64 bit and 32-bit DSN's check out this link,

http://support.microsoft.com/kb/942976.

Also, while creating the DSN check whether u have created System DSN or User DSN because only the system DSN can be used in a 32-bit application as specified in the above mentioned link.Or try to reinstall the 32-bit odbc drivers and follow the instructions given there may be the following link will help u,

http://www.microsoft.com/en-us/download/details.aspx?id=13255#overview

aravind said...

I get error
says "Exception in thread main java.lang.SQLException [Microsoft][ODBC Driver Manager] data source name is not found and no
default driver specified".

Asquare Cloud Hosting said...

Managing payroll with the QuickBooks application is not a complicated task. However, if QuickBooks Error Code 40001 occurs in between, it may be the most frustrating feeling for the users. It’s OK! You can go through this blog post and follow the steps given in this to troubleshoot the error. If you want to seek professionals, call us at +1(855)-738-0359!

Post a Comment

 
java errors and exceptions © 2010 | Designed by Chica Blogger | Back to top