Display the latest updated data

Question

I have to display the data that currently being updated in the text file. The problem is, I'm using the CONVERT function for the columns that I need to display. The data that being displayed is System.Byte[]. The actual data that I need to display is a varbinary string.

-- This query is displaying the data before updating the status.
query = SELECT CONVERT(varchar(10), Start_RG, 2) AS Start_RG, CONVERT(varchar(10), End_RG, 2) AS End_RG, Status FROM RG WHERE Status = 'New';
command = new SqlCommand(query, cnn);

dR = command.ExecuteReader();

        if (dR.HasRows)
        {
            dR.Close();

            -- This is the query for updating the status to 'In Use'. I'm using the OUTPUT clause to display the data that has been update.
          sql = UPDATE TOP (1) RG SET Status = 'In Use' OUTPUT deleted.Start_RG, deleted.End_RG, deleted.Status WHERE Status = 'New';
          cmd= new SqlCommand(sql, cnn);

            dataReader = cmd.ExecuteReader();
            
             

            using (StreamWriter tw = File.AppendText(Path))
            {
                while (dataReader.Read())
                {
                    
                    tw.WriteLine("assign..\n");
                    tw.WriteLine("Start RG: {0}", Convert.Tostring((byte[])dataReader["Start_RG"]));
                    tw.WriteLine("End RG: {0}", Convert.Tostring((byte[])dataReader["End_RG"]));
                }
            }
                
}

How can I fetch the Start_RG and End_RG that currently updated the status to In Use? Any other suggestion that I can use instead of OUTPUT clause?

There's an error `Incorrect syntax near the keyword 'CONVERT'` — user14376765, Oct 12 '20 at 03:30
OK, so just convert it in C#, first [extract the byte array](https://stackoverflow.com/questions/5371222/getting-binary-data-using-sqldatareader) then [convert to a string](https://stackoverflow.com/questions/3581674/converting-a-byte-to-a-binary-string-in-c-sharp) — Dale K, Oct 12 '20 at 03:33
You have to convert it fully to a string before calling `writeline` lol. Or at least put your string conversion code inside the `writeline` call. — Dale K, Oct 12 '20 at 05:10
I'm sorry, i've misplaced the code in this question. In the coding, i've put the code before writeline. The data that being displayed still System.Byte[]. — user14376765, Oct 12 '20 at 05:25
You're not assigning the results of your conversion to anything. You need `tw.WriteLine("Start RG: {0}", Convert.ToString((byte[])dataReader["Start_RG"]));` — Dale K, Oct 12 '20 at 05:40
The result still the same. It still displaying the System.Byte[] — user14376765, Oct 12 '20 at 05:42
You've misread the information on how to convert to a string. The code you copied only works for a single byte not an array. Check out the answers which convert the entire array. — Dale K, Oct 12 '20 at 05:45

score 0 · Accepted Answer · answered Oct 12 '20 at 06:01

Whatever you are doing using OUTPUT clause is fine. For reading byte[] to string, you can use below approach. Leveraged Stackoverflow answer: https://stackoverflow.com/a/4959269/634935

tw.WriteLine("Start RG: {0}", Encoding.ASCII.GetString(((byte[])dataReader["Start_RG"])); //based on encoding, you need to choose appropriate static method 
                    tw.WriteLine("End RG: {0}", Encoding.ASCII.GetString(((byte[])dataReader["End_RG"]));

Display the latest updated data

1 Answers1