Private Sub txtResult_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles txtResult.DragDrop
popAmount()
Dim strHolder As String
Dim strName As String
Dim strSSNsubstring As String
Dim strSeperator As String
Dim tb As TextBox = CType(sender, TextBox)
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbPath
Dim strSQL, strSQLInsert As String
Dim rdr As OleDbDataReader
Dim cn As New OleDbConnection(strConn)
Dim cmd As OleDbCommand = cn.CreateCommand()
Dim command As OleDbCommand = cn.CreateCommand()
strName = tb.Name
strName = tb.Text()
strSSNsubstring = strName.Substring(5, 9)
strSSNtext = CInt(strSSNsubstring)
strSeperator = "*************************************************"
strHolder = lstApplicant.Items(lstApplicant.SelectedIndex).ToString()
tb.Text = strName & vbCrLf & strSeperator & vbCrLf & "Awarded: " & strHolder & " scholarship" & _
vbCrLf & "SSN#: " & strSSNtext & vbCrLf & "Amount Award: $" & strAmount
cn.Open()
'change it to return the count of records found
strSQL = "SELECT COUNT(*) FROM Awarded WHERE awrdID=" & strSSNtext
Dim cmdSQL As New OleDbCommand(strSQL, cn)
Try
'just get single value and there is no need to close the connection if you are going to use it again
Dim count As Integer = cmdSQL.ExecuteScalar
If count > 0 Then
'some records found so update
cmd.CommandText = "UPDATE Awarded SET awrdName=" & "'" & strHolder & "'" & " awrdAmount =" & strAmount & " WHERE awrdID=" & strSSNtext
cmd.ExecuteNonQuery()
Else
'no records found so add
strSQLInsert = "INSERT INTO Awarded (awrdID, awrdName, awrdAmount) (strSSNtext, 'strHolder', 'strAmount')"
Dim cmdQuery As New OleDbCommand(strSQLInsert, cn)
cmdQuery.ExecuteNonQuery()
End If
Catch dbException As Exception
MessageBox.Show(dbException.Message)
Finally
cn.Close()
End Try
End Sub