Syntax error using INNER JOIN and SqlDataSource with MySQL

Question

I am trying to write an update command that joins 2 tables using an SqlDataSource. I have it working with 1 table, but when I put my INNER JOIN syntax in I get thrown an error. It says "My syntax is wrong. Check MySql manual for correct syntax"

Here is my Code from my ASPX page.:

    <asp:SqlDataSource ID="AdminSalesmanDetailDS" runat="server" 
        ConnectionString="<%$ ConnectionStrings:intelliairConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:intelliairConnectionString.ProviderName %>" 
        SelectCommand="individual_AddressByIndividualID" 
        SelectCommandType="StoredProcedure" 
        UpdateCommand="UPDATE individual SET
          FarmName = @FarmName,
          FirstName = @FirstName, 
          MiddleName = @MiddleName,
          Address1 = @Address1,
          City = @City
          INNER JOIN address a ON i.IndividualID = a.IndividualID,
         WHERE IndividualID=@IndividualID">
        <SelectParameters>
            <asp:ControlParameter ControlID="gvSalesman" Name="oIndividualID" 
                PropertyName="SelectedValue" Type="Int32" />
        </SelectParameters>
          <UpdateParameters>
   
          <asp:ControlParameter Name="FarmName" ControlId="fvAdminSalesmanDetail$CompanyTextBox" PropertyName="Text"/>
          <asp:ControlParameter Name="FirstName" ControlId="fvAdminSalesmanDetail$FirstNameTextBox" PropertyName="Text"/>
          <asp:ControlParameter Name="MiddleName" ControlId="fvAdminSalesmanDetail$MiddleNameTextBox" PropertyName="Text"/>
          <asp:ControlParameter Name="Address1" ControlId="fvAdminSalesmanDetail$Address1TextBox" PropertyName="Text"/>
          <asp:ControlParameter Name="City" ControlId="fvAdminSalesmanDetail$cityTextBox" PropertyName="Text"/>
      </UpdateParameters>
     

    </asp:SqlDataSource>

UPDATE

I have it working with 2 tables now. However it updates every Individual in the Table. For example: I am trying to update 1 person and I change the first name to Mark. When I click update , it changes everyone in the Database First Name to Mark.

Here is my new Code from my ASPX page.:

    <asp:SqlDataSource ID="AdminSalesmanDetailDS" runat="server" 
        ConnectionString="<%$ ConnectionStrings:intelliairConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:intelliairConnectionString.ProviderName %>" 
        SelectCommand="individual_AddressByIndividualID" 
        SelectCommandType="StoredProcedure" 
     UpdateCommand="UPDATE individual i
         inner join address a
         on a.individualID =  i.individualID 
        set 
        
        i.FarmName = @FarmName,
        i.FirstName = @FirstName,
        i.LastName = @LastName,
        i.MiddleName = @MiddleName,
        i.Phone = @Phone, 
        i.PhoneExtention = @PhoneExtention,
        i.MobilPhone = @MobilPhone,
        i.Fax = @Fax, 
        i.Email = @Email,
        
        a.Address1 = @Address1,
        a.Address2 = @Address2,
        a.City = @City,
        a.State = @State,
        a.Zip = @Zip,
        a.Country = @Country
        where 
        i.IndividualID = i.IndividualID">
        <SelectParameters>
            <asp:ControlParameter ControlID="gvSalesman" Name="oIndividualID" 
                PropertyName="SelectedValue" Type="Int32" />
        </SelectParameters>
          <UpdateParameters>
   
          <asp:ControlParameter Name="FarmName" ControlId="fvAdminSalesmanDetail$CompanyTextBox" PropertyName="Text"/>
          <asp:ControlParameter Name="FirstName" ControlId="fvAdminSalesmanDetail$FirstNameTextBox" PropertyName="Text"/>
          <asp:ControlParameter Name="MiddleName" ControlId="fvAdminSalesmanDetail$MiddleNameTextBox" PropertyName="Text"/>
          <asp:ControlParameter Name="Address1" ControlId="fvAdminSalesmanDetail$Address1TextBox" PropertyName="Text"/>
          <asp:ControlParameter Name="City" ControlId="fvAdminSalesmanDetail$cityTextBox" PropertyName="Text"/>
      </UpdateParameters>
     

    </asp:SqlDataSource>

Just a tip. When you update your question, don't erase the original code or question. When people come and see the question as it is now, they'll wonder why @jadarnel answered with almost the same as what you have. — ypercubeᵀᴹ, Feb 16 '12 at 22:15

Josh Darnell · Accepted Answer · 2012-02-17T13:38:15.820

3

You have your JOIN after the SET, and you're not being specific about which table each field is referencing. I think, to use JOIN in an UPDATE, you need syntax more like this:

UpdateCommand=" UPDATE 
                    individual i
                INNER JOIN 
                    address a 
                        ON i.IndividualID = a.IndividualID 
                SET 
                    i.FarmName = @FarmName, 
                    i.FirstName = @FirstName,  
                    i.MiddleName = @MiddleName, 
                    a.Address1 = @Address1, 
                    a.City = @City 
                WHERE 
                    i.IndividualID=@IndividualID" >

Edit: Based on the update to your question, it looks like you have (in your WHERE clause)

i.IndividualID=i.IndividualID

This is what's causing all your records to be updated (because that statement is always true). As in my above example, you need to have

i.IndividualID=@IndividualID

This way only the row(s) whose ID matches your parameter gets updated (presumably just one).

edited Feb 17 '12 at 13:38

answered Feb 16 '12 at 21:01

Josh Darnell

11,304
9
38
66

Ok. thanks. That did help in that I can now update both tables. However, It's applying the update to every INDIVIDUAL IN THE DATABASE. It just needs to update 1 individual. In other words. I updated an individuals First Name: Mark. and everyone in the database now has the first name Mark. I will repost my new query. Is my where clause not correct?? – EB. Feb 16 '12 at 22:05
I would guess that the Names are in the `Individual` table and the Address and City in the `Address` table :) – ypercubeᵀᴹ Feb 16 '12 at 22:06
@ypercube Haha, good point =P I'll update the answer in a bit. – Josh Darnell Feb 16 '12 at 22:08
@EB.: You have `where i.IndividualID = i.IndividualID`. This is causing all the rows to be updated. You should change it to `where i.IndividualID = @IndividualID` – ypercubeᵀᴹ Feb 16 '12 at 22:11
@EB. Ypercube is correct. You just copied the query incorrectly =) – Josh Darnell Feb 16 '12 at 23:54
1

@Ypercube. This works. Thanks! Success! Green Check marks for all!! – EB. Feb 17 '12 at 15:34

Syntax error using INNER JOIN and SqlDataSource with MySQL

1 Answers1