TN AppSvr 112 Updating a SQL database record using the SQL Client

Description

Shows how to use a SQLClient connection to update and existing record in a SQL Database.

• Author: Joseph Hefner
• Published: 01/06/2015
• Applies to: Application Server 3.1 or later

The screenshot below is an example of how to update a database record using a SQLClient connection. The script below will be triggered by the UpdateDB UDA belonging to this object being set to true. When this occurs, it will update the table named "Record" inside a database called XXXDB.  This table contains columns called Name and Number. The example code below will search for the record with the number "1" and update its Name to "User1".

The body of the script above can be copied from below:

Me.UpdateDB = false;

dim myConnection as System.Data.SqlClient.SqlConnection;
dim command as System.Data.SqlClient.SqlCommand;

dim Connection_String as string;

command = new System.Data.SqlClient.SqlCommand;

Connection_String = "Server = JH_Sp2k14;Database = XXXDB;User ID = wwAdmin;Password = wwAdmin";
myConnection = new System.Data.SqlClient.SqlConnection(Connection_String);

command.CommandType = System.Data.CommandType.Text;
command.CommandText = "Update Record SET [Name] = @Name where [Number] = @Number";
command.Parameters.AddWithValue("@Name","User1");
command.Parameters.AddWithValue("@Number","1");
command.Connection = myConnection;

myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
myConnection.Open();