Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

1 Solution

Accepted Solutions
Not applicable

Macro to insert data in database

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

12 Replies
Not applicable

Macro to insert data in database

Hi guys,

Anybody knows about?

Thanks,

Rodrigo.

Not applicable

Macro to insert data in database

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

Not applicable

Macro to insert data in database

Thanks my friend!

Not applicable

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!

Not applicable

Macro to insert data in database

Thanks Marcos!!

Not applicable

Macro to insert data in database

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

Not applicable

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.

Not applicable

Macro to insert data in database

Hi Rodrigo, the problem is that when I execute the select query I get no error but my dimension fng_timestamp is not updated in Qlikview with the new data from the database.

Thanks.

Obrigado.

Not applicable

Macro to insert data in database

Hi Marcos,

Ok... you want to load new data from database in qlikview......

I think that is possible... and I think that I have a code for this.

I'll try to find it in my projects at weekend and next week I'll post a new comment.

Abraço,

Rodrigo.

Community Browser