How to get delete/Update row + SQL

JohnPayton

Sure, this is a snippet from a routine in VB6, but it would be easy to convert it to VB.Net. In this instance I'm recording the method of payment and amounts from each transaction. If the log.txt file does not exist then create it, otherwise append to it. You will notice this particular log file is called LogReturns.txt and it's in the root directory of the program executable. Hope this is what you are looking for.

On Error Resume Next
Dim fso As New Scripting.FileSystemObject
Dim mFile As String
Dim txtfile As Object
mFile = "/LogReturns.txt"
If fso.FileExists(App.Path & mFile) Then
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.OpenTextFile(App.Path & "/LogReturns.txt", ForAppending)
Else
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.CreateTextFile(App.Path & "/LogReturns.txt", True)
End If
Dim mVar As Integer
txtfile.WriteLine (" ")
txtfile.WriteLine ("Receipt Number : " & recReceipt.Fields("ReceiptNum"))
txtfile.WriteLine ("Member Number : " & recReceipt.Fields("MembNum"))
For mVar = 0 To intMoviesReturnCount - 1
txtfile.WriteLine ("Movie Number(s): " & arrMoviesReturned(mVar))
Next mVar
txtfile.WriteLine ("Rec Date & Time: " & recReceipt.Fields("ReceiptDateTime"))
txtfile.WriteLine ("Amount Payable : " & Format(recReceipt.Fields("Amount"), "$0.00"))
' v1.0.159 : 26-Jul-2006 : JPG : Added CashNett value to report
txtfile.WriteLine ("Cash Nett : " & Format(recReceipt.Fields("CashNett"), "$0.00"))
txtfile.WriteLine ("Cash Tendered : " & Format(recReceipt.Fields("CashTendered"), "$0.00"))
txtfile.WriteLine ("Eftpos Selected: " & Format(recReceipt.Fields("Eftpos"), "$0.00"))
txtfile.WriteLine ("Cheque Provided: " & Format(recReceipt.Fields("Cheque"), "$0.00"))
txtfile.WriteLine ("Credit Card : " & Format(recReceipt.Fields("CreditCard"), "$0.00"))
txtfile.WriteLine ("GiftCard Used : " & Format(recReceipt.Fields("GiftCardUsed"), "$0.00"))
txtfile.WriteLine ("Discount : " & Format(recReceipt.Fields("Discount"), "$0.00"))
txtfile.WriteLine ("Transfer : " & Format(recReceipt.Fields("DebitMemberBalance"), "$0.00"))

txtfile.WriteLine (" ")
txtfile.WriteLine ("\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*")
txtfile.Close
Set fso = Nothing

jojoba2011

thanks for ans. But i wanna to get the info from my SQL database . i wanna to have every thing the user changed,Updated,Inserted,Deleted. So i can trace that.

JohnPayton

Hi jojoba2011, You get the values you need to save from the front end application, what I mean to say is the values you are using in the database insert or update query are also used for the text report audit trail. In the case of the original values that are being replaced, they are stored as a variable when the update/insert page is first populated. Do you understand what I'm saying?

jojoba2011

first of all thanks for your attention! Sorry i cant understand! I think that u add values to txt when inserting them to Database. Correct? But i wanna to get it from DB.even if the text file deleted i can get info back.

Lost User

jojoba2011 wrote:

But isnt it possible to have when running update SP.

Its very important to do that .
if possible then its end of story.

No, not possible.

jojoba2011 wrote:

is it possible to have the OLD trace if the user delete the .trc file from SQL.

Yes, as I already suggested when I answered the post; embed a trace-file as an embedded resource. That way it will be compiled "into" your executable. ..but no, unless you're the administrator and have more rights on my machine than I do, I'll not only change the trace-file, I'll even make sure that there's fake data entered for your amusement.

Bastard Programmer from Hell :suss:

jojoba2011

thanks, your really active one. can you give me an example that how can i embed a trace-file as an embedded resource in C# in my app.???

Lost User

I could, but it's your work. There are enough examples on the internet, and they don't take more than a few lines of code. Good luck :)

Bastard Programmer from Hell :suss:

JohnPayton

Yes that's right, you keep the values you are inserting/updating/deleting then call a routine to record the changes in the ascii text audit file. :) Re Getting data from the DB, I'm sorry but I don't know how to retrieve data from SQLServer from past transactions, I'm sure it's possible from the transaction log. But in my circumstances it's been easier to just store the old values and new values when they happen to variables then send to the ascii log file.

jojoba2011

thanks! but i wanna to have that .how to retrieve data from SQLServer from past transactions

jojoba2011

Hi,Thanks i wanna to have something like "ApexSQL Log" but not that much big.

Lost User

I don't know the product, but I'm guessing it's a tool that saves both the old and the new values. You already have a table - you can easily insert new values without ever touching the old ones. It won't be "as big" as any external audit, since any external data-file would have more overhead. If your client needs to keep all historic values, then you simply don't update or delete.

Bastard Programmer from Hell :suss:

jojoba2011

hi dear. this is exact the tool that i wanna.this read .ldf(log) file of SQL and convert it to human Language.it exactly show the old values and new values both .i wanna to do this my self without buying it and i dont wanna that much info. See can u give me something like that > I know that u can do that cause ur expert in SQL.

Lost User

jojoba2011 wrote:

hi dear

I'd like to suggest a more neutral greeting.

jojoba2011 wrote:

See can u give me something like that

No, because the structure of the ldf-file isn't public. You'd still need to talk to Microsoft if you want to read the ldf-file. Or try and lull the company into handing you some help.

jojoba2011 wrote:

I know that u can do that cause ur expert in SQL.

Nope; I can only give some options -

You can do a real audit in Sql Server, not Sql Express
You can trace, but that doesn't show the "old" values
You can insert without update. Sounds wrong, but is often the best solution, since your table is the most optimized structure to hold that type of data.
You could create triggers to copy both old and new values to some other table, which would be the complex version of the point above, without too much added value.
You can change the datalayer of the code talking to your server, and have it log there.
Talk to Microsoft

Bastard Programmer from Hell :suss: