Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a problem with my vbscript.
I want to create a button to make dynamic update. my code is this
sub PullData
dim sServer, sConn, oConn,oRS,counter
sServer="Ixxxxxxx"
sConn="Provider=SQLNCLI10;Initial Catalog=DynamicUpdate;Data Source=Ixxxxxxxxx"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "xxxxx", "xxxxxxx"
Set oRS =CreateObject("ADODB.Recordset")
sSQL = "select TotalSales from Customer"
set oRS = oConn.Execute(sSQL)
while not oRS.EOF
counter=counter+1
value = oRS("TotalSales")
'msgbox(value)
'msgbox(counter)
set possibleValues = ActiveDocument.Fields("CustomerId").GetPossibleValues
NoSelected=possibleValues.Count
msgbox(counter)
msgbox(NoSelected)
if counter<=NoSelected then
query="UPDATE Customer SET TotalSales='"&value & "' WHERE CustomerId='"&counter & "'"
msgbox(query)
set sqlerror=ActiveDocument.DynamicUpdateCommand(query)
if (sqlerror = false) then
MsgBox sqlerror.ErrorMessage
exit sub
end if
else
set curDoc = ActiveDocument.GetProperties
curDoc.EnableDynamicDataUpdate=true
ActiveDocument.SetProperties curDoc
msgbox("Insert")
query1="INSERT INTO Customer VALUES ('"&counter & "','"&value & "')"
end if
oRS.movenext
wend
Set oRS = Nothing
Set oConn = nothing
'oRS.Close
'oConn.Close
ActiveDocument.Save
end sub
My update query works fine but the insert query does nothing. I check the else statement with msgbox("Insert") and it walk through of it but the insert query makes nothing.
Has anyone any idea?
Hi,
Try this script, hope you missed the code in Bold below
sub PullData
dim sServer, sConn, oConn,oRS,counter
sServer="Ixxxxxxx"
sConn="Provider=SQLNCLI10;Initial Catalog=DynamicUpdate;Data Source=Ixxxxxxxxx"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "xxxxx", "xxxxxxx"
Set oRS =CreateObject("ADODB.Recordset")
sSQL = "select TotalSales from Customer"
set oRS = oConn.Execute(sSQL)
while not oRS.EOF
counter=counter+1
value = oRS("TotalSales")
'msgbox(value)
'msgbox(counter)
set possibleValues = ActiveDocument.Fields("CustomerId").GetPossibleValues
NoSelected=possibleValues.Count
msgbox(counter)
msgbox(NoSelected)
if counter<=NoSelected then
query="UPDATE Customer SET TotalSales='"&value & "' WHERE CustomerId='"&counter & "'"
msgbox(query)
set sqlerror=ActiveDocument.DynamicUpdateCommand(query)
if (sqlerror = false) then
MsgBox sqlerror.ErrorMessage
exit sub
end if
else
set curDoc = ActiveDocument.GetProperties
curDoc.EnableDynamicDataUpdate=true
ActiveDocument.SetProperties curDoc
msgbox("Insert")
query1="INSERT INTO Customer VALUES ('"&counter & "','"&value & "')"
msgbox(query1)
set sqlerror=ActiveDocument.DynamicUpdateCommand(query1)
if (sqlerror = false) then
MsgBox sqlerror.ErrorMessage
exit sub
end if
end if
oRS.movenext
wend
Set oRS = Nothing
Set oConn = nothing
'oRS.Close
'oConn.Close
ActiveDocument.Save
end sub
Regards,
Jagan.
Hi,
Get the Insert query generated and try manually in database to check whether it is working.
Use
msgbox(query1);
Regards,
Jagan.
Thanks for the response but I don't understand what you mean.Please could you give me an example?
Hi,
Try this script, hope you missed the code in Bold below
sub PullData
dim sServer, sConn, oConn,oRS,counter
sServer="Ixxxxxxx"
sConn="Provider=SQLNCLI10;Initial Catalog=DynamicUpdate;Data Source=Ixxxxxxxxx"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "xxxxx", "xxxxxxx"
Set oRS =CreateObject("ADODB.Recordset")
sSQL = "select TotalSales from Customer"
set oRS = oConn.Execute(sSQL)
while not oRS.EOF
counter=counter+1
value = oRS("TotalSales")
'msgbox(value)
'msgbox(counter)
set possibleValues = ActiveDocument.Fields("CustomerId").GetPossibleValues
NoSelected=possibleValues.Count
msgbox(counter)
msgbox(NoSelected)
if counter<=NoSelected then
query="UPDATE Customer SET TotalSales='"&value & "' WHERE CustomerId='"&counter & "'"
msgbox(query)
set sqlerror=ActiveDocument.DynamicUpdateCommand(query)
if (sqlerror = false) then
MsgBox sqlerror.ErrorMessage
exit sub
end if
else
set curDoc = ActiveDocument.GetProperties
curDoc.EnableDynamicDataUpdate=true
ActiveDocument.SetProperties curDoc
msgbox("Insert")
query1="INSERT INTO Customer VALUES ('"&counter & "','"&value & "')"
msgbox(query1)
set sqlerror=ActiveDocument.DynamicUpdateCommand(query1)
if (sqlerror = false) then
MsgBox sqlerror.ErrorMessage
exit sub
end if
end if
oRS.movenext
wend
Set oRS = Nothing
Set oConn = nothing
'oRS.Close
'oConn.Close
ActiveDocument.Save
end sub
Regards,
Jagan.
Thnx Jagan that works perfect......thnx a lot
Hello jagan,
I have a query, need to update data from QV to SQl... i dont know anything about this.. how should i start this..
Please help..:(