Asked by:
SQLCommand object having incorrect byte array

Question
-
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.
- Moved by Xingyu ZhaoMicrosoft contingent staff Thursday, October 8, 2020 5:36 AM
Tuesday, September 8, 2020 10:49 AM
All replies
-
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 SubTuesday, September 8, 2020 12:10 PM -
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