How to get first record(or random record) from table?

Question

This is follow up on the earlier thread: how-to-write-a-method-to-generate-randomunique-str.html

Backend database: Access file

I decide to make it simple from an earlier thread, so post this new thread. Just use a third party website random.org to generate enough random/unique string, then save to Access table TableReservedString. Then assign a string to each user, then delete the string from the table (after being used).

How should I write below code?

There are three things I would like to do:

1. Getting a record (first record or random record, it does not matter) from TableReservedString
2. Then assign the record value from step 1 to AssignedStr (I will insert this value to another table later)
3. Delete AssignedStr from TableReservedString (I mean deleting the record WHERE FieldString = AssignedStr , as shown below)

Thanks.

         private void buttonGettingStringFromAccessTable_Click(object sender, EventArgs e)  
         {  
 //When user clicks the button, the program will assign a unique string for his/her own record.  
//Table name: TableReservedString  
//Field name (only one field): FieldString  
      
      
                 using (OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString))  
                 {  
                     OdbcCommand cmd = new OdbcCommand("getting a record/first record or random record from TableReservedString", Cn);  
      
                     if (Cn.State != ConnectionState.Open)  
                     {  
                         Cn.Open();  
                     }  
      
                     cmd.ExecuteNonQuery();  
      
                 }  
  
 string AssignedStr;  
 AssignedStr = ....; //from the cmd result  
  
 string sqlDelete = "DELETE FROM TableReservedString WHERE FieldString = '" + AssignedStr + "'";  
                    OdbcCommand cmdDelete = new OdbcCommand(sqlDelete, Cn);  
                    cmdDelete.ExecuteNonQuery();      
      
         }  

Answer 1

Hi VAer-4038,
You need to use OdbcCommand.ExecuteReader method instead of OdbcCommand.ExecuteNonQuery method.
The command executes this stored procedure when you call ExecuteReader, and then you can use OdbcDataReader.Read method to read through the data.
Here is my test code you can refer to.

string AssignedStr;  
private void button1_Click(object sender, EventArgs e)  
{  
    using (OdbcConnection Cn = new OdbcConnection(connectionString)) //Access database  
    {  
        Cn.Open();  
        OdbcCommand cmd = new OdbcCommand("SELECT TOP 1 * FROM TableReservedString", Cn);  
        OdbcDataReader reader = cmd.ExecuteReader();  
        while (reader.Read())  
        {  
            AssignedStr = reader[0].ToString(); // index 0 will retrieve your first colum in my table  
            // Console.WriteLine(AssignedStr);  
        }  
        string sqlDelete = "DELETE FROM TableReservedString WHERE FieldString ='" + AssignedStr + "'";  
        OdbcCommand cmdDelete = new OdbcCommand(sqlDelete, Cn);  
        cmdDelete.ExecuteNonQuery();  
    }  
}  

Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.