locked
Parameterized Query Error RRS feed

  • Question

  • Can't determine why this message pops up.

    Here is the code.

     Private Sub TransferPhotos(ByVal Acct_No As String, ByVal Facct As String)

            Using cn As New SqlConnection(ConnectionString)
                cn.Open()
                Using cmd1 As New SqlCommand("", cn)
                    Using cmd2 As New SqlCommand("", cn)

                        cmd1.CommandText = "SELECT photo1 FROM appphotos WHERE facct = " & "'" & Acct_No & "'"
                        Dim arrImage1 = cmd1.ExecuteScalar()

                        cmd1.CommandText = "SELECT photo2 FROM appphotos WHERE facct= " & "'" & Acct_No & "'"
                        Dim arrImage2 = cmd1.ExecuteScalar()

                        cmd2.CommandText = "INSERT Into fpphotos " &
                          "(photo1, photo2, photo_date1, photo_date2, facct) " &
                          "VALUES(@photo1, @photo2, @photo_date1, @photo_date2, @facct)"


                        cmd2.Parameters.Add(New SqlClient.SqlParameter("@photo1", SqlDbType.Image)).Value = arrImage1
                        cmd2.Parameters.Add(New SqlClient.SqlParameter("@photo2", SqlDbType.Image)).Value = arrImage2
                        cmd2.Parameters.AddWithValue("@photo_date1", AppMain.datApproveDate.EditValue)
                        cmd2.Parameters.AddWithValue("@photo_date2", AppMain.datApproveDate.EditValue)
                        cmd2.Parameters.AddWithValue("@facct", Facct)


                        Try
                            cmd2.ExecuteNonQuery()
                        Catch SqlExceptionErr As SqlException
                            MessageBox.Show(SqlExceptionErr.Message)
                        End Try

                    End Using
                End Using
            End Using

        End Sub


    ISV using VB.net and SQL Server


    • Edited by Jeff07 Sunday, August 16, 2020 3:55 PM
    Sunday, August 16, 2020 3:53 PM

Answers

  • arrImage1 = nothing


    Is this expected according to the contents of tables and variables?


    • Marked as answer by Jeff07 Monday, August 17, 2020 2:26 PM
    Monday, August 17, 2020 1:40 PM

All replies

  • Hello,

    When this happens what is the value from arrImage1 ?


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, August 16, 2020 4:26 PM
  • Try these simplifications too:

       cmd2.Parameters.Add("@photo1", SqlDbType.Image).Value = arrImage1

       cmd2.Parameters.Add("@photo2", SqlDbType.Image).Value = arrImage2

    By the way, you can try replacing three statements with a single complex INSERT.

    • Edited by Viorel_MVP Sunday, August 16, 2020 5:26 PM
    Sunday, August 16, 2020 5:21 PM
  • Unfortunately your solution produced the same error.  Any other thoughts?

    ISV using VB.net and SQL Server

    Sunday, August 16, 2020 10:43 PM
  • Hi Jeff07,

    Thanks for your feedback.

    This happens when a parameter value is null, so you need to check whether 'arrImage1', 'arrImage2'  and so on is null.

    To fix it you can set parameter value to DbNull when the property is null.

            If String.IsNullOrEmpty(arrImage1) Then 
                cmd2.Parameters.AddWithValue("@photo1", DBNull.Value)
            Else
                cmd2.Parameters.AddWithValue("@photo1", arrImage1)
            End If
            '...

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 17, 2020 9:19 AM
  • arrImage1 = nothing


    ISV using VB.net and SQL Server

    Monday, August 17, 2020 1:32 PM
  • arrImage1 = nothing


    Is this expected according to the contents of tables and variables?


    • Marked as answer by Jeff07 Monday, August 17, 2020 2:26 PM
    Monday, August 17, 2020 1:40 PM
  • No, its not what is expected as it should be an image or NULL.  I determined why the value is nothing and will modify the code so it does not happen.  The suggested code below causes the program to crash, however I thinks I think I can find another way to test the value of arrImage1.

    You guys have put on the right path, thank you very much.

      If String.IsNullOrEmpty(arrImage1) Then 
                cmd2.Parameters.AddWithValue("@photo1", DBNull.Value)
            Else
                cmd2.Parameters.AddWithValue("@photo1", arrImage1)
            End If


    ISV using VB.net and SQL Server

    Monday, August 17, 2020 2:26 PM