Insert Macros

[noman212]

Hi Friends,

I want to write macro which takes a value from Variable and insert into the table .

i wrote macro but it doesn't work.

___________________________________________________________________

sub oracle

set db = CreateObject("ADODB.Connection")

db.open "DSN=xyz_server;UID=user;PWD=user"

set Orderfile = db.execute("INSERT INTO capture (Current_date,FNA,TIFF) values ('$(UserID)','$(Password)','$(Full Name)');")

set Orderfile = db.execute("COMMIT;")

db.Close

end sub

_____________________________________________________________________

UserID,Password,Full Name/ These are variable here which i used in Dasbhboard.

Please help. how can i resolve this issue.

[Anonymous]

Hi

First you need to get the variable in Macro (dashboard variables will not work directly here)

ex. Password= ActiveDocument.GetVariable("Password").GetContent.String

see below Macro . let me know if you need any more help on this.

Sub Insert_Holiday

Dim objADO

Dim objRs

Dim strSQL

Dim vTC_ID

dim vTC_IDs

dim a

dim x

dim j

DIM vHoliday1

DIm vHolidayCount1

vHoliday1=ActiveDocument.GetVariable("vHoliday").GetContent.String

vHolidayCount1=ActiveDocument.GetVariable("vHolidayCount").GetContent.String

if vHolidayCount1=1 then

intAnswer = _

    Msgbox("Do you want to insert the Holiday?", _

        vbYesNo, "Insert Holiday")

If intAnswer = vbYes Then

Set objADO = CreateObject("ADODB.Connection")

Set objRS = CreateObject("ADODB.Recordset")

objADO.Open "Provider=SQLOLEDB.1;Data Source=ABC;User ID=A;Password=X;"

Set objRS=objADO.Execute ("select count(*)from .[dbo].[Table1] where Holiday = '"&vHoliday1&"'" )

'Msgbox(objRS(0))

j = objRS(0).Value

if j>0 Then

Msgbox(" This Holiday already preasent in the list" )

Else

strSQL="INSERT INTO .[dbo].[Table1] (Holiday) values ('"&vHoliday1&"')"

Msgbox("Holiday Inserted" )

'end if

objADO.BeginTrans

objRS.Close

objADO.Execute strSQL, iAffected

objADO.CommitTrans

objADO.Close

end if

Else

Msgbox ("Holiday Not Inserted" )

End If

ELSE

Msgbox ("Please Select a Holiday Date!" )

END IF

End Sub