Handling FileNotFound Exception

Question

cn.Open()
    Dim query As String
    query = "INSERT INTO Documents(AdmissionNumber,FullName,LeavingCertificate,KCPEResultSlip,BirthCertificate,MedicalCertificate,ParentOrGuardianPhoto,ParentGuardianIDFront,ParentGuardianIDBack,AnyOtherDocument) VALUES('" & Tbx1.Text & "','" & Tbx2.Text & "', @LeavingCertificate,@KCPEResultSlip,@BirthCertificate,@MedicalCertificate,@ParentOrGuardianPhoto,@ParentGuardianIDFront,@ParentGuardianIDBack,@AnyOtherDocument)"
    cmd = New SqlCommand(query, cn)
    cmd.Parameters.Add(New SqlClient.SqlParameter("@LeavingCertificate", SqlDbType.Image)).Value = IO.File.ReadAllBytes(OpenFileDialog1.FileName)
    cmd.Parameters.Add(New SqlClient.SqlParameter("@KCPEResultSlip", SqlDbType.Image)).Value = IO.File.ReadAllBytes(OpenFileDialog2.FileName)
    cmd.Parameters.Add(New SqlClient.SqlParameter("@BirthCertificate", SqlDbType.Image)).Value = IO.File.ReadAllBytes(OpenFileDialog3.FileName)
    cmd.Parameters.Add(New SqlClient.SqlParameter("@MedicalCertificate", SqlDbType.Image)).Value = IO.File.ReadAllBytes(OpenFileDialog4.FileName)
    cmd.Parameters.Add(New SqlClient.SqlParameter("@ParentOrGuardianPhoto", SqlDbType.Image)).Value = IO.File.ReadAllBytes(OpenFileDialog5.FileName)
    cmd.Parameters.Add(New SqlClient.SqlParameter("@ParentGuardianIDFront", SqlDbType.Image)).Value = IO.File.ReadAllBytes(OpenFileDialog6.FileName)
    cmd.Parameters.Add(New SqlClient.SqlParameter("@ParentGuardianIDBack", SqlDbType.Image)).Value = IO.File.ReadAllBytes(OpenFileDialog7.FileName)
    cmd.Parameters.Add(New SqlClient.SqlParameter("@AnyOtherDocument", SqlDbType.Image)).Value = IO.File.ReadAllBytes(OpenFileDialog8.FileName)

    Reader = cmd.ExecuteReader
    MsgBox("Students' Documents Added Successfully to The Regista.", MsgBoxStyle.Information + MsgBoxStyle.OkOnly)
    Btn9_Click(sender, e)


End Sub

' Hi comrades. You see this code above? This item called (AnyOtherDocument) is optional. The user can either provide it or Not. But when not provided, am getting an Exception error. How do I handle this?

score 0 · Accepted Answer · edited May 23 '17 at 12:16

Just check with File.Exists. If there is no file then set the parameter to DbNull.Value otherwise read the file

Dim exists = File.Exists(OpenFileDialog8.FileName)

.....
cmd.Parameters.Add(New SqlClient.SqlParameter( _ 
     "@AnyOtherDocument", SqlDbType.Image)).Value _
     = If(exists, IO.File.ReadAllBytes(OpenFileDialog8.FileName), _
                  DBNull.Value)

By the way, you are using parameters for all of your image values, why don't you use them also for the two string values? The usefulness of parameters is not tied to the DataType of the value to pass. You use them for every value to avoid Sql Injection and parsing problems

query = "INSERT INTO Documents " & _
"(AdmissionNumber,FullName,LeavingCertificate,KCPEResultSlip," & _ 
"BirthCertificate,MedicalCertificate,ParentOrGuardianPhoto," & _
"ParentGuardianIDFront,ParentGuardianIDBack,AnyOtherDocument) " & _
"VALUES(@admnum, @fullname,  @LeavingCertificate,@KCPEResultSlip, " & _
"@BirthCertificate,@MedicalCertificate,@ParentOrGuardianPhoto," & _
"@ParentGuardianIDFront,@ParentGuardianIDBack,@AnyOtherDocument)"

cmd = New SqlCommand(query, cn)
cmd.Parameters.Add("@admnum", SqlDbType.NVarChar).Value = Tbx1.Text 
cmd.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = Tbx2.Text 
cmd.Parameters.Add("@LeavingCertificate", SqlDbType.Image).Value = IO.File.ReadAllBytes(OpenFileDialog1.FileName)
.... and so on with all the other parameters ....

Steve am so new in this. How I wish you can rewrite that code the way u are putting it and post it here i review. — Henry Gathigira, May 07 '16 at 19:59
I have just added a line to check if the file exists befor opening the connection, then I have changed the line that creates the @AnyOtherDocument parameter. What is not clear here? — Steve, May 07 '16 at 20:07
This one is okay in fact it has worked just so perfectly. Am saying you have told me that am using parameters for all my image values. Is there any better way i can do this? — Henry Gathigira, May 07 '16 at 20:11
I wish you use parameters for everything. You have two values (strings) that you don't pass as parameters. You should. — Steve, May 07 '16 at 20:13

Handling FileNotFound Exception

1 Answers1