function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
nbansal10nbansal10 

Too many SQL Queries 101

Hello Guys..!!

 

I am getting error "Too many SQL Queries 101". Please help me on how to resolve this. I understood that I have to get the "Select" statement out from loop, but not sure how to do this. Any help will be appreciated,

 

public class uploadCSVcontroller {

public Blob contentFile { get; set; }
public String nameFile { get; set; }
public Integer rowCount { get; set; }
public Integer colCount { get; set; }

//User related variables
public List<User> usr{get;set;}
public Datetime lastlogin;
public Id usrId;
public String username;
public boolean isactive;

public List<List<String>> getResults() {
List<List<String>> parsedCSV = new List<List<String>>();
rowCount = 0;
colCount = 0;
if (contentFile != null){
String fileString = contentFile.toString();

parsedCSV = parseCSV(fileString, false);

system.debug('@@@@parsedCSV.get(0): '+parsedCSV.get(0));
system.debug('@@@@parsedCSV: '+parsedCSV);
rowCount = parsedCSV.size();
for (List<String> row : parsedCSV){
if (row.size() > colCount){
colCount = row.size();
}
}
}
return parsedCSV;
}


public static List<List<String>> parseCSV(String contents,Boolean skipHeaders) {
List<List<String>> allFields = new List<List<String>>();

contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",');
contents = contents.replaceAll('""','DBLQT');
List<String> lines = new List<String>();
try {
//lines = contents.split('\n'); //correction: this only accomodates windows files
lines = contents.split('\r'); // using carriage return accomodates windows, unix, and mac files
} catch (System.ListException e) {
System.debug('Limits exceeded?' + e.getMessage());
}
Integer num = 0;
for(String line: lines) {
// check for blank CSV lines (only commas)
if (line.replaceAll(',','').trim().length() == 0) break;

List<String> fields = line.split(',');
List<String> cleanFields = new List<String>();
String compositeField;
Boolean makeCompositeField = false;
for(String field: fields) {
if (field.startsWith('"') && field.endsWith('"')) {
cleanFields.add(field.replaceAll('DBLQT','"'));
} else if (field.startsWith('"')) {
makeCompositeField = true;
compositeField = field;
} else if (field.endsWith('"')) {
compositeField += ',' + field;
cleanFields.add(compositeField.replaceAll('DBLQT','"'));
makeCompositeField = false;
} else if (makeCompositeField) {
compositeField += ',' + field;
} else {
cleanFields.add(field.replaceAll('DBLQT','"'));
}
}

allFields.add(cleanFields);
}
if (skipHeaders) allFields.remove(0);
return allFields;
}

public void resetpwd() {
system.debug('@@@@@ in resetpwd');
List<List<String>> parsedCSV = new List<List<String>>();
if (contentFile != null){
String fileString = contentFile.toString();
parsedCSV = parseCSV(fileString, false);
system.debug('@@@@parsedCSV in resetpwd: '+parsedCSV);
}
Integer count = 0;
system.debug('@@@@parsedCSV.size(): '+ parsedCSV.size());
while(count<parsedCSV.size()) {

usr=[select id,name,LastLoginDate, isactive, Username from User where Username= :parsedCSV.get(count)];
system.debug('_____________________'+usr);

for (User u : usr) {
lastlogin = u.LastLoginDate;
system.debug('@@@@@lastlogin' + lastlogin);
usrId = u.Id;
system.debug('@@@@@usrId' + usrId);
username = u.Username;
system.debug('@@@@@username'+ username);
isactive = u.isactive;
if(lastlogin==null && isactive == true) {

system.resetPassword(usrId, true);
}
}
}
}
}

SfdcStevenSfdcSteven

Instead of

 

while(count<parsedCSV.size()) {
usr=[select id,name,LastLoginDate, isactive, Username from User where Username= :parsedCSV.get(count)];

 

The simplest thing is to just create a separate set of Usernames outside, query them in bulk, then create a map out of the result

 

Set<String> userNames = new Set<String>();
for (int i = 0; i < parsedCSV.size()) userNames.add(parsedCSV.get(i));
 
Map<Id,User> usrMap = new Map<Id,User>([select id,name,LastLoginDate, isactive, Username from User where Username IN :userNames]);
while(count<parsedCSV.size()) {
   user = userMap.get(parsedCSV.get(count));

 My apex is a little rusty, so the syntax could be off.  Sorry

-Steven

 

 

nbansal10nbansal10

Hi, I am not able to understand a few things over here. Please help me understand the same. MArked in bold.  

 

Set<String> userNames = new Set<String>(); for (int i = 0; i < parsedCSV.size()) userNames.add(parsedCSV.get(i)); Map<Id,User> usrMap = new Map<Id,User>([select id,name,LastLoginDate, isactive, Username from User where Username IN :userNames]); while(count<parsedCSV.size()) { user = userMap.get(parsedCSV.get(count));

 

SfdcStevenSfdcSteven

forgot to increment i

 

 for (int i = 0; i < parsedCSV.size();i++) userNames.add(parsedCSV.get(i));

nbansal10nbansal10

What is this user?

 

{ user = userMap.get(parsedCSV.get(count));

nbansal10nbansal10

Also, this will not allow me to compile this.

since Incompatible element type LIST<String> for collection of String in line" userNames.add(parsedCSV.get(i)); "

Can you please try compiling at your end once?

SfdcStevenSfdcSteven

Yeah, I said my apex was rusty.  It was more a jist of what you need to do.  User should probably have been usr.