12 Replies Latest reply: Jul 15, 2014 3:17 AM by Javier Riera RSS

    Macro to insert data in database

      Hi guys,

       

      I need to use a macro to insert just one value in a SQL Server Database.

      I'm trying, but I think that my code is something wrong...

       

      What's the best way to do that?

       

      Exemple: insert into test (name) values ('qlikview')

       

      Which code I have to use?

      Could you send me a exemple or a qlik file that explain what to do?

       

       

      Thank you so much.

      Regards,

      Rodrigo Almeida.

        • Macro to insert data in database

          Hi guys,

           

          Anybody knows about?

           

          Thanks,

          Rodrigo.

          • Macro to insert data in database
            martial mazeleigue

            hi,

             

            try with this code in VBscript

             

             

            dim sConn, oConn

            Set oConn = CreateObject("ADODB.Connection")

            sConn = "Provider=MSDAORA;User ID="yourlogin";Password="yourpassword";Data Source="your data source";OLEDB.NET=true;SPPrmsLOB=true;Persist Security Info=False;"

            oConn.Open sConn

            sSQL="your request"

            oConn.Execute(sSQL)

            oConn.Close

            Set oConn = Nothing

              • Macro to insert data in database

                Thanks my friend!

                  • Macro to insert data in database

                    Hi, I have used this code to do the opposite,to load new data from the Database in qlikview using a Macro (cos I want to use variables in the conditions), and I get no errors but my dimension fng_timestamp is not updated with the new data.

                    You can find the code Im using as follows, can you please help me?Thanks a lot!

                     

                    sub con3

                    dim sConn, oConn

                     

                    Set oConn = CreateObject("ADODB.Connection")

                    sConn = "Provider=OraOLEDB.Oracle.1;User ID=Moneta_usage;Password=Moneta_usage;Data Source=orcl;OLEDB.NET=true;SPPrmsLOB=true;Persist Security Info=False;"

                     

                    oConn.Open sConn

                    sSQL="Select fng_timestamp as time FROM ""MONETA_USAGE"".""SW_NATIONAL2011"""

                    oConn.Execute(sSQL)

                    oConn.Close

                     

                    Set oConn = Nothing

                    end sub

                     

                     

                    Thanks a lot!

                      • Macro to insert data in database

                        Thanks Marcos!!

                          • Macro to insert data in database

                            Hi Rodrigo, could you help me to solve my problem? Thanks a lot! Gracias tio!

                              • Re: Macro to insert data in database

                                Hola Marcos,

                                 

                                Desculpame, yo no he mirado su pregunta.

                                 

                                Yo estoy usando el mismo codigo pero con la string del SQL en una variable.

                                 

                                Sigue mi codigo:

                                 

                                sub Gravar_DB

                                       'BASE DE DADOS

                                       set conn = CreateObject("ADODB.Connection")

                                       conn.open "Provider=sqloledb.1;Data Source=YOURSERVER;Initial Catalog=YOURDATABASE;User Id=USER;Password=PASS"

                                      

                                       operacaoInsertOrUpdate = ActiveDocument.Variables("vIns_Or_Upd").GetContent().String

                                       if operacaoInsertOrUpdate = "UPDATE" then

                                             StatementSqlToDatabase = ActiveDocument.Variables("vUpdate_SQL").GetContent().String

                                       else

                                             if operacaoInsertOrUpdate = "INSERT" then

                                              StatementSqlToDatabase = ActiveDocument.Variables("vInsert_SQL").GetContent().String

                                             end if

                                       end if

                                       'msgbox("StatementSqlToDatabase: " & StatementSqlToDatabase)

                                       if StatementSqlToDatabase <> "" then

                                             on error resume next

                                             conn.Execute StatementSqlToDatabase

                                       if err <> 0 then

                                             msgbox("Ocorreu um erro na actualização do registo! " & err )

                                             ActiveDocument.Variables("vInsertUpdate_Activo").SetContent "1", true

                                             ActiveDocument.Variables("vGravarBD").SetContent "Erro", true

                                       else

                                             msgbox("Registo efectuado com sucesso!")

                                             ActiveDocument.Variables("vIns_Or_Upd").SetContent "", true    

                                             ActiveDocument.Variables("vGravarBD").SetContent "Sucesso", true                  

                                             'call PCComercial_Inicializa_Valores

                                      

                                             'Reload

                                             'ActiveDocument.Reload

                                       end if                    

                                       conn.close                       

                                       else  

                                             msgbox("Ocorreu um erro imprevisto!")

                                             ActiveDocument.Variables("vGravarBD").SetContent "Erro", true

                                             ActiveDocument.Variables("vInsertUpdate_Activo").SetContent "1", true                        

                                       end if

                                       'BASE DE DADOS

                                end sub

                                 

                                Las variables vUpdate_SQL o vInsert_SQL contiene un scrip para SQL Server.

                                Uso uno y otra para actualizar o insertar registros.

                                No lo sé como anda solamente un SELECT...

                                Pero eso codigo anda perfectamente in mi aplicacion.

                                 

                                Abrazo,

                                Rodrigo.

                        • Macro to insert data in database

                          Hi,

                          Is it possible to help me on this.

                          I have a report where my source is a directory of log files and it loads the files incrementally to a qvd.

                          and I also have a ODBC connection as follow:

                          ODBC CONNECT TO EQUIPMENT (XUserId is fYRJFKC, XPassword is VMCHLXL);

                           

                          is it possible to send the data from the qvd to SQL table

                          the table name is PhoneReport and it has the same fields as the qvd.

                          Thxs

                          • Re: Macro to insert data in database
                            Javier Riera

                            Hello,

                             

                            I am having an issue with this code. It is working using the QV Desktop but when I publish it in the QV Server and try to do the same (QV 11R2 - IE Plugil - QV Web Server).. it raise a security error. I have checkd in the server, and the option to run unsafe macro is checked.

                             

                            Any clue?

                            Thnxs

                            Javier