SELECT MAX(userId) FROM in c#

optiplexgx270

Hi im trying to do a select max(userId) from table; in c# as follows

System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
connection.Open();

// get records from the Bugs table
string commandString =
"SELECT max(accountId) from account";

// create the dataset command object
// and the DataSet
SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, connectionString);

DataSet dataSet = new DataSet();

// fill the dataset object
dataAdapter.Fill(dataSet,"b4nClickTrackLoginInformation");

// Get the one table from the DataSet
DataTable dataTable = dataSet.Tables[0];

if (dataSet.ExtendedProperties.Count!=0)
{
DataRow dataRow = dataSet.Tables[0].Rows[0];
return (int)dataRow["accountId"] ;
}
else
{
return -1;
}


However when the progam runs i never am returned a value for MAX(accountId). i have also tried select max(accountId) as accountId from account;

accountId is a int primary key.


Any help on this one please ???

Thanks in advance.

charlo_b

should it not be:

SELECT max(accountId) as MaxAccountID from account

optiplexgx270

optiplexgx270 wrote:

i have also tried

select max(accountId) as accountId from account;

as well as

select max(accountId) as other varname from account;

in query analyser it works but in the c# code it does not return any values.

bonkey

return (int)dataRow["accountId"] ;

This is looking for a column named 'accountId'.
You don't have on in the output of that query. In fact, given that you appear to be using MSSQL, I'd guess that the column you have currently has no name.

There are two possible solutions :

1) Alias the column as shown by charlo_b, then fix the line I included at the top to specify the alias-name, not accountId.

2) CHange the line I included at the top to refer to a column by 0-based position:

return (int)dataRow[0]

optiplexgx270

its not getting by the

if (dataSet.ExtendedProperties.Count!=0)
{

so the dataset is empty

charlo_b

if (dataSet.ExtendedProperties.Count!=0)


In the past i've found values returned by recordCounts to be misleading....

Comment out this condition and see what happens.

Evil Phil

ExtendedProperties.Count will return the number of custom properties you've set for your DataSet, will it not? I think you're looking for a rowcount but you could always implement a try-catch block instead then reference your DataSet's objects. Something like:

try
{
	DataRow dataRow = dataSet.Tables[0].Rows[0];
	return (int)dataRow[0] ;
}
catch(Exception ex) // Not sure what exact exception you should be catching 
{
	// error handling code here

}

optiplexgx270

thanks guys turned out the the extended properties is not very reliable used this instead and works a treat

if(dataSet.Tables[0].Rows.Count != 0)
{
return (int)dataSet.Tables[0].Rows[0]["acc"] ;
}
else
{
return -1;
}