Search results matching tag 'MySqlClient'

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>

MySql.Data.MySqlClient does not contain any public member

Webmonkeymon — Thu, 19 Jun 2008 04:00:00 GMT

[quote user="daxmax"]Data Connections tab [/quote]

That post was helpful and I did connect.

Now I am back at this again but this time I cannot get the mysql.web.dll to register.

MySql.Data.MySqlClient does not contain any public member

the mysql.web.dll will not register. The mysql.data.client cannot find theNamespace.

i have added them to the projects, put the dll in the web bin folder, built the solutions and the built the website.

I also have another project open and working with a registered mysql.

I do not have it in the full project source code. The full project shows me dependancies but will not let me add one.

I should have documented this better the first time.

-- i will this time.

thanks

Re: problem with connections not being releases and strange mysql error (MySqlException: Sort Aborted)

aram — Thu, 03 Apr 2008 04:00:00 GMT

Hello,

Have you tried closing the reader after your while clause is closed?

while (reader.Read()) 
{

string ticketDateString = reader.IsDBNull(0) ? string.Empty : reader.GetString(0); 
DateTime ticketDate = Convert.ToDateTime(ticketDateString);

int userId = reader.IsDBNull(1) ? -1 : reader.GetInt32(1); 
int totalTickets = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);

Ticket ticket = new Ticket(totalTickets, ticketDate, userId,string.Empty); 
listTicket.Add(ticket);

}

reader.close()

or you may want to try to pur reader.close in a 'finaly' clause of your 'try catch' .

There is a difference between disposing the reader and closing it. And I'm also guessing you have to close the connection after you close the reader. Otherwise you would get the error indicating that it is in use by a reader.

May be that is what causing the trouble.

Regards,

Re: mysql 'latin5_turkish_ci' error

senol — Wed, 12 Mar 2008 04:00:00 GMT

In asp code, i solutate Turkish character error same this code. But i can't do this in asp .net. How can i do it.

{

Set bag = Server.CreateObject("ADODB.Connection")
bag.Open "Driver={MySql ODBC 3.51 Driver}; Server=localhost; uid=root; pwd=123456; database=deneme; option=3; port=3306;"

bag.execute ("SET CHARACTER SET latin5;")
bag.execute ("SET collation_connection = 'latin5_turkish_ci';")

}

asp.net c# mysql database

senol — Mon, 04 Feb 2008 05:00:00 GMT

help

asp.net c# mysql database connection string ?

PHPmyAdmin in asp

afroxav — Tue, 21 Aug 2007 04:00:00 GMT

I've being searching for an equivalent to phpmyadmin to administer my MySQL database hosted on Windows Vista. I'd like to know if there is one that i could use...

Thanks.

Re: INSERT into MYSQL results to NULL values.. I NEED HELP!!!

mrkaffa — Sun, 01 Jul 2007 04:00:00 GMT

Here is my code below...I need similar help as well. I've seen this thread before and not sure how to apply it. I don't get any errors and I know it gets through the code because when I click my Insert button (of type "link"), I get redirected to my defaultmainpage.aspx. I don't get any data into the actual table though. My declarations are fine and my connection string is fine as well because I use it elsewhere and works just fine. I'm thinking someone is off. Could use some help on this one!

myConnection = New MySqlConnection(MySQLConnectionString)
'qrySelect = "SELECT Title, Category, Description, Price, Phone, Email, State, UserPassword, AdNum FROM Ads"
qryInsert = "INSERT INTO Ads (Title, Category, Description, Price, Phone, Email, State, UserPassword) VALUES (?Title, ?Category, ?Description, ?Price, ?Phone, ?Email, ?State, ?UserPassword)"
qrySelect = "SELECT Title, Category, Description, Price, Phone, Email, State, UserPassword, AdNum FROM Ads"
'Dim cmdDel As MySqlCommand = New MySqlCommand(strDel, New MySqlConnection(DBC))
myDataAdapter = New MySqlDataAdapter(qrySelect, myConnection)
myDataSet = New Dataset()
myDataAdapter.Fill(myDataSet, "Ads")
Dim iCmd As MySql.Data.MySqlClient.MySqlCommand
'Dim iCmd As MySqlCommand
iCmd = New MySqlCommand(qryInsert, myConnection)
'iCmd.Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter("?Title", MySql.Data.MySqlClient.MySqlDbType.VarChar, 50, "Title"))
iCmd.Parameters.Add(New MySqlParameter("?Title", MySqlDbType.VarChar, 50, "Title"))
'iCmd = New MySqlCommand(qryInsert, myConnection)
'iCmd.Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter("?Category", MySql.Data.MySqlClient.MySqlDbType.VarChar, 50, "Category"))
iCmd.Parameters.Add(New MySqlParameter("?Category", MySqlDbType.VarChar, 50, "Category"))
iCmd.Parameters.Add(New MySqlParameter("?Description", MySqlDbType.VarChar, 50, "Description"))
iCmd.Parameters.Add(New MySqlParameter("?Price", MySqlDbType.VarChar, 50, "Price"))
iCmd.Parameters.Add(New MySqlParameter("?Phone", MySqlDbType.VarChar, 50, "Phone"))
iCmd.Parameters.Add(New MySqlParameter("?Email", MySqlDbType.VarChar, 50, "Email"))
iCmd.Parameters.Add(New MySqlParameter("?State", MySqlDbType.VarChar, 50, "State"))
iCmd.Parameters.Add(New MySqlParameter("?UserPassword", MySqlDbType.VarChar, 50, "UserPassword"))
myDataAdapter.InsertCommand = iCmd
MySQLDataGrid.DataSource = myDataSet.Tables("Ads").DefaultView
MySQLDataGrid.DataBind()

Response.Redirect("maindefault.aspx")

I am thinking that it is something simple. This code gets called after an Insert button is clicked on my DataGrid...

Re: SqlDataSource together with MySql ?

mrkaffa — Sat, 30 Jun 2007 04:00:00 GMT

Here is how I can connect to MySql with Godaddy shared hosting. I can grab data from a table and display it in a gridview. I am having challenges with the insert, update and delete at the moment - but I got this because I could not find anything out there with the combination I need. Shared hosting, VB and ASP.NET 2.0 with 5.0 MySql database (still have not found everything I need at the moment - but this is a start)...Hope this helps someone. Note, if you attempt to click on the data once it is displayed, it is looking for a details.aspx page I got from ASP.NET for Dummies - just fyi!
Also, remember, I am using the infamous recompiled MySql.dll which can be found in some earlier threads. Godady shared hosting is set to medium trust so take that into consideration. I placed the .dll it in my \bin folder and I am really good (if I can just get past these postback issues).

<%@ Page Explicit="True" Language="VB" Debug="True" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>

Sub Page_Load(sender As Object, e As EventArgs)
     Dim myConnection As MySqlConnection
     Dim myDataAdapter As MySqlDataAdapter
     Dim myDataSet As DataSet
     Dim qryFromDefault As String
     Dim strSQL As String
     Dim MySQLConnectionString As String

       MySQLConnectionString = "Server=yourserver;" & _
          "Database=yourdatabasename;" & _
          "Uid=yourlogin;" & _
          "Pwd=yourpassword;" & _
          "Connect Timeout=30;"

'
'Comments:
'
'Assuming you have a mysql 5.0 database and a table you can query from
'
'myConnection = New MySqlConnection(MySQLConnectionString)
'
'qryFromDefault = Request.QueryString("NameOfParamaterFromCallingPage") - you may use this code if this aspx is called from another page
'and use it below as a parameter of sorts
'
'strSQL = "SELECT * FROM MyTable WHERE (NameOfMyTableColumnName = '" & qryFromDefault & "');"
'
'But here is the easier route for now
'
     strSQL = "SELECT * FROM MyTable"
     myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
     myDataSet = New Dataset()
     myDataAdapter.Fill(myDataSet, "YourTableNameHere")
     MySQLDataGrid.DataSource = myDataSet.Tables("YourTableNameHere").DefaultView
     MySQLDataGrid.DataBind()
End Sub

'
'More code below I got from somewhere else
'
Sub PageIndexChanging(sender as Object, e as GridViewPageEventArgs)
     MySQLDataGrid.PageIndex = e.NewPageIndex
     MySQLDataGrid.DataBind()
End Sub

</script>

<html>

<h1>Classy Classifieds</h1>

</Columns>

</asp:GridView>
</form>

</body>

</html>

Re: SqlDataSource together with MySql ?

mrkaffa — Fri, 29 Jun 2007 04:00:00 GMT

I am checking with Godaddy since it's shared hosting. I am hoping they will load it for me. We will see. Otherwise, I will have to keep going the same route I am taking now and that's with using SqlAdapters, DataSets, etc. - which is fine - but it would be fun to use the SqlDataSource features.

Re: SqlDataSource together with MySql ?

mrkaffa — Fri, 29 Jun 2007 04:00:00 GMT

First, I am thankful for all the info I have seen here.

Next, I did use the recomplied MySql.dll and works but I cannot get it to work with the SqlDataSource fully in terms of adding records and deleting records.

So I looked for another solution...

Found this older .dll called MarcoMySql.dll out there on http://lab.aspitalia.com/31/MarcoMySql-DataProvider.aspx - after I downloaded his MarcoMySql.dll and copied it to my /bin folder, I was FINALLY able to add this code to my web.config file successfully (on godaddy) to use the <asp:SqlDataSource> tag BUT I am only able to read data. I'm not able to insert, edit or delete records yet using the <SqlDataSource> tag with it. I'm thinking it's due to the older .dll I have.

<system.data>

</DbProviderFactories>

</system.data>

I use this to reference it in the page in the .aspx I am using:

<% Import Namespace="MySql.Data.MySqlClient" %>

The challenge I have is that it's an old version as you can see and wanted to know if there was an updated MySql.dll out there with an updated <DbProviderFactories> tag. My MySql database is 5.0, I have ASP.NET 2.0 runtime.

Any help out there??? Let me know if you need more info!