straightforward way to programmatically retrieve string from db?

Hello -- I'm using asp.net 2 in visual web developer and I'm drawing a blank finding documentation on on this fairly simple task. Since this is the novice's thread, I felt bold enough to ask.

I'd like to programmatically retrieve a string from the aspnet_usermembership database -- the select statement would be something like the below. Can anyone point me at example code to do this programmatically -- so I end up with the LoweredEmail string from the DB? I guess I could either read it directly from the DB, or from a gridview control on the page -- but I'm new to SQL data manipulation and althoug I've seen examples nothing seems to work in this environment and it's probably simpler than I think to those who know what they;re doing..

Thanks for pointers.

"SELECT LoweredEmail FROM aspnet_Membership WHERE username=@dotnet.itags.org.Username"

Hey, just an idea. You can use SqlCommand.ExecuteScalar() to return a single value from database, assuming that your select SQL statment returns a scalar value. The method returns an object type so you need to comvert, in your case, a string type, like something below

...

dim LoweredEmail as String

dim SqlCmd as New SqlCommand("sql stmt", SqlConnObt)

LoweredEmail = CType(SqlCmd.ExecuteScalar(), String)

...

Thanks Bernie I'll give that a shot -- looks to be exactly what I needed.

Richard.

Worked fine -- just needed to do this in a two-stage process to get the GUID for the user from the username, which teyn gets teh email address. So added

Dim SqlEmailCmdAsNew SqlCommand("SELECT LoweredEmail FROM aspnet_Membership WHERE userid=@.Guid", myMembershipConnection)

SqlEmailCmd.Parameters.AddWithValue("@.Guid", userid)

Strange Error when executing a function

in my code-behind i call a class method that returns a string value...
i run step by step and works great!!

but when i run without breakpoint... sometimes i get error because my string is empty...
and then i run again step by step... and the surprise! works fine again...

why is this happening??

this is my code-behind

string param = " ";

param +=ConfigurarLoja.RetParams( );

Session.Add("ssnHabItemVincPromo",param);

if(param == " ")

{

response.redirect("~/Erro.aspx");

}

And this is my class method:

publicstaticstring RetParams()

{

string param ="";

int cd_empresa =Convert.ToInt32(HttpContext.Current.Session["ssnCdEmpresa"]);

string sql ="SELECT TOP 1 * FROM LjConfig WHERE cd_empresa = " + cd_empresa +" AND ISNULL(DATEADD(dd, 01, dt_exclusao), GETDATE()) > GETDATE() ORDER BY dt_inclusao DESC";SqlConnection con =newSqlConnection(ConfigurationManager.ConnectionStrings["StringDeConexao"].ConnectionString);

con.Open();

SqlCommand cmd =newSqlCommand(sql, con);

SqlDataReader dr = cmd.ExecuteReader();

dr.Read();

if (dr.HasRows)

{

param ="-" + dr["cd_empresa"].ToString().Trim();

}

con.Close();

return param;

}

ops... it seems solved...
i change the sql command 'WHERE'... i think it was a problem with the GETDATE... now its working fine...

AND (ISNULL(DATEADD(dd, 01, dt_exclusao), DATEADD(dd, 01, GETDATE())) > GETDATE()) "

Strange exception.

I am using the PetShop example's ExecuteReader function:
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{...}

However, I alwasy get the following exception:

Outter exception:
The type initializer for threw an exception.

Inner exception:
Object reference not set to an instance of an object.

One of the calling is:

SqlDataReader reader =
SqlHelper.ExecuteReader(SqlHelper.ConnectionString LocalTransaction,
CommandType.Text, SQL_SELECT_EVENTS_BETWEEN_DATES, null)

Which SqlHelper.ConnectionStringLocalTransaction has a valid connection
string, CommandType.Text is built-in type, SQL_SELECT_EVENTS_BETWEEN_DATES is
a valid string constant SQL statement. I tried to pass valid parameters too
and got the same exception?Matt,

Judging on the error description it looks like static constructor or the
class or initializer of one of it's static variables causes the error and not
that specific method - calling this method causes the class type to
instantiate in memory.

HTH

"Matt" wrote:

Quote:

Originally Posted by

I am using the PetShop example's ExecuteReader function:
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{...}
>
However, I alwasy get the following exception:
>
Outter exception:
The type initializer for threw an exception.
>
Inner exception:
Object reference not set to an instance of an object.
>
One of the calling is:
>
SqlDataReader reader =
SqlHelper.ExecuteReader(SqlHelper.ConnectionString LocalTransaction,
CommandType.Text, SQL_SELECT_EVENTS_BETWEEN_DATES, null)
>
Which SqlHelper.ConnectionStringLocalTransaction has a valid connection
string, CommandType.Text is built-in type, SQL_SELECT_EVENTS_BETWEEN_DATES is
a valid string constant SQL statement. I tried to pass valid parameters too
and got the same exception?
>
>

I use the file SQLHelper.cs the way exactly as the PetShop 4.

SQLHelper.cs:

namespace PetShop.DBUtility {
public abstract class SqlHelper {
......
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);

try {
PrepareCommand(cmd, conn, null, cmdType, cmdText,
commandParameters);
SqlDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection) ;
cmd.Parameters.Clear();
return rdr;
}
catch {
conn.Close();
throw;
}
}
.....
}

"Sergey Poberezovskiy" wrote:

Quote:

Originally Posted by

Matt,
>
Judging on the error description it looks like static constructor or the
class or initializer of one of it's static variables causes the error and not
that specific method - calling this method causes the class type to
instantiate in memory.
>
HTH
>
"Matt" wrote:
>

Quote:

Originally Posted by

I am using the PetShop example's ExecuteReader function:
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{...}

However, I alwasy get the following exception:

Outter exception:
The type initializer for threw an exception.

Inner exception:
Object reference not set to an instance of an object.

One of the calling is:

SqlDataReader reader =
SqlHelper.ExecuteReader(SqlHelper.ConnectionString LocalTransaction,
CommandType.Text, SQL_SELECT_EVENTS_BETWEEN_DATES, null)

Which SqlHelper.ConnectionStringLocalTransaction has a valid connection
string, CommandType.Text is built-in type, SQL_SELECT_EVENTS_BETWEEN_DATES is
a valid string constant SQL statement. I tried to pass valid parameters too
and got the same exception?

Matt,

I do not beleive that you can step into this method while debugging, do you?
As I mentioned in my previous post, I has this type of exception when the
class static constructor throws an error - so seeing the method code does not
help much :-(

If you can post the class constructor (in case one is present) and all
static initializers, such as following:
private static object _myCustom = new Custom();

Alternatively, if you can post here the link to download the petshot
application, I will try to investigate it further - otherwise there is not
enough info for me to make any conclusions...

I would prefer the first option though - less time for me :-)

"Matt" wrote:

Quote:

Originally Posted by

I use the file SQLHelper.cs the way exactly as the PetShop 4.
>
SQLHelper.cs:
>
namespace PetShop.DBUtility {
public abstract class SqlHelper {
.....
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
>
try {
PrepareCommand(cmd, conn, null, cmdType, cmdText,
commandParameters);
SqlDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection) ;
cmd.Parameters.Clear();
return rdr;
}
catch {
conn.Close();
throw;
}
}
....
}
>
"Sergey Poberezovskiy" wrote:
>

Quote:

Originally Posted by

Matt,

Judging on the error description it looks like static constructor or the
class or initializer of one of it's static variables causes the error and not
that specific method - calling this method causes the class type to
instantiate in memory.

HTH

"Matt" wrote:

Quote:

Originally Posted by

I am using the PetShop example's ExecuteReader function:
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{...}
>
However, I alwasy get the following exception:
>
Outter exception:
The type initializer for threw an exception.
>
Inner exception:
Object reference not set to an instance of an object.
>
One of the calling is:
>
SqlDataReader reader =
SqlHelper.ExecuteReader(SqlHelper.ConnectionString LocalTransaction,
CommandType.Text, SQL_SELECT_EVENTS_BETWEEN_DATES, null)
>
Which SqlHelper.ConnectionStringLocalTransaction has a valid connection
string, CommandType.Text is built-in type, SQL_SELECT_EVENTS_BETWEEN_DATES is
a valid string constant SQL statement. I tried to pass valid parameters too
and got the same exception?
>
>

Strange exception.

I am using the PetShop example's ExecuteReader function:
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters
)
{...}
However, I alwasy get the following exception:
Outter exception:
The type initializer for threw an exception.
Inner exception:
Object reference not set to an instance of an object.
One of the calling is:
SqlDataReader reader =
SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction,
CommandType.Text, SQL_SELECT_EVENTS_BETWEEN_DATES, null)
Which SqlHelper.ConnectionStringLocalTransaction has a valid connection
string, CommandType.Text is built-in type, SQL_SELECT_EVENTS_BETWEEN_DATES i
s
a valid string constant SQL statement. I tried to pass valid parameters too
and got the same exception?Matt,
Judging on the error description it looks like static constructor or the
class or initializer of one of it's static variables causes the error and no
t
that specific method - calling this method causes the class type to
instantiate in memory.
HTH
"Matt" wrote:

> I am using the PetShop example's ExecuteReader function:
> public static SqlDataReader ExecuteReader(string connectionString,
> CommandType cmdType, string cmdText, params SqlParameter[] commandParamete
rs)
> {...}
> However, I alwasy get the following exception:
> Outter exception:
> The type initializer for threw an exception.
> Inner exception:
> Object reference not set to an instance of an object.
> One of the calling is:
> SqlDataReader reader =
> SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction,
> CommandType.Text, SQL_SELECT_EVENTS_BETWEEN_DATES, null)
> Which SqlHelper.ConnectionStringLocalTransaction has a valid connection
> string, CommandType.Text is built-in type, SQL_SELECT_EVENTS_BETWEEN_DATES
is
> a valid string constant SQL statement. I tried to pass valid parameters to
o
> and got the same exception?
>
I use the file SQLHelper.cs the way exactly as the PetShop 4.
SQLHelper.cs:
namespace PetShop.DBUtility {
public abstract class SqlHelper {
.....
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters
)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try {
PrepareCommand(cmd, conn, null, cmdType, cmdText,
commandParameters);
SqlDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch {
conn.Close();
throw;
}
}
....
}
"Sergey Poberezovskiy" wrote:
> Matt,
> Judging on the error description it looks like static constructor or the
> class or initializer of one of it's static variables causes the error and
not
> that specific method - calling this method causes the class type to
> instantiate in memory.
> HTH
> "Matt" wrote:
>
Matt,
I do not beleive that you can step into this method while debugging, do you?
As I mentioned in my previous post, I has this type of exception when the
class static constructor throws an error - so seeing the method code does no
t
help much :-(
If you can post the class constructor (in case one is present) and all
static initializers, such as following:
private static object _myCustom = new Custom();
Alternatively, if you can post here the link to download the petshot
application, I will try to investigate it further - otherwise there is not
enough info for me to make any conclusions...
I would prefer the first option though - less time for me :-)
"Matt" wrote:
> I use the file SQLHelper.cs the way exactly as the PetShop 4.
> SQLHelper.cs:
> namespace PetShop.DBUtility {
> public abstract class SqlHelper {
> .....
> public static SqlDataReader ExecuteReader(string connectionString,
> CommandType cmdType, string cmdText, params SqlParameter[] commandParamete
rs)
> {
> SqlCommand cmd = new SqlCommand();
> SqlConnection conn = new SqlConnection(connectionString);
> try {
> PrepareCommand(cmd, conn, null, cmdType, cmdText,
> commandParameters);
> SqlDataReader rdr =
> cmd.ExecuteReader(CommandBehavior.CloseConnection);
> cmd.Parameters.Clear();
> return rdr;
> }
> catch {
> conn.Close();
> throw;
> }
> }
> ....
> }
> "Sergey Poberezovskiy" wrote:
>