Question update database table

[ED Controls]

using visual studio.net 2002 I have a piece of code that is manually run to update a sql server datbase tableThe line of code looks like this:
Update [005].dbo.TMOHistory set TMOADJ = NULL
[005] = database name, TMOHistory is table in database, TMOADJ is collumn in table.
I would like to create a visual studio.net widowns application that can run this code and update the database. I am a controls guy that works mainly with plc's, so I have limited knowledge of vb.net programming. Is this an easy application to do in visual studio.net? Any help or guidance would be greatly appreicated. So far I am able to connect to the server database (I think) using the code below(EVOLUTION is server name). Not sure where to go after that.


myConn = New SqlConnection("Initial Catalog=005;" & _
'"Data Source=EVOLUTION;Integrated Security=SSPI;")

myConn.Open()

Thanks,
-Erik

 

[JuggaloBrotha]

You're off to a great start with it, creating the connection is the first thing you need to do, what you need to do next is create a SqlCommand object, passing it our myConn variable & pass it the query as a string too. Then call the SqlCommand's ExecuteNonQuery() to actually run it on the DB.

Also, you should create all the needed objects, then use a Try/Catch block around the opening and executing the command inside. Be sure to close the connect and cleanup the objects when done too.

 

[ED Controls]

You're off to a great start with it, creating the connection is the first thing you need to do, what you need to do next is create a SqlCommand object, passing it our myConn variable & pass it the query as a string too. Then call the SqlCommand's ExecuteNonQuery() to actually run it on the DB.

Also, you should create all the needed objects, then use a Try/Catch block around the opening and executing the command inside. Be sure to close the connect and cleanup the objects when done too.

I tried the following based on your suggestions. When I debug in vb and click the button to run the code nothing happens. No errors but also no message saying anything was updated. Am I missing something? I set up a connection to the database in server explorer and can view the table there, so I'm pretty sure i'm connected. Sorry if this is too basic for some of you, but I am very new to using vb. Thanks for the help!
myConn = New SqlConnection("Initial Catalog=005;" & _
"Data Source=EVOLUTION;Integrated Security=SSPI;")
Dim sql As String = "UPDATE [005]. dbo.TMOHistory set TMOADJ = NULL"
Dim cmd As SqlCommand = New SqlCommand(sql, myConn)
Try
myConn.Open()
Catch ex As Exception
End Try
Try
Dim iCnt As Integer
cmd.ExecuteNonQuery()
MessageBox.Show("TMOHistory Updated " & iCnt & " Records")
Catch ex As Exception
End Try
myConn.Close()


When I added the following message box to the code there seems to be a problem executing the query. I dont get any unhandled exception errors, but the database is still not updated and the message box failed to update pops up. Suggestions? Thanks!
Try
Dim iCnt As Integer
cmd.ExecuteNonQuery()
MessageBox.Show("TMOHistory Updated " & iCnt & " Records")
Catch ex As Exception
MessageBox.Show("Failed to update")
End Try

 

[JuggaloBrotha]

I tried the following based on your suggestions. When I debug in vb and click the button to run the code nothing happens. No errors but also no message saying anything was updated. Am I missing something? I set up a connection to the database in server explorer and can view the table there, so I'm pretty sure i'm connected. Sorry if this is too basic for some of you, but I am very new to using vb. Thanks for the help!
myConn = New SqlConnection("Initial Catalog=005;" & _
"Data Source=EVOLUTION;Integrated Security=SSPI;")
Dim sql As String = "UPDATE [005]. dbo.TMOHistory set TMOADJ = NULL"
Dim cmd As SqlCommand = New SqlCommand(sql, myConn)
Try
myConn.Open()
Catch ex As Exception
End Try
Try
Dim iCnt As Integer
cmd.ExecuteNonQuery()
MessageBox.Show("TMOHistory Updated " & iCnt & " Records")
Catch ex As Exception
End Try
myConn.Close()


When I added the following message box to the code there seems to be a problem executing the query. I dont get any unhandled exception errors, but the database is still not updated and the message box failed to update pops up. Suggestions? Thanks!
Try
Dim iCnt As Integer
cmd.ExecuteNonQuery()
MessageBox.Show("TMOHistory Updated " & iCnt & " Records")
Catch ex As Exception
MessageBox.Show("Failed to update")
End Try

We'd need to see the error message from the exception, in order to get that in your MessageBox.Show("Failed to update") line, change it to:
MessageBox.Show(String.Format("Failed to update:{0}{1}", Environment.NewLine, ex.Message))

 

[ED Controls]

I was wondering how you get the error message to show up. Thanks. Here is the error message im getting when trying that code:

Failed to Update: The UPDATE permission was denied on the object 'TMOHistory', database '005', schema 'dbo'.


Does this mean I dont have permissions to update this table? How would I go about changing the permissions so I can update? Thanks again!

 

[jmcilhinney]

The topic of this thread sounds extremely suspicious and the fact that you don't have permission to update that table further suggests that it's something that you are trying to perform a change against legitimate security measures. I am going to close this thread as a result. If you can provide evidence of the legitimacy of your request then you may do so in a PM to me, otherwise you can considere this conversation at an end.