Write Back to database

[#1]

I have searched the site and cant seem to find very much information on writing back to databases. There are a few posts but the attachments are not there and I cant reply. Does any one know where I can find documentation on macros for writing to databases and maybe some examples of how this can be accomplished?

[#2]

does msgbox cstr(barcode) work?

[sukydhak]

No i get Type mismatch: 'cstr'

Ps i don't now the ABC of VB

[#4]

I had to populate the fields before I could write them. this is What I ended up with.

sub writeback ()
dim sServer, sConn, oConn,oRS
dim sSubcodeSelected, sActionSelected, sCommentSelected
dim sSubcode, sComments, sActions
dim sSubcodemsg, sCommentsmsg, sActionsmsg

sServer="xxxxxxxxxx"
sConn="provider=sqloledb;data source=" & sServer
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "xxxxxxxxxx", "xxxxxxxx"
Set oRS =CreateObject("ADODB.Recordset")

' Get selected Value
set sSubcodeValues = ActiveDocument.Fields("Subcode").GetPossibleValues
Set sCommentsValues = ActiveDocument.Variables("Comments2")
Set sActionsValues = ActiveDocument.Variables("Actions2")

sSubcodemsg ="([Subcode]='"& sSubcodeValues.item(0).text & "')"
sSubcode = sSubcodeValues.item(0).text
sCommentsmsg = sCommentsValues.GetContent.String
sComments = sCommentsValues.GetContent.String
sActionsmsg = sActionsValues.GetContent.String
sActions = sActionsValues.GetContent.String

'msgbox sSubcodemsg
'msgbox sCommentsmsg
'msgbox sActionsmsg

sSQL="insert into TroyAgentReviews.dbo.AgentReviewNotes(AgentSub,Comments,Actions,Year) " _
& "values('" & sSubcode & "','" & sComments & "','" & sActions & "','2009')"
oRS.open(sSQL), oConn
oConn.close
Set oConn = nothing
end sub

[#5]

The attached adds comments or deletes comment to an excel file. This may or may not assist you.

Open the zip file and place the files in a folder named "C:\QlikViewExcel".

hth,

Stephen

[sukydhak]

One step forward and i found another issue.

sub writeback ()
dim sServer, sConn, oConn,oRS
sServer="localhost"
sConn="provider=sqloledb;data source=" & sServer
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "sa", "q1w2zaxs"
Set oRS =CreateObject("ADODB.Recordset")

set table = ActiveDocument.GetSheetObject("TB01")
For RowIter = 0 to table.GetRowCount-1
'msgbox i & ": " & ID(i)
set ID = table.GetCell(RowIter,0)
'ActiveDocument.Fields("Barcode").GetPossibleValues
set Barcode = table.GetCell(RowIter,1)
set Partnercode = table.GetCell(RowIter,2)
' msgbox RowIter
' msgbox Barcode.Text
' out sqlstr
' wscript.echo ID
' sqlstr="insert into bis.dbo.Tbarcode_lookup (ID,Barcode,Partnercode) values('" & ID(i) & "','" & Barcode & "','" & Partnercode & "')"
sqlstr="insert into bis.dbo.Tbarcode_lookup (ID,Barcode,Partnercode) values('" & RowIter & "','" & Barcode.Text & "','" & Partnercode.Text & "')"
oRS.open sqlstr, oConn
Set oRS = oConn.Execute(sqlstr)
next

end sub

works now but it's writing 3 times the number of records.

[sukydhak]

Guys

A quick thanks to all

The working script is below.

sub writeback ()
dim sServer, sConn, oConn,oRS
sServer="localhost"
sConn="provider=sqloledb;data source=" & sServer
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "sa", "q1w2zaxs"
Set oRS =CreateObject("ADODB.Recordset")

set table = ActiveDocument.GetSheetObject("TB01")
For RowIter = 0 to table.GetRowCount-1
set ID = table.GetCell(RowIter,0)
set Barcode = table.GetCell(RowIter,1)
set Partnercode = table.GetCell(RowIter,2)
' msgbox RowIter
' msgbox Barcode.Text
sqlstr="insert into bis.dbo.Tbarcode_lookup (ID,Barcode,Partnercode) values('" & RowIter & "','" & Barcode.Text & "','" & Partnercode.Text & "')"
oRS.open sqlstr, oConn
'Set oRS = oConn.Execute(sqlstr)
next

end sub

Note that my barcode and partnercode feilds are varchar and ID is a number.

Thanks once again.

[#8]

I have this script :

but I'm using SQL Server locally, using Windows authentication-with no username or password.

How should I alter my 'oConn.Open sConn, "sa", "khgkhgghg"', as mine should probably empty.

My script is :

sub writeback ()

          dim sServer, sConn, oConn,oRS

          sServer="localhost"

          sConn="provider=sqloledb;data source=" & sServer

          Set oConn = CreateObject("ADODB.Connection")

          oConn.Open sConn, "sa", "khgkhgghg"

          Set oRS =CreateObject("ADODB.Recordset")

          set table = ActiveDocument.GetSheetObject("TB04")

          For RowIter = 0 to table.GetRowCount-1

          set RowID = table.GetCell(RowIter,0)

          set Name = table.GetCell(RowIter,1)

          set Grade = table.GetCell(RowIter,2)

          'set Working = table.GetCell(RowIter,3)

          'set [Date] = table.GetCell(RowIter,4)

          'set Code = table.GetCell(RowIter,5)

          ' msgbox RowIter

          ' msgbox Barcode.Text

          sqlstr="insert into Test.dbo.working (RowID,Name,Grade) values('" & RowID & "','" & Name.Text & "','" & Grade.Text & "')"  ',Working,Date,Code

          oRS.open sqlstr, oConn

          'Set oRS = oConn.Execute(sqlstr)

          next

end sub

Any ideas?