Search results matching tag 'database'

Conflict detection updates more than one row when I only want one!

LHYWebDesigner — Fri, 18 Dec 2009 05:00:00 GMT

I have a MySql database set with a primary key of: cust_code

This primary key cannot be changed to auto increment, it must be assigned by and correlate to a separate program!

When I perform the update comand every row that has data in any field that is the same as the entry I am trying to update is updated instead of just the one that the customer code corresponds to. It seams as if it is updating any row that has duplication such as same last names.

I suspect it has something to do with the conflict detection, but I am not sure if it can be fixed. When I eliminate the conflict detection for updating on every field but the primary key, it updates (only the selected entry) perfectly. However, that really defeats the purpose of conflict detection. I would like to be able to detect and overwrite conflicts only within the cust_code that is selected for editing. I will never be able to avoid duplicate names, dollars, etc.

Besides the web.config file, the program is three files: UpdatePrefCustomers.aspx prefcust_edit.aspx prefcust_insert.aspx

UpdatePrefCustomers.aspx is used to access the other two edit & insert pages. prefcust_insert.aspx does not have any problems, it's only prefcust_edit.aspx that is a problem.

Any help would be greatly appreciated.

The following is the access page, UpdatePrefCustomers.aspx:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Page Language="C#" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.Odbc" %>
<%@ Assembly Name="System.Data" %>


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

<head id="Head1" runat="server">


<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Preferred Customers at Lawrenceville Honda Yamaha!</title>
<meta name="robots" content="noindex, nofollow" /> 
<meta http-equiv="Cache-Control" content="no-cache" />
<meta http-equiv="Pragma" content="no-cache" />

<script type="text/javascript" language="javascript">
        function ConfirmOnDelete()
        {
          if (confirm("Are you sure to delete this Preferred Customer?")==true)
            return true;
          else
            return false;
        }
</script>

<style type="text/css">
.style1 {
	text-align: center;
	margin-left:auto; 
	margin-right:auto;
}
.style2 {
	text-align: center;
	font-size: 60pt;
	font-family: Mistral;
	color: #0000FF;
}

h1 { color: #003366; font-size: 24px; margin: 0px 0 0px 0;  line-height: 6px; }
.style3 {
	text-align: center;
	font-size: x-large;
}
.style4 {
	text-align: center;
	margin-top: 0;
	margin-bottom: 0;
}
.style5 {
	text-align: center;
	font-size: 60pt;
	font-family: Mistral;
}
</style>
</head>


<body>
<p class="style2">Lawrenceville Honda Yamaha </p>
<p class="style5">PREFERRED CUSTOMERS</p>

<p class="style3">To sort, click on column header.</p>
		
	<form id="form1" runat="server">
	<asp:HyperLink ID="HyperLink1" Text="Add New Preferred Customer..." NavigateUrl="prefcust_insert.aspx" runat="server" />
	<div id="PreferredCustomersGridView" class="style1">
		<asp:Gridview ID="grdPreferredCustomers" runat="server" DataKeyNames="cust_code" AllowSorting="True" AllowPaging="True" AutoGenerateColumns="False" CellPadding="1" CellSpacing="2" DataSourceID="srcPreferredCustomers">
			<Columns>
			<asp:TemplateField>
				<ItemTemplate>
					<asp:LinkButton 
					ID="LinkDelete" 
					runat="server"
					Text="Delete"
					CommandName="delete"
					OnClientClick="return ConfirmOnDelete();">
					Delete
					</asp:LinkButton>
				</ItemTemplate>
			</asp:TemplateField>
			<asp:BoundField
				DataField="cust_code"
				HeaderText="Customer Code" 
				SortExpression="cust_code"/>
			<asp:BoundField
				DataField="last_name"
				HeaderText="Last Name" 
				SortExpression="last_name" />
			<asp:BoundField
				DataField="first_name"
				HeaderText="First Name" 
				SortExpression="first_name" />
			<asp:BoundField
				DataField="join_date"
				HeaderText="Join Date" 
				dataformatstring="{0:D}"
				SortExpression="join_date" />
			<asp:BoundField
				DataField="amt_paid"
				HeaderText="Amount Paid" 
				dataformatstring="{0:n0}"
				SortExpression="amt_paid" />
			<asp:BoundField
				DataField="birthday_month"
				HeaderText="Birthday Month" 
				SortExpression="birthday_month" />
			<asp:BoundField
				DataField="birthday_day"
				HeaderText="Birthday Day" 
				SortExpression="birthday_day" />
			<asp:BoundField
				DataField="unit1_type"
				HeaderText="Unit 1 Type" 
				SortExpression="unit1_type" />
			<asp:BoundField
				DataField="last_4_vin_unit1"
				HeaderText="Last 4 Of 1st VIN" 
				SortExpression="last_4_vin_unit1" />
			<asp:BoundField
				DataField="unit2_type"
				HeaderText="Unit 2 Type" 
				SortExpression="unit2_type" />
			<asp:BoundField
				DataField="last_4_vin_unit2"
				HeaderText="Last 4 Of 2nd VIN" 
				SortExpression="last_4_vin_unit2" />
			<asp:BoundField
				DataField="email"
				HeaderText="Email" 
				SortExpression="email"/>
			<asp:BoundField
				DataField="notes"
				HeaderText="Notes" 
				SortExpression="notes"/>
			<asp:CheckBoxField
				DataField="gift_1"
				HeaderText="Recvd Gift 1" />
			<asp:BoundField
				DataField="gift_1_date"
				HeaderText="Gift 1 Date" 
				dataformatstring="{0:D}"
				SortExpression="gift_1_date" />
			<asp:hyperlinkfield 
			 	HeaderText="Edit"
			 	Text="Edit"
            	DataNavigateUrlFields="cust_code"
            	DataNavigateUrlFormatString="prefcust_edit.aspx?ID={0}" />
            </Columns>
			<FooterStyle BackColor="#CCCCCC" />
			<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
			<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
			<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
			<AlternatingRowStyle BackColor="#CCCCCC" />

		</asp:Gridview>
			
		<asp:SqlDataSource id="srcPreferredCustomers" runat="server" 
			ConnectionString="<%$ ConnectionStrings:LHYPrefCustomerServer %>" 
			providerName="MySql.Data.MySqlClient" 
			SelectCommand="SELECT * FROM PCustomers" 
			DeleteCommand="DELETE FROM PCustomers WHERE (cust_code=@cust_code)" >
			<DeleteParameters>
              <asp:Parameter Name="cust_code" Type="String" />
            </DeleteParameters>
        </asp:SqlDataSource>
	</div>
	</form>

</body>

</html>

This next one is the problem child! I cannot bold the code, so, lines 118 through 131 are the lines that make the page work when I remove them.

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

  protected void DetailsView1_ModeChanging(Object sender, System.Web.UI.WebControls.DetailsViewModeEventArgs e)
  {
    if (e.CancelingEdit == true)
    {
      Response.Redirect("UpdatePrefCustomers.aspx");
    }
  }
  protected void srcPrefCustomersEdit_Updated(object sender, SqlDataSourceStatusEventArgs e)
    {
        if (e.AffectedRows == 0)
            lblMessage.Text = "Could not update record because it has changed since you opened this page";
        else Response.Redirect("UpdatePrefCustomers.aspx");
    }
</script>
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
 <meta name="robots" content="noindex, nofollow" />
   <title>Edit Preferred Customers</title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:Label ID="lblMessage" EnableViewState="false" runat="server" />
    <asp:DetailsView DefaultMode="Edit" AutoGenerateRows="False" DataKeyNames="cust_code"
      DataSourceID="srcPrefCustomersEdit" HeaderText="Edit Preferred Customer" ID="DetailsView1" runat="server"
      Width="275px" OnModeChanging="DetailsView1_ModeChanging" >
      <Fields>
        <asp:BoundField DataField="cust_code" HeaderText="Customer Code" ReadOnly="True" SortExpression="cust_code" />
        <asp:BoundField DataField="last_name" HeaderText="Last Name" SortExpression="last_name" />
        <asp:BoundField DataField="first_name" HeaderText="First Name" SortExpression="first_name" />
        <asp:BoundField DataField="join_date" HeaderText="Join Date" DataFormatString="{0:d}" SortExpression="join_date" />
        <asp:BoundField DataField="amt_paid" HeaderText="Amount Paid" SortExpression="amt_paid" />
        <asp:TemplateField HeaderText="Birthday Month">
        	<ItemTemplate>
        	<%# Eval("months") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:DropDownList
        		ID="ddlMonths"
        		DataSourceID="srcMonths"
        		DataTextField="months"
        		DataValueField="months"
        		SelectedValue='<%# Bind("birthday_month") %>'
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Birthday Day">
        	<ItemTemplate>
        	<%# Eval("days") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:DropDownList
        		ID="ddlDays"
        		DataSourceID="srcDays"
        		DataTextField="days"
        		DataValueField="days"
        		SelectedValue='<%# Bind("birthday_day") %>'
        		AppendDataBoundItems="true"
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Unit 1 Type">
        	<ItemTemplate>
        	<%# Eval("type") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:DropDownList
        		ID="ddlUnit1Type"
        		DataSourceID="srcUnitTypes"
        		DataTextField="type"
        		DataValueField="type"
        		SelectedValue='<%# Bind("unit1_type") %>'
        		AppendDataBoundItems="true"
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>
       	<asp:BoundField DataField="last_4_vin_unit1" HeaderText="Last 4 of Unit 1 VIN" SortExpression="last_4_vin_unit1" />
       	<asp:TemplateField HeaderText="Unit 2 Type">
        	<ItemTemplate>
        	<%# Eval("unit2_type") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:DropDownList
        		ID="ddlUnit2Type"
        		DataSourceID="srcUnitTypes"
        		DataTextField="type"
        		DataValueField="type"
        		SelectedValue='<%# Bind("unit2_type") %>'
        		AppendDataBoundItems="true"
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="last_4_vin_unit2" HeaderText="Last 4 of Unit 2 VIN" SortExpression="last_4_vin_unit2" />
        <asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" />
        <asp:BoundField DataField="notes" HeaderText="Notes" SortExpression="notes" />
        <asp:CheckBoxField DataField="gift_1" HeaderText="Recvd Gift 1" SortExpression="gift_1" />
        <asp:BoundField DataField="gift_1_date" HeaderText="Date Gift 1 Recvd" DataFormatString="{0:d}" SortExpression="gift_1_date" />
        <asp:CommandField ShowEditButton="True" />
      </Fields>
      <FooterStyle BackColor="#CCCCCC" />
	  <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
	  <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
	  <AlternatingRowStyle BackColor="#CCCCCC" />
    </asp:DetailsView>
    
    <asp:SqlDataSource id="srcPrefCustomersEdit"  
    	ConnectionString="<%$ ConnectionStrings:LHYPrefCustomerServer %>" 
    	providerName="MySql.Data.MySqlClient" 
    	SelectCommand="SELECT * FROM PCustomers WHERE cust_code=@cust_code" 
    	UpdateCommand="UPDATE PCustomers 
        	SET last_name=@last_name, first_name=@first_name, join_date=@join_date, amt_paid=@amt_paid, birthday_month=@birthday_month, birthday_day=@birthday_day, unit1_type=@unit1_type, last_4_vin_unit1=@last_4_vin_unit1, unit2_type=@unit2_type, last_4_vin_unit2=@last_4_vin_unit2, email=@email, notes=@notes, gift_1=@gift_1, gift_1_date=@gift_1_date 
        	WHERE (@original_cust_code IS NULL AND cust_code IS NULL) OR cust_code=@original_cust_code 
        	AND (@original_last_name IS NULL AND last_name IS NULL) OR last_name=@original_last_name 
        	AND (@original_first_name IS NULL AND first_name IS NULL) OR first_name=@original_first_name
        	AND (@original_join_date IS NULL AND join_date IS NULL) OR join_date=@original_join_date        	
        	AND (@original_amt_paid IS NULL AND amt_paid IS NULL) OR amt_paid=@original_amt_paid 
        	AND (@original_birthday_month IS NULL AND birthday_month IS NULL) OR birthday_month=@original_birthday_month 
        	AND (@original_birthday_day IS NULL AND birthday_day IS NULL) OR birthday_day=@original_birthday_day 
        	AND (@original_unit1_type IS NULL AND unit1_type IS NULL) OR unit1_type=@original_unit1_type 
        	AND (@original_last_4_vin_unit1 IS NULL AND last_4_vin_unit1 IS NULL) OR last_4_vin_unit1=@original_last_4_vin_unit1 
        	AND (@original_unit2_type IS NULL AND unit2_type IS NULL) OR unit2_type=@original_unit2_type 
        	AND (@original_last_4_vin_unit2 IS NULL AND last_4_vin_unit2 IS NULL) OR last_4_vin_unit2=@original_last_4_vin_unit2
        	AND (@original_email IS NULL AND email IS NULL) OR email=@original_email
        	AND (@original_notes IS NULL AND notes IS NULL) OR notes=@original_notes
        	AND (@original_gift_1 IS NULL AND gift_1 IS NULL) OR gift_1=@original_gift_1
        	AND (@original_gift_1_date IS NULL AND gift_1_date IS NULL) OR gift_1_date=@original_gift_1_date"
        ConflictDetection="CompareAllValues"  
        OldValuesParameterFormatString="original_{0}"  
        runat="server" OnUpdated="srcPrefCustomersEdit_Updated" >
      <SelectParameters>
        <asp:QueryStringParameter Name="cust_code" QueryStringField="ID" Type="String" />
      </SelectParameters>
      <UpdateParameters>
        <asp:Parameter Name="last_name" Type="String" />
        <asp:Parameter Name="first_name" Type="String" />
        <asp:Parameter Name="join_date" Type="DateTime"/>
        <asp:Parameter Name="amt_paid" Type="Decimal" />
        <asp:Parameter Name="birthday_month" Type="String" />
        <asp:Parameter Name="birthday_day" Type="Int16" />
        <asp:Parameter Name="unit1_type" Type="String" />
        <asp:Parameter Name="last_4_vin_unit1" Type="String" />
        <asp:Parameter Name="unit2_type" Type="String" />
        <asp:Parameter Name="last_4_vin_unit2" Type="String" />
        <asp:Parameter Name="email" Type="String" />
        <asp:Parameter Name="notes" Type="String" />
        <asp:Parameter Name="gift_1" Type="Boolean" />
        <asp:Parameter Name="gift_1_date" Type="DateTime" />
        <asp:Parameter Name="cust_code" Type="String" />
      </UpdateParameters>
    </asp:SqlDataSource>
    
    <asp:SqlDataSource id="srcMonths" runat="server" 
    	ConnectionString="<%$ ConnectionStrings:LHYPrefCustomerServer %>" 
    	providerName="MySql.Data.MySqlClient" 
    	SelectCommand="SELECT * FROM Months ORDER BY month_number ASC" />
    <asp:SqlDataSource id="srcDays" runat="server" 
    	ConnectionString="<%$ ConnectionStrings:LHYPrefCustomerServer %>" 
    	providerName="MySql.Data.MySqlClient" 
    	SelectCommand="SELECT * FROM Days ORDER BY days ASC" />	
    <asp:SqlDataSource id="srcUnitTypes" runat="server" 
    	ConnectionString="<%$ ConnectionStrings:LHYPrefCustomerServer %>" 
    	providerName="MySql.Data.MySqlClient" 
    	SelectCommand="SELECT * FROM UnitTypes ORDER BY type_id ASC" /> 
  </div>
  </form>
</body>
</html>

The following is prefcust_insert.aspx which is not causing a problem:

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

  protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)
  {
    Response.Redirect("UpdatePrefCustomers.aspx");
  }

  protected void DetailsView1_ModeChanging(Object sender, System.Web.UI.WebControls.DetailsViewModeEventArgs e)
  {
    if (e.CancelingEdit == true)
    {
      Response.Redirect("UpdatePrefCustomers.aspx");
    }
  }
</script>
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<meta name="robots" content="noindex, nofollow" />
  <title>Insert Preferred Customers</title>
</head>
<body>
  <form id="form1" runat="server">
    <asp:DetailsView DefaultMode="Insert" AutoGenerateRows="False" DataKeyNames="cust_code"
      DataSourceID="srcPrefCustomersInsert" HeaderText="Insert Preferred Customer" ID="DetailsView1" runat="server"
      Width="325px" OnItemInserted="DetailsView1_ItemInserted" OnModeChanging="DetailsView1_ModeChanging">
      <Fields>
        <asp:TemplateField HeaderText="Customer Code">
        	<ItemTemplate>
        	<%# Eval("cust_code") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:TextBox
        		ID="txtCustCode"
        		Text='<%# Bind("cust_code") %>'
        		Runat="server" />
        	<asp:RequiredFieldValidator
        		ID="reqCustCode" 
        		ControlToValidate="txtCustCode" 
        		Text="(Required)" 
        		Display="Dynamic" 
        		SetFocusOnError="true"
        		runat="server" />
        	<asp:RegularExpressionValidator
        		id="regCustCode2"
        		ControlToValidate="txtCustCode"
        		Text="The customer code you have entered is either to long or contains invalid characters."
        		ValidationExpression="^[0-9a-zA-Z]{1,4}$"
        		SetFocusOnError="true"
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>

        <asp:BoundField DataField="last_name" HeaderText="Last Name" SortExpression="last_name" />
        <asp:BoundField DataField="first_name" HeaderText="First Name" SortExpression="first_name" />
        <asp:BoundField DataField="join_date" HeaderText="Join Date" SortExpression="join_date" />
        <asp:BoundField DataField="amt_paid" HeaderText="Amount Paid" SortExpression="amt_paid" />
        
        <asp:TemplateField HeaderText="Birthday Month">
        	<ItemTemplate>
        	<%# Eval("months") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:DropDownList
        		ID="ddlMonths"
        		DataSourceID="srcMonths"
        		DataTextField="months"
        		DataValueField="months"
        		SelectedValue='<%# Bind("birthday_month") %>'
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>
        
        <asp:TemplateField HeaderText="Birthday Day">
        	<ItemTemplate>
        	<%# Eval("days") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:DropDownList
        		ID="ddlDays"
        		DataSourceID="srcDays"
        		DataTextField="days"
        		DataValueField="days"
        		SelectedValue='<%# Bind("birthday_day") %>'
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>
        
        <asp:TemplateField HeaderText="Unit 1 Type">
        	<ItemTemplate>
        	<%# Eval("type") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:DropDownList
        		ID="ddlUnit1Type"
        		DataSourceID="srcUnitTypes"
        		DataTextField="type"
        		DataValueField="type"
        		SelectedValue='<%# Bind("unit1_type") %>'
        		AppendDataBoundItems="true"
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>

       	<asp:BoundField DataField="last_4_vin_unit1" HeaderText="Last 4 of Unit 1 VIN" SortExpression="last_4_vin_unit1" />
       	
       	<asp:TemplateField HeaderText="Unit 2 Type">
        	<ItemTemplate>
        	<%# Eval("type") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:DropDownList
        		ID="ddlUnit2Type"
        		DataSourceID="srcUnitTypes"
        		DataTextField="type"
        		DataValueField="type"
        		SelectedValue='<%# Bind("unit2_type") %>'
        		AppendDataBoundItems="true"
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>

       	<asp:BoundField DataField="last_4_vin_unit2" HeaderText="Last 4 of Unit 2 VIN" SortExpression="last_4_vin_unit2" />
        
        <asp:TemplateField HeaderText="Email" SortExpression="email" >
        	<ItemTemplate>
        	<%# Eval("email") %>
        	</ItemTemplate>
        	<EditItemTemplate>
        	<asp:TextBox
        		ID="txtEmail"
        		Text='<%# Bind("email") %>'
        		Runat="server" />
        	<asp:RegularExpressionValidator
        		id="regEmail"
        		ControlToValidate="txtEmail"
        		Text="The email you have entered does not comply with the format:  xxxx@xxxx.xxx"
        		ValidationExpression="^([0-9a-zA-Z]([-\.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$"
        		SetFocusOnError="true"
        		Runat="server" />
        	</EditItemTemplate>
        </asp:TemplateField>

		<asp:BoundField DataField="notes" HeaderText="Notes" SortExpression="notes" />
        <asp:CheckBoxField DataField="gift_1" HeaderText="Recvd Gift 1" SortExpression="gift_1" />
        <asp:BoundField DataField="gift_1_date" HeaderText="Date Gift 1 Recvd" SortExpression="gift_1_date" />
        <asp:CommandField ShowInsertButton="True" />
        
      </Fields>
      <FooterStyle BackColor="#CCCCCC" />
	  <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
	  <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
	  <AlternatingRowStyle BackColor="#CCCCCC" />
    </asp:DetailsView>
      
    <asp:SqlDataSource id="srcPrefCustomersInsert" runat="server" 
    	ConnectionString="<%$ ConnectionStrings:LHYPrefCustomerServer %>" 
    	providerName="MySql.Data.MySqlClient" 
    	SelectCommand="SELECT * FROM PCustomers WHERE cust_code=@cust_code" 
        InsertCommand="INSERT INTO PCustomers (cust_code, last_name, first_name, join_date, amt_paid, birthday_month, birthday_day, unit1_type, last_4_vin_unit1, unit2_type, last_4_vin_unit2, email, notes, gift_1, gift_1_date) VALUES (@cust_code, @last_name, @first_name, @join_date, @amt_paid, @birthday_month, @birthday_day, @unit1_type, @last_4_vin_unit1, @unit2_type, @last_4_vin_unit2, @email, @notes, @gift_1, @gift_1_date)">
      <SelectParameters>
        <asp:QueryStringParameter Name="cust_code" QueryStringField="cust_code" Type="String" />
      </SelectParameters>
      <InsertParameters>
        <asp:Parameter Name="cust_code" Type="String" />
        <asp:Parameter Name="last_name" Type="String" />
        <asp:Parameter Name="first_name" Type="String" />
        <asp:Parameter Name="join_date" Type="DateTime" />
        <asp:Parameter Name="amt_paid" Type="Decimal" />
        <asp:Parameter Name="birthday_month" Type="String" />
        <asp:Parameter Name="birthday_day" Type="Int16" />
        <asp:Parameter Name="unit1_type" Type="String" />
        <asp:Parameter Name="last_4_vin_unit1" Type="String" />
        <asp:Parameter Name="unit2_type" Type="String" />
        <asp:Parameter Name="last_4_vin_unit2" Type="String" />
        <asp:Parameter Name="email" Type="String" />        
        <asp:Parameter Name="notes" Type="String" />
        <asp:Parameter Name="gift_1" Type="Boolean" />
        <asp:Parameter Name="gift_1_date" Type="DateTime" />
      </InsertParameters>
      </asp:SqlDataSource>
    <asp:SqlDataSource id="srcMonths" runat="server" 
    	ConnectionString="<%$ ConnectionStrings:LHYPrefCustomerServer %>" 
    	providerName="MySql.Data.MySqlClient" 
    	SelectCommand="SELECT * FROM Months ORDER BY month_number ASC" />
    <asp:SqlDataSource id="srcDays" runat="server" 
    	ConnectionString="<%$ ConnectionStrings:LHYPrefCustomerServer %>" 
    	providerName="MySql.Data.MySqlClient" 
    	SelectCommand="SELECT * FROM Days ORDER BY days ASC" />
    <asp:SqlDataSource id="srcUnitTypes" runat="server" 
    	ConnectionString="<%$ ConnectionStrings:LHYPrefCustomerServer %>" 
    	providerName="MySql.Data.MySqlClient" 
    	SelectCommand="SELECT * FROM UnitTypes ORDER BY type_id ASC" />

  </form>
</body>
</html>

Re: error: 26 - Error Locating Server/Instance Specified

shantanu.gupta — Sun, 13 Dec 2009 05:00:00 GMT

Here is the resolution to error 26. Check this video to configure your error. It was a real headache for me for a long time. But this worked.

http://techpint.com/programming/error-26-%E2%80%93-error-locating-serverinstance-specified-sql-server

high loading time

RajibRoy — Tue, 08 Dec 2009 05:00:00 GMT

Hi,

I made a search engine www.khonz.com. The size of MSSQL database is around 2 GB. It is working fine. But main problem it's loading time is high for the first time and after that the speed is ok.

What will be the reason for this and how i can get rid from this.

Thanks

Rajib Roy

SSAS IDataReader Issue...

btrabon — Tue, 08 Dec 2009 05:00:00 GMT

I'm pulling data from an OLAP cube (SQL Server Analysis Services 2008) and am seeing a wierd issue. When I run the MDX query in Management Studio I get a value back in both fields being requested, but when the results come back in my IDataReader object only the first field has a value; the second field contains a null. I thought it might be an issue with the way I was using the data reader so I tried a simple test where I used the AdomdDataAdapter object to call the same MDX query and fill the results into a simple DataSet, but still got the same issue. The fields I'm trying to return are calculated members so I'm not sure if the problem is that I have two members defined at the top of my MDX or if something else is wrong. My MDX looks like the following below:

WITH 
	MEMBER [Measures].[Measure1] AS 
		'[Measures].[Calculated Field 1] + [Measures].[Calculated Field 2]' 
	MEMBER [Measures].[Measure2] AS 
		'[Measures].[Calculated Field 3] + [Measures].[Calculated Field 4]'
SELECT NON EMPTY
{
	[Measures].[Measure1],
	[Measures].[Measure2]
}
ON COLUMNS
FROM [MyCube]
WHERE
(
	[Dimension1].[Dimension1 Value].[Dimension1 Value].[123],
	[Dimension2].[Dimension2 Value].[Dimension2 Value].[456]
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Then my code to load the data into the simple dataset looks like this:

AdomdDataAdapter tempAdapter = new AdomdDataAdapter(comm);
DataSet tempDataSet = new DataSet();
tempAdapter.Fill(tempDataSet);

The comm variable is an AdomdCommand object with the MDX query loaded into it. At this point if I look at the data loaded into the tempDataSet variable I will see that "Measure1" has a value while "Measure2" does not. Has anyone else ever run into an issue like this? Any clues or hints would be greatly appreciated.

Shopping Cart Gridview

Yanayaya — Fri, 04 Dec 2009 05:00:00 GMT

I found a solution to my shopping cart scenario thanks to the kind peeps on here. However I have a quick question. I have been working through an article about making a shopping cart. I have one that works fine but...Id like to change a specific detail about the setup.

I would like the shopping cart to display all the items a users adds to their cart in a gridview and have that gridview show the total of those items. The article I worked through does not do this, it shows the items in a list view without a total.

Here is my front end code for my cart page.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Cart.aspx.vb"

Inherits="Cart" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Shopping Cart</title>

</head>

<body>

Your shopping cart:

<table style="width: 500px" cellspacing="0"

cellpadding="0" border="0">

<tr>

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

</td>

<asp:Button ID="btnRemove" runat="server"

Width="100px" Text="Remove Item" /><br /><br />

<asp:Button ID="btnEmpty" runat="server"

Width="100px" Text="Empty Cart" />

</td>

</tr>

</table>

<br />

<asp:Button ID="btnContinue" runat="server"

PostBackUrl="~/Order.aspx" Text="Continue Shopping" /> 

<asp:Button ID="btnCheckOut" runat="server" Text="Check Out" /><br />

<br />

<asp:Label ID="lblMessage" runat="server"></asp:Label>

</div>
</form>
</body>
</html>

Here is the code behind

Partial Class Cart

Inherits System.Web.UI.Page

Private Cart As SortedList

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Cart = GetCart()

If Not IsPostBack Then

Me.DisplayCart()

End If

End Sub

Private Function GetCart() As SortedList

If Session("Cart") Is Nothing Then

Session.Add("Cart", New SortedList)

End If

Return CType(Session("Cart"), SortedList)

End Function

Private Sub DisplayCart()

lstCart.Items.Clear()

Dim CartItem As CartItem

Dim CartEntry As DictionaryEntry

For Each CartEntry In Cart

CartItem = CType(CartEntry.Value, CartItem)

lstCart.Items.Add(CartItem.Display)

End Sub

Protected Sub btnRemove_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRemove.Click

If lstCart.SelectedIndex > -1 And Cart.Count > 0 Then

Cart.RemoveAt(lstCart.SelectedIndex)

Me.DisplayCart()

End If

End Sub

Protected Sub btnEmpty_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEmpty.Click

Cart.Clear()

lstCart.Items.Clear()

lblMessage.Text = ""

End Sub

Protected Sub btnCheckOut_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCheckOut.Click

lblMessage.Text = "Please Wait Checking out..."

End Sub

End Class

The trick is getting this code to use a gridview rather than this list view. Also to get the total as well.

Any help appreciated

Null Values

STEVE.com — Mon, 30 Nov 2009 05:00:00 GMT

I have a real estate website that uses a database. In my database I have three fields: Garage, NumberOfCars, GarageType. Garage is a Yes/No field and NumberOfCars and GarageType are text fields. On my web page that displays all the details about a house, I would like for the garage information to display like the following:

Garage: Yes
2 Car Attached

The bold words are my databinding Labels. I would like for "Yes" (or "No") to always be displayed, but "2" and "Attached" to be displayed, unless the field in my database is null, then I would like for "Car" also to be omitted.

Consider the following examples:

If Garage is unchecked (No), NumberOfCars has a value of 3, and GarageType is null, I would like my page display this...

Garage: No
3 Car

If Garage is checked (Yes), Number of Cars is null, and GarageType has a value of Detached, I would like my page to display this...

Garage: Yes
Detached

If Garage is checked (Yes), Number of Cars is null, and GarageType is null, I would like my page to display this...

Garage: Yes

Thanks,
Steve

Displaying Yes and No for Database Fields

STEVE.com — Sun, 29 Nov 2009 05:00:00 GMT

I have a database that has several Yes or No fields. When I insert a Label control into my site and connect it to the Yes or No field it displays "True" for Yes and "False" for No. I would like to display them with the text Yes or No. I have tried the following:

<asp:Label id="myLabel" runat="server" Text='<%# Eval("myBooleanColumn") == "true" ? "Yes" : "No" %>' />

but no matter if my database info is yes or no it will only display "Yes"

Any ideas?

Improved ORM Model Designer and New Monitoring Capabilities for the New Version of ADO.NET Data Providers

Devart Software — Thu, 26 Nov 2009 05:00:00 GMT

Devart has recently announced the release of dotConnect products for Oracle, MySQL, PostgreSQL, and SQLite ADO.NET providers that offer Entity Framework support, LINQ to SQL support, and contain an ORM model designer for developing LINQ to SQL and EF models based on different database engines.

Entity Developer 2.50, a designer for modeling and code generation for ADO.NET Entity Framework and LINQ to SQL, included in Professional Edition of every dotConnect, was greatly improved. Now Entity Developer allows to view and edit data of tables, views, and model entities, create and execute Entity SQL, LINQ to Entities, and LINQ to SQL queries against the model, eliminating the need for additional applications and reducing time for accessing these operations.

Code generation for models was completely redesigned. New T4 like template language allows you to use the full power of C# or Visual Basic in the templates, allowing virtually anything you may want for code generation. Template editor now provides syntax highlighting, code completion, and parameter information features. Syntax highlighting allows you to easily distinguish text and code blocks.

The model got two new properties, affecting code generation: File Per Class and View Generation. The File Per Class property allows placing each model class to a separate file, and View Generation adds code for pre-generating views for Entity Framework models.

In LINQ to SQL, dotConnect providers offer better performance and new DbLinqDataSource class.

To ease the database application development, integration with improved Devart dbMonitor was added, allowing you to monitor interaction with database remotely.

Please, feel free to download, try and write any comments and suggestions about our products!

Pricing and Availability

We offer a free Express edition for each product from the dotConnect product line.

The Single licenses start from as little as $99.95, and you can always choose the edition that matches your needs best. To learn more, download trial and free editions, or order a license please visit Devart site:
www.devart.com/dotconnect/.

About Devart

Devart is a software development company with 11 years of experience on the software market and over 20 thousands of devoted users.

We specialize in providing native connectivity solutions as well as comprehensive development and management tools for the most popular databases, including Oracle, SQL Server, MySQL, PostgreSQL, InterBase, Firebird, and SQLite.

For additional information about Devart, visit www.devart.com/company/.

Dynamically generate a menu from database.

SureshMali — Mon, 23 Nov 2009 05:00:00 GMT

Hi Friends,

I have a scenario where I need to generate a menu dynamically from the database.

I have a table call "Categories" which has fields CatID and CatName. There is another table Products which has fields CatID, ProdID, ProdName. Now I want to generate a menu in such a way that the Products related to a particular category are displayed under that category.

Something Like this

CDs
>>miniCD
>>Standard CD

DVDs
    >>miniDVD
    >>Standard DVD 4.6GB
    >>Dual Layer DVD 8.0 GB

In the above case CD and DVD are the category and the other items are their Products.

Would appreciate if anybody could direct me to some example code or give any idea how this can be achieved.

Thanks

GridView Devexpress control problem

chchinmaya — Tue, 17 Nov 2009 05:00:00 GMT

Hi All,

I am using Devexpress controls in my project i am using Uploadcontrol for uploading images and adding that images in to my database and showing in devexpressdataview control

My problem is after uploding images and inserting data to database and binding the updated records to devexpressdataview control but the dataset is not updating

can any one suggest me how to do this its very urgent requirement

I am trying from 3 days but i unable to get the actual data.

Thanks ALL..