How to read Listbox to a Recordset

General
1

Hi All!

I have a Listbox filled of data from a CSV File with columns and rows, I wonder if would be possible to pass the data of the Listbox to a Recordset. In order to Insert it on my MySQL Database:

app.mDb.SQLExecute(“INSERT INTO Recibidas(UUID, XML,RFCemisor) VALUES ( '”+ “Car” +"’, "+ “20” + “,”+ “1987”+ “)”)
app.MDb.SQLExecute( “COMMIT” )

Where says Car, 20, 1987, I want to put the data of the Listbox.
I planning to use a For Instruction

Ideas?

2

Somtehing Like:

//listbox contents
for i = 0 to listBox1.listcount - 1
app.mDb.SQLExecute(“INSERT INTO Recibidas(UUID, XML,RFCemisor) VALUES ( '”+ “Car” +"’, "+ “20” + “,”+ “1987”+ “)”)

next

I’m Thinking

3

assuming your columns match your record layout

dim s as string
dim  i as integer
dim j as integer
for i=0 to listbox.listcount-1
s=""
for j=0 to listbox.columncount-1
s=s+chrb(34)+listbox1.cell(i,j)+chrb(34)+","
next j
sql="INSERT INTO recibidas VALUES("+left(s,len(s)-1)+")"
app.mdb.sqlexecute(s)
next i
4

[quote=189973:@Dave S]assuming your columns match your record layout

dim s as string dim i as integer dim j as integer for i=0 to listbox.listcount-1 s="" for j=0 to listbox.columncount-1 s=s+chrb(34)+listbox1.cell(i,j)+chrb(34)+"," next j sql="INSERT INTO recibidas VALUES("+left(s,len(s)-1)+")" app.mdb.sqlexecute(s) next i [/quote]
I have a question, Where goes sql? I probe it and it doesn’t work.

This is the headers of my CSV File:
XML Rfc Emisor Nombre Emisor Rfc Receptor Nombre Receptor Tipo Serie Folio Fecha Sub Total Descuento Impuesto Trasladado Nombre Impuesto Impuesto Retenido Nombre Impuesto Total UUID Metodo de Pago Moneda Tipo de Cambio Estado Estatus Conceptos Traslado IVA: 16 Traslado IVA: 0.16 Traslado IVA: 11

The Name of the Columns of the mySQL database are this:

Dim sql As String
sql = “CREATE table Recibidas (XML TEXT, RFCemisor TEXT, NombreEmisor TEXT, RFCReceptor TEXT, NombreReceptor TEXT, Tipo TEXT, Serie TEXT, Folio TEXT, Fecha DATETIME, Subtotal DECIMAL, Descuento DECIMAL, ImpuestoTrasladado DECIMAL, NombreImpuesto TEXT, ImpuestoRetenido DECIMAL, NombreImpuesto2 TEXT, Total DECIMAL, UUID INTEGER NOT NULL, MetodoDePago TEXT, Moneda DECIMAL, TipoDeCambio TEXT, Estatus TEXT, Conceptos TEXT, TrasladoIVA16 DECIMAL, TrasladoIVA016 DECIMAL,TrasladoIVA11 DECIMAL, PRIMARY KEY(UUID) )”

If app.mDb.Connect Then
app.mDB.SQLExecute(sql)

If app.mDB.Error Then
  Msgbox "DB Error: " + app.mDB.ErrorMessage
  Return
End If

EstatusConexion.Text = "Table Successfully Created."

Else
MsgBox(“Connect to the Database First.”)
End If

5

I mean the SQLExecute is recording only S only not with SQL,

6

And app.mdb.SQLExecute(“COMMIT”) is missing

7

If I Use This I can make records on my table:

Dim row As New DatabaseRecord
// ID will be updated automatically
row.Column(“XML”) = “027BB01E-E54D-473E-8616-91407405EBC3.xml”
row.Column(“RFCEmisor”) = “GHU071016HE1”
row.Column(“NombreEmisor”) = “GASOLINERA HUITEPEC S.A. DE C.V.”
row.Column(“UUID”) = “027BB01E-E54D-473E-8616-91407405EBC3”

app.mDb.InsertRecord(“Recibidas”, row)

If app.mDb.Error Then
MsgBox("DB Error: " + app.mDb.ErrorMessage)
End If

But its Individual, I need to get a loop to extract the data of the listbox.

8

Basically In need to get the data inside of a Listbox and put it on a DatabaseRecord

9

Finally I got it! with this:

dim x As Integer
dim dr as new databaserecord
if app.mdb.Connect then

for x = 0 to listafacturas.ListCount-1
  // dr is the database record and looping through the rows (x)
  // cells defined as cell(rows, columns)
  
  dr.column("XML")=listafacturas.cell(x,0)
  dr.column("RFCEmisor")=listafacturas.cell(x,1)
  dr.column("NombreEmisor")=listafacturas.cell(x,2)
  dr.column("RFCReceptor")=listafacturas.cell(x,3)
  dr.column("NombreReceptor")=listafacturas.cell(x,4)
  dr.column("Tipo")=listafacturas.cell(x,5)
  dr.column("Serie")=listafacturas.cell(x,6)
  dr.column("Folio")=listafacturas.cell(x,7)
  dr.column("Fecha")=listafacturas.cell(x,8)
  dr.column("Subtotal")=listafacturas.cell(x,9)
  dr.column("Descuento")=listafacturas.cell(x,10)
  dr.column("ImpuestoTrasladado")=listafacturas.cell(x,11)
  dr.column("NombreImpuesto")=listafacturas.cell(x,12)
  dr.column("ImpuestoRetenido")=listafacturas.cell(x,13)
  dr.column("NombreImpuesto2")=listafacturas.cell(x,14)
  dr.column("Total")=listafacturas.cell(x,15)
  dr.column("UUID")=listafacturas.cell(x,16)
  dr.column("MetodoDePago")=listafacturas.cell(x,17)
  dr.column("Moneda")=listafacturas.cell(x,18)
  dr.column("TipoDeCambio")=listafacturas.cell(x,19)
  dr.column("Estado")=listafacturas.cell(x,20)
  dr.column("Estatus")=listafacturas.cell(x,21)
  dr.column("Conceptos")=listafacturas.cell(x,22)
  dr.column("TrasladoIVA16")=listafacturas.cell(x,23)
  dr.column("TrasladoIVA016")=listafacturas.cell(x,24)
  dr.column("TrasladoIVA11")=listafacturas.cell(x,25)
  
  
  App.mDb.InsertRecord ("Recibidas", dr)
  // Checking for DB errors
  If App.mDB.Error Then
    MsgBox("DB Error: " + App.mDb.ErrorMessage)
    return
  End If
next

else

MsgBox("DB Error: " + App.mDB.ErrorMessage)

end if

In this mode I can get all Values from the Listbox to the DataBaseRecord and Finally Insert Records.