sql query from vb6 to oracle

Question

sql query from vb6 to oracle

Navigation

#1 by (0 votes)

0

I need to insert an insert from VB6 to an Oracle database, I changed the procedure, according to the help they already gave me, respecting the Oracle syntax. but now I get Now I get the error "invalid column name", could you help me with some idea of how to solve?

Thanks! Copy the code:

Private Sub inserta_registro()
Dim cad As String
Dim a1 As String * 1
Dim b1 As String * 1

    var_cod_val = CLng(txtCodVal)
    var_monto = CLng(txtMonto)
    var_cod_verif = CLng(txtCodVer)
    var_ref_form = CLng(txtRef)
    a1 = "S"
    b1 = "N"
    
    cad = "INSERT INTO CDJ.CDJ_CONSULTAS_PFS " & _
    "(ID_CONSULTA_PFS,FC_CONSULTA_PFS,ID_VLT," & _
    " HS_CONSULTA,MIN_CONSULTA,HS_RTA,MIN_RTA,HS_PAGO,MIN_PAGO," & _
    " TIPO,FL_TICKET,FL_RTA_CDJ,TIPO_DE_FALLA, COD_VALIDACION, MONTO," & _
    " COD_VERIFICADOR,REF_FORMULARIO,ID_MOTIVO_TICKET,ID_ASISTENTE_SIC," & _
    " ID_SUPERVISOR_SIC,ID_MOTIVO_CONSULTA_PFS,US_CREACION,TS_CREACION," & _
    " US_ULT_MODIF,TS_ULT_MODIF,COD_TIPO_VALIDACION)" & _
    " VALUES(:ID_CONSULTA_PFS,:FC_CONSULTA_PFS,:ID_VLT," & _
    "  :HS_CONSULTA,:MIN_CONSULTA,:HS_RTA,:MIN_RTA,:HS_PAGO,:MIN_PAGO," & _
    "  :TIPO,:FL_TICKET,:FL_RTA_CDJ,:TIPO_DE_FALLA,:COD_VALIDACION,:MONTO," & _
    "  :COD_VERIFICADOR,:REF_FORMULARIO,:ID_MOTIVO_TICKET,:ID_ASISTENTE_SIC,:ID_SUPERVISOR_SIC," & _
    "  :ID_MOTIVO_CONSULTA_PFS,:US_CREACION,:TS_CREACION," & _
    "  :US_ULT_MODIF,:TS_ULT_MODIF,:COD_TIPO_VALIDACION)"
    oradatabase.Parameters.Add "ID_CONSULTA_PFS", reg_actual, 1, 2
    oradatabase.Parameters.Add "FC_CONSULTA_PFS", Date, 1, 12
    oradatabase.Parameters.Add "ID_VLT", var_slot, 1, 2
    oradatabase.Parameters.Add "HS_CONSULTA", val(txthora(0).text), 1, 2
    oradatabase.Parameters.Add "MIN_CONSULTA", val(txtmin(0).text), 1, 2
    oradatabase.Parameters.Add "HS_RTA", val(txthora(1).text), 1, 2
    oradatabase.Parameters.Add "MIN_RTA", val(txtmin(1).text), 1, 2
    oradatabase.Parameters.Add "HS_PAGO", val(txthora(2).text), 1, 2
    oradatabase.Parameters.Add "MIN_PAGO", val(txtmin(2).text), 1, 2
    oradatabase.Parameters.Add "TIPO", var_tipo, 1, 96
    oradatabase.Parameters.Add "FL_TICKET", var_ticket, 1, 96
    oradatabase.Parameters.Add "FL_RTA_CDJ", a1, 1, 96
    oradatabase.Parameters.Add "TIPO_DE_FALLA", var_Falla, 1, 1
    oradatabase.Parameters.Add "COD_VALIDACION", var_cod_val, 1, 2
    oradatabase.Parameters.Add "MONTO", var_monto, 1, 2
    oradatabase.Parameters.Add "COD_VERIFICADOR", var_cod_verif, 1, 2
    oradatabase.Parameters.Add "REF_FORMULARIO", var_ref_form, 1, 2
    oradatabase.Parameters.Add "ID_MOTIVO_TICKET", var_mot_ticket, 1, 2
    oradatabase.Parameters.Add "ID_ASISTENTE_SIC", var_asist, 1, 2
    oradatabase.Parameters.Add "ID_SUPERVISOR_SIC", var_super, 1, 2
    oradatabase.Parameters.Add "ID_MOTIVO_CONSULTA_PFS", var_consulta, 1, 2
    oradatabase.Parameters.Add "US_CREACION", UCase(var_usuario), 1, 1
    oradatabase.Parameters.Add "TS_CREACION", Date, 1, 12
    oradatabase.Parameters.Add "US_ULT_MODIF", UCase(var_usuario), 1, 1
    oradatabase.Parameters.Add "TS_ULT_MODIF", Date, 1, 12
    oradatabase.Parameters.Add "COD_TIPO_VALIDACION", a1, 1, 96
    oradatabase.ExecuteSQL cad

End Sub

sql oracle plsql vb6

asked by look68 30.08.2017 в 14:49

source

1 answer

kivy opencv android How to separate a text string by means of a space? [duplicate]

score 0 · Answer 1

First of all I recommend you to study a bit about PL-SQL and about practices to avoid SQL injection, I give you an example of how you can implement an insert:

Code:

cad = "INSERT INTO CDJ.CDJ_CONSULTAS_PFS " +
" (ID_CONSULTA_PFS,FC_CONSULTA_PFS,ID_VLT) " +
" VALUES " +
" (:ID_CONSULTA_PFS, :FC_CONSULTA_PFS, :ID_VLT);"

oradatabase.Parameters.Add "ID_CONSULTA_PFS", reg_actual, 1, 1
oradatabase.Parameters.Add "FC_CONSULTA_PFS", Date, 1, 12
oradatabase.Parameters.Add "ID_VLT", var_slot, 1, 2
oradatabase.ExecuteSQL cad

Explanation

In the string of the SQL statement you must declare the parameters with the prefix colon ":" and then assign the parameters by means of the collection Parameters using the Add method where you will have to put in order the Parameters of the method (Parameter, Value, I / O, Type) Where Parameter is the name you declared, Value is what you will take from your form or variable, I / O is if the parameter is input or output, and finally the type where 1 is VARCHAR, 2 is Number and 12 is Date.

References:

link link