public bsRecord vanRecord(int recNr) { bsRecord bsRec = new bsRecord(); bsRec.Bstl_Id = lstBestellingRecord[recNr].Bstl_Id; bsRec.Bstl_StatusId = lstBestellingRecord[recNr].Bstl_StatusId; bsRec.Bstl_DispStatus = lstBestellingRecord[recNr].Bstl_DispStatus; bsRec.Bstl_BgnrId = lstBestellingRecord[recNr].Bstl_BgnrId; bsRec.Bstl_DispBgnr = lstBestellingRecord[recNr].Bstl_DispBgnr; bsRec.Bstl_KlBgId = lstBestellingRecord[recNr].Bstl_KlBgId; bsRec.Bstl_DispKlBg = lstBestellingRecord[recNr].Bstl_DispKlBg; bsRec.Bstl_VerwerkperiodeId = lstBestellingRecord[recNr].Bstl_VerwerkperiodeId; bsRec.Bstl_valutadatum = lstBestellingRecord[recNr].Bstl_valutadatum; bsRec.Bstl_Bestelbedrag = lstBestellingRecord[recNr].Bstl_Bestelbedrag; bsRec.Bstl_Diversen = lstBestellingRecord[recNr].Bstl_Diversen; bsRec.Bstl_DiversenId = lstBestellingRecord[recNr].Bstl_DiversenId; bsRec.Bstl_DispDiversen = lstBestellingRecord[recNr].Bstl_DispDiversen; bsRec.Bstl_Verschil = lstBestellingRecord[recNr].Bstl_Verschil; bsRec.Bstl_VerschilId = lstBestellingRecord[recNr].Bstl_VerschilId; bsRec.Bstl_DispVerschil = lstBestellingRecord[recNr].Bstl_DispVerschil; bsRec.Bstl_Vastekostenperbestelling = lstBestellingRecord[recNr].Bstl_Vastekostenperbestelling; bsRec.Bstl_BonnengevraagdJN = lstBestellingRecord[recNr].Bstl_BonnengevraagdJN; bsRec.Bstl_Mutatiedatum = lstBestellingRecord[recNr].Bstl_Mutatiedatum; bsRec.Bstl_Opmerking = lstBestellingRecord[recNr].Bstl_Opmerking; return(bsRec); }
public bsRecord vulDefaultBs() { pf pf = new pf(); bsRecord bsRec = new bsRecord(); bsRec.Bstl_StatusId = 120009; bsRec.Bstl_DispStatus = "Bestelling-record is leeg / Tabelinitrecord"; bsRec.Bstl_BgnrId = 1; bsRec.Bstl_DispBgnr = "NL59ONBB0000000000"; bsRec.Bstl_KlBgId = 1; bsRec.Bstl_DispKlBg = "Klant-Begunstigde"; bsRec.Bstl_VerwerkperiodeId = 1; bsRec.Bstl_valutadatum = DateTime.Parse("2000-01-01 00:00:00"); bsRec.Bstl_Bestelbedrag = 0; bsRec.Bstl_Diversen = 0; bsRec.Bstl_DiversenId = 121009; bsRec.Bstl_DispDiversen = "Bstl_Div-Leeg"; bsRec.Bstl_Verschil = 0; bsRec.Bstl_VerschilId = 122009; bsRec.Bstl_DispVerschil = "Bstl_Vschl-Leeg"; bsRec.Bstl_Vastekostenperbestelling = 0; bsRec.Bstl_BonnengevraagdJN = 1; bsRec.Bstl_Mutatiedatum = DateTime.Parse("2000-01-01 00:00:00"); bsRec.Bstl_Opmerking = ""; return(bsRec); }
public void saveRecord(int iBsId, bsRecord bsR) { string sqlStr = "Update Bestelling (Bstl_StatusId=@2, Bstlr_DispStatus=@3, Bstl_BgnrId=@4, Bstl_DispBgnr=@5, Bstl_KlBgId=@6, " + "Bstl_DispKlBg=@7, Bstl_VerwerkperiodeId=@8, Bstl_Valutadatum=@9, Bstl_Bestelbedrag=@10, Bstl_Diversen=@11, " + "Bstl_DiversenId=@12, Bstl_DispDiversen=@13, Bstl_Verschil=@14, Bstl_VerschilId=@15, Bstl_DispVerschil=@16, " + "Bstl_Vastekostenperbestelling=@17, Bstl_BonnengevraagdJN=@18, Bstl_Mutatiedatum=@19, Bstl_Opmerking=@20 " + "Where Bstl_Id=@1"; string sCs = "Data Source=" + gv.sDataFilePad + ";Version=3;New=False;"; using (SQLiteConnection dbcDa = new SQLiteConnection(sCs)) { dbcDa.Open(); try { using (SQLiteCommand sqlCmd = new SQLiteCommand(sqlStr, dbcDa)) { sqlCmd.Parameters.AddWithValue("@1", iBsId); sqlCmd.Parameters.AddWithValue("@2", bsR.Bstl_StatusId); sqlCmd.Parameters.AddWithValue("@3", bsR.Bstl_DispStatus); sqlCmd.Parameters.AddWithValue("@4", bsR.Bstl_BgnrId); sqlCmd.Parameters.AddWithValue("@5", bsR.Bstl_DispBgnr); sqlCmd.Parameters.AddWithValue("@6", bsR.Bstl_KlBgId); sqlCmd.Parameters.AddWithValue("@7", bsR.Bstl_DispKlBg); sqlCmd.Parameters.AddWithValue("@8", bsR.Bstl_VerwerkperiodeId); sqlCmd.Parameters.AddWithValue("@9", bsR.Bstl_valutadatum); sqlCmd.Parameters.AddWithValue("@10", bsR.Bstl_Bestelbedrag); sqlCmd.Parameters.AddWithValue("@11", bsR.Bstl_DispDiversen); sqlCmd.Parameters.AddWithValue("@12", bsR.Bstl_DiversenId); sqlCmd.Parameters.AddWithValue("@13", bsR.Bstl_DispDiversen); sqlCmd.Parameters.AddWithValue("@14", bsR.Bstl_Verschil); sqlCmd.Parameters.AddWithValue("@15", bsR.Bstl_VerschilId); sqlCmd.Parameters.AddWithValue("@16", bsR.Bstl_DispVerschil); sqlCmd.Parameters.AddWithValue("@17", bsR.Bstl_Vastekostenperbestelling); sqlCmd.Parameters.AddWithValue("@18", bsR.Bstl_BonnengevraagdJN); sqlCmd.Parameters.AddWithValue("@19", bsR.Bstl_Mutatiedatum); sqlCmd.Parameters.AddWithValue("@20", bsR.Bstl_Opmerking); sqlCmd.ExecuteNonQuery(); } } catch (Exception) { } dbcDa.Close(); } }
private void recordsInList(SQLiteDataReader r) { bsRecord bsr = new bsRecord(); lstBestellingRecord.Clear(); bsListCount = 0; bsListTCount = 0; while (r.Read()) { //Maak list van geselecteerde rijen bsListTCount++; bsr.Bstl_Id = r.GetInt32(r.GetOrdinal("Bstl_Id")); bsr.Bstl_StatusId = r.GetInt32(r.GetOrdinal("Bstl_StatusId")); if (r.GetInt32(r.GetOrdinal("Bstl_StatusId")) != 120009) { bsListCount++; } bsr.Bstl_DispStatus = r.GetString(r.GetOrdinal("Bstl_DispStatus")); bsr.Bstl_BgnrId = r.GetInt32(r.GetOrdinal("Bstl_BgnrId")); bsr.Bstl_DispBgnr = r.GetString(r.GetOrdinal("Bstl_DispBgnr")); bsr.Bstl_KlBgId = r.GetInt32(r.GetOrdinal("Bstl_KlBgId")); bsr.Bstl_DispKlBg = r.GetString(r.GetOrdinal("Bstl_DispKlBg")); bsr.Bstl_VerwerkperiodeId = r.GetInt32(r.GetOrdinal("Bstl_VerwerkperiodeId")); bsr.Bstl_valutadatum = r.GetDateTime(r.GetOrdinal("Bstl_Valutadatum")); bsr.Bstl_Bestelbedrag = r.GetDecimal(r.GetOrdinal("Bstl_Bestelbedrag")); bsr.Bstl_Diversen = r.GetDecimal(r.GetOrdinal("Bstl_Diversen")); bsr.Bstl_DiversenId = r.GetInt32(r.GetOrdinal("Bstl_DiversenId")); bsr.Bstl_DispDiversen = r.GetString(r.GetOrdinal("Bstl_DispDiversen")); bsr.Bstl_Verschil = r.GetDecimal(r.GetOrdinal("Bstl_Verschil")); bsr.Bstl_VerschilId = r.GetInt32(r.GetOrdinal("Bstl_VerschilId")); bsr.Bstl_DispVerschil = r.GetString(r.GetOrdinal("Bstl_DispVerschil")); bsr.Bstl_Vastekostenperbestelling = r.GetDecimal(r.GetOrdinal("Bstl_Vastekostenperbestelling")); bsr.Bstl_BonnengevraagdJN = r.GetInt32(r.GetOrdinal("Bstl_BonnengevraagdJN")); bsr.Bstl_Mutatiedatum = r.GetDateTime(r.GetOrdinal("Bstl_Mutatiedatum")); try { bsr.Bstl_Opmerking = r.GetString(r.GetOrdinal("Bstl_Opmerking")); } catch (Exception) { } lstBestellingRecord.Add(bsr); } }
public int newbsRecord() { pf pf = new pf(); bsRecord dbs = new bsRecord(); dbs = vulDefaultBs(); int newBsId = new int(); string sCs = "Data Source=" + gv.sDataFilePad + ";Version=3;New=False;"; using (SQLiteConnection dbcDa = new SQLiteConnection(sCs)) { dbcDa.Open(); string findstring = pf.randomString(6); string sqlStr = "Insert Into Bestelling (Bstl_StatusId, Bstlr_DispStatus, Bstl_BgnrId, Bstl_DispBgnr, Bstl_KlBgId, Bstl_DispKlBg " + "Bstl_Verwerkperiode, Bstl_Valutadatum, Bstl_Bestelbedrag, Bstl_Diversen, Bstl_DiversenId, Bstl_DispDiversen, Bstl_Verschil, " + "Bstl_VerschilId, Bstl_DispVerschil, Bstl_Vastekostenperbestelling, Bstl_BonnengevraagdJN, Bstl_Mutatiedatum, Bstl_Opmerking) Values " + "(@2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16, @17, @18, @19, @20)"; using (SQLiteCommand sqlCmd = new SQLiteCommand(sqlStr, dbcDa)) { SQLiteParameter p2 = new SQLiteParameter(); p2.ParameterName = "@2"; p2.Value = 120009; sqlCmd.Parameters.Add(p2); SQLiteParameter p3 = new SQLiteParameter(); p3.ParameterName = "@3"; p3.Value = "Bestelling-record is leeg / Tabelinitrecord"; sqlCmd.Parameters.Add(p3); SQLiteParameter p4 = new SQLiteParameter(); p4.ParameterName = "@4"; p4.Value = 1; sqlCmd.Parameters.Add(p4); SQLiteParameter p5 = new SQLiteParameter(); p5.ParameterName = "@5"; p5.Value = "NL59ONBB0000000000"; sqlCmd.Parameters.Add(p5); SQLiteParameter p6 = new SQLiteParameter(); p6.ParameterName = "@6"; p6.Value = 1; sqlCmd.Parameters.Add(p6); SQLiteParameter p7 = new SQLiteParameter(); p7.ParameterName = "@7"; p7.Value = "Klant-Begunstigde"; sqlCmd.Parameters.Add(p7); SQLiteParameter p8 = new SQLiteParameter(); p8.ParameterName = "@8"; p8.Value = 1; sqlCmd.Parameters.Add(p8); SQLiteParameter p9 = new SQLiteParameter(); p9.ParameterName = "@9"; p9.Value = DateTime.Parse("2000-01-01 00:00:00"); sqlCmd.Parameters.Add(p9); SQLiteParameter p10 = new SQLiteParameter(); p10.ParameterName = "@10"; p10.Value = 0; sqlCmd.Parameters.Add(p10); SQLiteParameter p11 = new SQLiteParameter(); p11.ParameterName = "@11"; p11.Value = 0; sqlCmd.Parameters.Add(p11); SQLiteParameter p12 = new SQLiteParameter(); p12.ParameterName = "@12"; p12.Value = 121009; sqlCmd.Parameters.Add(p12); SQLiteParameter p13 = new SQLiteParameter(); p13.ParameterName = "@13"; p13.Value = "Bstl_Div-Leeg"; sqlCmd.Parameters.Add(p13); SQLiteParameter p14 = new SQLiteParameter(); p14.ParameterName = "@14"; p14.Value = 0; sqlCmd.Parameters.Add(p11); SQLiteParameter p15 = new SQLiteParameter(); p15.ParameterName = "@15"; p15.Value = 122009; sqlCmd.Parameters.Add(p12); SQLiteParameter p16 = new SQLiteParameter(); p16.ParameterName = "@16"; p16.Value = "Bstl_Vschl-Leeg"; sqlCmd.Parameters.Add(p13); SQLiteParameter p17 = new SQLiteParameter(); p17.ParameterName = "@17"; p17.Value = 0; sqlCmd.Parameters.Add(p17); SQLiteParameter p18 = new SQLiteParameter(); p18.ParameterName = "@18"; p18.Value = 1; sqlCmd.Parameters.Add(p18); SQLiteParameter p19 = new SQLiteParameter(); p19.ParameterName = "@19"; p19.Value = DateTime.Parse("2000-01-01 00:00:00"); sqlCmd.Parameters.Add(p19); SQLiteParameter p20 = new SQLiteParameter(); p20.ParameterName = "@20"; p20.Value = findstring; sqlCmd.Parameters.Add(p20); sqlCmd.ExecuteNonQuery(); dbcDa.Close(); } // Zoek toegevoegde record tblBs bs = new tblBs(); bs.zoekBestellingRecord("Bstl_Opmerking = " + "\"" + findstring + "\""); newBsId = bs.lstBestellingRecord[0].Bstl_Id; // Verwijder infor uit Opmerking-veld dbcDa.Open(); sqlStr = "Update Bestelling set Bstl_Opmerking=@20 where Bstl_Id = @1;"; using (SQLiteCommand sqlCmd = new SQLiteCommand(sqlStr, dbcDa)) { sqlCmd.Parameters.AddWithValue("@1", newBsId); sqlCmd.Parameters.AddWithValue("@20", ""); sqlCmd.ExecuteNonQuery(); } dbcDa.Close(); return(newBsId); } }