Please tell me whats wrong with the following code:
Many thanks in advance!
Chong
What I want to do is to either Update the data in the database if its already exist or Insert if the account does not exist yet. Heres the problem,there is no single data in the table name Awarded, but somehow the cmdSQL.ExecuteReader() doesnt generate an error of "No data exists for the row/column." so the program will jump to the Catch Exception section and do an Insert since there is no account exist in the Award table yet.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()
strSQL = "SELECT * FROM Awarded WHERE awrdID=" & strSSNtext
Dim cmdSQL As New OleDbCommand(strSQL, cn)
Try
cmdSQL.ExecuteReader()
cn.Close()
cn.Open()
cmd.CommandText = "UPDATE Awarded SET awrdName=" & "" & strHolder & "" & " awrdAmount =" & strAmount & " WHERE awrdID=" & strSSNtext
cmd.ExecuteNonQuery()
Catch dbException As Exception
MessageBox.Show(dbException.Message)
Dim strError As String = dbException.Message.ToString()
If strError = "No data exists for the row/column." Then
cn.Close()
cn.Open()
strSQLInsert = "INSERT INTO Awarded (awrdID, awrdName, awrdAmount) (strSSNtext, strHolder, strAmount)"
Dim cmdQuery As New OleDbCommand(strSQLInsert, cn)
cmdQuery.ExecuteNonQuery()
End If
End Try
cn.Close()
End Sub
Many thanks in advance!
Chong