Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Database Writeback Help

I've read quite a number of posts in regard to writing back to the database and had it working when I hard-coded the values but when I changed to use variables, it does not work.  When I try to run the code below using the variables, I get a "No update permissions!" error (exactly as the code says - just not sure what, exactly, I am doing wrong.

Details:

Desktop client only (no web deployment planned), write back to database (initially insert, but later edit & delete options as well).

The Macro is as follows (the real code has an actual server listed in the Data Source= portion, left it out here):

sub DataInsert

     set conn=CreateObject("ADODB.Connection")

     conn.Open "Provider='sqloledb';Data Source=<MyServer>;Initial Catalog='Rally';Integrated Security='SSPI';"

     set rs=CreateObject("ADODB.recordset")

' Database Update

     sql="INSERT INTO mw_TestTable VALUES ('" & vTestChar & "', " & vTestInt & ", '" & vTestDate & "')"

     on error resume next

     conn.Execute sql

     if err <> 0 then

          msgbox("No update permissions! " & err )

     end if

     conn.close

end sub

Thanks for any help you can provide.

Mark.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Where the variables defined? You cannot use QV variables directly in a macro. You need syntax like this:

set v = ActiveDocument.Variables("vTestChar ")

vTestChar  = v.GetContent.String

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

20 Replies
sinanozdemir
Specialist III
Specialist III

Hi Mark,

Try it by writing your variables like the below:

'$(vTestChar)' and follow the same format for other variables.

Thanks

Not applicable
Author

Thanks Sinan.

When I tried using that format I can no longer test the code and get an error, it says "Invalid character".

Mark.

sinanozdemir
Specialist III
Specialist III

You may need to use chr(39) instead of single quote.

Not applicable
Author

Changed to use chr(39):

sql="INSERT INTO mw_TestTable VALUES (" & chr(39) & vTestChar  & chr(39) & ", " & vTestInt & ", " & chr(39) & vTestDate & chr(39) & ")"

Same error as when I used the single quote.

sinanozdemir
Specialist III
Specialist III

Sorry Mark. I am on a computer, but can you just avoid using double quotes as well?

Can you try the below:

sql_text = 'INSERT INTO mw_TestTable VALUES (' & chr(39) & '$(vTestChar)' & ',' & chr(39) & '$(vTestInt)' & chr(39) & ',' & chr(39) & '$(vTestDate)' & chr(30) & ')';

Thanks

Not applicable
Author

Thank you for your continued help.  The line returns a syntax error.

Not applicable
Author

I believe the error is because a single quote designates commented out code and so by using single quotes, I've effectively commented out the line.

sinanozdemir
Specialist III
Specialist III

Let's try one more time:

sql_text = "INSERT INTO mw_TestTable VALUES (" & vTestChar & "," & vTestInt & "," & vTestDate & ")";

didn't realize you were writing this in VB.

This is not going to work either. I will get back to you.

Not applicable
Author

Still getting the same error.