Insert and Update failed

Chong

Well-known member
Joined
Apr 4, 2003
Messages
79
Please tell me whats 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 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.

Many thanks in advance!

Chong
 
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?

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

I cant 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:

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

should read:

Code:
strSQL = "SELECT * FROM Awarded WHERE awrdID= " & strSSNtext & ""
 
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. Youll need to lookup the exact error constant for this.
 
Thanks for the help. I have revised the script.

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 Im passing contains nothing? I did a debug and found that the Insert query parameters of strSSNtext, strHolder, strAmount doesnt show the actually value instead it only show the variable name.

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

Code:
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:

Code:
VALUES(intSSNnumb, strHolder, strAmount)
 
Back
Top