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]

here's an example of a vbscript writing to sql server.

dim sServer, sConn, oConn,oRS
sServer="sqlservername"
sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=logoninfo"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "username", "secretpassword"
Set oRS =CreateObject("ADODB.Recordset")
sSQL="insert into logonoff(username,compname,ip,ontime) values('" & sUserName & "','" & sComputerName & "','" & sIP & "',getdate())"
ors.open sSQL, oconn

[#3]

Is Logonoff the table name? Also, what I want the user to be able to do is enter text into an input box and write that information to the database via ajax. Any ideas on how to make that work. The user is evaluating Agents and wants to document the actions to take as well as comments. I want that to be written out to a SQL database. Will that work with the code listed here. How can I create the input boxes. At this point, I can only have an in input box for system created variables.

I want to pass four data elements to the data base.

Agent

Comments

Actions

Year

[#4]

I was getting an error that I couldnt connect. everything is connecting fine and it appears to be working only it is not writing to the database. Am I missing anything here?

sub writeback ()
dim sServer, sConn, oConn,oRS
sServer="XXXXXXXXX"
sConn="provider=sqloledb;data source=" & sServer
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "XXXXXXXX", "XXXXXXX"
Set oRS =CreateObject("ADODB.Recordset")
sSQL="insert into TroyAgentReviews.dbo.AgentReviewNotes(AgentSub,Comments,Actions,Year) values('" & Subcode & "','" & Comments & "','" & Actions & "','2009')"
oRS.open sSQL, oConn
'Set oRS = oConn.Execute(sSQL)
end sub

[#5]

the line that does the actual writing is commented:

'Set oRS = oConn.Execute(sSQL)

[#6]

It writes with or without that particular line. I have been able to write to the database although, it only passes 2009 as the year. The other fields are blank. why isnt it passing the other data?

[#7]

your insert statement is hard-coded to always write 2009 in the year field

sSQL="insert into TroyAgentReviews.dbo.AgentReviewNotes(AgentSub,Comments,Actions,Year) values('" & Subcode & "','" & Comments & "','" & Actions & "','2009')" <----

[#8]

It is writing that as I have it hard-coded. It is not writing what is populated in the Subcode, Comments or Actions fields.

here is my sql table after I run the macro

Subcode Comments Actions Year

2009

I have one row of data in each of these fields and they are not being written to the database: Subcode, Comments, Actions. Do I need to use a set statement like

Set Subcode = ActiveDocument.Fields("Subcode").GetPossibleValues

?

[#9]

thoneycutt wrote:
It is writing that as I have it hard-coded. It is not writing what is populated in the Subcode, Comments or Actions fields.
here is my sql table after I run the macro
Subcode Comments Actions Year
 2009
I have one row of data in each of these fields and they are not being written to the database: Subcode, Comments, Actions. Do I need to use a set statement like
Set Subcode = ActiveDocument.Fields("Subcode").GetPossibleValues
?<div></div>

sorry, I misunderstood your question.

Yes, you are correct, you need to get the values from qV before you send them to SQL.

[sukydhak]

Help

Just i'm also having the same 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", ""
Set oRS =CreateObject("ADODB.Recordset")

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


Error message Type mismatch
Msgbox i does provide me the record number but my msgbox barcode fails.

if i remove the message box for barcode, i can insert my one hardcoded value above. Any ideas would i'm doing wrong.