m3mdicl
asked on
Update Rows Automatically Based on another Table
Hi Everyone!
Here's the scenario. I have an existing SQL SERVER 2005 Database using a C#.Net App Frontend. The system has been running 24/7 for 2 years now. But the development team has been halted and there's no way to update the source code. Now, there's a change we want to make on the database so we decided to make an app in VB6, which is I'm comfortable with.
We want to add Character without space columns in one table because we need it. The column on the existing database is Character with space only.
Now what I did was create another table and put the IDs and the CharWithoutSpace, then by joining the two tables we can get what we want.
Here's the code of the Linecout Updater:
NOTE:
DocumentDetailID = Primary Key
DocumentID = int
FinalDocument = Binary (Word File)
The code simply loop all rows for DocumentDetailID then open corresponding Binary file using word object, getting the correct char with no spaces linecount then inserting it on VBCTable.
I called it VBCTable since VBC stands for Visible Black Characters. It's also synonymous to Character without spaces.
Now my problem is I want to create an app that will automatically update all values in real time.
Can somebody help me. I already started the code can someone modify it so that it will update values automatically. Use like a timer like update every 30 minutes something like that. In the end this VB APP will run 24/7 also.
Is this the right approach to this or is there any other simple way to do this like script or something.
Any help is greatly appreciated.
Here's the scenario. I have an existing SQL SERVER 2005 Database using a C#.Net App Frontend. The system has been running 24/7 for 2 years now. But the development team has been halted and there's no way to update the source code. Now, there's a change we want to make on the database so we decided to make an app in VB6, which is I'm comfortable with.
We want to add Character without space columns in one table because we need it. The column on the existing database is Character with space only.
Now what I did was create another table and put the IDs and the CharWithoutSpace, then by joining the two tables we can get what we want.
Here's the code of the Linecout Updater:
Private Sub Command1_Click()
Dim stm As ADODB.Stream
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
Dim objWord As Word.Application
Dim VBC As String
Dim SQL As String
Dim SQL2 As String
Dim SQL3 As String
Dim x As Long
Set objWord = New Word.Application
Call Open_Connection
SQL = "SELECT DocumentDetailID FROM DocumentDetail"
Set rs = New ADODB.Recordset
rs.Open SQL, con, adOpenKeyset, adLockOptimistic
Set stm = New ADODB.Stream
stm.Type = adTypeBinary
stm.Open
For x = 1 To rs.RecordCount
SQL3 = "SELECT DocumentDetailID, DocumentID, FinalDocument FROM DocumentDetail " & _
"WHERE DocumentDetailID = " & rs.Fields("DocumentDetailID")
Set rs3 = New ADODB.Recordset
rs3.Open SQL3, con, adOpenKeyset, adLockOptimistic
With stm
.Write rs3.Fields("FinalDocument").Value
.SaveToFile App.Path & "\Temp.doc", adSaveCreateOverWrite
End With
objWord.Documents.Open App.Path & "\Temp.doc"
VBC = objWord.ActiveDocument.ComputeStatistics(wdStatisticCharacters)
SQL2 = "INSERT INTO VBCTable (DocumentDetailID, DocumentID, VBC) VALUES ('" & rs3.Fields("DocumentDetailID").Value & "', '" & rs3.Fields("DocumentID").Value & "', '" & VBC & "')"
Set rs2 = con.Execute(SQL2)
Set rs2 = Nothing
objWord.ActiveDocument.Close False
Set rs3 = Nothing
Label1.Caption = x & " of " & rs.RecordCount
toMoveNext:
rs.MoveNext
Next
objWord.Quit
rs.Close
Set rs = Nothing
con.Close
End Sub
NOTE:
DocumentDetailID = Primary Key
DocumentID = int
FinalDocument = Binary (Word File)
The code simply loop all rows for DocumentDetailID then open corresponding Binary file using word object, getting the correct char with no spaces linecount then inserting it on VBCTable.
I called it VBCTable since VBC stands for Visible Black Characters. It's also synonymous to Character without spaces.
Now my problem is I want to create an app that will automatically update all values in real time.
Can somebody help me. I already started the code can someone modify it so that it will update values automatically. Use like a timer like update every 30 minutes something like that. In the end this VB APP will run 24/7 also.
Is this the right approach to this or is there any other simple way to do this like script or something.
Any help is greatly appreciated.
ASKER
Ok then how do I add the characters without space on the new table?
The only source to get that characters without space is that FinalDocument column, which is a binary (word file). AFAIK, you cannot convert that binary or even count its characters using LEN. I've already tried using SQL alone to convert it but to no avail.
Is there a way to convert the BLOB without the use of a VB app?
The only source to get that characters without space is that FinalDocument column, which is a binary (word file). AFAIK, you cannot convert that binary or even count its characters using LEN. I've already tried using SQL alone to convert it but to no avail.
Is there a way to convert the BLOB without the use of a VB app?
ASKER
Hi I would like to change the question if it's quite complicated.
How can I make 2 tables have the same unique IDs and count.
For Example:
Assuming the col Linecount on Table B is the actual linecount of the binary (word file ) from Table A.
Table A has UniqueID, DocumentID and BinaryDoc colums.
Table B has UniqueID, DocumentID and Linecount colums.
Now what SQL should i use to make it efficient in replicating both tables?
Say for example Table A has 100 count and Table B has 90 count. How do I update those 10 remaining rows in Table B to match Table A?
How can I make 2 tables have the same unique IDs and count.
For Example:
Assuming the col Linecount on Table B is the actual linecount of the binary (word file ) from Table A.
Table A has UniqueID, DocumentID and BinaryDoc colums.
Table B has UniqueID, DocumentID and Linecount colums.
Now what SQL should i use to make it efficient in replicating both tables?
Say for example Table A has 100 count and Table B has 90 count. How do I update those 10 remaining rows in Table B to match Table A?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No other comments have helped me with my problem. I figured it out myself.
Greg