Listview

        

private void lvStudents_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
        {
            if (e.IsSelected)
                getRecord(lvStudents.SelectedItems[0].Text);
        }

       

private void getRecord(string studentID)

        {
            if (!ConnectedToDB())
                return;
            string strSQL = "SELECT * FROM tblStudents WHERE Stud_ID = ?";
            OleDbCommand cmdStudents = new OleDbCommand(strSQL, Conn);
            cmdStudents.Parameters.AddWithValue("Stud_ID", studentID);
            OleDbDataReader rdrStudents = cmdStudents.ExecuteReader();

            rdrStudents.Read();
            strID = studentID;
            txtName.Text = rdrStudents["Stud_Name"].ToString();
            txtAddress.Text = rdrStudents["Stud_Address"].ToString();
            txtAge.Text = rdrStudents["Stud_Age"].ToString();
            //assign the stud_course as the selectedValue for the course combobox
            //it will then be reflected in the combobox's text
            cboCourses.SelectedValue = rdrStudents["Stud_Course"].ToString();
            cmdStudents.Dispose();
            Conn.Close();
        }

Listview

Procedure:
1. Draw a ListView in the form and name it lvStudents.
2. Copy the code below the image.

Code:

        private void Form1_Load(object sender, EventArgs e)
        {
            //initialize list view
            lvStudents.GridLines = true;
            lvStudents.View = View.Details;
            lvStudents.FullRowSelect = true;
            lvStudents.Columns.Add("id").Width = 0;
            lvStudents.Columns.Add("StudName").Width = 200;
            fillListView();
            FillCoursesCombo();
        }

        private void fillListView()
        {
            if (!ConnectedToDB())
                return;
            //get all records 
            string strSQL = "SELECT Stud_ID, Stud_Name FROM tblStudents ORDER BY Stud_Name";
            OleDbCommand cmdStudents = new OleDbCommand(strSQL, Conn);
            OleDbDataReader rdrStudents = cmdStudents.ExecuteReader();

            //clear the listview
            lvStudents.Items.Clear();
            //check if it returns any record
            if (rdrStudents.HasRows)
            {
                //iterate thru all records and add each to the listview
                while (rdrStudents.Read())
                {
                    ListViewItem myRow = new ListViewItem(rdrStudents["Stud_ID"].ToString());
                    myRow.SubItems.Add(rdrStudents["Stud_Name"].ToString());

                    lvStudents.Items.Add(myRow);
                }
            }
            //cleanup the mess!
            cmdStudents.Dispose();
            rdrStudents.Close();
            Conn.Close();                    
        }

Lookup Table

Add a comboBox in the form.

 private void FillCoursesCombo()
        {
            if (!ConnectedToDB())
                return;
            //instantiate data adapter and dataset
            OleDbDataAdapter courseAdapter = new OleDbDataAdapter();
            DataSet courseDataSet = new DataSet();
            //instantiate command object
            string strSQL = "SELECT * FROM tblCourses ORDER BY Crs_Description";
            OleDbCommand cmdCourses = new OleDbCommand(strSQL, Conn);
            //set the cmdCourses for the adapter's selectcommand property
            courseAdapter.SelectCommand = cmdCourses;
            //fill the dataset with records from the adapter
            courseAdapter.Fill(courseDataSet);
            //clean up
            courseAdapter.Dispose();
            cmdCourses.Dispose();
            Conn.Close();

            //set the datasourse of the course combobox to the only table from the dataset
            cboCourses.DataSource = courseDataSet.Tables[0];
            //set the course ID as the value member/return value
            cboCourses.ValueMember = "Crs_ID";
            //set the course description as the display member
            cboCourses.DisplayMember = "Crs_Description";
            cboCourses.SelectedIndex = -1;
        }

Fetch Records

ANDROID

DEPENDENCIES
    implementation 'com.squareup.picasso:picasso:2.71828'


ACTIVITY

package com.eimrodaapps.myapplication

import android.content.Context
import android.os.Bundle
import android.view.LayoutInflater
import android.view.MenuItem
import android.view.View
import android.view.ViewGroup
import android.widget.*
import androidx.appcompat.app.AppCompatActivity
import com.squareup.picasso.Picasso
import kotlinx.android.synthetic.main.activity_main2.*
import okhttp3.*
import okhttp3.HttpUrl.Companion.toHttpUrlOrNull
import org.json.JSONArray
import java.io.IOException

class Main2Activity : AppCompatActivity() {
    var studentsList = ArrayList<Student>()
    val pictureUrl = "http://192.168.43.95/advprog/webservices/images/"

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main2)

        //val name:String? = intent.getStringExtra("ngalan")

        listView.onItemClickListener = object:AdapterView.OnItemClickListener{
            override fun onItemClick(p0: AdapterView<*>?, p1: View?, p2: Int, p3: Long) {
                val id = studentsList.get(p2).id
                val name = studentsList.get(p2).name

                Toast.makeText(this@Main2Activity, "Full Name : $name and the ID is $id", Toast.LENGTH_LONG).show()
            }
        }
        getRecords()
}

    fun getRecords(){
        //fetch all records from the db
        //add them to arraylist of type student
        //set listview adapter

        var url = "http://192.168.43.95/advprog/webservices/getallstudents.php"
        val okHttpClient = OkHttpClient()
        val request = Request.Builder().url(url).build()
        okHttpClient.newCall(request).enqueue(object: Callback{
            override fun onFailure(call: Call, e: IOException) {
                runOnUiThread{Toast.makeText(this@Main2Activity, "Connection Error", Toast.LENGTH_LONG).show()}
            }

            override fun onResponse(call: Call, response: Response) {
                val jsonString = response.body?.string()

                if (jsonString?.trim() != "0"){
                    val jsonArray = JSONArray(jsonString)
                    for (i in 0 until jsonArray.length() - 1){
                        //extract the first object in the json array
                        val jsonObject = jsonArray.getJSONObject(i)
                        //extract each column/field from the json object
                        val id = jsonObject.getString("stud_id")
                        val name = jsonObject.getString("stud_name")
                        val address = jsonObject.getString("stud_address")
                        val imageUrl = jsonObject.getString("stud_imageUrl")

                        val student = Student(id.toInt(), name, address, pictureUrl + imageUrl)
                        studentsList.add(student)
                    }
                    val adapter = CustomAdapter(studentsList, this@Main2Activity)
                    runOnUiThread{
                        listView.adapter = adapter
                    }
                }else{
                    runOnUiThread{Toast.makeText(this@Main2Activity, "Error in fetching records", Toast.LENGTH_LONG).show()}
                }
            }
        })
    }

    class CustomAdapter(val data:ArrayList<Student>, val context:Context) : BaseAdapter() {
        class ViewHolder(view:View){
            val textView_name: TextView
            val textView_address: TextView
            val imageView: ImageView

            init{
                textView_name = view.findViewById(R.id.textView_name)
                textView_address = view.findViewById(R.id.textView_address)
                imageView = view.findViewById(R.id.imageView)
            }
        }
        override fun getView(p0: Int, p1: View?, p2: ViewGroup?): View? {
            var viewHolder: ViewHolder? = null
            var v: View? = p1
            if (v == null) {
                v = LayoutInflater.from(context).inflate(R.layout.listview_layout, p2, false)
                viewHolder = ViewHolder(v)
                v.tag = viewHolder
            } else {
                viewHolder = v.tag as ViewHolder
            }
            //assign values
            viewHolder.textView_name.text = data.get(p0).name
            viewHolder.textView_address.text = data.get(p0).address
            Picasso.get().load(data.get(p0).imageUrl).into(viewHolder.imageView)

            return v
        }

        override fun getItem(p0: Int): Any {
            return data.get(p0).id
        }

        override fun getItemId(p0: Int): Long {
            return 0
        }

        override fun getCount(): Int {
            return data.size
        }

    }

    class Student(val id:Int, val name:String, val address:String, val imageUrl:String)
}

LAYOUT

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="wrap_content">


    <ImageView
        android:id="@+id/imageView"
        android:layout_width="114dp"
        android:layout_height="89dp"
        android:layout_marginStart="8dp"
        android:layout_marginLeft="8dp"
        android:layout_marginTop="8dp"
        android:layout_marginBottom="8dp"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:srcCompat="@android:drawable/alert_dark_frame" />

    <TextView
        android:id="@+id/textView_name"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_marginStart="8dp"
        android:layout_marginLeft="8dp"
        android:layout_marginTop="16dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:text="Pitok"
        android:textSize="24sp"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.0"
        app:layout_constraintStart_toEndOf="@+id/imageView"
        app:layout_constraintTop_toTopOf="parent" />

    <TextView
        android:id="@+id/textView_address"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_marginStart="8dp"
        android:layout_marginLeft="8dp"
        android:layout_marginTop="16dp"
        android:layout_marginEnd="8dp"
        android:layout_marginRight="8dp"
        android:text="Address"
        android:textSize="24sp"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.0"
        app:layout_constraintStart_toEndOf="@+id/imageView"
        app:layout_constraintTop_toBottomOf="@id/textView_name" />

</androidx.constraintlayout.widget.ConstraintLayout>

PHP
<?php

include "myconnection.php";

$sql = "SELECT * FROM tblstudents ORDER By stud_name";
$stmt = $conn->prepare($sql);
$stmt->execute();
$rs = $stmt->get_result();

if ($rs->num_rows > 0) {
while ($row = $rs->fetch_assoc()) {
$rows[] = $row;
}

//encode to json format
$jsonData = json_encode($rows);

echo($jsonData);

}else{
echo "0";
}
$stmt->close();
$conn->close();

?>

QUIZ

Database name : dbPayroll
Table Name : tblEmployees
Columns:

emp_id   Auto
emp_name   Text(30)
emp_birthday   Date
emp_department  Text(30)
emp_basicSalary  Currency

Requirement: Create a data entry app for the table tblEmployees.

ADV PROG

PHP

<?php
include "myconnection.php";

$id = $_GET['id'];

$sql = "SELECT * FROM tblstudents WHERE stud_id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $id);
$stmt->execute();

$rs = $stmt->get_result();

if ($rs->num_rows > 0) {
$row = $rs->fetch_assoc();

//encode to json format
$jsonData = json_encode($row);

echo($jsonData);

}else{
echo "0";
}
$stmt->close();
$conn->close();
?>


ANDROID

import okhttp3.HttpUrl.Companion.toHttpUrlOrNull

    fun accessInternet(){
        val studentId = tfUsername.editText?.text.toString()
        val lugar = tfPassword.editText?.text.toString()

        var url = "http://192.168.43.95/advprog/webservices/getstudent.php"
        val urlBuilder = url.toHttpUrlOrNull()!!.newBuilder()
        urlBuilder.addQueryParameter("id", studentId)        

        url = urlBuilder.build().toString()

        val okHttpClient = OkHttpClient()
        val request = Request.Builder().url(url).build()
        okHttpClient.newCall(request).enqueue(object: Callback{
            override fun onFailure(call: Call, e: IOException) {
                runOnUiThread{

                    Toast.makeText(applicationContext, e.message, Toast.LENGTH_LONG).show()               

                }            

            }

            override fun onResponse(call: Call, response: Response) {
                val jsonString = response.body?.string()

            val msg = if (jsonString?.trim() == "0"){
                          "Invalid Student ID "                      

                      }else{
                          val jsonObject = JSONObject(jsonString)
                          val ngalan = jsonObject.getString("stud_name")
                          val address = jsonObject.getString("stud_address")
                          "Full Name : $ngalan and the address is $address"

                       }

                runOnUiThread{
                    Toast.makeText(applicationContext, msg, Toast.LENGTH_LONG).show()
                }

            }
        })



    }

Database Connectivity

create this method:

private Boolean connectedToDB()
{
  try
  {
     String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\dbStudents.accdb;Persist Security Info=False;";
                
     Conn.ConnectionString = strConn;
     Conn.Open();
     return true;
  }
  catch (Exception ex)
  {
     MessageBox.Show("There's an error while connecting to the database.\n\n" + ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
     return false;
  }
}

To call this method:

if (!connectedToDB())

   return;

==========================================================

Connection String (MS Access)

OleDbConnection Conn = new OleDbConnection();
String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data  Source=c:\\dbms\\dbStudents.accdb;Persist Security Info=False;";
Conn.ConnectionString = strConn;
Conn.Open();

The Command Object

string strSQL = "INSERT INTO tblStudents(Stud_Name, Stud_Address) VALUES(?,?)";
OleDbCommand cmdStudents = new OleDbCommand();

cmdStudents.Parameters.AddWithValue("Stud_Name", txtName.Text);
cmdStudents.Parameters.AddWithValue("Stud_Address", txtAddress.Text);

cmdStudents.Connection = Conn;
cmdStudents.CommandText = strSQL;
cmdStudents.ExecuteNonQuery();
Conn.Close();