I am trying to figure out how I can update a datatable in vb.net. I have literally searched every site possible and find it almost unbelievable that it is so hard to simply execute an update statement to a datatable and update the database. What I am trying to do is compare two date fields and depending on the result update another field.....THAT'S IT. I create a connection to the database, populate a datatable with a query using 3 fields, store the 3 fields into variables, execute logic against 2 date fields, and simply want to end by executing an UPDATE to the last field depending on the outcome of the comparison between the date fields. Please email me a way that this can be done because I find it hard to believe that vb.net makes it so hard to do something that is so simple and can be done in vb6 in about 10 lines of code. I have included my code below and have drawn a bracket around where I need the update to happen. Please email me any "coding" suggestions to help with this. Thank you very much for your time.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim conn As SqlClient.SqlConnection Dim cmd As SqlClient.SqlCommand Dim myCommand As SqlClient.SqlCommand Dim da As SqlClient.SqlDataAdapter Dim dt As DataTable Dim dr As DataRowCollection Dim connString As String Dim strSQL As String Dim strSQL2 As String Dim i As Integer Dim monthlyCost Dim depreciationendDate Dim depreciationstartDate Dim whatToDo As Integer 'contains my connection string information connString = "Data Source=zulync01db01;Initial Catalog=trackit70_data;User ID=trackit70;Password = xxxxxx" 'Initial select to pull 2 date fields and monthlyCost field strSQL = "SELECT PO_TEXT4, PO_DATE1, PO_DATE2 FROM dbo.PURCHASE where PO_DATE1 <>'' AND PO_DATE2<>'' and PO_TEXT4 <>'' ORDER BY PO_TEXT4" 'this was the TEST sql to delete the monthlyCost strSQL2 = "DELETE from PURCHASE where PO_TEXT4 = monthlyCost" Try conn = New SqlClient.SqlConnection(connString) 'this determines the connection cmd = New SqlClient.SqlCommand(strSQL, conn) 'this is the command that will be executed to pull the SQL values 'myCommand = New SqlClient.SqlCommand(strSQL2, conn) 'this is to setup the query to delete the monthlyCost for the PO da = New SqlClient.SqlDataAdapter() 'this declares a data adapter da.SelectCommand = cmd 'gives the selectCommand to the data adapter dt = New DataTable() 'declares a new DataTable conn.Open() 'this opens the connection da.Fill(dt) 'this fills the data table with the information stored in the data adapter dr = dt.Rows 'this sets the dr variable to the rows in the datatable 'this is where we will begin stepping through each row in the table For i = 0 To (dr.Count - 1) 'assigns values to each of the selection criteria in the SQL If Not dr(i)("PO_TEXT4") Is DBNull.Value Then _ monthlyCost = dr(i)("PO_TEXT4") If Not dr(i)("PO_DATE1") Is DBNull.Value Then _ depreciationstartDate = dr(i)("PO_DATE1") If Not dr(i)("PO_DATE2") Is DBNull.Value Then _ depreciationendDate = dr(i)("PO_DATE2") 'compare the date fields Dim dataComparison = (DateDiff(DateInterval.Year, depreciationstartDate, depreciationendDate)) '///////////////////////////////////////////////////////////////////// [b]'THIS IS WHERE I NEED THE HELP 'IF the date compares states that more than 3 years has past THEN delete the monthly cost OR UPDATE it with an EMPTY VALUE[/b] If (dataComparison >= 3) Then [b]'DELETE the monthlyCost value or UPDATE it with an EMPTY VALUE[/b] Else 'DO NOTHING End If '////////////////////////////////////////////////////////////////////// [b]'I HAD THOUGHT ABOUT USING THIS SELECT CASE BUT IT DIDN'T WORK EITHER[/b] 'this is the select statement (incase we ever want to add to this Select Case whatToDo Case 0 Console.WriteLine("NO CHANGE" _ & vbTab & depreciationstartDate & vbTab & depreciationendDate) Case 1 'execute this because we don't care about returning any data 'myCommand.ExecuteNonQuery() 'print that something was deleted and the rest of the fields Console.WriteLine("DELETED" _ & vbTab & depreciationstartDate & vbTab & depreciationendDate) End Select Next 'close the connection to the database conn.Close() Catch ex As Exception End Try End Sub End Class
Again, thanks again for any help offered.