How to fill Data in Android Spinner using MS SQL Database

In my previous posts I explained How to connect Android with MS SQL DatabaseSimple Login application in android using MS SQL Database. In this post I will be explaining how to fill Data in a spinner. First of all create a database in MS SQL with name MyDatabase and create following table with name “countries”

The Layout file :

Make a xml layout file in your project named spinners.xml

spinners.xml :


<?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:orientation="vertical"
android:padding="10dp" >

<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="horizontal" >

<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="COUNTRY : " />

<Spinner
android:id="@+id/spinnercountry"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />
</LinearLayout>

</LinearLayout>

 

 

Create a new Activity class in your project named : ForSpinner.java

ForSpinner.java

package com.hitesh.mssqlapp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import android.annotation.SuppressLint;
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.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import android.widget.Toast;

public class ForSpinner extends ActionBarActivity {
	Spinner spinnercountry;

	String ip, db, un, passwords;
	Connection connect;
	PreparedStatement stmt;
	ResultSet rs;

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

		ip = "192.168.0.100";
		un = "sa";
		passwords = "123";
		db = "MyDatabase";

		spinnercountry = (Spinner) findViewById(R.id.spinnercountry);

		connect = CONN(un, passwords, db, ip);
		String query = "select CountryName from countries";

		try {
			connect = CONN(un, passwords, db, ip);
			stmt = connect.prepareStatement(query);
			rs = stmt.executeQuery();
			ArrayList<String> data = new ArrayList<String>();
			while (rs.next()) {
				String id = rs.getString("CountryName");
				data.add(id);

			}
			String[] array = data.toArray(new String[0]);
			ArrayAdapter NoCoreAdapter = new ArrayAdapter(this,
					android.R.layout.simple_list_item_1, data);
			spinnercountry.setAdapter(NoCoreAdapter);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		spinnercountry.setOnItemSelectedListener(new OnItemSelectedListener() {

			@Override
			public void onItemSelected(AdapterView<?> parent, View view,
					int position, long id) {

				String name = spinnercountry.getSelectedItem().toString();
				Toast.makeText(ForSpinner.this, name, Toast.LENGTH_SHORT)
						.show();
			}

			@Override
			public void onNothingSelected(AdapterView<?> parent) {

			}
		});

	}

	@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;
	}

}


AndroidManifest.xml


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

<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="19" />

<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
<uses-permission android:name="android.permission.ACCESS_WIFI_STATE" />
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<activity
android:name=".ForSpinner"
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>


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

  • Christian

    You solved my long time problem.
    Thank You very much!

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

  • Arun Madhav

    Hi.. Thank you.. Is it required to create a new Class in case if we include the Spinner in the same AddProducts layout and class? Please advise

    • Arun Madhav

      Like, need to include a spinner to get list of Countries to select the country of Origin of that product. Please Help

      • It depends on you.

        • Arun Madhav

          Hitesh Bhai, I am learning android from your codes.. very new.. a week old.:). Any chance of posting code to include a spinner in add products form plz?

          • Arun Madhav

            In fact I am creating a userMaster form .. reached somehow till here.. Please help

            public class AddUsers extends Activity {
            ConnectionClass connectionClass;
            EditText edtUsername, edtPassword;
            Button btnadd,btnupdate,btndelete;
            Spinner spnRolls;
            ProgressBar pbbar;
            ListView lstUsers;
            String UserID;

            Connection con;
            PreparedStatement stmt;
            ResultSet rs;

            @Override
            protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.user_master);
            connectionClass = new ConnectionClass();
            con = connectionClass.CONN();
            String z = “”;
            if(con == null){
            z = “Error in connection with SQL server”;
            }else{
            String query = “select Roll from tbl_UserRolls”;
            try {
            stmt = con.prepareStatement(query);
            rs = stmt.executeQuery();
            ArrayList data = new ArrayList();
            while (rs.next()){
            String id = rs.getString(“Roll”);
            data.add(id);
            }
            String[] array = data.toArray(new String[0]);
            ArrayAdapter NoCoreAdapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, data);
            spnRolls.setAdapter(NoCoreAdapter);
            } catch (SQLException e) {
            e.printStackTrace();
            }

            }

            edtUsername = (EditText)findViewById(R.id.edtUserName);
            edtPassword = (EditText)findViewById(R.id.edtPassword);
            spnRolls = (Spinner)findViewById(R.id.spnRolls);
            btnadd = (Button)findViewById(R.id.btnadd);
            btnupdate = (Button)findViewById(R.id.btnupdate);
            btndelete = (Button)findViewById(R.id.btndelete);
            pbbar =(ProgressBar)findViewById(R.id.pbbar);
            pbbar.setVisibility(View.GONE);
            lstUsers = (ListView)findViewById(R.id.lstUsers);
            UserID = “”;

            btnadd.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

            edtUsername.setText(“”);
            edtPassword.setText(“”);
            }
            });
            btnupdate.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
            edtUsername.setText(“”);
            edtPassword.setText(“”);
            }
            });
            btndelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
            edtUsername.setText(“”);
            edtPassword.setText(“”);
            }
            });

            }
            }

        • Arun Madhav

          Hi friend,

          Its working fine with the same code you posted. Please let me know if we can use the Connection class.java without writing the connection strings in all forms.. plz reply. Thanks

  • hello sir i want to load data of MS SQL in autocomplete textview in android so please help me as soon as possible
    waiting for your response.
    Thanks in advance.

  • Mark Hernz Martz

    how add id colum to spinner….. please

  • Mario German Agudelo

    An excellent tutorial, thanks for sharing your knowledge with all of us, I have the following question: I have a database already created and which is shown in a Recyclerview with values such as ID, name and telephone number and I want to place a spinner which leave me place a set of values, as I can do so that when you click on a value of the spinner that value in SQLite is saved and then when reentering activity show me the stored value, I appreciate all the help you can me offer. Thank you.

  • Arish khan

    Thanks for this.. Brother can you tell me, how we can get Id of the selected text in spinner.