Can someone help me figure out why the technicianID doesn't like it when it comes out null. Please. The program is in Visual Basics 2019 using .NET Framework. 
Project 3A SQL Server Sample Solution whe Sportowe do final De Eye Bred Date Tour Fat Jock Ter Web 90 Debes X Federige -All Windows Open Dacidente 1200, Sherb My Produd Dade Dried Camer T tregowe Cekule Dave Cincident hab ampere Techniniwa Updecidit Wide . 3 Techperite My Cube Ce0b Meditab det Pro Reb Tech Tech Suspek ROV 2 De nad Log Fawwa Fondos Hd Rede Maches System.Windows belit AH Fan Color More Surs,825 Windows Fabe Genesember Orden Motos Kulleitet The Hu Tading e Cono rathetaai liews The item in the livin Try Imports TechSupportData Public class frmOpen Incidents Private Sub frmopen Incidents_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim incidentList As List (of Incident) incidentList - Incident DB.GetOpenIncidents If incidentlist.Count > Then Dim incident As Incident For 1 As Integer = @ To incidentlist.Count - 1 incident = incidentList(i) lvIncidents.Items.Add(incident.ProductCode) lvincidents. Items (1).SubItems.Add(CDate( incident.DateOpened).ToShortDateString) lvIncidents.Items(1).SubItems.Add( incident.CustomerName) lvIncidents. Items (1).SubItems.Add( incident.TechName) lvincidents. Items(1).SubItems.Add( incident.Title) Next Page 1 of 1 Project 3A SQL Server Sample Solution Get Else MessageBox.Show("All incidents are closed.", "No Open Incidents") End If Catch ex As Exception MessageBox.Show(ex.Message, ex.getType.ToString) End Try End Sub End Class Imports System.Data.SqlClient Public Class Incident Private IncidentID As Integer Private m_CustomerID As Integer Private m_Product Code As String Private m_TechID As Nullable(Of Integer) Private m_DateOpened As Date Private m_Dateclosed As Nullable(of Date) Private m_Title As String Private m_Description As String Public Sub New) End Sub Public Property IncidentID() As Integer Return m_Incident ID End Get Set(ByVal value As Integer) _Incident ID = value End Set End Property Public Property CustomerID() As Integer Get Return m_Customer ID End Get Set(ByVal value As Integer) m_Customer ID - Value End Set End Property Public Property Productcode() As String Return m_ProductCode End Get Set(ByVal value As String) m_Product Code = value End Set End Property Public Property TechID() As Nullable(of Integer) Get If m_TechID. HasValue Then Return Cint(m_TechID) Else Get Page 2 of 10 Project 3A SQL Server Sample Solution Get Return Nothing End If End Get Set(ByVal value As Nullable(of Integer)) m_TechID = value End Set End Property Public Property DateOpened() As Date Get Return m_DateOpened End Get Set(ByVal value As Date) m_DateOpened = value End Set End Property Public Property DateClosed() As Nullable(of Date) If m_Dateclosed. HasValue Then Return CDate(m_DateClosed) Else Return Nothing End If End Get Set(ByVal value As Nullable(of Date)) m_Dateclosed = value End Set End Property Public Property Title() As String Get Return m_Title End Get Set(ByVal value As String) m_Title = value End Set End Property Public Property Description() As String Get Return m_Description End Get Set(ByVal value As String) m_Description = value End Set End Property Public ReadOnly Property CustomerName() As String Get Dim name As String = If m_CustomerID Then Try name = CustomerDB.GetCustomerName(m_CustomerID) Catch ex As Exception Throw ex End Try End If Page 3 of 10 Project 3A SQL Server Sample Solution Return name End Get End Property Public Readonly Property TechName() As String Get Dim name As String - If _TechID.HasValue Then Try name = Techniciande. Get TechnicianName(CInt (m_TechID)) Catch ex As Exception Throw ex End Try End If Return name End Get End Property Public Readonly Property ProductName() As String Get Dim name As String = If I_Product Code "" Then Try name = Productos.GetProductName(m_ProductCode) Catch ex As Exception Throw ex End Try If Return name End Get End Property End Class Imports System.Data.SqlClient Public class IncidentDB Public Shared Function GetOpenIncidents() As List (of Incident) Dim incidentList As New List (of Incident) Dim connection As SqlConnection - TechSupportDB.GetConnection Dim selectStatement As String = "SELECT CustomerID, ProductCode, TechID, DateOpened, Title - & "FROM Incidents" & "WHERE Dateclosed IS NULL" Dim selectCommand As New SqlCommand(selectStatement, connection) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader Dim incident As Incident Do while reader.Read incident - New Incident incident.CustomerID - CInt(reader("CustomerID")) incident.ProductCode = reader("ProductCode").ToString If IsDBNull(reader("TechID")) Then Page 4 of 10 Project 3A SQL Server Sample Solution incident.TechID = Nothing Else incident.TechID = CInt (reader("TechID")) End If incident.DateOpened - CDate(reader("DateOpened")) incident.Title = reader("Title").ToString incidentList.Add( incident) Loop reader.close() Catch ex As Exception Throw ex Finally connection.close() End Try Return incidentList End Function Public Shared Sub Add Incident(ByVal incident As Incident) Dim connection As SqlConnection - TechSupportDB.GetConnection Dim insertStatement As String = "INSERT Incidents "& "(CustomerID, Product Code, DateOpened, Title, Description)" & "VALUES (@CustomerID, @ProductCode, @DateOpened, @Title, @Description)" Dim insert Command As New SqlCommand(insertStatement, connection) insert Command.Parameters.AddWithvalue("@CustomerID", incident.CustomerID) insert Command. Parameters. AddWithValue("@ProductCode", incident. Product Code) insert Command. Parameters.AddWithValue("@DateOpened", incident.DateOpened) insert Command. Parameters.AddWithValue("@Title", incident.Title) insert Command.Parameters.AddWithvalue("@Description", incident. Description) Try connection.Open() insert Command.ExecuteNonQuery() Catch ex As SqlException Throw ex Finally connection.Close() End Try End Sub Public Shared Function GetIncident(ByVal incidentID As Integer) As Incident Dim incident As New Incident Dim connection As SqlConnection - TechSupportDD.GetConnection Dim selectStatement As String = "SELECT Incident ID, CustomerID, Product Code, TechID, " & "DateOpened, DateClosed, Title, Description" & "FROM Incidents " & "WHERE IncidentID = @IncidentID" Dim selectCommand As New SqlCommand(selectStatement, connection) selectCommand. Parameters.AddWithvalue("@IncidentID", incident ID) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior. SingleRow) If reader.Read Then incident. Incident ID - CInt(reader("IncidentID")) incident.CustomerID = CInt (reader("CustomerId")) incident. Product Code = reader("ProductCode").ToString If IsDBNull(reader("TechID")) Then Page 5 of 10 Project 3A SQL Server Sample Solution incident.TechID - Nothing Else incident.TechID = Cint(reader("TechID")) End If incident.DateOpened = CDate(reader("DateOpened")) If IsDBNull(reader("DateClosed")) Then incident.DateClosed = Nothing Else incident.DateClosed + CDate(reader("DateClosed")) End If incident.Title = reader("Title").ToString incident. Description = reader("Description").ToString Else incident - Nothing End If reader.Close() Catch ex As SqlException Throw ex Finally connection.Close() End Try Return incident End Function Public Shared Function UpdateIncident(ByVal incident As Incident, ByVal description As String) As Boolean Dim connection As SqlConnection = TechSupportDB.GetConnection Dim updateStatement As String = "UPDATE Incidents " & "SET Description = @NewDescription" & "WHERE Incident ID = @IncidentID " & AND Description = @Description " & AND Dateclosed IS NULL" Dim updateCommand As New SqlCommand(updateStatement, connection) updateCommand. Parameters.AddWithValue("@NewDescription", description) updateCommand.Parameters.AddwithValue("@IncidentID", incident. Incident ID) updateCommand.Parameters. AddwithValue("@Description", incident. Description) Try connection.Open() Dim count As Integer = updateCommand.ExecuteNonQuery If count > Then Return True Else Return False End If Catch ex As SqlException Throw ex Finally connection.Close() End Try End Function Public Shared Function CloseIncident(ByVal incident As Incident) As Boolean Dim connection As SqlConnection = TechSupportDB.GetConnection Dim updateStatement As String = "UPDATE Incidents "& "SET DateClosed - @DateClosed " & "WHERE Incident ID = @IncidentID " & Page 6 of 10 Project 3A SQL Server Sample Solution Try " AND Description = @Description" & AND DateClosed IS NULL" Dim updateCommand As New SqlCommand(updateStatement, connection) updateCommand. Parameters.AddWithvalue("@DateClosed", DateTime. Today) updateCommand. Parameters.AddWithValue("IncidentID", incident. IncidentID) updateCommand.Parameters. AddwithValue("Description", incident. Description) connection.Open() Dim count As Integer = updateCommand.ExecuteNonQuery If count > Then Return True Else Return False End If Catch ex As SqlException Throw ex Finally connection.Close() End Try End Function Public Shared Function GetOpenTechnicianIncidents(ByVal techID As Integer) As List (of Incident) Dim incidentlist As New List(of Incident) Dim connection As SqlConnection = TechSupportDB.GetConnection Dim selectStatement As String - "SELECT CustomerID, ProductCode, DateOpened, Title, Description" & "FROM Incidents " & "WHERE TechID - @TechID " & AND Dateclosed IS NULL" Dim selectCommand As New SqlCommand(selectStatement, connection) selectCommand.Parameters.AddWithvalue("@TechID", techID) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader Dim incident As Incident Do While reader. Read incident - New Incident incident.CustomerID. CInt(reader("CustomerID")) incident. Product Code = reader("ProductCode").ToString incident.DateOpened = CDate(reader("DateOpened"> incident.Title = reader("Title").ToString incident.Description = reader("Description").ToString incidentList.Add( incident) Loop reader.Close() Catch ex As Exception Throw ex Finally connection.Close() End Try Return incidentList End Function End Class Page 7 of 10 Project 3A SQL Server Sample Solution Imports System.Data.SqlClient Public class TechSupportDB Public Shared Function GetConnection() As SqlConnection Dim connectionString As String "Data Source=localhost\SqlExpress; Initial Catalog=TechSupport;" & "Integrated Security=True" Return New SqlConnection(connectionString) End Function End Class Imports System.Data.SqlClient Public class CustomerDB Public Shared Function GetCustomerName(ByVal customerID As Integer) As String Dim name As String Dim connection As SqlConnection = Techsupportov.GetConnection Dim selectStatement As String = "SELECT Naine FROM Customers " & "WHERE CustomerID = " & customerID Dim select Command As New SqlCommand(selectStatement, connection) Try connection.Open() name = selectCommand.ExecuteScalar.ToString Catch ex As Exception Throw ex Finally connection.Close() End Try Return name End Function Public Shared Function GetCustomerlist() As List (of Customer) Dim customerlist As New List (of Customer) Dim connection As SqlConnection = TechSupportDB.GetConnection Dim selectStatement As String = "SELECT CustomerID, Name FROM Customers " & "ORDER BY Name" Dim selectCommand As New SqlCommand(selectStatement, connection) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader Dim customer As Customer Do While reader. Read customer = New Customer customer. CustomerID = CInt(reader("CustomerId")) customer.Name = reader("Name").ToString customerlist.Add(customer) Loop reader.close() Page 8 of 10 Project 3A SQL Server Sample Solution Catch ex As SqlException Throw ex Finally connection.Close() End Try Return customerlist End Function End Class Imports System.Data.SqlClient Public class TechnicianDB Public Shared Function GetTechnicianName(ByVal techID As Integer) As String Dim name As String Dim connection As SqlConnection = TechSupportDB.GetConnection Dim selectStatement As String = "SELECT Name FROM Technicians "& "WHERE TechID " & techID Dim selectCommand As New SqlCommand (selectStatement, connection) Try connection.Open() name = selectCommand.ExecuteScalar.ToString Catch ex As Exception Throw ex Finally connection.Close() End Try Return name End Function Public Shared Function GetTechnicianList() As List (of Technician) Din techlist As New List(Of Technician) Din connection As SqlConnection = TechSupporto.GetConnection Dim selectStatement As String = "SELECT TechID, Name FROM Technicians & "ORDER BY Name" Dim selectCommand As New SqlCommand(selectStatement, connection) Try connection.Open() Dim reader As SqlDataReader - selectCommand.ExecuteReader Dim technician As Technician Do While reader. Read technician New Technician technician. TechID = Cint(reader("TechID")) technician. Name-reader("Name").ToString techList.Add(technician) Loop reader.close() Catch ex As SqlException Throw ex Finally connection.close() End Try Page 9 of 10 Project 3A SQL Server Sample Solution Return techlist End Function Public Shared Function Get Technician(ByVal techId As Integer) As Technician Dim technician As New Technician Dim connection As SqlConnection = TechSupportDB.GetConnection Dim selectStatement As String - "SELECT TechID, Name, Email, Phone " & "FROM Technicians " & "WHERE TechID - @TechID" Dim selectCommand As New SqlCommand(selectStatement, connection) selectCommand.Parameters.AddWithvalue("@TechID", techID) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow) If reader.Read Then technician. TechID = CInt(reader("TechID")) technician. Name = reader("Name").ToString technician. Email = reader("Email").ToString technician.Phone - reader("Phone").ToString Else technician = Nothing End If reader.Close() Catch ex As SqlException Throw ex Finally connection.Close() End Try Return technician End Function End Class Project 3A SQL Server Sample Solution whe Sportowe do final De Eye Bred Date Tour Fat Jock Ter Web 90 Debes X Federige -All Windows Open Dacidente 1200, Sherb My Produd Dade Dried Camer T tregowe Cekule Dave Cincident hab ampere Techniniwa Updecidit Wide . 3 Techperite My Cube Ce0b Meditab det Pro Reb Tech Tech Suspek ROV 2 De nad Log Fawwa Fondos Hd Rede Maches System.Windows belit AH Fan Color More Surs,825 Windows Fabe Genesember Orden Motos Kulleitet The Hu Tading e Cono rathetaai liews The item in the livin Try Imports TechSupportData Public class frmOpen Incidents Private Sub frmopen Incidents_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim incidentList As List (of Incident) incidentList - Incident DB.GetOpenIncidents If incidentlist.Count > Then Dim incident As Incident For 1 As Integer = @ To incidentlist.Count - 1 incident = incidentList(i) lvIncidents.Items.Add(incident.ProductCode) lvincidents. Items (1).SubItems.Add(CDate( incident.DateOpened).ToShortDateString) lvIncidents.Items(1).SubItems.Add( incident.CustomerName) lvIncidents. Items (1).SubItems.Add( incident.TechName) lvincidents. Items(1).SubItems.Add( incident.Title) Next Page 1 of 1 Project 3A SQL Server Sample Solution Get Else MessageBox.Show("All incidents are closed.", "No Open Incidents") End If Catch ex As Exception MessageBox.Show(ex.Message, ex.getType.ToString) End Try End Sub End Class Imports System.Data.SqlClient Public Class Incident Private IncidentID As Integer Private m_CustomerID As Integer Private m_Product Code As String Private m_TechID As Nullable(Of Integer) Private m_DateOpened As Date Private m_Dateclosed As Nullable(of Date) Private m_Title As String Private m_Description As String Public Sub New) End Sub Public Property IncidentID() As Integer Return m_Incident ID End Get Set(ByVal value As Integer) _Incident ID = value End Set End Property Public Property CustomerID() As Integer Get Return m_Customer ID End Get Set(ByVal value As Integer) m_Customer ID - Value End Set End Property Public Property Productcode() As String Return m_ProductCode End Get Set(ByVal value As String) m_Product Code = value End Set End Property Public Property TechID() As Nullable(of Integer) Get If m_TechID. HasValue Then Return Cint(m_TechID) Else Get Page 2 of 10 Project 3A SQL Server Sample Solution Get Return Nothing End If End Get Set(ByVal value As Nullable(of Integer)) m_TechID = value End Set End Property Public Property DateOpened() As Date Get Return m_DateOpened End Get Set(ByVal value As Date) m_DateOpened = value End Set End Property Public Property DateClosed() As Nullable(of Date) If m_Dateclosed. HasValue Then Return CDate(m_DateClosed) Else Return Nothing End If End Get Set(ByVal value As Nullable(of Date)) m_Dateclosed = value End Set End Property Public Property Title() As String Get Return m_Title End Get Set(ByVal value As String) m_Title = value End Set End Property Public Property Description() As String Get Return m_Description End Get Set(ByVal value As String) m_Description = value End Set End Property Public ReadOnly Property CustomerName() As String Get Dim name As String = If m_CustomerID Then Try name = CustomerDB.GetCustomerName(m_CustomerID) Catch ex As Exception Throw ex End Try End If Page 3 of 10 Project 3A SQL Server Sample Solution Return name End Get End Property Public Readonly Property TechName() As String Get Dim name As String - If _TechID.HasValue Then Try name = Techniciande. Get TechnicianName(CInt (m_TechID)) Catch ex As Exception Throw ex End Try End If Return name End Get End Property Public Readonly Property ProductName() As String Get Dim name As String = If I_Product Code "" Then Try name = Productos.GetProductName(m_ProductCode) Catch ex As Exception Throw ex End Try If Return name End Get End Property End Class Imports System.Data.SqlClient Public class IncidentDB Public Shared Function GetOpenIncidents() As List (of Incident) Dim incidentList As New List (of Incident) Dim connection As SqlConnection - TechSupportDB.GetConnection Dim selectStatement As String = "SELECT CustomerID, ProductCode, TechID, DateOpened, Title - & "FROM Incidents" & "WHERE Dateclosed IS NULL" Dim selectCommand As New SqlCommand(selectStatement, connection) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader Dim incident As Incident Do while reader.Read incident - New Incident incident.CustomerID - CInt(reader("CustomerID")) incident.ProductCode = reader("ProductCode").ToString If IsDBNull(reader("TechID")) Then Page 4 of 10 Project 3A SQL Server Sample Solution incident.TechID = Nothing Else incident.TechID = CInt (reader("TechID")) End If incident.DateOpened - CDate(reader("DateOpened")) incident.Title = reader("Title").ToString incidentList.Add( incident) Loop reader.close() Catch ex As Exception Throw ex Finally connection.close() End Try Return incidentList End Function Public Shared Sub Add Incident(ByVal incident As Incident) Dim connection As SqlConnection - TechSupportDB.GetConnection Dim insertStatement As String = "INSERT Incidents "& "(CustomerID, Product Code, DateOpened, Title, Description)" & "VALUES (@CustomerID, @ProductCode, @DateOpened, @Title, @Description)" Dim insert Command As New SqlCommand(insertStatement, connection) insert Command.Parameters.AddWithvalue("@CustomerID", incident.CustomerID) insert Command. Parameters. AddWithValue("@ProductCode", incident. Product Code) insert Command. Parameters.AddWithValue("@DateOpened", incident.DateOpened) insert Command. Parameters.AddWithValue("@Title", incident.Title) insert Command.Parameters.AddWithvalue("@Description", incident. Description) Try connection.Open() insert Command.ExecuteNonQuery() Catch ex As SqlException Throw ex Finally connection.Close() End Try End Sub Public Shared Function GetIncident(ByVal incidentID As Integer) As Incident Dim incident As New Incident Dim connection As SqlConnection - TechSupportDD.GetConnection Dim selectStatement As String = "SELECT Incident ID, CustomerID, Product Code, TechID, " & "DateOpened, DateClosed, Title, Description" & "FROM Incidents " & "WHERE IncidentID = @IncidentID" Dim selectCommand As New SqlCommand(selectStatement, connection) selectCommand. Parameters.AddWithvalue("@IncidentID", incident ID) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior. SingleRow) If reader.Read Then incident. Incident ID - CInt(reader("IncidentID")) incident.CustomerID = CInt (reader("CustomerId")) incident. Product Code = reader("ProductCode").ToString If IsDBNull(reader("TechID")) Then Page 5 of 10 Project 3A SQL Server Sample Solution incident.TechID - Nothing Else incident.TechID = Cint(reader("TechID")) End If incident.DateOpened = CDate(reader("DateOpened")) If IsDBNull(reader("DateClosed")) Then incident.DateClosed = Nothing Else incident.DateClosed + CDate(reader("DateClosed")) End If incident.Title = reader("Title").ToString incident. Description = reader("Description").ToString Else incident - Nothing End If reader.Close() Catch ex As SqlException Throw ex Finally connection.Close() End Try Return incident End Function Public Shared Function UpdateIncident(ByVal incident As Incident, ByVal description As String) As Boolean Dim connection As SqlConnection = TechSupportDB.GetConnection Dim updateStatement As String = "UPDATE Incidents " & "SET Description = @NewDescription" & "WHERE Incident ID = @IncidentID " & AND Description = @Description " & AND Dateclosed IS NULL" Dim updateCommand As New SqlCommand(updateStatement, connection) updateCommand. Parameters.AddWithValue("@NewDescription", description) updateCommand.Parameters.AddwithValue("@IncidentID", incident. Incident ID) updateCommand.Parameters. AddwithValue("@Description", incident. Description) Try connection.Open() Dim count As Integer = updateCommand.ExecuteNonQuery If count > Then Return True Else Return False End If Catch ex As SqlException Throw ex Finally connection.Close() End Try End Function Public Shared Function CloseIncident(ByVal incident As Incident) As Boolean Dim connection As SqlConnection = TechSupportDB.GetConnection Dim updateStatement As String = "UPDATE Incidents "& "SET DateClosed - @DateClosed " & "WHERE Incident ID = @IncidentID " & Page 6 of 10 Project 3A SQL Server Sample Solution Try " AND Description = @Description" & AND DateClosed IS NULL" Dim updateCommand As New SqlCommand(updateStatement, connection) updateCommand. Parameters.AddWithvalue("@DateClosed", DateTime. Today) updateCommand. Parameters.AddWithValue("IncidentID", incident. IncidentID) updateCommand.Parameters. AddwithValue("Description", incident. Description) connection.Open() Dim count As Integer = updateCommand.ExecuteNonQuery If count > Then Return True Else Return False End If Catch ex As SqlException Throw ex Finally connection.Close() End Try End Function Public Shared Function GetOpenTechnicianIncidents(ByVal techID As Integer) As List (of Incident) Dim incidentlist As New List(of Incident) Dim connection As SqlConnection = TechSupportDB.GetConnection Dim selectStatement As String - "SELECT CustomerID, ProductCode, DateOpened, Title, Description" & "FROM Incidents " & "WHERE TechID - @TechID " & AND Dateclosed IS NULL" Dim selectCommand As New SqlCommand(selectStatement, connection) selectCommand.Parameters.AddWithvalue("@TechID", techID) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader Dim incident As Incident Do While reader. Read incident - New Incident incident.CustomerID. CInt(reader("CustomerID")) incident. Product Code = reader("ProductCode").ToString incident.DateOpened = CDate(reader("DateOpened"> incident.Title = reader("Title").ToString incident.Description = reader("Description").ToString incidentList.Add( incident) Loop reader.Close() Catch ex As Exception Throw ex Finally connection.Close() End Try Return incidentList End Function End Class Page 7 of 10 Project 3A SQL Server Sample Solution Imports System.Data.SqlClient Public class TechSupportDB Public Shared Function GetConnection() As SqlConnection Dim connectionString As String "Data Source=localhost\SqlExpress; Initial Catalog=TechSupport;" & "Integrated Security=True" Return New SqlConnection(connectionString) End Function End Class Imports System.Data.SqlClient Public class CustomerDB Public Shared Function GetCustomerName(ByVal customerID As Integer) As String Dim name As String Dim connection As SqlConnection = Techsupportov.GetConnection Dim selectStatement As String = "SELECT Naine FROM Customers " & "WHERE CustomerID = " & customerID Dim select Command As New SqlCommand(selectStatement, connection) Try connection.Open() name = selectCommand.ExecuteScalar.ToString Catch ex As Exception Throw ex Finally connection.Close() End Try Return name End Function Public Shared Function GetCustomerlist() As List (of Customer) Dim customerlist As New List (of Customer) Dim connection As SqlConnection = TechSupportDB.GetConnection Dim selectStatement As String = "SELECT CustomerID, Name FROM Customers " & "ORDER BY Name" Dim selectCommand As New SqlCommand(selectStatement, connection) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader Dim customer As Customer Do While reader. Read customer = New Customer customer. CustomerID = CInt(reader("CustomerId")) customer.Name = reader("Name").ToString customerlist.Add(customer) Loop reader.close() Page 8 of 10 Project 3A SQL Server Sample Solution Catch ex As SqlException Throw ex Finally connection.Close() End Try Return customerlist End Function End Class Imports System.Data.SqlClient Public class TechnicianDB Public Shared Function GetTechnicianName(ByVal techID As Integer) As String Dim name As String Dim connection As SqlConnection = TechSupportDB.GetConnection Dim selectStatement As String = "SELECT Name FROM Technicians "& "WHERE TechID " & techID Dim selectCommand As New SqlCommand (selectStatement, connection) Try connection.Open() name = selectCommand.ExecuteScalar.ToString Catch ex As Exception Throw ex Finally connection.Close() End Try Return name End Function Public Shared Function GetTechnicianList() As List (of Technician) Din techlist As New List(Of Technician) Din connection As SqlConnection = TechSupporto.GetConnection Dim selectStatement As String = "SELECT TechID, Name FROM Technicians & "ORDER BY Name" Dim selectCommand As New SqlCommand(selectStatement, connection) Try connection.Open() Dim reader As SqlDataReader - selectCommand.ExecuteReader Dim technician As Technician Do While reader. Read technician New Technician technician. TechID = Cint(reader("TechID")) technician. Name-reader("Name").ToString techList.Add(technician) Loop reader.close() Catch ex As SqlException Throw ex Finally connection.close() End Try Page 9 of 10 Project 3A SQL Server Sample Solution Return techlist End Function Public Shared Function Get Technician(ByVal techId As Integer) As Technician Dim technician As New Technician Dim connection As SqlConnection = TechSupportDB.GetConnection Dim selectStatement As String - "SELECT TechID, Name, Email, Phone " & "FROM Technicians " & "WHERE TechID - @TechID" Dim selectCommand As New SqlCommand(selectStatement, connection) selectCommand.Parameters.AddWithvalue("@TechID", techID) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow) If reader.Read Then technician. TechID = CInt(reader("TechID")) technician. Name = reader("Name").ToString technician. Email = reader("Email").ToString technician.Phone - reader("Phone").ToString Else technician = Nothing End If reader.Close() Catch ex As SqlException Throw ex Finally connection.Close() End Try Return technician End Function End Class
