Problem writing Task Notes to Project2000/2003 mdb-file in VB.NET

[Hans Larsson]

Hi,

Can anybody help to convert from VB6/ADO to VB.NET/ADO.NET
This subprogram: WriteRtfTaskNotesToMSP writes a RTF-note(Only text/No
bitmaps) to The MSAcess table MSP_TASK in MSProject2000/2003
The field TASK_RTF_NOTES is of type OLEObj
What Is wrong in the VB.NET/ADO.NET version??


'VB6/ADO This works!
Public Sub WriteRtfTaskNotesToMSP(ByVal strThisNotes As String, ByVal
intThisTaskUniqueID As Integer)
'Created: 2000-03-28 to MSP2000/2003
Dim objMyConn As ADODB.Connection
Dim objMyCmd As ADODB.Command
Dim objMyParam As ADODB.Parameter
Dim strRTF As String
Dim rtf
' Trap any error/exception
On Error GoTo AdoError
' Technique # 1(Early Binding)
objMyConn = New ADODB.Connection
objMyConn.Open(gtxtDSNMSProj, gtxtUIDMSProj, gtxtPWDMSProj)
objMyConn.CursorLocation = adUseServer
'Technique #1(Early Binding)
objMyCmd = New ADODB.Command
objMyParam = New ADODB.Parameter
gstrSQL = "UPDATE MSP_TASKS "
gstrSQL = gstrSQL & "SET TASK_RTF_NOTES = ?, "
gstrSQL = gstrSQL & "TASK_HAS_NOTES = True "
gstrSQL = gstrSQL & "WHERE (MSP_TASKS.PROJ_ID = 1 "
gstrSQL = gstrSQL & "AND MSP_TASKS.TASK_UID = " &
Str(lngThisTaskUniqueID) & ");"
objMyParam.Direction = adParamInput
objMyParam.Type = adVarBinary
objMyParam.Size = Len(strRTF) + 100
objMyParam.Value = StrConv(strRTF, vbFromUnicode)
objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = gstrSQL
objMyCmd.Parameters.Append(objMyParam)
objMyCmd.Execute()
' milliseconds
Sleep(1000)
objMyCmd = Nothing
objMyConn.Close()
objMyConn = Nothing
Done:
Exit Sub
AdoError:
Call ADOErrorHandler(objMyConn, True, "WriteRtfTaskNotesToMSP/1")
GoTo Done
End Sub
*************
'VB:NET/ADO.NET This Does NOT work!
Public Sub WriteRtfTaskNotesToMSP(ByVal strThisNotes As String, ByVal
intThisTaskUniqueID As Integer)
Dim objConn As New OleDb.OleDbConnection(gstrConnMSProj)
Dim strRTF As String
Try
objConn.Open()
Dim objCmd As OleDb.OleDbCommand = objConn.CreateCommand
Dim objParam As New OleDb.OleDbParameter
strRTF = strThisNotes.Trim & ControlChars.Lf & Chr(0)
gstrSQL = "UPDATE MSP_TASKS "
gstrSQL = gstrSQL & "SET TASK_RTF_NOTES = ?, "
gstrSQL = gstrSQL & "TASK_HAS_NOTES = True "
gstrSQL = gstrSQL & "WHERE (MSP_TASKS.PROJ_ID = 1 "
gstrSQL = gstrSQL & "AND MSP_TASKS.TASK_UID = " &
intThisTaskUniqueID.ToString & ");"
objParam.Direction = ParameterDirection.Input
objParam.DbType = DbType.Binary
objParam.Size = strRTF.Length + 100
objParam.Value = Encoding.Default.GetBytes(strRTF)
objCmd.CommandText = gstrSQL
objCmd.Parameters.Add(objParam)
objCmd.ExecuteNonQuery()
Catch err As Exception
System.Windows.Forms.MessageBox.Show(err.ToString)
Finally
If objConn.State = ConnectionState.Open Then
objConn.Close()
End If
End Try
End Sub

Sincerely
Hans Larsson

 

[Rod Gill]

Have you read the pjdb.htm file that comes with Project in one of its
program folders? I has a section on reading and writing rtf notes.

 

[Hans Larsson]

Yes, BUT this contain only the VB/ADO solution! NOT the VB.NET/ADO.NET
solution!

Thank You!
Hans Larsson

 

[Rod Gill]

The code you show below is not how the example in the prjdb.htm file goes
for write to db. Using strconv works to read, but not write according to the
file. Strconv is only used for read.

 

[Hans Larsson]

Hi,

You're too fast.....My Function for writing RTF-notes in VB6/ADO works and
do include strconv as in the prjdb.htm-file....but thanks to your hint about
the prjdb.htm
I have now found the solution for VB.NET by putting a string1 before
strMyNotes and a string2 after. Both strings contains some rtf-syntax from
the writeRtf-sample in prjdb.htm
I do not understand the syntax but it works!
Here is the solution for VB.NET/ADO.NET
Imports System.Text
Public Sub WriteRtfTaskNotesToMSP(ByVal strThisNotes As String, ByVal
intThisTaskUniqueID As Integer)
Dim objConn As New OleDb.OleDbConnection(gstrConnMSProj)
Dim strRTF As String
Try
objConn.Open()
Dim objCmd As OleDb.OleDbCommand = objConn.CreateCommand
Dim objParam As New OleDb.OleDbParameter
Dim strTemp1 As String =
"{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0
Arial;}}" & ControlChars.NewLine & _
"\viewkind4\uc1\pard\f0\fs20 "
Dim strTemp2 As String = "\par" & ControlChars.NewLine & "}" &
ControlChars.NewLine & ControlChars.Lf & Chr(0)
strRTF = strTemp1 & strThisNotes & strTemp2
gstrSQL = "UPDATE MSP_TASKS "
gstrSQL = gstrSQL & "SET TASK_RTF_NOTES = ?, "
gstrSQL = gstrSQL & "TASK_HAS_NOTES = True "
gstrSQL = gstrSQL & "WHERE (MSP_TASKS.PROJ_ID = 1 "
gstrSQL = gstrSQL & "AND MSP_TASKS.TASK_UID = " &
intThisTaskUniqueID.ToString & ");"
objParam.Direction = ParameterDirection.Input
objParam.DbType = DbType.Binary
objParam.Size = strRTF.Length + 100
objParam.Value = Encoding.Default.GetBytes(strRTF)
objCmd.CommandText = gstrSQL
objCmd.Parameters.Add(objParam)
objCmd.ExecuteNonQuery()
Catch err As Exception
System.Windows.Forms.MessageBox.Show(err.ToString)
Finally
If objConn.State = ConnectionState.Open Then
objConn.Close()
End If
End Try
End Sub