Writing to SQL DB from Macro

[#1]

Hi,  Im successfully updatin an SQL db from Macro. However, when I try to add a datestamp the write fails.

This is my write command:

sSQL="insert into qlikview_comments (id,user_id,indicator_number, comment,time_added)  values (newid(),'" & vuser  & "',12345,'" & vcomment  & "','" & vtime  & "')"

Any help appreciated.

Tom

[Anonymous]

Hi,

Could you give a sample of the result-string eSQL and also a correct sample you expect it to be ?

Paul

[jonasheisterkam]

Test it with the normal SQL tool to check the exact syntax of the type. Then show var in the macro via massagebox and modify the string until it match.

[#4]

Hi,

This is my test SQL string :

insert into qlikview_comments (id,user_id,indicator_number, comment,time_added)

values (newid(), 'User1',12345,'this is a test comment',getdate())

and this is my resultset :

id	user_id	indicator_number	time_added	comment
83CF78C7-EE27-4C25-940E-A3B5B5F4DFB6	User1	12345	2013-11-21 09:40:52.560	this is a test comment

The macro insert from the document works without the datetime .....

Hope this info helps,

Tom

[Anonymous]

Hi,

Thanks for the info, but could you also add the result you expedted ? I mean the string which will work with SQL.

I don't know how to use the SQL-tool to test (like Jonas mentioned), but perhaps you could test with QV and change the string vtime with a fixed string like f.ex. '2013-11-21 09:40:52.560' or '11-21-2013 09:40:52.560' or '11-21-2013 09:40' or .....

There are many possibilities, but I think it depends on the field 'time_added' and the time/date settings on the server.

Paul

[#6]

Hi Paul,

thanks for your reply. I've amended my Macro with these lines before the write statement:

vdate = "2013-11-21 09:40:52"

MsgBox(vdate)

which is showing the correct date,

My write statement now looks like this  :

sSQL="insert into qlikview_comments (id,user_id,indicator_number, comment,time_added)  values (newid(),'" & vuser  & "','" & vdate  & "',12345,'" & vcomment  & "')"

which is where the macro fails.  Im guessing that I need to convert vdate to a datetime variable but not sure how to do this....

Hope you can help,

Tom

[Anonymous]

Hi,

I suppose next lines will do :

vdate = (FormatDateTime(date(),2)  & " " & FormatDateTime(time() ,4) )

Msgbox vdate

But you have to know (test with trial) how the date and time has to look in the SQL statement.

In our SQL-DB I did not work with 'writing to SQL' but I used 'Stored Procedures' thru QV and I had to format the date as DD/MM/YYYY HH:NN

Paul

[#8]

Hi,

Your suggested lines did work....

do you have any advice/examples of calling stored procedures from QV?

Thanks again,

Tom

[Anonymous]

Hi,

Until now I only used raporting-procedures, no procedures who change data.

But here is a sample of what I used.

ODBC CONNECT32 TO SQL_ERP;

/* StockList */

StockList:

SQL EXEC [sql_DB_masterdata].[dbo].[pr_reporting_stocklist]

        @id_bu = 1,

        @id_warehouse = -1,

        @id_language = 2

;

DISCONNECT;

Hope you can use it, it gave me a boost when I found this in the forum.

PS: Could you share your SQL-writing-macro with us.

Paul

[#10]

Hi Thanks for this.

This is my macro for inserting to SQL DB  :

Sub Update_Comment

dim sServer, sConn, oConn,oRS

sServer="myserver"

sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=db_name"

Set oConn = CreateObject("ADODB.Connection")

oConn.Open sConn, "username", "password"

Set oRS =CreateObject("ADODB.Recordset")

sSQL="insert into db_table (field1)  values ("12345")"

ors.open sSQL, oconn

end sub

Many thanks,

Tom