Category Archives: AccessDataSource

[RESOLVED] populate DDL using data from database

I have a simple ASP.NET assignment to complete. We are supposed to create a simple database with one table. That table should contain records that contain a question and 3 possible choices. We need to populate a DDL with the 3 possible choices. But, as far as I can tell, you can only populate a DDL with a FIELD of values, not select values from one row. So, I'm confused.

Is it possible to get select values from a row of data in a databse using an AccessDataSource and populate a DDL with them?

Ideally, for this requirement table should be created like this:

 

And you can fill out the answer dropdownlist with this query like:

Select AnswerID, AnswerText from tblAnswer where QuestionID = @CurrentQuestionID

 

Hope it helps./.

This makes sense. Although the instructor required that we only use one table for all of the data including the question text and the 3 answers. I'll try your suggestion though. I'll be happy if I can get it to work like this. I'm not that familiar with this stuff yet but I think that your suggestion invloves using some "code-behind" to make this happen. The way I'm doiing it now is through the Data Source Wizard. So I'm not quite sure if I'll be able to figure this out but I'll give it a shot.

Using the Wizard you can only set the name and value properties to a FIELD. No matter what select statement I build I can only get values from a FIELD, not values from an individual record. Like, I want to get the 3 answers from one record and populate the DDL with them. I don't know if it's possible to do that.

 

Thanks!

Ok. You can do that as well.

The query will be a simple UNION ALL Query.

Check this out:

select AnswerText1 AS 'Answers' from tblQuestionAnswer
Where QuestionID = 1
union all
select AnswerText2 AS 'Answers' from tblQuestionAnswer
Where QuestionID = 1
union all
select AnswerText3 AS 'Answers' from tblQuestionAnswer
Where QuestionID = 1

I have used SqlDataSource instead of AccessDataSource here:

 

 

<asp:DropDownList ID="ddlAnswer" runat="server" DataSourceID="SqlDataSource1" DataTextField="Answers">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:testdatabaseConnectionString %>"
    SelectCommand="select AnswerText1 AS 'Answers' from tblQuestionAnswer union all select AnswerText2 AS 'Answers' from tblQuestionAnswer union all select AnswerText3 AS 'Answers' from tblQuestionAnswer">
</asp:SqlDataSource>

 

 

hope it helps./.

Right on. That sounds more like what I need to do. I forgot about UNION ALL (just had a DB course last semester, so that stuff is slowly coming back to me now). I'll give this idea a shot. I'll report back one way or the other.

 

Thanks!

Thanks! Your UNION ALL solution worked. I had to change the code to work with an Access Data Source but it worked. I still have to tweak the select statement a little too so it grabs grab the exact data values from my DB that I need in the DDL but it's working.

 

Thanks again!

[RESOLVED] How to define a DataList in the code behind (C#)

Hi,

I want to define a DataList in the code behind but i don't know how to do it.

I just just know how to do it in the ASP.NET:

Data Source DataList

<asp:AccessDataSource ID="adsExample" runat="server"
            DataFile="~/MyDatabase.mdb"
           
            SelectCommand="SELECT Best4.[Year], Countries.Flag, Countries.ENAME AS Champion FROM (Best4 INNER JOIN Champions ON Best4.CHID=Champions.CHID) INNER JOIN Countries ON Champions.CountryID=Countries.CountryID ORDER BY Best4.[Year];">
            </asp:AccessDataSource>

DataList

<asp:DataList ID="DataList1" runat="server" DataSourceID="adsExample">
            <ItemTemplate>
                <asp:Label ID="YearLabel" runat="server" Text='<%# Eval("Year") %>' />
                <asp:Image ID="FlagImage" runat="server" ImageUrl='<%# Eval("Flag") %>' />
                <asp:Label ID="ChampionLabel" runat="server" Text='<%# Eval("Champion") %>' />
                <br />
            </ItemTemplate>
        </asp:DataList>

When i am running my Visual Web Developper, the result is a list of x numbers of three values:

1999 [an image (Canadian Flag)] Canada

2003 [an image (US Flag)] USA

2005 [an image (Indian Flag)] India

...

 

I know how to create my Access Data Base source in C#, but i don't know how to define a DataList.

Can you help me please ?

Thank you very much for your attention.

Look at datasets and data tabeles in the System.data dll.  There is also a file called a dataset which you can set your connections and it will do the rest when you point it ot the DB and table or Stored Proc.  Basically it creates a class with methods to handle the data.  Look at the Web Forms section under data controls to learn more. 

Hi Phil,

You're concerned about creating a dynamic DataList control, so you will need to add a new class that inherite from ITemplate interface in aim to create an Item Template for your list.

you could test the following code:

classe< My Template: 

 

using System;
using System.Web.UI.WebControls;
using System.Web.UI;

namespace Test
{
    public class MyTemplate: ITemplate
    {
     //A variable to hold the type of ListItemType.
    ListItemType _templateType;
    //A variable to hold the column name.
    string _columnName;
    //Constructor where we define the template type and column name.
    public MyTemplate(ListItemType type, string colname)
    {
        //Stores the template type.
        _templateType = type;
        //Stores the column name.
        _columnName = colname;
    }
    void ITemplate.InstantiateIn(System.Web.UI.Control container)
    {
        switch (_templateType)
        {
         case ListItemType.Item:
                //Creates a new label control and add it to the container.
                Label tb1 = new Label();
                tb1.ID = "?";
                tb1.EnableViewState = true;
                tb1.DataBinding += new EventHandler(tb1_DataBinding);
                container.Controls.Add(tb1);
                break;
        }
    }
    void tb1_DataBinding(object sender, EventArgs e)
    {
        Label txtdata = (Label)sender;
         DataListItem container = (DataListItem)txtdata.NamingContainer;
        object dataValue = DataBinder.Eval(container.DataItem, _columnName);
        if (dataValue != DBNull.Value)
        {
            txtdata.Text = dataValue.ToString();
        }
    }
    }
}

 

Default Page :

using System;
using System.Web.UI.WebControls;

namespace Test
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataList datalist = ConstructDataList("Year");
            // declare the Source provider
            AccessDataSource aDS = new AccessDataSource();
            aDS.ID = "adsExample";
            aDS.DataFile = "~/MyDatabase.mdb";
            aDS.SelectCommand = "SELECT Best4.[Year], Countries.Flag, Countries.ENAME AS Champion FROM (Best4 INNER JOIN Champions ON Best4.CHID=Champions.CHID) INNER JOIN Countries ON Champions.CountryID=Countries.CountryID ORDER BY Best4.[Year];";
           // populate the data
            datalist.DataSourceID = "adsExample";
            datalist.DataBind();
            PlaceHolder1.Controls.Add(datalist);
        }

        
        public DataList ConstructDataList(string field)
        {
            DataList dataList = new DataList();
            dataList.ItemTemplate = new MyTemplate(ListItemType.Item, field);
            return dataList;
        }
    }
}

[RESOLVED] Dynamically change an SQL statement

I'd like to have a standard sql select statement that depending on user input changes to alternative one.

I've written code similar to this:

if (something is true....)

{
SQLdataSRC1.SelectCommand = "Select *...  etc";

// where SQLdataSRC1 is the name of the sql select statement datasource in the front end code.
}

I've put this code in the onload event and in the SQL datasource event handler but still it gets ignored - and only the SQL select statement in the front end code is recognised.

What am I doing wrong?

Can you show how the two different options compare? And where exactly within the code-behind have you put your conditional code?

 

I think best place to alter the SELECT Command is the SQLDataSource Selecting event.

I seem to have fixed it.

I was adding the new select statement to the onSelecting event - and I discovered that if I add the code to the onLoad event that it seems to work. Take a look at the code I have pasted below.

--------------------------------------------------------------------------------------

protected void Page_Load(object sender, EventArgs e)
    {
        if
           (Request.QueryString["LinkID"] == "11")
        {
            AccessDataSource2.SelectCommand = "SELECT * FROM [BizSales] WHERE ([id] = 170)";
        }
        if
          (Request.QueryString["LinkID"] == "12")
        {
            AccessDataSource2.SelectCommand = "SELECT * FROM [BizSales] WHERE ([id] = 165)";
        }

    }

-----------------------------------------------------------------------------------------------

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
        DataKeyNames="id" DataSourceID="AccessDataSource2" Height="50px"
        onpageindexchanging="DetailsView1_PageIndexChanging" Width="125px">
        <Fields>
            <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False"
                ReadOnly="True" SortExpression="id" />
            <asp:BoundField DataField="AdType" HeaderText="AdType"
                SortExpression="AdType" />
            <asp:BoundField DataField="CompanyName" HeaderText="CompanyName"
                SortExpression="CompanyName" />
            <asp:BoundField DataField="BodyTxt" HeaderText="BodyTxt"
                SortExpression="BodyTxt" />
            <asp:BoundField DataField="WebLink" HeaderText="WebLink"
                SortExpression="WebLink" />
            <asp:BoundField DataField="Logo" HeaderText="Logo" SortExpression="Logo" />
            <asp:BoundField DataField="Ranking" HeaderText="Ranking"
                SortExpression="Ranking" />
            <asp:BoundField DataField="DateAdded" HeaderText="DateAdded"
                SortExpression="DateAdded" />
            <asp:BoundField DataField="RemovalDate" HeaderText="RemovalDate"
                SortExpression="RemovalDate" />
            <asp:BoundField DataField="StartDate" HeaderText="StartDate"
                SortExpression="StartDate" />
        </Fields>
    </asp:DetailsView>
    <asp:AccessDataSource ID="AccessDataSource2" runat="server"
        DataFile="~/App_Data/Biz.mdb" 
        SelectCommand="SELECT * FROM [BizSales] WHERE ([id] = 160)"
        onselecting="AccessDataSource2_Selecting1">
   </asp:AccessDataSource>

Save yourself a ton of grief in future and use parameters.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.querystringparameter.aspx

 

Thanks Mike, but could you think of a reason why it only works in the onload event and not in the selecting event?

The only reason I can think of is that the selecting event isn't being fired. And the only reason for that is if it isn't properly hooked up to the datasource control.

Put a breakpoint on the Selecting event in your code behind and see if execution halts when you press F5. If it does, hit F10 to step through the code line by line and see if anything obvious jumps out in your Locals window.

 

Thanks, I give it a go.

Sending an email and updating a Database

I have a formview to insert data into a DB.  Works great... I would also like to send an email after the user submits the form. 

 I put the mail message code on the .cs page and it worked great!!!!  Except it did not update the DB.......

I really need it to do both......... but one or the other works fine!!!

What am I missing here.....

 

As always thanks!!!!!

 

Show ur code.

You can do it easily with LINQ to sql. Example below,

            LinqDataContext dc = new LinqDataContext();
            LinqDC ct = new LinqDC();

            ct.Address = "Address";
            ct.Company = "Company";
            ct.Email = "Email";           

            dc.LinqDCs.InsertOnSubmit(ct);
            try
            {
                dc.SubmitChanges();
               
            }
            catch (Exception ex)
            {
                //handle exception
            }

This code works to update the DB..

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

using

 

System;

using

 

System.Collections.Generic;

using

 

System.Linq;

using

 

System.Web;

using

 

System.Web.UI;

using

 

System.Web.UI.WebControls;

using

 

System.Net.Mail;

public

 

partial class explorers_ridetime_riderequest : System.Web.UI.

Page

{

 

protected void Page_Load(object sender, EventArgs

e)

{

}

 

protected void date_requestedTextBox_TextChanged(object sender, EventArgs

e)

{

}

[System.Web.Services.

WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute

()]

 

public static int GetNextValue(int current, string

tag)

{

 

return default(int

);

}

 

protected void TextBox1_TextChanged(object sender, EventArgs

e)

{

}

 

protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs

e)

{

}

 

protected void InsertButton_Click(object sender, EventArgs

e)

{

 

 

}

[System.Web.Services.

WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute

()]

 

public static string GetDynamicContent(string

contextKey)

{

 

return default(string

);

}

}

 

This code works to send the email when added

 

 

 

 

 

 

 

 

 

MailMessage message = new MailMessage

();

{

message.From =

new MailAddress

(emailaddress.Text.ToString());

message.To.Add(

new MailAddress("xxxxx@xxxxxx.com"

));

message.CC.Add(

new MailAddress("xxxxxx@xxxxx.com"

));

message.Body = (

"join"

);

 

SmtpClient client = new SmtpClient

();

client.Host =

"mail.xxxxxx.com"

;

client.Send(message);

Response.Redirect(

http://www.xxxxxxxxxx.com/xxxxxx.htm

);

}

 

 THANKS!!!!!!!

Hi Jangle,

You should ensure that you invoke the function of sending the email.

You can put the code into page unload event and try it.

    protected void Page_Unload(object sender, EventArgs e) 
   
{ 
       //send the email

        }

http://msdn.microsoft.com/en-us/library/ms178472.aspx

Thank you !!!!  And this works, except it works too good.

I would like the notification email send after the user inputs data and submits the form.  the way I have it, it seems to fire off the email each time the page is loaded.....

Any more help appreciated.....

Jim

This is the code behind....

 

public

 partial class explorers_ridetime_riderequest : System.Web.UI.

Page

{

 protected void Page_Load(object sender, EventArgs e)

{

}

 protected void date_requestedTextBox_TextChanged(object sender, EventArgs e)

{

}

 protected void TextBox1_TextChanged(object sender, EventArgs e)

{

}

 protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)

{

}

 protected void InsertButton_Click(object sender, EventArgs e)

{

}

 protected void Page_Unload(object sender, EventArgs e)

{

 MailMessage message = new MailMessage();

{

 

 

 

message.From = (

 

new MailAddress("XXX@XXXX.com"));

new MailAddress("XXX@XXXX.com"));

message.To.Add(

 

new MailAddress("XXX@XXXX.com"));

message.CC.Add(

 

"A new Ride Request has been posted");

message.Body = (

 

 SmtpClient client = new SmtpClient(); "mail.xxxxxx.com";

client.Host =

client.Send(message);

}

 }

}