Macro to insert data in database

[#1]

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.

[#2]

Hi guys,

Anybody knows about?

Thanks,

Rodrigo.

[#3 · Accepted solution]

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

[#4]

Thanks my friend!

[#5]

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!

[#6]

Thanks Marcos!!

[#7]

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

[#8]

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.

[#9]

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.

[#10]

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.