android sql listview

Android Listview from MS SQL server

In my previous posts on Connecting Android to MS SQL server and simple login application in android using MS SQL Server I explained about how to connect Android with MS SQL server. So here’s a another post on populating Listview from MS SQL server database in android

android sql listview

Softwares used :

  1. Eclispe ADT
  2. MS SQL Server

Library :
jtds-1.2.7.jar

Database table script :

USE [mydatabase]
GO
/****** Object:  Table [dbo].[countries]    Script Date: 06/11/2015 23:13:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[countries](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CountryName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

Create a new xml layout file with name res>layout>countrylist.xml in your project and edit it as following :

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#c0392b"
    android:orientation="vertical"
    android:weightSum="10" >

    <TextView
        android:id="@+id/lblheader"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:padding="10dp"
        android:text="COUNTRIES : "
        android:textColor="#fff"
        android:textSize="15sp" />

    <ListView
        android:id="@+id/lstcountry"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="8"
        android:divider="#252525"
        android:dividerHeight="1dp" >
    </ListView>

    <Button
        android:id="@+id/btnview"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:text="VIEW"
        android:background="#fff"
        android:layout_marginLeft="10dp"
        android:layout_marginRight="10dp"
        android:textSize="20sp"
        android:textColor="#c0392b" >
    </Button>

</LinearLayout>

The layout is quite simple to understand.

Create another .xml file with name res>layout>listtemplate.xml and edit it as following :

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#c0392b"
    android:orientation="vertical" >

    <TextView
        android:id="@+id/lblcountryname"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="#fff"
        android:padding="10dp"
        android:text="HITRES"
        android:textColor="#252525"
        android:textSize="20sp" />

</LinearLayout>

This file will be used as template for our listview control.

CODE

Create a new class with name ConnectionClass.java and edit it as following :

package com.example.temp;

public class ConnectionClass {

	String ip;
	String classs;
	String db;
	String un;
	String password;

	public ConnectionClass() {
		classs = "net.sourceforge.jtds.jdbc.Driver";
		db = "MyDatabase";
		un = "sa";
		password = "123";
		ip = "192.168.0.103";
	}

	public ConnectionClass(String Ip, String Classs, String Db, String Un,
			String Password) {
		ip = Ip;
		classs = Classs;
		db = Db;
		un = Un;
		password = Password;
	}

	public String getip() {
		return ip;

	}

	public String getclasss() {
		return classs;

	}

	public String getdb() {
		return db;
	}

	public String getun() {
		return un;
	}

	public String getpassword() {
		return password;
	}

	public void setip(String Ip) {
		ip = Ip;
	}

	public void setdb(String Db) {
		db = Db;
	}

	public void setclasss(String Classs) {
		classs = Classs;
	}

	public void setun(String Un) {
		un = Un;
	}

	public void setpassword(String Password) {
		password = Password;
	}

}

Make another class with name CountryList.java and edit it as following :

package com.example.temp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.annotation.SuppressLint;
import android.graphics.Typeface;
import android.os.Bundle;
import android.os.StrictMode;
import android.support.v7.app.ActionBarActivity;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;
import android.widget.Toast;

public class CountryList extends ActionBarActivity {

	TextView lblheader;
	Typeface font;
	Button btn;
	ListView lstcountry;
	SimpleAdapter ADAhere;

	/*********** CONNECTION DATABASE VARIABLES **************/

	ConnectionClass connectionclass;
	String usernameS;
	String datets;
	String call, db, un, passwords;
	Connection connect;
	ResultSet rs;

	@SuppressLint("NewApi")
	private Connection CONN(String _user, String _pass, String _DB,
			String _server) {
		StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
				.permitAll().build();
		StrictMode.setThreadPolicy(policy);
		Connection conn = null;
		String ConnURL = null;
		try {

			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			ConnURL = "jdbc:jtds:sqlserver://" + _server + ";"
					+ "databaseName=" + _DB + ";user=" + _user + ";password="
					+ _pass + ";";
			conn = DriverManager.getConnection(ConnURL);
		} catch (SQLException se) {
			Log.e("ERRO", se.getMessage());
		} catch (ClassNotFoundException e) {
			Log.e("ERRO", e.getMessage());
		} catch (Exception e) {
			Log.e("ERRO", e.getMessage());
		}
		return conn;
	}

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.countrylist);

		lblheader = (TextView) findViewById(R.id.lblheader);

		lstcountry = (ListView) findViewById(R.id.lstcountry);

		btn = (Button) findViewById(R.id.btnview);

		/************* CONNECTION DATABASE VARIABLES ***************/
		connectionclass = new ConnectionClass();
		call = connectionclass.getip();
		un = connectionclass.getun();
		passwords = connectionclass.getpassword();
		db = connectionclass.getdb();
		connect = CONN(un, passwords, db, call);
		btn.setOnClickListener(new View.OnClickListener() {

			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				String querycmd = "select * from Countries";
				try {

					Statement statement = connect.createStatement();
					rs = statement.executeQuery(querycmd);
					List<Map<String, String>> data = null;
					data = new ArrayList<Map<String, String>>();

					while (rs.next()) {
						Map<String, String> datanum = new HashMap<String, String>();
						datanum.put("A", rs.getString("CountryName"));
						data.add(datanum);
					}
					String[] fromwhere = { "A" };
					int[] viewswhere = { R.id.lblcountryname };
					ADAhere = new SimpleAdapter(CountryList.this, data,
							R.layout.listtemplate, fromwhere, viewswhere);
					lstcountry.setAdapter(ADAhere);

				} catch (SQLException e) {
					Toast.makeText(CountryList.this, e.getMessage().toString(),
							Toast.LENGTH_LONG).show();
				}

			}
		});

		lstcountry.setOnItemClickListener(new OnItemClickListener() {

			@Override
			public void onItemClick(AdapterView<?> parent, View view,
					int position, long id) {
				// TODO Auto-generated method stub
				HashMap<String, Object> obj = (HashMap<String, Object>) ADAhere
						.getItem(position);
				String VehicleId = (String) obj.get("A");
				Toast.makeText(CountryList.this, VehicleId, Toast.LENGTH_LONG)
						.show();
			}
		});

	}

}

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.temp"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="21" />
    
    <uses-permission android:name="android.permission.INTERNET" />
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name=".CountryList"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Now run the application.


If you like my work, please click the like button and like my page on facebook. You can also comment belowโ€ฆI will try my best to help you out.

android sql listview

  • Pingback: Connect Android to MS SQL Database. • ParallelCodes()

  • Bartek

    Hi. Can You post some codes of application to show in list view some records from database (as on this project) and after select (touch) record it makes update on database and change column status to value 2 for selected record (it’s id)

    • Hi Bartek!! Sorry to reply this late, but I cannot understand the requirement you have given. I can surely make a listview which updates when the user clicks or selects a certain item from it. If so let me know I will post about it.

      • Bartek

        Hi. I found the solution myself. Just add datanum.put(“status_c”, rs.getString(“ststus”)) and then on setOnItemClickListener: HashMap selected_row =
        (HashMap) ADAhere.getItem(i);
        String str_selected_item = selected_row.get(“status_c”);

        But I have another problem. How format the listview items based on value from one of column in database ? For example I have table with 2 columns : name,status and I want something like : if status=1 then font color=green else font color= black. Can You help me ?

  • Pingback: Android Import excel into Sqlite Database()

  • Nidhin Kumar

    how to upload image from android to ms sql database and how to reterive the image from ms sql database to android

    • Stylesmylez Io

      You still need help with this image issue???

  • Nidhin Kumar

    Hi Hitesh,
    Instead of list view whether we can populate the data to spinners in android,if so what changes should be done.

  • Green White

    I was wondering if you could provide code for a simple Search. User will Part Code then the app would search the database and display the Part Name

    • Its easy just make a new edittext and a button, enter the part code in the edittext and a button click listener. Query will be as such :
      “select * from yourtablename where PartCode=”+edittextid.getText().toString();

      • Green White

        Great. atleast I got that part correct.
        in the ipaddress, should i only use 192.xxx.xxx.xxx or can I use the server name?
        I’m getting “illegal escape character”

        • Use the server name not the IP address, it will work

          • Green White

            I’m new in developing Apps in Android Studio. I’m just guessing which part of the code is causing “Unfortunately, has stopped”. I was hoping the ipaddress was the one. I used the actual ip address of the server but no luck

            logcat entry;

            java.lang.RuntimeException: Unable to start activity ComponentInfo{com../com…MainActivity}: java.lang.NullPointerException: Attempt to invoke virtual method ‘void android.widget.Button.setOnClickListener(android.view.View$OnClickListener)’ on a null object reference

            this is the onCreate code;

            @Override
            protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            ipaddress = “192.xxx.xxx.xxx”;
            db = “”;
            username = “”;
            password = “”;
            connect = ConnectionHelper(username, password, db, ipaddress);
            btnSearch.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
            try {
            connect = ConnectionHelper(username, password, db, ipaddress);
            st = connect.createStatement();
            ResultSet rs = st.executeQuery(“select * from ProdnParts where PartCode='” + txtPartCode.getText().toString() + “‘”);
            if (rs != null)
            {
            lblPartName.setText(rs.getString(“PartName”));
            }
            else {
            lblPartName.setText(“Invalid Code… Not Found!”);
            }
            } catch (SQLException e) {
            lblPartName.setText(e.getMessage().toString());
            }
            }
            });
            }

          • btnSearch is not been referenced thats why your are getting this error.

          • Green White

            referencing done. I was able to get the app to accept input but now getting ‘null object reference’. I suspect it’s the ipaddress part or i may be wrong. This doesn’t have anything to do with the fact that we’re using SQLExpress?.

          • Green White

            i tried to debug, ConnectionHelper is returning connection = null. I tried both the ip address and the server name, same result

          • Green White

            replaced the slash between the and , new error message. java.lang.SecurityException: Permission denied (missing INTERNET permission?). I insert this in the manifest:

            any thoughts on this error?
            ๐Ÿ™ still a long way to go. I still need to figure out how to print labels to our network and then connect a barcode reader to an android device.

          • Green White

            I placed the ‘uses-permission’… outside the tags, saw this on the internet. sigh ๐Ÿ™ now another error message; java.sql.SQLException: Unknown server host name ‘Unable to resolve host “”: No address associated with hostname’.

          • that means your server name or ip address that you provided is incorrect.

          • Green White

            Hi Hitesh,

            thanks for replying. I really appreciate you taking the time to answer my inquiries.

            I got pass that error. I was able to connect to the server using the ip address 192.xxx.xxx.xxx. ๐Ÿ™‚

            I’m running in debug mode. everything’s working fine except when it reaches the event listener;

            btnSearch.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
            try {
            connect = ConnectionHelper(username, password, db, ipaddress);
            st = connect.createStatement();
            ResultSet rs = st.executeQuery(“select * from ProdnParts where PartCode='” + txtPartCode.getText().toString() + “‘”);
            if (rs != null )

            {
            lblPartName.setText(rs.getString(“PartName”));
            }
            else {
            lblPartName.setText(“Invalid Code… Not Found!”);
            }
            } catch (SQLException e) {
            lblPartName.setText(e.getMessage().toString());
            }
            }
            });

            am I missing something after the if condition rs!=null? omitted part of the code from your site;

            if (rs != null && rs.next())

            because the &&amp is causing error.

            rs!=null condition has been satisfied but when it reaches the part when displaying the PartName, the code jumps to the ‘catch’ portion and displays “No current row in the ResultSet”.

          • You have to use

            if (rs.next()) {

            // your code here.
            }

            I used this code in my application and it worked perfectly fine.

          • Green White

            Great! it’s working. Thank you very much for being patient with my inquiries.

            You wouldn’t happen to have sample codes for printing labels to printers connected to a network? ๐Ÿ˜€
            Would appreciate also to know if it is possible to connect barcode scanners to android tablet as input device?

          • No..I don’t have sorry.

          • Green White

            How to I generate the APK file for my project?

          • Green White

            Again, thank you very much and God Bless…

  • Christian

    instead of listview. how about populating spinner from mssql database?

    • Stylesmylez Io

      You still need help with this?

  • Amar

    Hello
    Can you provide me how to access the items of listview on next page through sql server?

  • Pingback: Connect Android to MS SQL Database. • ParallelCodes()

  • Mirce Novakovski

    Hello Hitesh,

    thanks for the code, very useful
    please can you help me with this
    i want to execute a store procedure from android and get the results

    Thanks in advance,
    Mirce

  • Xebat Rammo

    Hello Hitesh, thanks for the great app.
    i am having problem i hope yo coul help me with it ,
    so i am trying to get in to my database and call some view in SQL server .
    i hope u have so instractons
    thanks alot

    • Stylesmylez Io

      You still need help with this?!

      • yes please.
        I do.

        • Stylesmylez Io

          Please explain what exactly you want to do

  • Kaan

    Istanbul not a country, just a little information ๐Ÿ™‚ Happy coding.

  • Eudes

    Hello Hitesh. I am new in Android. I have a project to deliver but i’m stuck. How can i fill the listview with image from Sql Server. Please help me

  • najib khanafer

    do you have a example on how to view employee profile in an activity edit boxes based on employee id using sql 2008 r2

  • master

    hi i just complete my code and run devices i get fatal execption in this code how i solve it?

  • van khanh Nguyen

    Hello Hitesh,How to get images from database sql server and show the Listview.
    Please help me!

  • vignesh T

    This app working how to export the listview countries to excel format

  • Hoang Thuong

    admin parallelcodes.com can send source code

    Android Listview from MS SQL server to email: htsolutiontech@gmail.com
    Tks very much

    • Karthik Mellechervu

      Hello, I would really appreciate, if you can send me the source code to karthik.mece@gmail.com please…thank you very much!!!

  • santunu23

    Hitash in line no 96 there are a error create statement shows error message.

  • fanxy

    hello, thanks for the code. but when i try this code, and click view button the app stopped. can you help me?