[daniel.hudnall]

Hello,

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.

[modi123_1]

I see you have a select statement and a working delete statement... update operates the same way. You can either call a stored procedure or write your own text on the fly.

Just make sure that if you are using a variable to determine your 'where' on your delete or update that you get the VALUE and not the name.. you know. .because everything is in double quotes and is taken for text..

"WHERE <var_name> = 0" is bad
"WHERE " + <var_name>.tostring + " = 0" is good.

But for all intents and purposes this is how you should make your connection.. How you fit it into your code is your prerogative.. Hollar if you need more assistance.

			Dim command As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()

			Try
				command.Connection = "your connection string here"
				command.CommandText = "your sql UPDATE statement here"
				command.CommandType = System.Data.CommandType.StoredProcedure

				connection.Open()
				command.ExecuteNonQuery()
			Catch ex As Exception
				'-- handle exceptoin
			Finally
				connection.close()
			End Try

[daniel.hudnall]

That is my problem, I don't know where to fit it in the code and I don't understand what you mean by the "working" delete statement because I don't know how to use that. I am very new to vb.net and I am just trying to figure out how everything works. The code that I currently have simply executes a query against the database and loads into a table.........then I increment through the table in print out what is in each row to the console depending on the comparison that I perform while incrementing. I will show you where I need the help.

I am OK up to this part......but after the comparison I don't know how to update the database. What I want to do is if the two date fields below (depreciationstartDate and depreciationendDate) return 3 --> meaning they are older than 3 years......then I want to update the MONTHLYCOST field on that exact record by either deleting it or setting the value to a blank value so no cost is visible in the database. I have absolutely no idea of how to do that, much less the code to perform that. I have tried everything from using the sqladapter.update command, to executing update queries....and the list goes on but everytime I try to do that it freezes the program. If you look at the code the delete query is commented out because I didn' tknow how to implement it. I just simply want to step through each and every row, perform the comparison on the two date fields, if the date fields return the number 3, then I want to delete the monthlyCost field (PO_TEXT4) for that same record. Please show me the code to do this by either adding to my program or completely altering it if you want. Thank you very much for your time.

  '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

[daniel.hudnall]

This is the full version that I am running right now. This code works and simply displays the results in the select statement to the console window. Please show me how to alter this program to update after the datacomparison. Thanks

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 = xxxx"

			'contains my SQL information
		strSQL = "SELECT PO_TEXT4, PO_DATE1, PO_DATE2 FROM dbo.PURCHASE where PO_DATE1 <>'' AND PO_DATE2<>'' and PO_TEXT4 <>'' ORDER BY PO_TEXT4"

		'strSQL2 = "DELETE from PURCHASE where PO_TEXT4 = + monthlyCost.tostring +"

			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))

				'/////////////////////////////////////////////////////////////////////

				'IF the date compares states that more than 3 years has past THEN delete the monthly cost OR UPDATE it with an EMPTY VALUE
				If (dataComparison >= 3) Then
					whatToDo = 1
				Else
					whatToDo = 0
				End If

				'//////////////////////////////////////////////////////////////////////

					'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("REMOVED" & vbTab & monthlyCost _
						& vbTab & depreciationstartDate & vbTab & depreciationendDate)
				End Select

				Next

				'close the connection to the database
				conn.Close()

			Catch ex As Exception

			End Try

	End Sub

[Hanzie]

I also new to vb.net, hell i'm even new to programming. But trying to help!

You made a select-command (like: select * from table) and execute this command by command.executenonquery

To delete someting you have to do the same, like:

Delete column1 where row=somevalue. After that execute this command.
And the update the tableadapter.

I believe this is the way to do it.

Hope i could help!

[modi123_1]

Tips and info:
First.. add your variable types when you declare them.
Second, instantiate your variables to initial values.
Third, break a part your code.. data access should be in separate functions.
Fourth, what ever you SQL table looks like must be horrible.. I hope this is not for a work project or something. I understand you are new, but I would suggest looking into SQL as well.
Fifth, my update statement may or may not be right for your table.. I took a stab at it.
Sixth, an update statement should never be used as "update with empty" as I have below, but with the information given that's all I could specifiy.
Seventh, always have your exceptions printed in the console or popup in a message box.
Eighth, there are a few better ways of tightening up the code, yes I am aware of that, but for this example it works.
Ninth, I did squat with your for-loop... just inserted the function calls when I needed to.

Any questions, speak up.

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

		Dim dt As DataTable = New DataTable
		Dim dr As DataRowCollection
		Dim strSQL2 As String = ""
		Dim i As Integer = 0

		Dim monthlyCost As Decimal = 0
		Dim depreciationendDate As New DateTime
		Dim depreciationstartDate As New DateTime
		Dim whatToDo As Integer = 0

		'-- load here
		Me.LoadData(dt)

		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 As Long = (DateDiff(DateInterval.Year, depreciationstartDate, depreciationendDate))

			'/////////////////////////////////////////////////////////////////////

			'IF the date compares states that more than 3 years has past THEN delete the monthly cost OR UPDATE it with an EMPTY VALUE
			If (dataComparison >= 3) Then
				'-- whatToDo = 1
				DeleteData(monthlyCost)
			Else
				'--whatToDo = 0
				UpdateDataWithEmtpy()
			End If

			'//////////////////////////////////////////////////////////////////////

			'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("REMOVED" & vbTab & monthlyCost _
					& vbTab & depreciationstartDate & vbTab & depreciationendDate)
			End Select

		Next


	End Sub

	Private Sub LoadData(ByRef myTable As DataTable)
		Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection
		Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand
		Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
		Dim connString As String = ""
		Dim strSQL As String = ""


		'contains my connection string information
		connString = "Data Source=zulync01db01;Initial Catalog=trackit70_data;User ID=trackit70;Password = xxxx"

		'contains my SQL information
		strSQL = "SELECT PO_TEXT4, PO_DATE1, PO_DATE2 FROM dbo.PURCHASE where PO_DATE1 <>'' AND PO_DATE2<>'' and PO_TEXT4 <>'' ORDER BY PO_TEXT4"

		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

			da = New SqlClient.SqlDataAdapter() 'this declares a data adapter
			da.SelectCommand = cmd 'gives the selectCommand to the data adapter
			myTable = New DataTable() 'declares a new DataTable
			conn.Open() 'this opens the connection

			da.Fill(myTable) 'this fills the data table with the information stored in the data adapter
		Catch ex As Exception
			Console.WriteLine(ex.Message.ToString)
		Finally
			'close the connection to the database
			conn.Close()
		End Try
	End Sub

	Private Sub DeleteData(ByVal myMonthlyCost As Decimal)
		Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection
		Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand

		Dim connString As String = ""
		Dim strSQL As String = ""


		'contains my connection string information
		connString = "Data Source=zulync01db01;Initial Catalog=trackit70_data;User ID=trackit70;Password = xxxx"

		'contains my SQL information
		'-- this gets the VALUE of myMonthlyCost
		'-- anything inside "" is treated as text and words.. so your statement would be 'where PO_TEXT4 equals (the word)myMonthlyCost'
		strSQL = "DELETE from PURCHASE where PO_TEXT4 = " + myMonthlyCost.ToString

		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

			conn.Open() 'this opens the connection

			cmd.ExecuteNonQuery()
		Catch ex As Exception
			Console.WriteLine(ex.Message.ToString)
		Finally
			'close the connection to the database
			conn.Close()
		End Try
	End Sub

	Private Sub UpdateDataWithEmtpy()
		Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection
		Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand

		Dim connString As String = ""
		Dim strSQL As String = ""


		'contains my connection string information
		connString = "Data Source=zulync01db01;Initial Catalog=trackit70_data;User ID=trackit70;Password = xxxx"

		'contains my SQL information
		strSQL = "UPDATE from PURCHASE SET PO_TEXT4 = null"

		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

			conn.Open() 'this opens the connection

			cmd.ExecuteNonQuery()
		Catch ex As Exception
			Console.WriteLine(ex.Message.ToString)
		Finally
			'close the connection to the database
			conn.Close()
		End Try
	End Sub

Hanzie, on 26 Jun, 2008 - 12:34 PM, said:

I also new to vb.net, hell i'm even new to programming. But trying to help!

You made a select-command (like: select * from table) and execute this command by command.executenonquery

FYI, enumerate your columns... avoid selecting by *.

[daniel.hudnall]

Hello and thank you very much for the reply. I was able to get the field I wanted to update in the database only instead of updating that INDIVIDUAL field it updated the entire database with that value for the field name. Therefore, I was wondering if you knew what the syntax was for updating the "individual" field as the record is incrementing through the database? I will show you what I mean below and hopefully you can give me some advice because I simply just can't figure out the code and from what I have seen it is something like:

myRow as datarow = myTable.rows."something"."something"----->it just seems abnormally long

'AFTER pulling the fields from the database and populating a datatable, you saw where I created this for loop to walk through each record in the database table and assign fields to variables like I did with the "monthlyCost" field below. 

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")

'THEN I perform some calculations on the two other fields that I had already assigned values to as seen below
			  
 Dim dataComparison = (DateDiff(DateInterval.Year, depreciationstartDate, depreciationendDate))

				'IF the date compares states that more than 3 years has past THEN delete the monthly cost OR UPDATE it with an EMPTY VALUE
				If (dataComparison >= 3) Then
					whatToDo = 1
				Else
					whatToDo = 0
				End If

'INSIDE of the whatToDo select statement I have the execution of the query for the update. My question to you is...how/what is the syntax for the update to reference the INDIVIDUAL field for each record and update it instead of updating the entire column in the database. My SQL is as follows:

strSQL2 =UPDATE PURCHASE set PO_TEXT4 = 20 WHERE (this is the part I don't know)

'I want to reference the INDIVIDUAL field for the PO_TEXT4 "individual" record. As you can see from above I declared the variable "monthlyCost" and set that equal to the PO_TEXT4 field. I think that I need to use monthlyCost in some way in the WHERE part of the clause fo the update I am just unfamiliar with the syntax to do so. I am sorry if this may sound confusing but it is kinda hard to explain. Thanks for any help with this in advance.

[modi123_1]

daniel.hudnall, on 26 Jun, 2008 - 01:08 PM, said:

Hello and thank you very much for the reply. I was able to get the field I wanted to update in the database only instead of updating that INDIVIDUAL field it updated the entire database with that value for the field name. Therefore, I was wondering if you knew what the syntax was for updating the "individual" field as the record is incrementing through the database? I will show you what I mean below and hopefully you can give me some advice because I simply just can't figure out the code and from what I have seen it is something like:

myRow as datarow = myTable.rows."something"."something"----->it just seems abnormally long

'AFTER pulling the fields from the database and populating a datatable, you saw where I created this for loop to walk through each record in the database table and assign fields to variables like I did with the "monthlyCost" field below. 

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")

'THEN I perform some calculations on the two other fields that I had already assigned values to as seen below
			  
 Dim dataComparison = (DateDiff(DateInterval.Year, depreciationstartDate, depreciationendDate))

				'IF the date compares states that more than 3 years has past THEN delete the monthly cost OR UPDATE it with an EMPTY VALUE
				If (dataComparison >= 3) Then
					whatToDo = 1
				Else
					whatToDo = 0
				End If

'INSIDE of the whatToDo select statement I have the execution of the query for the update. My question to you is...how/what is the syntax for the update to reference the INDIVIDUAL field for each record and update it instead of updating the entire column in the database. My SQL is as follows:

strSQL2 =UPDATE PURCHASE set PO_TEXT4 = 20 WHERE (this is the part I don't know)

'I want to reference the INDIVIDUAL field for the PO_TEXT4 "individual" record. As you can see from above I declared the variable "monthlyCost" and set that equal to the PO_TEXT4 field. I think that I need to use monthlyCost in some way in the WHERE part of the clause fo the update I am just unfamiliar with the syntax to do so. I am sorry if this may sound confusing but it is kinda hard to explain. Thanks for any help with this in advance.

Lordy lord.. you are the greenest of green horns aren't ya? I knew that would happen, but since I don't know what your table stucture looks like I couldn't place a 'where' statement. Post a copy of your table's defintions. What you are looking for is the KEY that says this row is unique versus the other rows there... then you would have that returned in the select statement, so the update function has something to pick it out of the crowd.

Again, table defintion is something I will need to help you.

[daniel.hudnall]

Heh, I told you I was new to vb.net..........and when I saw new I mean as in I needed to start learning it about 3 days ago. So I went from c++ and perl to learning vb.net and it was a big change for me....especially when working with database and sqlserver. As far as your question.......I am not exactly sure how to get the data definition. I think the primary key is the ID field which would make sense. Does that help? Sorry for the inconvenience and I do appreciation you taking the time to help me out with this. I am working with a test database so everything I am affecting the database doesn't matter.

[modi123_1]

daniel.hudnall, on 26 Jun, 2008 - 01:36 PM, said:

Heh, I told you I was new to vb.net..........and when I saw new I mean as in I needed to start learning it about 3 days ago. So I went from c++ and perl to learning vb.net and it was a big change for me....especially when working with database and sqlserver. As far as your question.......I am not exactly sure how to get the data definition. I think the primary key is the ID field which would make sense. Does that help? Sorry for the inconvenience and I do appreciation you taking the time to help me out with this. I am working with a test database so everything I am affecting the database doesn't matter.

Mkay.. you don't know what your table looks like?
Just give me a list of columns and any keys.

If your ID is a key than make sure your select statement returns it. The my update sub looks like this:

Private Sub UpdateDataWithEmtpy(byval myID as int32)

the update statement would be:

strSQL = "UPDATE from PURCHASE SET PO_TEXT4 = null WHERE ID = " + myID.tostring

the code where it is called:

 UpdateDataWithEmtpy(CInt(dr(i)("ID")))

your select statement would be:

strSQL = "SELECT ID, PO_TEXT4, PO_DATE1, PO_DATE2 FROM dbo.PURCHASE where PO_DATE1 <>'' AND PO_DATE2<>'' and PO_TEXT4 <>'' ORDER BY PO_TEXT4"

That should be about it.

[daniel.hudnall]

Also....I hadn't seen the code that you submitted. I am very greatful for the time that you took in rearranging my code and changing a few things. It definitely cleared things up and I am very greatful. It just gave me another way of coding and it definitely makes sense.Thanks again for the time you have put into helping me. Once I figure out how to update that "individual" field for each individual record...........then the script should be complete. Then I will just create into an executable and run when necessary. Thanks again..and hope to hear from ya soon.

[modi123_1]

daniel.hudnall, on 26 Jun, 2008 - 01:47 PM, said:

Also....I hadn't seen the code that you submitted. I am very greatful for the time that you took in rearranging my code and changing a few things. It definitely cleared things up and I am very greatful. It just gave me another way of coding and it definitely makes sense.Thanks again for the time you have put into helping me. Once I figure out how to update that "individual" field for each individual record...........then the script should be complete. Then I will just create into an executable and run when necessary. Thanks again..and hope to hear from ya soon.

What? I just told you which lines to update. Again, this is all a shot in the dark since you haven't told me what your table columns are.

[daniel.hudnall]

Thanks for the quick response. As for the table columns i know what they are but there are 30 fields in the table and I didn't want to list all of those out. The ID field would definitely be the key for the table. As for the code.......I am going to try implementing that and that should take care of the problem. Again, sorry for all the stupid questions but the only thing I have ever used to perform operations like this is vb6 code using recordsets and compared to vb.net EVERYTHING has changed.

[modi123_1]

Mkay.. let me know if you are having problems.

[daniel.hudnall]

No i was saying with the last post that I made I made it BEFORE I saw your code that showed me what I needed to update. I was just apologizing. I modified the code and I think I can make it work. I got an exception stating that something was wrong near the keyword "from" in the update statement which seems rediculous.......but shouldn't be hard to fix. There isn't anything around the from that could be doing anything but it is throwing an error. Again, I really appreciation your help with this. I learned a bunch from working with you today and I hope I didn't tie up too much of your time.

strSQL = "UPDATE from dbo.PURCHASE SET PO_TEXT4 = 0 WHERE ID=" + myID.ToString