I am needing import txt files (Tab delimited) to a .db3 base.
These two lines I cant execute it
SQLcommand.CommandText = ".import siniestros.txt " & ctbl_TmpSiniestros
SQLcommand.ExecuteNonQuery()
I dont know how to run sqlite "dot" commands with this dll library. I am
googling it so much. :(
I am using
System.Data.SQLite
ADO.NET 2.0 Data Provider for SQLite
V.1.0.66.0
Engine version v2.0.50727
Regards.
Esteban
This is my code. It work swell, until stop in the mentioned lines
Imports System.Data.SQLite
Imports System.IO
Public Class frmSiniestralidad
Private Sub btnNvoProyecto_Click(ByVal sender As System.Object, ByVal
e AsSystem.EventArgs)
Handles btnNvoProyecto.Click
'Mostrar cuadro de dilogo para guardar proyecto
'-------------------------------------------------
Dim f As New SaveFileDialog
f.Filter = "SQLite 3 (*.db3)|*.db3|All Files|*.*"
f.Title = "Crear Base de Siniestralidad"
If f.ShowDialog() = DialogResult.OK Then
'Create Database
Dim SQLconnect As New SQLite.SQLiteConnection()
'La base la creamos a partir de la informacin que nos brinda el cuadro de
dilogo guardar
SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
SQLconnect.Open()
'SQLconnect.Close()
'tellena el Path de proyecto.
txtPathDB.Text = f.FileName
'Activar y desactivar Tabs
'tab_container.TabPages(1).Enabled = True
'tab_container.TabPages(2).Enabled = True
'tab_container.TabPages(3).Enabled = True
'tab_container.TabPages(4).Enabled = True
'Crear Tablas
'-------------
Dim SQLcommand As SQLiteCommand
Const ctbl_DatSiniestros As String = "DatSiniestros"
Const ctbl_ParCostoMedio As String = "ParCostoMedio"
Const ctbl_ParFDA As String = "ParFDA"
Const ctbl_ParTransiciones As String = "ParTransiciones"
Const ctbl_ParConfig As String = "ParConfig"
Const ctbl_ResRdosGrupo As String = "ResRdosGrupo"
SQLcommand = SQLconnect.CreateCommand
'query SQL para Crear Tabla
SQLcommand.CommandText = _
"CREATE TABLE " & ctbl_DatSiniestros & "( " _
& "SINIESTRO TEXT PRIMARY KEY, " _
& "CONTRATO INTEGER, " _
& "GRUPO TEXT , " _
& "FECHAACC NUMERIC , " _
& "DELAY INTEGER , " _
& "ESTADO INTEGER , " _
& "BITALTA NUMERIC , " _
& "ALTAPROBABLE NUMERIC , " _
& "DIAS INTEGER , " _
& "IBM REAL , " _
& "SALARIO REAL , " _
& "SEXOFEM NUMERIC , " _
& "EDAD INTEGER , " _
& "EP NUMERIC , " _
& "PORINC REAL , " _
& "ILT REAL , " _
& "INDEM REAL " _
& "); "
SQLcommand.ExecuteNonQuery()
SQLcommand.Dispose()
'popular tabla
'primero importo una tabla temporal
'inicio una transaccin
Dim SQLTransaction As SQLiteTransaction
SQLTransaction = SQLconnect.BeginTransaction() 'ver constantes no estoy
seguro sea snapshot
'asigno la transaccin al comando SQL
SQLcommand.Transaction = SQLTransaction
'parece superfluo, pero hay que asignar la conexin a la transaccin que hice
desde ah
'SQLcommand.Connection = SQLconnect
Const ctbl_TmpSiniestros As String = "TmpSiniestros"
Try
SQLcommand.CommandText = ".mode tabs"
SQLcommand.ExecuteNonQuery()
SQLcommand.CommandText = _
"CREATE TABLE " & ctbl_TmpSiniestros & "( " _
& "SINIESTRO TEXT PRIMARY KEY, " _
& "PARQUE INTEGER, " _
& "CONTRATO INTEGER, " _
& "FECHAACC NUMERIC , " _
& "DELAY INTEGER , " _
& "ESTADO INTEGER , " _
& "FECHAALTA NUMERIC , " _
& "ALTAPROBABLE NUMERIC , " _
& "DIAS INTEGER , " _
& "IBM REAL , " _
& "SALARIO REAL , " _
& "SEXO TEXT , " _
& "EDAD INTEGER , " _
& "EP NUMERIC , " _
& "PORINC REAL , " _
& "); "
SQLcommand.ExecuteNonQuery()
SQLcommand.CommandText = ".import siniestros.txt " & ctbl_TmpSiniestros
SQLcommand.ExecuteNonQuery()
'SQLcommand.CommandText = "DROP TABLE " & ctbl_TmpSiniestros & " ;"
'SQLcommand.ExecuteNonQuery()
'Realizar la sucesin de transacciones.
SQLTransaction.Commit()
Console.WriteLine("Se ha importado la tabla " & ctbl_TmpSiniestros)
Catch ex As Exception
Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
Console.WriteLine(" Mensaje: {0}", ex.Message)
'Trato de volver todo atrs
Try
SQLTransaction.Rollback()
Catch ex2 As Exception
' This catch block will handle any errors that may have occurred
' on the server that would cause the rollback to fail, such as
' a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
Console.WriteLine(" Mensaje: {0}", ex2.Message)
End Try
End Try
'insert into Strings ( Code ) select * from StringsImport;
'drop table StringsImport;
SQLconnect.Close()
End If
End Sub
End Class
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
