Insert and Update failed

[Chong]

Please tell me what's wrong with the following code:

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

What I want to do is to either Update the data in the database if it's already exist or Insert if the account does not exist yet. Here's the problem,there is no single data in the table name Awarded, but somehow the cmdSQL.ExecuteReader()doesn't 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.

Many thanks in advance!

Chong

[Edneeis]

Sorry thats not how the ExecuteReader bit works, but there is a better way to do this:

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

Also when posting code enclose it within a code block instead of a quote one and its easier to read. [ vbcode ][ /vbcode ] without the spaces in the brackets.

[Chong]

Thank you very much for the help. I have an error on the Insert query. Here's the error message.

Syntax error in INSERT INTO Statement.

What could this be?

Chong

[Chong]

The Syntax error is because I missed the Values in front of the parenthesis. However, after I put in the word Values in, I got this error No value given for one or more required paramenters. Is this mean the parameters I'm passing contains nothing? I did a debug and found that the Insert query parameters of strSSNtext, 'strHolder', 'strAmount' doesn't show the actually value instead it only show the variable name.

Chong

[Edneeis]

The syntax of your INSERT sql is wrong. I think you forgot the word 'VALUES'.