cjrancur

Write to SQL server using OleDb based upon selected row in SQL rowset

Discussion created by cjrancur on Nov 11, 2009
Latest reply on Nov 12, 2009 by pcombellick

A SQL database contains lab data that will be sent to PI.  I have worked out how to sent the data to PI.  Now, after the data has been sent to PI, I want to doublecheck that it is there in PI, and then use an OleDB transaction to write a date in a "DatePosted" field of the SQL table.  I also want to increment an integer transaction count in a "PITransferCount" field of the SQL server.

 

After each row of the SQL database has been selected and sent to PI, then I want to write back these values to SQL.  I also want to be safe and prevent SQL injection attacks.  I need some tips on doing that.  Microsoft says to use parameterized SQL, but I wonder if I can just check the Now value for a db date type before setting the myrow.item("DatePosted") to Now, and check the "PItransferCount" value for a db integer.  Isn't that the benefit that Microsoft wants to see with the parameterized SQL?  Or, if I go ahead and use parameterized SQL, is there any way to easily point to the row with the same keys as the selected row for the update statement?  This SQL table has 9 keys, so parameterizing the full thing using where clauses against the keys to identify the selected row will be tedious. Is there anyway to point to the row easily? Can you provide some vb.net code samples?  I found a link on Microsoft for updating rowsets, but it was in C++, and I'm not confident that I can translate to vb.net without mistakes, plus I don't understand the bookmark concept.  Tell me if there's an easier way.  I don't mind ignoring my link below.

 

http://msdn.microsoft.com/en-us/library/xwdy8hwx(VS.71).aspx

 

Currently my code for updating the PITransferCount without SQL injection precautions looks like this.  How would I incorporate SQL injection protection, and create an update command?  In this case, TestResultsRow is a single row returned into a datatable using a dataadapter select command followed by a loop like this:

dim testresultsrow as datarow
for each TestResultsRow in mydatatable.rows then

next

 

Here's the code I've built so far to set the value of the selected row's "PITransferCount".  Now that I have my own row in vb.net updated, how do I transfer that same information to the SQL table. How do I make sure that I update only the selected row in the SQL database, and that I do it in a way that prevents SQL injection attacks?

If Not IsDBNull(TestResultsRow.Item("PITransferCount")) AndAlso CInt(TestResultsRow.Item("PITransferCount")) >= 0 Then
    TestResultsRow.Item("PITransferCount") = CInt(TestResultsRow.Item("PITransferCount")) + 1
Else
    TestResultsRow.Item("PITransferCount") = 1
End If

Outcomes