sqlite returned: error code = 1, msg = no such table: login

101-21 18:35:20.054: I/SqliteDatabaseCpp(1402): sqlite returned: error code = 1, msg = no such table: login, db=/data/data/hogau.absenzen/databases/hogau_absenzen

1package datenbanktest;
2
3import java.util.ArrayList;
4import java.util.HashMap;
5import java.util.List;
6import datenbanktest.Stammdaten;
7import android.content.ContentValues;
8import android.content.Context;
9import android.database.Cursor;
10import android.database.sqlite.SQLiteDatabase;
11import android.database.sqlite.SQLiteOpenHelper;
12import android.util.Log;
13
14public class DatabaseHandler extends SQLiteOpenHelper {
15
16 // All Static variables
17 // Database Version
18 private static final int DATABASE_VERSION = 3;
19
20 // Database Name
21 private static final String DATABASE_NAME = "hogau_absenzen";
22
23 // Login table name
24 private static final String TABLE_LOGIN = "login";
25 private static final String TABLE_ABS = "absenzen";
26
27 // Stammdaten Table Columns names ( Table login)
28 private static final String KEY_ID = "id";
29 private static final String KEY_LASTDATE = "lastdate";
30 private static final String KEY_LASTTIME = "lasttime";
31 private static final String KEY_MAILADRESSE = "mailadresse";
32 private static final String KEY_STRASSE = "strasse";
33 private static final String KEY_VORNAME = "vorname";
34 private static final String KEY_ANREDE_ERZ = "anredeErz";
35 private static final String KEY_NAME = "name";
36 private static final String KEY_NAME_ERZ = "nameErz";
37 private static final String KEY_ORT = "ort";
38 private static final String KEY_PLZ = "plz";
39 private static final String KEY_GESCHLECHT = "geschlecht";
40 private static final String KEY_GEBDAT = "gebdat";
41 private static final String KEY_KLASSE = "klasse";
42 private static final String KEY_PW = "pw";
43 private static final String KEY_MAILADRESSE_ELTERN = "mailadresse_eltern";
44 private static final String KEY_MAILREMINDER = "mailreminder";
45
46 // AlleABsenzen Table Columns names ( Table absenzen)
47 private static final String KEY_ART = "art";
48 private static final String KEY_AMVOM = "amvom";
49 private static final String KEY_TAGE = "tage";
50 private static final String KEY_STUNDEN = "stunden";
51 private static final String KEY_GRUND = "grund";
52 private static final String KEY_ERFASST_GENEHMIGT = "erfasst_genehmigt";
53 private static final String KEY_ERFASST_AM = "erfasst_am";
54 private static final String KEY_VERVOLLST_BEARBEITET = "vervollstbearbeitet";
55 private static final String KEY_MAZ = "maz";
56 private static final String KEY_TAN = "tan";
57 private static final String KEY_ABSID = "absid";
58
59 public DatabaseHandler(Context context) {
60 super(context, DATABASE_NAME, null, DATABASE_VERSION);
61 }
62
63 // Creating Tables
64 @Override
65 public void onCreate(SQLiteDatabase db) {
66 String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_LOGIN + "("
67 + KEY_ID + " INTEGER PRIMARY KEY," + KEY_LASTDATE + " TEXT,"
68 + KEY_LASTTIME + " TEXT," + KEY_MAILADRESSE + " TEXT,"
69 + KEY_STRASSE + " TEXT," + KEY_VORNAME + " TEXT," + KEY_NAME
70 + " TEXT," + KEY_ORT + " TEXT," + KEY_PLZ + " TEXT,"
71 + KEY_GESCHLECHT + " TEXT," + KEY_GEBDAT + " TEXT,"
72 + KEY_KLASSE + " TEXT," + KEY_PW + " TEXT," + KEY_NAME_ERZ
73 + " TEXT," + KEY_MAILREMINDER + " TEXT," + KEY_ANREDE_ERZ
74 + " TEXT," + KEY_MAILADRESSE_ELTERN + " TEXT" + ")";
75
76 db.execSQL(CREATE_LOGIN_TABLE);
77
78 String CREATE_ABS_TABLE = "CREATE TABLE " + TABLE_ABS + "(" + KEY_ABSID
79 + " INTEGER PRIMARY KEY," + KEY_ART + " TEXT," + KEY_AMVOM
80 + " TEXT," + KEY_TAGE + " TEXT," + KEY_STUNDEN + " TEXT,"
81 + KEY_GRUND + " TEXT," + KEY_ERFASST_GENEHMIGT + " TEXT,"
82 + KEY_ERFASST_AM + " TEXT," + KEY_VERVOLLST_BEARBEITET
83 + " TEXT," + KEY_MAZ + " TEXT," + KEY_TAN + " TEXT" + ")";
84
85 db.execSQL(CREATE_ABS_TABLE);
86
87 }
88
89 // Upgrading database
90 @Override
91 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
92 // Drop older table if existed
93 db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);
94
95 // Create tables again
96 onCreate(db);
97
98 db.execSQL("DROP TABLE IF EXISTS " + TABLE_ABS);
99
100 onCreate(db);
101 }
102
103 /**
104 * Storing user details in database
105 * */
106 public void addUser(String id, String lasttime, String lastdate,
107 String mailadresse, String strasse, String vorname, String name,
108 String ort, String plz, String geschlecht, String gebdat,
109 String klasse, String pw, String nameErz, String anredeErz,
110 String mailadresse_eltern, String mailreminder) {
111
112 SQLiteDatabase db = this.getWritableDatabase();
113 ContentValues values = new ContentValues();
114
115 values.put(KEY_ID, id);
116 values.put(KEY_LASTTIME, lasttime);
117 values.put(KEY_LASTDATE, lastdate);
118 values.put(KEY_MAILADRESSE, mailadresse);
119 values.put(KEY_STRASSE, strasse);
120 values.put(KEY_VORNAME, vorname);
121 values.put(KEY_NAME, name);
122 values.put(KEY_ORT, ort);
123 values.put(KEY_PLZ, plz);
124 values.put(KEY_GESCHLECHT, geschlecht);
125 values.put(KEY_GEBDAT, gebdat);
126 values.put(KEY_KLASSE, klasse);
127 values.put(KEY_PW, pw);
128 values.put(KEY_ANREDE_ERZ, anredeErz);
129 values.put(KEY_NAME_ERZ, nameErz);
130 values.put(KEY_MAILADRESSE_ELTERN, mailadresse_eltern);
131 values.put(KEY_MAILREMINDER, mailreminder);
132 // Inserting Row
133 db.insert(TABLE_LOGIN, null, values);
134 db.close(); // Closing database connection
135 }
136
137 /**
138 * Storing Abs details in database
139 * @param AbsId
140 * @param art
141 * @param amvom
142 * @param tage
143 * @param stunden
144 * @param grund
145 * @param erfasst_genehmigt
146 * @param erfasst_am
147 * @param vervollstbearbeitet
148 * @param maz
149 * @param tan
150 */
151 public void addAbs(String AbsId, String art, String amvom, String tage,
152 String stunden, String grund, String erfasst_genehmigt,
153 String erfasst_am, String vervollstbearbeitet, String maz,
154 String tan) {
155
156 SQLiteDatabase db = this.getWritableDatabase();
157 ContentValues values = new ContentValues();
158
159 values.put(KEY_ABSID, AbsId);
160 values.put(KEY_ART, art);
161 values.put(KEY_AMVOM, amvom);
162 values.put(KEY_TAGE, tage);
163 values.put(KEY_STUNDEN, stunden);
164 values.put(KEY_GRUND, grund);
165 values.put(KEY_ERFASST_GENEHMIGT, erfasst_genehmigt);
166 values.put(KEY_ERFASST_AM, erfasst_am);
167 values.put(KEY_VERVOLLST_BEARBEITET, vervollstbearbeitet);
168 values.put(KEY_MAZ, maz);
169 values.put(KEY_TAN, tan);
170 // Inserting Row
171 db.insert(TABLE_ABS, null, values);
172 db.close(); // Closing database connection
173 }
174
175 // Getting single stammdaten
176 public Stammdaten getStammdaten(int id) {
177 SQLiteDatabase db = this.getReadableDatabase();
178
179 Cursor cursor = db.query(TABLE_LOGIN, new String[] { KEY_ID,
180 KEY_LASTTIME, KEY_LASTDATE, KEY_MAILADRESSE, KEY_STRASSE,
181 KEY_VORNAME, KEY_NAME, KEY_ORT, KEY_PLZ, KEY_GESCHLECHT,
182 KEY_GEBDAT, KEY_KLASSE, KEY_PW, KEY_NAME_ERZ, KEY_ANREDE_ERZ,
183 KEY_MAILADRESSE_ELTERN, KEY_MAILREMINDER }, KEY_ID + "=?",
184 new String[] { String.valueOf(id) }, null, null, null, null);
185 if (cursor != null)
186 cursor.moveToFirst();
187
188 Stammdaten stammdaten = new Stammdaten(Integer.parseInt(cursor
189 .getString(0)), cursor.getString(1), cursor.getString(2),
190 cursor.getString(3), cursor.getString(4), cursor.getString(5),
191 cursor.getString(6), cursor.getString(7), cursor.getString(8),
192 cursor.getString(9), cursor.getString(10),
193 cursor.getString(11), cursor.getString(12),
194 cursor.getString(13), cursor.getString(14),
195 cursor.getString(15), cursor.getString(16));
196 // return contact
197 cursor.close();
198 db.close();
199 return stammdaten;
200 }
201
202 // get single absenz
203 public Absenz getAbs(int AbsId) {
204 SQLiteDatabase db = this.getReadableDatabase();
205
206 Cursor cursor = db.query(TABLE_ABS, new String[] { KEY_ABSID, KEY_ART,
207 KEY_AMVOM, KEY_TAGE, KEY_STUNDEN, KEY_GRUND,
208 KEY_ERFASST_GENEHMIGT, KEY_ERFASST_AM,
209 KEY_VERVOLLST_BEARBEITET, KEY_MAZ, KEY_TAN }, KEY_ID + "=?",
210 new String[] { String.valueOf(AbsId) }, null, null, null, null);
211 if (cursor != null)
212 cursor.moveToFirst();
213
214 Absenz absenz = new Absenz(Integer.parseInt(cursor
215 .getString(0)), cursor.getString(1), cursor.getString(2),
216 cursor.getString(3), cursor.getString(4), cursor.getString(5),
217 cursor.getString(6), cursor.getString(7), cursor.getString(8),
218 cursor.getString(9), cursor.getString(10));
219 // return contact
220 cursor.close();
221 db.close();
222 return absenz;
223 }
224
225 // Getting All Stammdaten
226 public List<Stammdaten> getAllStammdaten() {
227 List<Stammdaten> stammdatenList = new ArrayList<Stammdaten>();
228 // Select All Query
229 String selectQuery = "SELECT * FROM " + TABLE_LOGIN;
230
231 SQLiteDatabase db = this.getWritableDatabase();
232 Cursor cursor = db.rawQuery(selectQuery, null);
233
234 // looping through all rows and adding to list
235 if (cursor.moveToFirst()) {
236 do {
237 Stammdaten stammdaten = new Stammdaten();
238 stammdaten.set_id(Integer.parseInt(cursor.getString(0)));
239 stammdaten.set_lasttime(cursor.getString(1));
240 stammdaten.set_lastdate(cursor.getString(2));
241 stammdaten.set_mailadresse(cursor.getString(3));
242 stammdaten.set_strasse(cursor.getString(4));
243 stammdaten.set_vorname(cursor.getString(5));
244 stammdaten.set_name(cursor.getString(6));
245 stammdaten.set_ort(cursor.getString(7));
246 stammdaten.set_plz(cursor.getString(8));
247 stammdaten.set_geschlecht(cursor.getString(9));
248 stammdaten.set_gebdat(cursor.getString(10));
249 stammdaten.set_klasse(cursor.getString(11));
250 stammdaten.set_pw(cursor.getString(12));
251 stammdaten.set_nameErz(cursor.getString(13));
252 stammdaten.set_anredeErz(cursor.getString(14));
253 stammdaten.set_mailadresseeltern(cursor.getString(15));
254 stammdaten.set_mailadresseeltern(cursor.getString(16));
255 // Adding contact to list
256 stammdatenList.add(stammdaten);
257 } while (cursor.moveToNext());
258 }
259
260 // return contact list
261 return stammdatenList;
262 }
263
264 /**
265 * Gettin All Absenzen
266 * @return
267 */
268 public List<Absenz> getAllAbsenzen() {
269 List<Absenz> absenzenList = new ArrayList<Absenz>();
270 // Select All Query
271 String selectQuery = "SELECT * FROM " + TABLE_ABS;
272
273 SQLiteDatabase db = this.getWritableDatabase();
274 Cursor cursor = db.rawQuery(selectQuery, null);
275
276 // looping through all rows and adding to list
277 if (cursor.moveToFirst()) {
278 do {
279 Absenz absenz = new Absenz();
280 absenz.set_absid(Integer.parseInt(cursor.getString(0)));
281 absenz.set_art(cursor.getString(1));
282 absenz.set_amvom(cursor.getString(2));
283 absenz.set_tage(cursor.getString(3));
284 absenz.set_stunden(cursor.getString(4));
285 absenz.set_grund(cursor.getString(5));
286 absenz.set_erfasst_genehmigt(cursor.getString(6));
287 absenz.set_erfasst_am(cursor.getString(7));
288 absenz.set_vervollstbearbeitet(cursor.getString(8));
289 absenz.set_maz(cursor.getString(9));
290 absenz.set_tan(cursor.getString(10));
291 // Adding contact to list
292 absenzenList.add(absenz);
293 } while (cursor.moveToNext());
294 }
295
296 // return contact list
297 return absenzenList;
298 }
299
300 /**
301 * Getting user login status return true if rows are there in table
302 * */
303 public int getRowCountLogin() {
304 String countQuery = "SELECT * FROM " + TABLE_LOGIN;
305 SQLiteDatabase db = this.getReadableDatabase();
306 Cursor cursor = db.rawQuery(countQuery, null);
307 int rowCount = cursor.getCount();
308 db.close();
309 cursor.close();
310
311 // return row count
312 return rowCount;
313 }
314
315 /**
316 * Getting user Abs status return true if rows are there in the table
317 * @return
318 */
319 public int getRowCountAbs() {
320 String countQuery = "SELECT * FROM " + TABLE_ABS;
321 SQLiteDatabase db = this.getReadableDatabase();
322 Cursor cursor = db.rawQuery(countQuery, null);
323 int rowCount = cursor.getCount();
324 db.close();
325 cursor.close();
326
327 // return row count
328 return rowCount;
329 }
330
331 /**
332 * Re crate database Delete all tables and create them again
333 * */
334 public void resetTables() {
335 SQLiteDatabase db = this.getWritableDatabase();
336 // Delete All Rows
337 db.delete(TABLE_LOGIN, null, null);
338 db.close();
339 db.delete(TABLE_ABS, null, null);
340 db.close(); // TODO fragen ob das in ordnung geht!!
341 }
342
343}