|
Subject:
|
Database Queries
|
|
Posted By:
|
frosty
|
Post Date:
|
5/12/2006 3:26:32 PM
|
I am a coldfusion programmer moving to dotnet, and while I am not having a problem populating asp.net controls with data from my sql server. I can't figure out how to identify a data field value from the database in a conditional statement.
EG
If [table].[field] is X
then [do something]
I am sure it is simple, but I have been working on this for 2 days and can only get information on how to populate controls.
Thanks,
|
|
Reply By:
|
Imar
|
Reply Date:
|
5/13/2006 3:46:24 AM
|
Hi there,
Ca you show us some code? There's a big difference in how you should compare depending on where you use this code, and what data objects you are using (e.g. in the markup, in code behind with a dataset or a datareader and so on)
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
|
|
Reply By:
|
frosty
|
Reply Date:
|
5/13/2006 11:27:59 AM
|
In my example, I will keep the code in the codebehind. When the page loads, I would like to pull one record from the jobDetails table where the jobDetailsID is 1.
Lets say there is one bit field in the jobDetails table called requiresAddress
I would like to set the visibility of a form field based on the value of that bit field.
Here is what I have based on code from a wrox book.
if (!Page.IsPostBack )
{
SqlConnection MyConnection;
SqlCommand MyCommand;
DataTable MyDataTable;
SqlDataReader MyReader;
SqlParameter lobParam;
MyConnection = new SqlConnection();
MyConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["rdConnectionString"].ConnectionString;
MyCommand = new SqlCommand();
MyCommand.CommandText =
" SELECT jobDetailsID, requiresAddress FROM jobDetails WHERE jobDetailsID = @jobDtailsID ";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
lobParam = new SqlParameter();
lobParam.ParameterName = "@jobDetailsID";
lobParam.SqlDbType = SqlDbType.Int;
lobParam.Size = 4;
lobParam.Direction = ParameterDirection.Input;
lobParam.Value = "1";
MyCommand.Parameters.Add(lobParam);
MyCommand.Connection.Open();
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
MyDataTable.Load(MyReader);
// This is where I don't know what to do
topinfo.Visible = MyDataTable.requiresAddress;
MyDataTable.Dispose();
MyCommand.Dispose();
MyConnection.Dispose();
}
|
|
Reply By:
|
Imar
|
Reply Date:
|
5/13/2006 6:20:06 PM
|
Hi frosty,
If all you need to do is check a single field, then you don't need the DataTable. Instead, you can read from the reader directly:
if (MyReader .Read())
{
someControl.Visible = MyReader .GetBoolean(3);
}
where 3 is the (zero-based) index of the colum you want to read. If you want to improve the readability of the code, you can use GetOrdinal, that returns the index based on a name:
someControl.Visible = MyReader.GetBoolean(MyReader.GetOrdinal("requiresAddress"))
And if SomeColumn can be null in the database, you can check it before you try to get a boolean:
if (MyReader.IsDBNull(MyReader.GetOrdinal("requiresAddress")))
{
// value is null
}
Hope this helps,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
|
