Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
SQLCommand object having incorrect byte array RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    My .NET application uses SQLCommand to execute queries and the DB SPs. In one such SP that also returns the SQL 'timestamp' and that is read as byte array into the application. This byte array is incorrect causing DB to throw errors in the workflow later.

    Here is the code:

    public void ExecCommand(string strCommand,
                            CommandType CommandType,
                            IParameters IParameters,
                            ref IRows[] IRowsArr)
    {
                    SqlDataReader dataReader = null;

        if (this.m_sqlTransaction == null) //if not in transaction
            OpenConnection();

        SqlCommand dbCommand = this.CreateCommand();
        dbCommand.CommandType = CommandType;
        dbCommand.CommandText = Normalize(strCommand);
                                try
                    {
                        // Trace the sql Command string and its Execution Start Time
                        using (var sqlLogger = CreateSQLLogger(dbCommand))
                        {
                            dataReader = dbCommand.ExecuteReader();
                        }
                    }
                    catch (DbException ex)
                    {
                        LogError(ex);
                        throw;
                    }

    }

    Workflow:

    -Create a new record, add some parameters. It first asks to save the information.

    -Information is saved and a timestamp is returned by the DB SP.

    -This timestamp is converted into bye array in the .NET code and here byte array is wrong

    -Add some additional parameters and save the same record. .NET code passes on the same byte array to the SP that has timestamp check, causing mismatch and error is thrown.

    Whereas, the follow workflow works fine -

    -Create a new record, add some parameters. It first asks to save the information. -

    -Information is saved and a timestamp is returned by the DB SP.

    -This timestamp is converted into bye array in the .NET code and here byte array is wrong again.

    -Close the record and reopen(this causes calling another SP that returns the timestamp) and add some new parameters and save.


    Tuesday, September 8, 2020 10:49 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
            Dim connection As SqlConnection = Nothing
            Try
                Dim img As FileUpload = CType(QuestionUp, FileUpload)
                Dim imgByte As Byte() = Nothing
                If img.HasFile AndAlso Not img.PostedFile Is Nothing Then
                    'To create a PostedFile
                    Dim File As HttpPostedFile = QuestionUp.PostedFile
                    'Create byte Array with file len
                    imgByte = New Byte(File.ContentLength - 1) {}
                    'force the control to load data in array
                    File.InputStream.Read(imgByte, 0, File.ContentLength)
                End If
                ' Insert the employee name and image into db
                Dim conn As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
                connection = New SqlConnection(conn)

                connection.Open()

                Dim sql As String = "INSERT INTO QN_QUESTION_MAST(QUES_CODE,Ques_ImageData) VALUES(@QUES_CODE, @Ques_ImageData) SELECT @@IDENTITY"

                Dim cmd As SqlCommand = New SqlCommand(sql, connection)
                cmd.Parameters.AddWithValue("@QUES_CODE", txtQuestionCode.Text)
                cmd.Parameters.AddWithValue("@Ques_ImageData", imgByte)
                Dim id As Integer = Convert.ToInt32(cmd.ExecuteScalar())
                lblMsg.Text = String.Format("QUES_CODE is {0}", id)
                Image2.ImageUrl = "~/ShowImage.ashx?id=" & id
            Catch
                lblMsg.Text = "There was an error"
            Finally
                connection.Close()
            End Try
        End Sub
    Tuesday, September 8, 2020 12:10 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sandeep_varian123,

    Thank you for posting here.

    According to your problem, I need more information to make a test.

    Could you provide some related code about 'IParameters', 'IRows', 'Normalize' and 'CreateSQLLogger' ? It will help us analyze your problem.

    We are waiting for your update.

    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.

    Wednesday, September 9, 2020 8:11 AM