Insert and Update failed

[Chong]

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

 

  Quote

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

[hog]

Your code is hard to follow, could use comments to say what you are doing.

 

Why are you using the second statement to overwrite the first assignment?

 

strName = tb.Name
strName = tb.Text()

 

I can't see that you have declared strSSNtext as anything, but the str prefix suggests you expect it to be a String. If it is then this line:

 

strSQL = "SELECT * FROM Awarded WHERE awrdID=" & strSSNtext

 

should read:

 

strSQL = "SELECT * FROM Awarded WHERE awrdID= '" & strSSNtext & "'"

[hog]

Another point you should use an DBException type to check if the error that occured was due to no records rather than comparing the text output. You'll need to lookup the exact error constant for this.

[Chong]

Thanks for the help. I have revised the script.

 

  Quote

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()

 

strName = tb.Text()

strSSNsubstring = strName.Substring(5, 9)

intSSNnumb = CInt(strSSNsubstring) 'this is the SSN number

strSeperator = "*************************************************"

 

strHolder = lstApplicant.Items(lstApplicant.SelectedIndex).ToString()

 

tb.Text = strName & vbCrLf & strSeperator & vbCrLf & "Awarded: " & strHolder & " scholarship" & _

vbCrLf & "SSN#: " & intSSNnumb & vbCrLf & "Amount Award: $" & strAmount

cn.Open()

strSQL = "SELECT COUNT(*) FROM Awarded WHERE awrdID=" & intSSNnumb

Dim cmdSQL As New OleDbCommand(strSQL, cn)

 

Try

Dim count As Integer = cmdSQL.ExecuteScalar

If count > 0 Then

'records found so update

cmd.CommandText = "UPDATE Awarded SET awrdName=" & "'" & strHolder & "'" & " awrdAmount =" & strAmount & " WHERE awrdID=" & intSSNnumb

cmd.ExecuteNonQuery()

Else

'records not found so add to database

strSQLInsert = "INSERT INTO Awarded (awrdID, awrdName, awrdAmount) VALUES(intSSNnumb, 'strHolder', 'strAmount')" 'this line is the one that causes the error to be generated

Dim cmdQuery As New OleDbCommand(strSQLInsert, cn)

cmdQuery.ExecuteNonQuery()

End If

Catch dbException As Exception

MessageBox.Show(dbException.Message)

End Try

cn.Close()

End Sub

 

The error generated from the Insert query is 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

[hog]

I haven't created an Insert command like this as yet, but I think this line should not have the quotes around the variable names

 

VALUES(intSSNnumb, 'strHolder', 'strAmount')

 

This would mean you are passing the text strHolder and strAmount to the query rather than the variable value.

 

Try this:

 

VALUES(intSSNnumb, strHolder, strAmount)

[Chong]

Thanks! I got it. It's working now.

 

Chong