Frustrating Newbie Parameter Question

Is it possible to create a parameter where a NULL value returns ALL records
and entering a value returns the filtered data? I am filtering a date field
"ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user a
choice of filtering the dataset with a paramater or leaving it blank and
returning records with all years.
There must be an easy way to do this. I am aware of placing an iif
statement in the dataset paramater
(i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
Parameters!Parameter1.Value)),
but if the parameter is empty, how will this return ALL records? I am
confused and would appreciate any help.
My query looks like this...
SELECT OrderDetail.*
FROM OrderDetail
WHERE (fkCategoryID = 4) AND (ConvYear = ?)
Thanks!Two points, unless you have to use a filter, don't (hard for me to tell if
you are using a filter or not). A filter first retrieves all the data
defined in the dataset and then filters it. So for instance with what you
have below, you should use that query as the definition for your dataset.
Also note that for a query parameter you use @.whateveryouwanttocallit if you
are going against SQL Server and ? if going against oledb or odbc datasourc.
OK, the trick here is to use like. Then for your parameters have an All that
returns a %. To test this out first try have your labels and values be a
list that you input on the report parameters screen (you can also use a
Union query to do this as well). What I like about this solution is you
don't have to use the generic query screen plus you don't lose having a
field list. Anyway, give it a try.
SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID =4) AND (ConvYear like ?)
Bruce L-C
"Brian" <bcovington@.hotmail.com> wrote in message
news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a parameter where a NULL value returns ALL
records
> and entering a value returns the filtered data? I am filtering a date
field
> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user
a
> choice of filtering the dataset with a paramater or leaving it blank and
> returning records with all years.
> There must be an easy way to do this. I am aware of placing an iif
> statement in the dataset paramater
> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
> Parameters!Parameter1.Value)),
> but if the parameter is empty, how will this return ALL records? I am
> confused and would appreciate any help.
> My query looks like this...
> SELECT OrderDetail.*
> FROM OrderDetail
> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> Thanks!
>
>
>
>|||This works great! Would this be a good solution if I wanted say 5 optional
parameters in a report, or is the LIKE clause too slow? I have about 20,000
records to search on, but the report isn't run very often.
Also, I have seen others discuss using dynamic SQL with iff statements to
obtain the same results. Would this possibly be a more efficient solution
than the LIKE clause?
Thanks,
Brian
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> Two points, unless you have to use a filter, don't (hard for me to tell if
> you are using a filter or not). A filter first retrieves all the data
> defined in the dataset and then filters it. So for instance with what you
> have below, you should use that query as the definition for your dataset.
> Also note that for a query parameter you use @.whateveryouwanttocallit if
> you
> are going against SQL Server and ? if going against oledb or odbc
> datasourc.
> OK, the trick here is to use like. Then for your parameters have an All
> that
> returns a %. To test this out first try have your labels and values be a
> list that you input on the report parameters screen (you can also use a
> Union query to do this as well). What I like about this solution is you
> don't have to use the generic query screen plus you don't lose having a
> field list. Anyway, give it a try.
> SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID => 4) AND (ConvYear like ?)
> Bruce L-C
> "Brian" <bcovington@.hotmail.com> wrote in message
> news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to create a parameter where a NULL value returns ALL
> records
>> and entering a value returns the filtered data? I am filtering a date
> field
>> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user
> a
>> choice of filtering the dataset with a paramater or leaving it blank and
>> returning records with all years.
>> There must be an easy way to do this. I am aware of placing an iif
>> statement in the dataset paramater
>> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
>> Parameters!Parameter1.Value)),
>> but if the parameter is empty, how will this return ALL records? I am
>> confused and would appreciate any help.
>> My query looks like this...
>> SELECT OrderDetail.*
>> FROM OrderDetail
>> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
>> Thanks!
>>
>>
>>
>|||I prefer the method I showed you because it is soooo much easier to develop,
you get the list of fields, you can use the normal query designer. Whether
it is slow or not depends on the database you are going against. If it is
SQL Server 2000 and you have 20,000 records it won't even breath hard.
20,000 is nothing. I suggest trying it this way, if performance is a problem
you can always go to dynamic sql.
Bruce L-C
"Brian" <bcovington@.hotmail.com> wrote in message
news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
> This works great! Would this be a good solution if I wanted say 5
optional
> parameters in a report, or is the LIKE clause too slow? I have about
20,000
> records to search on, but the report isn't run very often.
> Also, I have seen others discuss using dynamic SQL with iff statements to
> obtain the same results. Would this possibly be a more efficient solution
> than the LIKE clause?
> Thanks,
> Brian
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> > Two points, unless you have to use a filter, don't (hard for me to tell
if
> > you are using a filter or not). A filter first retrieves all the data
> > defined in the dataset and then filters it. So for instance with what
you
> > have below, you should use that query as the definition for your
dataset.
> >
> > Also note that for a query parameter you use @.whateveryouwanttocallit if
> > you
> > are going against SQL Server and ? if going against oledb or odbc
> > datasourc.
> >
> > OK, the trick here is to use like. Then for your parameters have an All
> > that
> > returns a %. To test this out first try have your labels and values be a
> > list that you input on the report parameters screen (you can also use a
> > Union query to do this as well). What I like about this solution is you
> > don't have to use the generic query screen plus you don't lose having a
> > field list. Anyway, give it a try.
> > SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID
=> > 4) AND (ConvYear like ?)
> >
> > Bruce L-C
> >
> > "Brian" <bcovington@.hotmail.com> wrote in message
> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> Is it possible to create a parameter where a NULL value returns ALL
> > records
> >> and entering a value returns the filtered data? I am filtering a date
> > field
> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
user
> > a
> >> choice of filtering the dataset with a paramater or leaving it blank
and
> >> returning records with all years.
> >>
> >> There must be an easy way to do this. I am aware of placing an iif
> >> statement in the dataset paramater
> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
> >> Parameters!Parameter1.Value)),
> >> but if the parameter is empty, how will this return ALL records? I am
> >> confused and would appreciate any help.
> >>
> >> My query looks like this...
> >>
> >> SELECT OrderDetail.*
> >> FROM OrderDetail
> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> >>
> >> Thanks!
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Bruce,
Your time was very much appreciated. Thank you!
Brian
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ueS7sG1nEHA.3216@.tk2msftngp13.phx.gbl...
>I prefer the method I showed you because it is soooo much easier to
>develop,
> you get the list of fields, you can use the normal query designer. Whether
> it is slow or not depends on the database you are going against. If it is
> SQL Server 2000 and you have 20,000 records it won't even breath hard.
> 20,000 is nothing. I suggest trying it this way, if performance is a
> problem
> you can always go to dynamic sql.
> Bruce L-C
> "Brian" <bcovington@.hotmail.com> wrote in message
> news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> This works great! Would this be a good solution if I wanted say 5
> optional
>> parameters in a report, or is the LIKE clause too slow? I have about
> 20,000
>> records to search on, but the report isn't run very often.
>> Also, I have seen others discuss using dynamic SQL with iff statements to
>> obtain the same results. Would this possibly be a more efficient
>> solution
>> than the LIKE clause?
>> Thanks,
>> Brian
>>
>>
>> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
>> > Two points, unless you have to use a filter, don't (hard for me to tell
> if
>> > you are using a filter or not). A filter first retrieves all the data
>> > defined in the dataset and then filters it. So for instance with what
> you
>> > have below, you should use that query as the definition for your
> dataset.
>> >
>> > Also note that for a query parameter you use @.whateveryouwanttocallit
>> > if
>> > you
>> > are going against SQL Server and ? if going against oledb or odbc
>> > datasourc.
>> >
>> > OK, the trick here is to use like. Then for your parameters have an All
>> > that
>> > returns a %. To test this out first try have your labels and values be
>> > a
>> > list that you input on the report parameters screen (you can also use a
>> > Union query to do this as well). What I like about this solution is you
>> > don't have to use the generic query screen plus you don't lose having a
>> > field list. Anyway, give it a try.
>> > SELECT OrderDetail.* FROM OrderDetail WHERE
>> > (fkCategoryID
> =>> > 4) AND (ConvYear like ?)
>> >
>> > Bruce L-C
>> >
>> > "Brian" <bcovington@.hotmail.com> wrote in message
>> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> >> Is it possible to create a parameter where a NULL value returns ALL
>> > records
>> >> and entering a value returns the filtered data? I am filtering a date
>> > field
>> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
> user
>> > a
>> >> choice of filtering the dataset with a paramater or leaving it blank
> and
>> >> returning records with all years.
>> >>
>> >> There must be an easy way to do this. I am aware of placing an iif
>> >> statement in the dataset paramater
>> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = "
>> >> +
>> >> Parameters!Parameter1.Value)),
>> >> but if the parameter is empty, how will this return ALL records? I am
>> >> confused and would appreciate any help.
>> >>
>> >> My query looks like this...
>> >>
>> >> SELECT OrderDetail.*
>> >> FROM OrderDetail
>> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
>> >>
>> >> Thanks!
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||We've used a statement in the where clause like :
where (ssn.Item_Number = IsNull(@.Stock_Code, ssn.Item_Number))
which returns all items if the sp parameter is null of uses the parameter
value to filter.
Julian
"Brian" <bcovington@.hotmail.com> wrote in message
news:uJyf4T1nEHA.132@.TK2MSFTNGP09.phx.gbl...
> Bruce,
> Your time was very much appreciated. Thank you!
> Brian
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ueS7sG1nEHA.3216@.tk2msftngp13.phx.gbl...
> >I prefer the method I showed you because it is soooo much easier to
> >develop,
> > you get the list of fields, you can use the normal query designer.
Whether
> > it is slow or not depends on the database you are going against. If it
is
> > SQL Server 2000 and you have 20,000 records it won't even breath hard.
> > 20,000 is nothing. I suggest trying it this way, if performance is a
> > problem
> > you can always go to dynamic sql.
> >
> > Bruce L-C
> >
> > "Brian" <bcovington@.hotmail.com> wrote in message
> > news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> This works great! Would this be a good solution if I wanted say 5
> > optional
> >> parameters in a report, or is the LIKE clause too slow? I have about
> > 20,000
> >> records to search on, but the report isn't run very often.
> >>
> >> Also, I have seen others discuss using dynamic SQL with iff statements
to
> >> obtain the same results. Would this possibly be a more efficient
> >> solution
> >> than the LIKE clause?
> >>
> >> Thanks,
> >>
> >> Brian
> >>
> >>
> >>
> >>
> >> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> >> > Two points, unless you have to use a filter, don't (hard for me to
tell
> > if
> >> > you are using a filter or not). A filter first retrieves all the data
> >> > defined in the dataset and then filters it. So for instance with what
> > you
> >> > have below, you should use that query as the definition for your
> > dataset.
> >> >
> >> > Also note that for a query parameter you use @.whateveryouwanttocallit
> >> > if
> >> > you
> >> > are going against SQL Server and ? if going against oledb or odbc
> >> > datasourc.
> >> >
> >> > OK, the trick here is to use like. Then for your parameters have an
All
> >> > that
> >> > returns a %. To test this out first try have your labels and values
be
> >> > a
> >> > list that you input on the report parameters screen (you can also use
a
> >> > Union query to do this as well). What I like about this solution is
you
> >> > don't have to use the generic query screen plus you don't lose having
a
> >> > field list. Anyway, give it a try.
> >> > SELECT OrderDetail.* FROM OrderDetail WHERE
> >> > (fkCategoryID
> > => >> > 4) AND (ConvYear like ?)
> >> >
> >> > Bruce L-C
> >> >
> >> > "Brian" <bcovington@.hotmail.com> wrote in message
> >> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> >> Is it possible to create a parameter where a NULL value returns ALL
> >> > records
> >> >> and entering a value returns the filtered data? I am filtering a
date
> >> > field
> >> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
> > user
> >> > a
> >> >> choice of filtering the dataset with a paramater or leaving it blank
> > and
> >> >> returning records with all years.
> >> >>
> >> >> There must be an easy way to do this. I am aware of placing an iif
> >> >> statement in the dataset paramater
> >> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear ="
> >> >> +
> >> >> Parameters!Parameter1.Value)),
> >> >> but if the parameter is empty, how will this return ALL records? I
am
> >> >> confused and would appreciate any help.
> >> >>
> >> >> My query looks like this...
> >> >>
> >> >> SELECT OrderDetail.*
> >> >> FROM OrderDetail
> >> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> >> >>
> >> >> Thanks!
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Excellent tip! The visual designer messes up statements like:
(@.ItemNo Is Null OR ItemNumber = @.ItemNo)sql

Frustrated: Error Output in Source and Destination Editor.

Hi

Is there anyway I can multi select rows when I'm changing the value for either Error or Truncation? I'm finding it frustrating having to change 2 values for each row in the output. for example redirect row, when I have 10,20,30 columns.

It would have been nice to have a global drop down at the top of each column so that I could then selectively change rows back.

Any hints and tips on making this easier would be gratefully appreciated.

ThanksThe global drop down that you're looking for is at the bottom of the Configure Error Output page, above the buttons, rather than at the top. You select multiple rows and columns, select the option that you want from the drop down list (eg Redirect row), and click Apply.

(I don't know whether this feature may be a recent addition. If so, you will see it at the latest in the next CTP.)

-Doug|||Hi doug,

I'm using The June CTP downloaded from MSDN and that doesn't have aany buttons. What release are you running?

And thankyou for your reply

Marcus|||This functionality was added after the June CTP, it should be available in a future CTP.

thankssql

Frustrated with Nested Groups/Lists subtotals

I have a set of three nested lists.

Office_List header sum(fields!Cost.value,"Office_List") sum(fields!Access.value,"Office_List")
Clerk_List header sum(fields!Cost.value,"Clerk_List") sum(fields!Access.value,"Clerk_List")
Order_List header sum(fields!Cost.value,"Order_List") sum(fields!Access.value,"Order_List")
Order_ List detail: Fields!Cost.value Fields!Access.value Fields!Total.value
Order_List Detail: Table_Detail
Order_List footer
Clerk_List Footer
Office_List footer

Order_list groups by order id
Clerk_list groups by clerk id
Office_list groups by office id

Using the sample above for the list header Order_List, I have subtotals for each list grouping. Every single one displays a grandtotal for the whole report, NOT by grouping, even though I've indicated a scope value. Please what am I doing incorrectly here? None of my summaries are recognizing my scopes, spelling is correct. If I have a simple single grouping I do not have any issues, Order_list contains the only detail, one table and three standalone fields.

Again I am indicating scope for each sum but the scope is being ignored. I want a sum of the order cost by clerk and by office, instead I am getting grandtotals.

Thank you for any assistance.

I am just clarifying your question. Please correct me if I am wrong.

I think you want to add the distinct row values by group.At the grop footer you want the grand total of the values displayed.

|||

I do want to add the distinct row values by List group, and maybe that is where the problem lies. I am treating the list group in a similar fashion as I would a table group,and possibly it does not evaluate the same way.

My innermost nested list conatins 6 free form fields, and one table with a group, and a subreport linked by parameter to the order id - the list is grouped by Order ID and works perfectly.

The Next list contains the first list, plus in addition 6 free form fields. It is grouped by Clerk ID, and the free form fields are sums of the free form fields in the previous list based on scope. <for instance a field in the innermost list would be Fields!Cost.value), in the Clerk List I am doing Sum(Fields!Cost.value,"Order_List")>

The last list contains the previous two lists, is grouped by Office ID and contains again 6 free form fields that are sums of the original fields based on scope: ie Sum(Fields!Cost.value,"Office_list")

Below the collection of lists I have 6 grand total fields, for example Sum(fields!Cost.value, "Overview") these are the correct grand totals for the whole report.

When I run the report, the very first innermost list displays perfectly as do the final grand totals. But the totals that are displaying are identical to the grand totals instead of being totals based on group.

Ie List A Office1 4
List B ClerkA 4
List A Office2 4
List B ClerkB 4
GrandTotal 4

Instead of :
List A Office1 1
ListB Clerk 1
List A Office 2 3
ListB ClerkB 3

Grand Total 4

from view inserts the value of the last inserting at page reload a second time

Hi folks,

After getting a form view inserting some values into a mdb file, it inserts the same values a second time on page reload.

How may I cure this? Any suggestions?

VB Code is below.

many thanks in advance

Rosi

1<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="km_Eingabe.aspx.vb"Inherits="km_Eingabe" title="km-Eingabe" %>2<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">3 <table style="width: 750px; height: 210px">4 <tr>5 <td style="height: 38px">6 </td>7 <td style="height: 38px">8 <asp:DropDownList ID="dropdownlist1" runat="server" AutoPostBack="True" DataSourceID="AccessDataSource2"9 DataTextField="polKennz" DataValueField="polKennz">10 </asp:DropDownList>11 </td>12 <td style="height: 38px">13 </td>14 </tr>15 <tr>16 <td style="height: 235px">17 </td>18 <td style="height: 235px" valign="top">19   20 <asp:FormView ID="FormView1" runat="server" CellPadding="4" DataSourceID="SqlDataSource1"21 ForeColor="#333333">22 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />23 <EditRowStyle BackColor="#999999" />24 <EditItemTemplate>2526 </EditItemTemplate>27 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />28 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />29 <EmptyDataTemplate>30 keine Daten vorhanden31 <br />32 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>33 </EmptyDataTemplate>34 <InsertItemTemplate>35 Datum:36 <asp:TextBox ID="DatumTextBox" runat="server" Text='<%# Bind("Datum", "{0:d}") %>'>37 </asp:TextBox>38 <br />39 Fahrer:40 <asp:TextBox ID="FahrerTextBox" runat="server" Text='<%# Bind("Fahrer") %>'>41 </asp:TextBox>42 <br />43 polKennz:44 <asp:TextBox ID="polKennzTextBox" runat="server" Text='<%# Bind("polKennz") %>'>45 </asp:TextBox>46 <br />47 neuer_Eintrag:48 <asp:TextBox ID="neuer_EintragTextBox" runat="server" Text='<%# Bind("neu") %>'></asp:TextBox>49 <br />50 aktuell:51 <asp:TextBox ID="aktuellTextBox" runat="server" Text='<%# Bind("aktuell") %>'></asp:TextBox> <br />52 <br />53 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"54 Text="Einfügen" OnClick="InsertButton_Click">55 </asp:LinkButton> 56 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"57 Text="Abbrechen">58 </asp:LinkButton>59 </InsertItemTemplate>60 <ItemTemplate>61 Datum:62 <asp:Label ID="DatumLabel" runat="server" Text='<%# Bind("Datum") %>'></asp:Label><br />63 Fahrer:64 <asp:Label ID="FahrerLabel" runat="server" Text='<%# Bind("Fahrer") %>'></asp:Label><br />65 polKennz:66 <asp:Label ID="polKennzLabel" runat="server" Text='<%# Bind("polKennz") %>'></asp:Label><br />67 neu:68 <asp:Label ID="neuLabel" runat="server" Text='<%# Bind("neu") %>'></asp:Label><br />69 lfdNr:70 <asp:Label ID="lfdNrLabel" runat="server" Text='<%# Eval("lfdNr") %>'></asp:Label><br />71 aktuell:72 <asp:Label ID="aktuellLabel" runat="server" Text='<%# Bind("aktuell") %>'></asp:Label><br />73 Dienststelle:74 <asp:Label ID="DienststelleLabel" runat="server" Text='<%# Bind("Dienststelle") %>'></asp:Label><br />75 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>76 </ItemTemplate>77 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />78 </asp:FormView>79 </td>80 <td style="height: 235px">81 </td>82 </tr>83 <tr>84 <td>85 </td>86 <td>87 </td>88 <td>89 </td>90 </tr>91 </table>92<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"93 SelectCommand="SELECT DISTINCT [polKennz] FROM [qry_KennzeichenAlle_ohne_ausgesondert]">94 </asp:AccessDataSource>95 <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"96 SelectCommand="SELECT DISTINCT [Datum], [Nutzer], [Fahrer], [polKennz], [aktuell], [neu], [gefahren] FROM [qry_Fahrtenbuch_letzter_Eintrag_pro_Kfz] WHERE ([polKennz] = ?)">97 <SelectParameters>98 <asp:ControlParameter ControlID="DropDownList1" Name="polKennz" PropertyName="SelectedValue"99 Type="String" />100 </SelectParameters>101 </asp:AccessDataSource>102 <asp:SqlDataSource ID="SqlDataSource1" DataSourceMode="DataSet" ConflictDetection="CompareAllValues" InsertCommandType="Text" runat="server" ConnectionString="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString %>" ProviderName="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString.ProviderName %>"103 InsertCommand="INSERT INTO Tab_import_Fahrtenbuch([Datum], [Fahrer], [polKennz], [neu], [aktuell]) VALUES (@.Datum, @.Fahrer, @.polKennz, @.Eingabe_neu, @.Eingabe_aktuell )" >104 <SelectParameters>105 <asp:ControlParameter ControlID="dropdownlist1" Name="newparameter" PropertyName="SelectedValue" />106 </SelectParameters>107 <InsertParameters>108 <asp:FormParameter FormField="DatumTextBox" Name="Datum" Type="string" />109 <asp:FormParameter FormField="FahrerTextBox" Name="Fahrer" Type="string" />110 <asp:FormParameter FormField="polKennzTextBox" Name="polKennz" Type="string" />111 <asp:FormParameter FormField="neuer_EintragTextBox" Name="neu" Type="Int32" ConvertEmptyStringToNull="false" />112 <asp:FormParameter FormField="aktuellTextBox" Name="aktuell" Type="Int32" ConvertEmptyStringToNull="false" Direction="Input" />113 </InsertParameters>114 </asp:SqlDataSource>115     116</asp:Content>117118

Hi Rosi,

It seems that the code has been re-executed when the page re-loads. So you may use "IsPostBack" property to provent the re-execute phenomenon.

If this does not answer your question, pls feel free to reply. Thanks！

|||

HiNai-Dong Jin

Thanks for your answer.

I'm doing my first steps with this stuff. How to use that "IsPostBack"?

Many thanks for any suggestions.

Rosi

|||

Hi Rosi,

Can you show me the km_Eingabe.aspx.vb(code-behind file) ?

Thanks!

|||

RosiRonja:

Hi folks,

After getting a form view inserting some values into a mdb file, it inserts the same values a second time on page reload.

How may I cure this? Any suggestions?

VB Code is below.

many thanks in advance

Rosi

1<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="km_Eingabe.aspx.vb"Inherits="km_Eingabe" title="km-Eingabe" %>
2<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
3 <table style="width: 750px; height: 210px">
4 <tr>
5 <td style="height: 38px">
6 </td>
7 <td style="height: 38px">
8 <asp:DropDownList ID="dropdownlist1" runat="server" AutoPostBack="True" DataSourceID="AccessDataSource2"
9 DataTextField="polKennz" DataValueField="polKennz">
10 </asp:DropDownList>
11 </td>
12 <td style="height: 38px">
13 </td>
14 </tr>
15 <tr>
16 <td style="height: 235px">
17 </td>
18 <td style="height: 235px" valign="top">
19   
20 <asp:FormView ID="FormView1" runat="server" CellPadding="4" DataSourceID="SqlDataSource1"
21 ForeColor="#333333">
22 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
23 <EditRowStyle BackColor="#999999" />
24 <EditItemTemplate>
25
26 </EditItemTemplate>
27 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
28 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
29 <EmptyDataTemplate>
30 keine Daten vorhanden
31 <br />
32 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>
33 </EmptyDataTemplate>
34 <InsertItemTemplate>
35 Datum:
36 <asp:TextBox ID="DatumTextBox" runat="server" Text='<%# Bind("Datum", "{0:d}") %>'>
37 </asp:TextBox>
38 <br />
39 Fahrer:
40 <asp:TextBox ID="FahrerTextBox" runat="server" Text='<%# Bind("Fahrer") %>'>
41 </asp:TextBox>
42 <br />
43 polKennz:
44 <asp:TextBox ID="polKennzTextBox" runat="server" Text='<%# Bind("polKennz") %>'>
45 </asp:TextBox>
46 <br />
47 neuer_Eintrag:
48 <asp:TextBox ID="neuer_EintragTextBox" runat="server" Text='<%# Bind("neu") %>'></asp:TextBox>
49 <br />
50 aktuell:
51 <asp:TextBox ID="aktuellTextBox" runat="server" Text='<%# Bind("aktuell") %>'></asp:TextBox> <br />
52 <br />
53 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
54 Text="Einfügen" OnClick="InsertButton_Click">
55 </asp:LinkButton> 
56 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
57 Text="Abbrechen">
58 </asp:LinkButton>
59 </InsertItemTemplate>
60 <ItemTemplate>
61 Datum:
62 <asp:Label ID="DatumLabel" runat="server" Text='<%# Bind("Datum") %>'></asp:Label><br />
63 Fahrer:
64 <asp:Label ID="FahrerLabel" runat="server" Text='<%# Bind("Fahrer") %>'></asp:Label><br />
65 polKennz:
66 <asp:Label ID="polKennzLabel" runat="server" Text='<%# Bind("polKennz") %>'></asp:Label><br />
67 neu:
68 <asp:Label ID="neuLabel" runat="server" Text='<%# Bind("neu") %>'></asp:Label><br />
69 lfdNr:
70 <asp:Label ID="lfdNrLabel" runat="server" Text='<%# Eval("lfdNr") %>'></asp:Label><br />
71 aktuell:
72 <asp:Label ID="aktuellLabel" runat="server" Text='<%# Bind("aktuell") %>'></asp:Label><br />
73 Dienststelle:
74 <asp:Label ID="DienststelleLabel" runat="server" Text='<%# Bind("Dienststelle") %>'></asp:Label><br />
75 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>
76 </ItemTemplate>
77 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
78 </asp:FormView>
79 </td>
80 <td style="height: 235px">
81 </td>
82 </tr>
83 <tr>
84 <td>
85 </td>
86 <td>
87 </td>
88 <td>
89 </td>
90 </tr>
91 </table>
92<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"
93 SelectCommand="SELECT DISTINCT [polKennz] FROM [qry_KennzeichenAlle_ohne_ausgesondert]">
94 </asp:AccessDataSource>
95 <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"
96 SelectCommand="SELECT DISTINCT [Datum], [Nutzer], [Fahrer], [polKennz], [aktuell], [neu], [gefahren] FROM [qry_Fahrtenbuch_letzter_Eintrag_pro_Kfz] WHERE ([polKennz] = ?)">
97 <SelectParameters>
98 <asp:ControlParameter ControlID="DropDownList1" Name="polKennz" PropertyName="SelectedValue"
99 Type="String" />
100 </SelectParameters>
101 </asp:AccessDataSource>
102 <asp:SqlDataSource ID="SqlDataSource1" DataSourceMode="DataSet" ConflictDetection="CompareAllValues" InsertCommandType="Text" runat="server" ConnectionString="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString %>" ProviderName="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString.ProviderName %>"
103 InsertCommand="INSERT INTO Tab_import_Fahrtenbuch([Datum], [Fahrer], [polKennz], [neu], [aktuell]) VALUES (@.Datum, @.Fahrer, @.polKennz, @.Eingabe_neu, @.Eingabe_aktuell )" >
104 <SelectParameters>
105 <asp:ControlParameter ControlID="dropdownlist1" Name="newparameter" PropertyName="SelectedValue" />
106 </SelectParameters>
107 <InsertParameters>
108 <asp:FormParameter FormField="DatumTextBox" Name="Datum" Type="string" />
109 <asp:FormParameter FormField="FahrerTextBox" Name="Fahrer" Type="string" />
110 <asp:FormParameter FormField="polKennzTextBox" Name="polKennz" Type="string" />
111 <asp:FormParameter FormField="neuer_EintragTextBox" Name="neu" Type="Int32" ConvertEmptyStringToNull="false" />
112 <asp:FormParameter FormField="aktuellTextBox" Name="aktuell" Type="Int32" ConvertEmptyStringToNull="false" Direction="Input" />
113 </InsertParameters>
114 </asp:SqlDataSource>
115     
116</asp:Content>
117
118

try to remove your cache,
<meta http-equiv="Cache-Control" content="no-cache"/>

here is the way to of ispost back

If Page.IsPostBack Then
/// place business logic

else

End If

|||

Hi folks

I did it with Response.Redirect("km_Eingabe.aspx")

It works well that way.

1Protected Sub SqlDataSource1_Inserted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Inserted2 Response.Redirect("km_Eingabe.aspx")34End Sub

Thekm_Eingabe.aspx.vb (code behind file)in not more then this and works.

Either way I'll try the  other solutions (cache and postback) too.

What did you think is the best way to do?

Many Thanks

Rosi

sql