Category Archives: AccessDataSource

[RESOLVED] Reading values from a Database and output the data to Labels

I think I'm maybe not far off with this, but need a second opinion...

 

I have a database with a table Employees which includes these fields:

 

ID

EmployeeName

EmailAddress

EmployeeNo

 

Out of this I want to take 2 values, the EmailAddress and EmployeeNo and display their values in 2 separate labels.

 

So far I am guessing:

 

Dim myConnection As New SqlConnection
myConnection.ConnectionString = “server=(local); database=UserStatus; Trusted_Connection=True”

Dim myAdapter As SqlDataAdapter
myAdapter = New SqlDataAdapter(“SELECT * FROM Employees”, myConnection)

Dim myDataSet As New DataSet
myConnection.Open()
myAdapter.Fill(myDataSet)
myConnection.Close()

 

Have I got this correct and if so, where and how should I declare this so that it can be used across the site?

 

And the code for when the button is clicked, to place the values into the Labels, I have so far:

 

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click


Label1.Text = myDataSet.Tables(0).Rows(2)(“EmailAddress”)

Label2.Text = myDataSet.Tables(0).Rows(3)(“EmployeeNo”)


End Sub

 

Can anyone please tell me if I'm near or far and/or what I need to change?

if it is less data, you can put it on a session variable

session ("MyData")=myDataSet;

than you can type cast when ever you want.

dataset ds=new dataset()

ds=(datasete)session ("MyData")=

You are close but we need to know exactly what you want from your table. You are retrieving everything from your table now.  You need to watch the index of Rows. The Row one has an index of 0. Most likelu, you need to use the same row index.

The following will show the first row returned with field values from "EmailAddress” and "EmployeeNo" to the
labels when you click button:

Label1.Text = myDataSet.Tables(0).Rows(0)(“EmailAddress”)
Label2.Text = myDataSet.Tables(0).Rows(0)(“EmployeeNo”)

 

Dim myDataSet As New DataSet

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim myConnection As New SqlConnection
        myConnection.ConnectionString = myConnection.ConnectionString = “server=(local); database=UserStatus; Trusted_Connection=True”


        Dim myAdapter As SqlDataAdapter
        myAdapter = New SqlDataAdapter("SELECT * FROM Employees", myConnection)

        myConnection.Open()
        myAdapter.Fill(myDataSet)
        myConnection.Close()
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Label1.Text = myDataSet.Tables(0).Rows(1)("EmailAddress")
        Label2.Text = myDataSet.Tables(0).Rows(1)("EmployeeNo")
    End Sub



limno

You are close but we need to know exactly what you want from your table. You are retrieving everything from your table now.  You need to watch the index of Rows. The Row one has an index of 0. Most likelu, you need to use the same row index.

The following will show the first row returned with field values from "EmailAddress” and "EmployeeNo" to the
labels when you click button:

Label1.Text = myDataSet.Tables(0).Rows(0)(“EmailAddress”)
Label2.Text = myDataSet.Tables(0).Rows(0)(“EmployeeNo”)

 

 

Doh I completely missed out such a key point - I am trying to match values not just return the first row/first record!

 

I also have 2 textboxes, which the user will enter values into:

 

Textbox1 - user will enter their ID

Textbox2 - user will enter their Name

 

 

The code I showed before then, is meant to match the values in the database...

ie. If user enters their ID is 1 and their name is Peter Jones, then the matching values for that record for EmailAddress and EmployeeNo are returned and passed to be displayed in Label1 and Label2 respectively.

 

If no matching record exists, then another label - Label3 displays a message saying "Record not found".

 

How do I rewrite my code to implement this?

 

also one more thing...you are selecting All form the table by mentioning *, i think that is not required....

just use SELECT TOP 1 [EmailAddress], [EmployeeNo] FROM Employees

or else you can use a WHERE CLAUSE to get the specific row....

in above cases only you will be getting a datatable with one row, which can be used easily....

Thanks.

It was the WHERE clause I was thinking of, but I'm not sure how to put this into code.

 

I need to SELECT record from database WHERE data entered in TEXTBOX1 and TEXTBOX2 match a record in database

then output these fields to LABEL1 and LABEL2 as text

 

it is the matching part (SQL statement in code) I'm not so sure about

 

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click     Dim myConnection As New SqlConnection
        myConnection.ConnectionString = myConnection.ConnectionString = “server=(local); database=UserStatus; Trusted_Connection=True”


        Dim cmd As SqCommand=New SqlCommand("SELECT * FROM Employees WHERE employeeName=@employeeName AND @otherField Like '%'+@otherField+%' ", myConnection)

cmd.Parameters.AddWithVaues("@employeeName",TextBox1.Text)
cmd.Parameters.AddWithVaues("@otherField",TextBox2.Text)
myConnection.Open()

Dim myReader as SqlReader=cmd.ExecuteReader()
If myReader.HasRows then
While myReader.Read()
Label1.Text=myReader("employeeEmail")
end while

Else
Label1.Text="No record!"
End if
 End Sub

Errr... couple of small problems here.

 

Whenever I try to test the code, I get the error: 'AddWithValues' is not a member of System.Data.SqlClient.SqlParameterCollection

 

Other problem, the database I'm using here is an Access database :S

 

Anyway the code can be modified to work with an Access database?

Okay, thats the SQL issue...

 

But since I'm working with an Access Database here, the Sql calls won't work as it's using a different library ?

 

...So how do I go about rewriting it for Access DB instead?

Here is a similar post talking about working with MS Access Database

<div class="mcePaste" id="_mcePaste" style="position: absolute; width: 1px; height: 1px; overflow: hidden; top: 0px; left: -10000px;"></div>

http://forums.asp.net/t/1446465.aspx/1/10?Working+with+MS+Access+Database

http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

Replace with blow code...

I have update  code with using command object.

 

Dim myConnection As New SqlConnection
        myConnection.ConnectionString = "server=(local); database=UserStatus; Trusted_Connection=True"

        If (myConnection.State <> ConnectionState.Open) Then
            myConnection.Open()
        End If


        Dim myCommand As New SqlCommand("SELECT * FROM Employees", myConnection)
        myCommand.CommandType = CommandType.Text



        Dim myAdapter As SqlDataAdapter
        myAdapter = New SqlDataAdapter(myCommand)

        Dim myDataSet As New DataSet

        myAdapter.Fill(myDataSet)
        If (myConnection.State = ConnectionState.Open) Then
            myConnection.Open()
        End If

[RESOLVED] Duplicate records saved on Click of save button.

Hi,

   I am working on a .NET 3.5 web application. I am saving the values from the textboxes on the page on click of save button.

Sometimes If i click on the Save button 3 or 4 times , duplicate records are saved in the database.

I want to avoid duplicate entries. 

How to prevent the user from clicking the save button second time?

 

Thanks

Ashok 

 

  

Hi,

you can try using this server-side trick:

add a unique ID to viewstate on page load when first loading the page

if (!IsPostBack)

{

ViewState[
"PostBackID"] = Guid.NewGuid().ToString();

}

Write a validation function

private bool validatePostback()

{

// verify duplicate postback

string postbackid = ViewState["PostBackID"] as string;

bool isValid = Cache[postbackid] == null;

if (isValid)

Cache.Insert(postbackid, true, null, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(10));

return isValid;

}

Finally, check for postback validation in your button onclick event:

if (validatePostback())

// handle your click event here

else

// ignore or print some alert to the user

Hope this is what you were looking for. Bye!

I agree with WereWolf on adding a unique ID, but I've done that on the database table end, so that I can recall an individual record by its ID.  The method I used to resolve duplicate entires is the following:

1) Add a RequiredFieldValidator to at least one TextBox you are using to add to the database.

2) At the end of your OnClick code set all the TextBoxes back to blanks.

If the button is clicked more than once, the TextBox will be blank after the insert and throw the validation error.

Example:

Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim TaskIns As AccessDataSource = CType(cphldr.FindControl("adsTasks"), AccessDataSource)

'Variables for the Tasks table insert.

Dim gvTask As TextBox = CType(cphldr.FindControl("tbTaskDesc"), TextBox)

TaskIns.InsertParameters("Task").DefaultValue = gvTask.Text

TaskIns.Insert()

gvTask.Text = ""

End Sub

Yes, it is good practice to have a unique id for each record on the database.

But even clearing all fields in the server click handler may not guarantee the user does not click the submit button more than once.

This could happen on slow connection: the user does not have an immediate feedback and tries to re-click that button...

Disabling the submit button on postback could be another nice trick, but this is not a substition to server-side validation.

Hi

    thank you all for your replies.. Generating a Unique id during first time page load works fine. I am able to prevent duplicate entries..

I want to implement this is all the pages in many button click events. Is there any generic way of handling this?

How to disable the submit button on postback to prevent the user from clicking for the second time?

I tried to disable the button in onclick event and enable after saving the record. But it did not work.

 How to disable the button for preventing multiple clicks?

 

Thanks

Ashok

Hi Ashok,

I usually call a routine on page load to enable/disable certain buttons. Like New, Edit etc. should be enabled, but Save, Cancel would be disabled. Then when the user clicks on New or Edit, these buttons will be disabled, but Save, Cancel will be enabled.

Eventually when the user clicks on Save or Cancel, the buttons will revert back to page load state.

For e.g., code 2 procedures btnsEnable and btnsDisable and set the properties for appropriate buttons accordingly and call the necessary procedure as and when required.

This will also ensure some data safety, though the user might need to have a couple of extra mouse clicks.

 

Tom

[RESOLVED] Pass values to form - then add to access database

Hi

I have 2 forms that pass values from page 1 to page 2.
http://www.sella-ronda.co.uk/form1.aspx
http://www.sella-ronda.co.uk/form2.aspx

These forms work fine

I would like to make it so that a user can enter data into form 1 the review their input on form 2 and if it's OK then add the data to a database.

I am using expression web so (of course being MS) as soon as I use the wizard to add the data to the database I get an error.
See

I can get data into a database by a user typing stuff into a form and then clicking a button - the problems are caused by the RequestForm = etc, etc.

Is there another way of doing this

Thank you

Kelly

If you set the PostBackUrl of the MyButton button on Form 1 to be Form2.aspx, you can access the Form1 controls from Form2 by using:

 

if (Page.PreviousPage != nul)

{

     TextBox TextBox1 = Page.PreviousPage.FindControl("TextBox1") as TextBox;

}

Hi

Thanks for that - I have no probelms with passing values between forms.
The problems is the insertion into a databse "with" a request (or a PreviousPage.FindControl).
I have used the EW tutourial to create the link the a Database and this works but when you add a reference to another page it fails.

I think I will be missing something really basic here - so sorry for the dumb question
Any ideas would be really helpful.

Thanks

 

This works fine

<html xmlns="http://www.w3.org/1999/xhtml">
<head> </head>
<body> <form id="form1" runat="server">
 <asp:FormView id="FormView1" runat="server" DataKeyNames="id" DataSourceID="test_database" DefaultMode="Insert">
  <InsertItemTemplate>
   <asp:textbox id="MyTestBox" runat="server" Text='<%# Bind("Field1") %>' />
   <br />
 <asp:Button id="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert into DB" />
  </InsertItemTemplate>
 </asp:FormView>
 <asp:AccessDataSource ID="test_database" runat="server" DataFile="fpdb/mdb_forms.mdb"  InsertCommand="INSERT INTO [tblTest] ([Field1]) VALUES (?)"  >
  <InsertParameters>
   <asp:parameter Name="Field1" Type="String" />
  </InsertParameters>
 </asp:AccessDataSource>
</form> </body> </html>

But as soon as I add in a reference to the previous page it fails

So this doesn't work (with the small script added)

<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<script language="VB" runat="server">
Dim strSomeName
Sub Page_Load()
strSomeName = Request.Form("txtTestOne")
MyTestBox.Text = strSomeName
End Sub
</script>

 </head>
<body> <form id="form1" runat="server">
 <asp:FormView id="FormView1" runat="server" DataKeyNames="id" DataSourceID="test_database" DefaultMode="Insert">
  <InsertItemTemplate>
   <asp:textbox id="MyTestBox" runat="server" Text='<%# Bind("Field1") %>' />
   <br />
 <asp:Button id="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert into DB" />
  </InsertItemTemplate>
 </asp:FormView>
 <asp:AccessDataSource ID="test_database" runat="server" DataFile="fpdb/mdb_forms.mdb"  InsertCommand="INSERT INTO [tblTest] ([Field1]) VALUES (?)"  >
  <InsertParameters>
   <asp:parameter Name="Field1" Type="String" />
  </InsertParameters>
 </asp:AccessDataSource>
</form> </body> </html>

 

Hi reflo,

According to your descripition, since what unsolved is relevant to access database, I suggest you also post your issue in Access Databases and AccessDataSource Control  forum.

This is a duplicate thread, please focus on this one : http://forums.asp.net/t/1685318.aspx

Regards

[RESOLVED] Filtered dropdowns on a form then save to database

<div class="container"> <div class="body">

Hi

I asked this in the asp forum but got told it was an EW question so ....

I have followed the tutourial on HomePageDoctor and have come up against something I have been googleing for ages and all I can find is other people asking the same question....

I have a form that saves data to an access database.  On this form are a number of dropdowns - this works fine.

But as soon as I try and filter a drop down it crashes.

eg. 

I have a dropdown (see below) called resorts and a dropdown called hotels.

a user will select a resort - then select from a filtered list - then the results are saved into aDB
but of course it doesn't work.

Any ideas please (or even a link to a page that will help)

PS - it not the database that causeing the problems as i'm OK with those.

Thanks

 

This is what I have tried without success

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Page Language="VB" %>
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Form 1</title>
</head>
<body>

<form id="form1" runat="server">
 <asp:FormView id="FormView1" runat="server" DataKeyNames="id" DataSourceID="UKOP_DB" DefaultMode="Insert">
  <InsertItemTemplate>
  <asp:dropdownlist id="txtResortTextBox" runat="server" AutoPostBack="True"
  DataSourceID="AccessDataSourceResorts" DataTextField="ResortName"  DataValueField="ResortName" Width="130px" Text='<%# Bind("txtResort") %>' >
  </asp:dropdownlist>

  <asp:dropdownlist id="txtHotelTextBox" runat="server" AutoPostBack="True"
  DataSourceID="AccessDataSourceHotels" DataTextField="HotelName" DataValueField="HotelName" Width="130px" Text='<%# Bind("txtHotel") %>' >
  </asp:dropdownlist>
   <br />
   <asp:linkbutton id="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" />
  </InsertItemTemplate>
 </asp:FormView>
 <asp:AccessDataSource ID="UKOP_DB" runat="server" DataFile="fpdb/mdb_forms.mdb" InsertCommand="INSERT INTO [tblholder] ([txtResort], [txtHotel]) VALUES (?, ?)" >
  <InsertParameters>
   <asp:parameter Name="txtResort" Type="String" />
   <asp:parameter Name="txtHotel" Type="String" />
  </InsertParameters>
 </asp:AccessDataSource>

     <asp:accessdatasource ID="AccessDataSourceResorts" runat="server" DataFile="fpdb/mdb2012.mdb"
     SelectCommand="SELECT [ID], [ResortName] FROM [tblResorts] ORDER BY [ID] DESC">
     </asp:accessdatasource>

     <asp:accessdatasource ID="AccessDataSourceHotels" runat="server" DataFile="fpdb/mdb2012.mdb"
     SelectCommand="SELECT [ID], [Resort], [HotelName] FROM [tblHotels] WHERE ([Resort] = ?) ORDER BY [ID] DESC">
     <SelectParameters>
     <asp:controlparameter ControlID="txtResortTextBox" Name="Resort" PropertyName="SelectedValue" Type="String" />
     </SelectParameters>
     </asp:accessdatasource>
</form>
</body>
</html>

Without ther items in italic Bold it works fine</div> </div>

Hi,

please try this:

 

<asp:FormView ID="FormView1" runat="server" DataKeyNames="id" DataSourceID="UKOP_DB"
        DefaultMode="Insert">
        <InsertItemTemplate>
            <asp:DropDownList ID="txtResortTextBox" runat="server" AutoPostBack="True" DataSourceID="AccessDataSourceResorts"
                DataTextField="ResortName" DataValueField="ResortName" Width="130px">
            </asp:DropDownList>
            <asp:DropDownList ID="txtHotelTextBox" runat="server" AutoPostBack="True" DataSourceID="AccessDataSourceHotels"
                DataTextField="HotelName" DataValueField="HotelName" Width="130px">
            </asp:DropDownList>
            <br />
            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
                Text="Insert" />
        </InsertItemTemplate>
    </asp:FormView>
    <asp:AccessDataSource ID="UKOP_DB" runat="server" DataFile="fpdb/mdb_forms.mdb" InsertCommand="INSERT INTO [tblholder] ([txtResort], [txtHotel]) VALUES (?, ?)">
        <InsertParameters>
            <asp:ControlParameter ControlID="txtResortTextBox" Name="txtResort" PropertyName="SelectedValue"
                Type="String" />
            <asp:ControlParameter ControlID="txtHotelTextBox" Name="txtHotel" PropertyName="SelectedValue"
                Type="String" />
        </InsertParameters>
    </asp:AccessDataSource>
    <asp:AccessDataSource ID="AccessDataSourceResorts" runat="server" DataFile="fpdb/mdb2012.mdb"
        SelectCommand="SELECT [ID], [ResortName] FROM [tblResorts] ORDER BY [ID] DESC">
    </asp:AccessDataSource>
    <asp:AccessDataSource ID="AccessDataSourceHotels" runat="server" DataFile="fpdb/mdb2012.mdb"
        SelectCommand="SELECT [ID], [Resort], [HotelName] FROM [tblHotels] WHERE ([Resort] = ?) ORDER BY [ID] DESC">
        <SelectParameters>
            <asp:ControlParameter ControlID="txtResortTextBox" Name="Resort" PropertyName="SelectedValue"
                Type="String" />
        </SelectParameters>
    </asp:AccessDataSource>
    </form>

also you might want to use CascadingDropDown

 

Hi

Thanks for your reply.  I can get the dropdowns to casscade without ajax but what I can not get is the results to save to an access database. 

Normal dropdowns based on a database selection will save no problem but as soon as I put any form of filter (select from - where) or casscade on the dropdowns it crashes.

I used your code and it still crashes - there MUST be a way to do this.

 

You mention that the application "crashes" - can you be more specific?  Do you get an error?  If you do, can you please post the error and the stack trace?  Remember, an Access database is a file based database, so you have to make sure that your application pool is running as a user that has write permissions.  There are also issues with the file being locked and I believe there can be issues with Access needing to write to some temporary files.  If you have an error message, that would be a terrific hint as to what is going on.

Here is a walkthrough:  http://msdn.microsoft.com/en-us/library/ms971485.aspx 

I know this isn't specifically what you are doing, but if you go through that are you at least able to save to the database?

Hi reflo,

I don't think the FormView control is a must here, the "crashes"  may be caused by that.

Please check out if this is exactly your scenario.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script runat="server">
        protected void btnInsert_Click(object sender, EventArgs e)
        {
            AccessDS2.Insert();
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="AccessDS1" DataTextField="ResortName"
            DataValueField="ResortName">
        </asp:DropDownList>
        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="AccessDS1" DataTextField="HotelName"
            DataValueField="HotelName">
        </asp:DropDownList>

        <asp:AccessDataSource ID="AccessDS1" runat="server" DataFile="~/App_Data/fpdb/mdb2012.mdb"
            SelectCommand="SELECT * FROM [tblHotels]"></asp:AccessDataSource>

            
        <asp:AccessDataSource ID="AccessDS2" runat="server" DataFile="~/App_Data/fpdb/mdb_forms.mdb"
            InsertCommand="INSERT INTO [tblHotels] ([ResortName], [HotelName]) VALUES (?, ?)">
            <InsertParameters>
                <asp:ControlParameter ControlID="DropDownList1" Name="ResortName" PropertyName="SelectedValue"/>
                <asp:ControlParameter ControlID="DropDownList2" Name="HotelName" PropertyName="SelectedValue"/>
            </InsertParameters>
        </asp:AccessDataSource>
        <p>
            <asp:Button ID="btnInsert" runat="server" Text="Click this to insert"
                OnClick="btnInsert_Click" />
        </p>
    </div>
    </form>
</body>
</html>

 

This is a duplicate thread. I suggest you focus on the latest one http://forums.asp.net/t/1685318.aspx

Regards

Refer here

http://aspsnippets.com/Search.aspx?q=cascading

You will find articles that do what you need

 

[RESOLVED] sorting listview control with paging

hi guys

i have a list view control with two combo boxes, a dataPager and a button control.

the first comboBox is to SortBy, the other one is to Order (Asc, Desc),

when i click the button, i need to sort the list view according to the combo box values, this works fine if i use queryStringFied property of the datapager, but will sort only the first page of the list view!!,

if i omit queryStringField, i get the listview sorted but, with no arguments at the address bar.!!

however i need a complete solution for this issue showing some thing like this at the address bar: www.mysite.com/testpage?PageNumber=1&SortBy=BookTitle&Order=ASC

 BTW am using accessDatasource , so no place for stored procedures. thank you all

 PLEASE HELP PPPPPPPP

Well you must provide your oder by clause to your select in access datasource instead providing it to pages.

I do sort like 

public string BuildCommand()
    {

        StringBuilder CommandBuilder = new StringBuilder("");

        
            CommandBuilder.Append("SELECT [ProductID], (Case When LEN(Title)>20 Then Left(Title,20) Else Title + '...' End) as [Title], Cast([UnitPrice] as Decimal(18,0)) as [UnitPrice] , Right(UnitPrice , 2) as [Deci]  , 'USD' as [CurrencyCode],'$' as [CurrencySymbol], [SmallImageUrl] FROM [vt_FE_Product] ");
              
       

        if (Session["SortOrder"] != null)
            CommandBuilder.Append(Session["SortOrder"].ToString());

        return CommandBuilder.ToString();



    }

    public void UpdateListView()
    {
        sdsProducts.SelectCommand = BuildCommand();
        sdsProducts.DataBind();
        ListView1.DataSourceID = "sdsProducts";
        ListView1.DataBind();
    }
set it in session  Session["SortOrder"] = " Order By UnitPrice DESC ";