all listview data insert only one row

Question

I have a ListView with multiple rows. When I insert the entries in the ListView into an sql database, all the entries get inserted into a single row.

I get all the entries from the view with a String builder.

How can I solve this problem?

StringBuilder sb = new StringBuilder();

StringBuilder sb2 = new StringBuilder();

StringBuilder sb3 = new StringBuilder();
//get data form text view

for(int i=0; i<simpleAdapter.getCount(); i++) {
    String a = ((TextView) findViewById(R.id.al)).getText().toString();
    String b = ((TextView) findViewById(R.id.sh)).getText().toString();
    String c = ((TextView) findViewById(R.id.acc)).getText().toString();

    simpleAdapter.getItem(i).toString();
    sb.append(a);
    sb.append("\n");
    sb2.append(b);
    sb2.append("\n");
    sb3.append(c);
    sb3.append("\n");

}

String text = sb.toString();
String text2 = sb2.toString();
String text3 = sb3.toString();

//my sql connection insert query
try {
    Connection con = connectionClass.CONN();
    if (con == null) {
        Toast.makeText(getApplicationContext(), "Error in connection with SQL server", Toast.LENGTH_LONG).show();
    } else {

        String query = "INSERT INTO suigen.TransAlmiraShelf (Almira, Shelf, AccessionNo) VALUES('" + String.valueOf(text) + "','" + String.valueOf(text2) + "','" + String.valueOf(text3) + "')";

        Statement stmt = con.createStatement();
        stmt.execute(query);
        Toast.makeText(Main13Activity.this, "Your Data Successfully Saved", Toast.LENGTH_LONG).show();
    }

}
catch (Exception ex) {
     Toast.makeText(getApplicationContext(), "Exceptions", Toast.LENGTH_LONG).show();
}

Please Refer this link. https://stackoverflow.com/questions/42619923/how-to-insert-multiple-rows-into-sqlite-android — sivaprakash, Aug 06 '17 at 08:17
how to use this method i cant understand in sql server please help me — Puneet Khattar, Aug 06 '17 at 08:20
Try statement.executeBatch(); Refer the below link https://stackoverflow.com/questions/6892105/bulk-insert-in-java-using-prepared-statements-batch-update — sivaprakash, Aug 06 '17 at 08:29
yes but my q is how to get every row data or how to insert every list row data into sql — Puneet Khattar, Aug 06 '17 at 09:30

score 0 · Answer 1 · answered Aug 06 '17 at 09:47

Get the list of Values form the table usng JDBC

Connection connection = DriverManager.getConnection("URL", "USERNAME", 
"PASSWORD");
PreparedStatement statement = connection.prepareStatement("select * from 
table");
ResultSet resultSet = statement.executeQuery();

if (resultSet != null) {
while (resultSet.next()) {
    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
    for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {

        int type = resultSetMetaData.getColumnType(i);
        if (type == Types.VARCHAR || type == Types.CHAR) {
             System.out.println(resultSet.getString(i));
        } else {
             System.out.println(resultSet.getLong(i));
        }
    }

     System.out.println("-----------");
}

}

yes i am get list of values from the textview and put into the sql server — Puneet Khattar, Aug 06 '17 at 11:16

ItamarG3 · Answer 2 · 2017-08-06T10:47:00.707

The problem is that you are adding all the information into the string builders before you insert into the sql database.

You can fix this by getting the information and immediately insert it into the database:

ListView listView =  ((ListView) findViewById(R.id.listView));
try {
    Connection con = connectionClass.CONN();//first you do the connection
    if (con == null) {
        Toast.makeText(getApplicationContext(), "Error in connection with SQL server", Toast.LENGTH_LONG).show();
    } else {
        for(int i=0; i<simpleAdapter.getCount(); i++) {//then create the information you're going 
            View row = simpleAdapter.getView(i, null, listView);
            String a = ((TextView) row.findViewById(R.id.al)).getText().toString();
            String b = ((TextView) row.findViewById(R.id.sh)).getText().toString();
            String c = ((TextView) row.findViewById(R.id.acc)).getText().toString();


            sb.append(a);
            sb.append("\n");
            sb2.append(b);
            sb2.append("\n");
            sb3.append(c);
            sb3.append("\n");



            String text = sb.toString();
            String text2 = sb2.toString();
            String text3 = sb3.toString();



            String query = "INSERT INTO suigen.TransAlmiraShelf (Almira, Shelf, AccessionNo) VALUES('" + String.valueOf(text) + "','" + String.valueOf(text2) + "','" + String.valueOf(text3) + "')";

            Statement stmt = con.createStatement();
            stmt.execute(query);
            Toast.makeText(Main13Activity.this, "Your Data Successfully Saved", Toast.LENGTH_LONG).show();
        }
    }

}
catch (Exception ex) {
     Toast.makeText(getApplicationContext(), "Exceptions", Toast.LENGTH_LONG).show();
}

The reason you got all the data in one row is that you used the string builder to build a string that contains all the information from the ListView, and not just a single row in the view. Then you put it all in the database.

The solution is to insert into the database one at a time.

how to get all row and insert – Puneet Khattar Aug 06 '17 at 10:21 — Puneet Khattar, Aug 06 '17 at 10:21
@PuneetKhattar ok... hold on – ItamarG3 Aug 06 '17 at 10:25 — ItamarG3, Aug 06 '17 at 10:25

score 0 · Answer 3 · answered Aug 06 '17 at 11:04

how to get row value on every time 

StringBuilder sb = new StringBuilder();
                StringBuilder sb2 = new StringBuilder();
                StringBuilder sb3 = new StringBuilder();
                   for(int i=0; i<simpleAdapter.getCount(); i++) {
                    String a = ((TextView) findViewById(R.id.al)).getText().toString();
                    String b = ((TextView) findViewById(R.id.sh)).getText().toString();
                    String c = ((TextView) findViewById(R.id.acc)).getText().toString();
                    simpleAdapter.getItem(i).toString();
                    sb.append(a);
                    sb.append("\n");
                    sb2.append(b);
                    sb2.append("\n");
                    sb3.append(c);
                    sb3.append("\n");

                    String text = sb.toString();
                    String text2 = sb2.toString();
                    String text3 = sb3.toString();
}}

all listview data insert only one row

3 Answers3