locked
SQLdatasource + gridview + mysql RRS feed

  • Question

  • User748783080 posted

    Hi,

     

    I have a sqldatasource and a gridview. I work with a mysql 3.51 database.

    My problem is :

    When I try tu update a record through the update command of the gridview, it doesn't work.  Here is my code :

    <asp:SqlDataSource ID="SQL_Panier" runat=server ConnectionString="<%$ ConnectionStrings:Connection to NI %>" ProviderName="<%$ ConnectionStrings:Connection to NI.ProviderName %>" SelectCommand="selectcommand"

    UpdateCommand="Update Panier SET qtt=@qtt,commentaires=@commentaires WHERE email=?"

    DeleteCommand="delete from Panier where email=? and code=@code">

    <SelectParameters>

    <asp:SessionParameter Name="?" SessionField="email" />

    </SelectParameters>

    </asp:SqlDataSource>

    Voici votre panier :<br />

    <br />

    <asp:GridView ID="Grille_Panier" runat="server" AllowPaging="True" AllowSorting="True"

    AutoGenerateColumns="False" DataSourceID="SQL_Panier">

    <Columns>

    <asp:CommandField ShowSelectButton="True" />

    <asp:CommandField ShowEditButton="True" />

    <asp:CommandField ShowDeleteButton="True" />

    <asp:BoundField DataField="code" HeaderText="code" SortExpression="code" ReadOnly="True" />

    <asp:BoundField DataField="courte" HeaderText="courte" SortExpression="courte" ReadOnly="True" />

    <asp:BoundField DataField="qtt" HeaderText="qtt" SortExpression="qtt" />

    <asp:BoundField DataField="prixttc" HeaderText="prixttc" SortExpression="prixttc" ReadOnly="True" />

    <asp:BoundField DataField="commentaires" HeaderText="commentaires" SortExpression="commentaires" />

    </Columns>

    <EmptyDataTemplate>

    Actuellement, votre panier ne contient aucun produit.

    </EmptyDataTemplate>

    </asp:GridView>

     

     

     

    The select command works but not the update command!!!!

    Can you help me please?

     

    Thanks

    Monday, August 7, 2006 5:30 AM

All replies

  • User748783080 posted

    I tried to change "@qtt" in mys update command by a number like 5. And it works!!!!!

     

    So the problem comes from the field I change in the gridview.

     

    The parameter "@qtt" doesn't contain the value I just put in the textbox!!!!

    Sunday, August 20, 2006 4:50 AM
  • User51465595 posted

    I have the same problem.
    The thing is that all the @DataField contains NULL.

    If found that out when I tried this:

    UpdateCommand="UPDATE titemgroup SET  Module=@Module, Title='test', Description=@Description  WHERE ItemGroupID=6"

    All fields contained data before the Update-call, but afterwards Title was set to "test", Module and Description was NULL.

     

    Wednesday, August 23, 2006 10:01 AM
  • User51465595 posted

    Tried the same calls using a MS Access db instead of MySQL and it all works... Very annoying.

    Does anyone know how to get it to work with MySQL?

    Wednesday, August 23, 2006 10:23 AM
  • User51465595 posted

    Right, MySQL does not support @.

    Simply remove all @-signs in your sql-calls and it works like a charm.

    like this:
    UpdateCommand="Update Panier SET qtt=qtt,commentaires=commentaires WHERE email=?" DeleteCommand="delete from Panier where email=? and code=code">

    I hope it works for you aswell.

    Wednesday, August 23, 2006 10:29 AM
  • User51465595 posted

    Sorry, nevermind my last post...

    I was a bit too fast and confused so I was still using the Access connection. Just ignore my last post as it's very wrong.

    Sorry for that..

    Wednesday, August 23, 2006 10:55 AM
  • User748783080 posted

    Does anybody know how can I get the modified value for a field?

     

    If we can do that, we can build the query in vb and put it in the sqldatasource.

     

    In sqldatasource_rowupdating event :

     

    dim sql as string="Update Table set field1=" & ModifiedValue & " where field0=value"

    sqldatasource.updatecommand=sql

     

     

    Thursday, August 24, 2006 7:18 AM
  • User-925735727 posted

    You could try using the request obect. It goes something like:

    <%= Request.Forms("itemName") %>

     Notice the field names in the actual posting HTML though,

    <input name="GridView1$ctl04$ctl03" type="text" value="0" size="5" title="test2" />

     If you can't grab hold of the title name with the request object then you might just have to parse the column out of the name and go from there or maybe even try doing it by index #... can't remember all the ins and outs...

    Also, just in case you don't know, turn on page tracing so you can see exactly what variable names the server is recievign on a post.

     Hope this helps.

     

    Tuesday, August 29, 2006 9:53 AM
  • User-925735727 posted

    I had been using the format '@variableName' but that doesn't work. Use a question mark ? by itself. It's kind of like a numbered array. The first ? is for the first parameter in the list, the second ? is for the second parament and so on. Here is some sample code that works for me:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT advertise4me.fsdfsdaf.* FROM advertise4me.fsdfsdaf"

    UpdateCommand="UPDATE advertise4me.fsdfsdaf SET TESTDATA =? WHERE ID =?">

    <UpdateParameters>

    <asp:Parameter Name="TESTDATA" />

    <asp:Parameter Name="ID" />

    </UpdateParameters>

    </asp:SqlDataSource>

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">

    <Columns>

    <asp:CommandField ShowEditButton="True" />

    <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />

    <asp:BoundField DataField="TESTDATA" HeaderText="TESTDATA" SortExpression="TESTDATA" />

    </Columns>

    </asp:GridView> 

    Oh, by the way, be careful of atributes to do with visibility and readonly... I don't have it figured out yet, but they block the variables from being posted or something.

    Please let me know if this works.

     

     

     

    Tuesday, August 29, 2006 6:09 PM
  • User49523753 posted

    This did not work for me, please help i cannot get mysql db to update.

    <asp:GridView ID="GridView1" AllowSorting="true" AllowPaging="false" Runat="server"

    DataSourceID="SqlDataSource1" AutoGenerateEditButton="true" DataKeyNames="Team"

    AutoGenerateColumns="False">

    <Columns>

    <asp:BoundField HeaderText="prid" DataField="prid" />

    <asp:BoundField ReadOnly="true" HeaderText="Team" DataField="Team" />

    <asp:TemplateField HeaderText="Rank" SortExpression="rank">

    <ItemTemplate>

    <asp:Label Runat="server" Text='<%# Bind("rank") %>' ID="label2"></asp:Label>

    </ItemTemplate>

    <EditItemTemplate>

    <asp:DropDownList ID="ddlPr" runat="server" SelectedValue='<%# Bind("rank") %>' >

    <asp:ListItem>1</asp:ListItem>

    <asp:ListItem>2</asp:ListItem>

    <asp:ListItem>3</asp:ListItem>

    <asp:ListItem>4</asp:ListItem>

    <asp:ListItem>5</asp:ListItem>

    <asp:ListItem>6</asp:ListItem>

    <asp:ListItem>7</asp:ListItem>

    <asp:ListItem>8</asp:ListItem>

    <asp:ListItem>9</asp:ListItem>

    <asp:ListItem>10</asp:ListItem>

    <asp:ListItem>11</asp:ListItem>

    <asp:ListItem>12</asp:ListItem>

    <asp:ListItem>13</asp:ListItem>

    <asp:ListItem>14</asp:ListItem>

    <asp:ListItem>15</asp:ListItem>

    <asp:ListItem>16</asp:ListItem>

    <asp:ListItem>17</asp:ListItem>

    <asp:ListItem>18</asp:ListItem>

    <asp:ListItem>19</asp:ListItem>

    <asp:ListItem>20</asp:ListItem>

    <asp:ListItem>21</asp:ListItem>

    <asp:ListItem>22</asp:ListItem>

    <asp:ListItem>23</asp:ListItem>

    <asp:ListItem>24</asp:ListItem>

    <asp:ListItem>25</asp:ListItem>

    <asp:ListItem>26</asp:ListItem>

    <asp:ListItem>27</asp:ListItem>

    <asp:ListItem>28</asp:ListItem>

    <asp:ListItem>29</asp:ListItem>

    <asp:ListItem>30</asp:ListItem>

    </asp:DropDownList>

    </EditItemTemplate>

    </asp:TemplateField>

    </Columns>

    </asp:GridView>

    <br />

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:testConnectionString %>"

    ProviderName="<%$ ConnectionStrings:testConnectionString.ProviderName %>"

    SelectCommand="Select prid, team, rank, wins, loses, ties, otl, comment from power_rankings_holder"

    UpdateCommand="UPDATE power_rankings_holder SET rank = ? WHERE prid = ?" >

    <UpdateParameters>

    <asp:Parameter name="rank" />

    <asp:Parameter name="prid" />

    </UpdateParameters>

    </asp:SqlDataSource>

    Friday, January 12, 2007 12:40 PM
  • User49523753 posted

    This did not work for me, please help i cannot get mysql db to update.

    <asp:GridView ID="GridView1" AllowSorting="true" AllowPaging="false" Runat="server"

    DataSourceID="SqlDataSource1" AutoGenerateEditButton="true" DataKeyNames="Team"

    AutoGenerateColumns="False">

    <Columns>

    <asp:BoundField HeaderText="prid" DataField="prid" />

    <asp:BoundField ReadOnly="true" HeaderText="Team" DataField="Team" />

    <asp:TemplateField HeaderText="Rank" SortExpression="rank">

    <ItemTemplate>

    <asp:Label Runat="server" Text='<%# Bind("rank") %>' ID="label2"></asp:Label>

    </ItemTemplate>

    <EditItemTemplate>

    <asp:DropDownList ID="ddlPr" runat="server" SelectedValue='<%# Bind("rank") %>' >

    <asp:ListItem>1</asp:ListItem>

    <asp:ListItem>2</asp:ListItem>

    <asp:ListItem>3</asp:ListItem>

    <asp:ListItem>4</asp:ListItem>

    <asp:ListItem>5</asp:ListItem>

    <asp:ListItem>6</asp:ListItem>

    <asp:ListItem>7</asp:ListItem>

    <asp:ListItem>8</asp:ListItem>

    <asp:ListItem>9</asp:ListItem>

    <asp:ListItem>10</asp:ListItem>

    <asp:ListItem>11</asp:ListItem>

    <asp:ListItem>12</asp:ListItem>

    <asp:ListItem>13</asp:ListItem>

    <asp:ListItem>14</asp:ListItem>

    <asp:ListItem>15</asp:ListItem>

    <asp:ListItem>16</asp:ListItem>

    <asp:ListItem>17</asp:ListItem>

    <asp:ListItem>18</asp:ListItem>

    <asp:ListItem>19</asp:ListItem>

    <asp:ListItem>20</asp:ListItem>

    <asp:ListItem>21</asp:ListItem>

    <asp:ListItem>22</asp:ListItem>

    <asp:ListItem>23</asp:ListItem>

    <asp:ListItem>24</asp:ListItem>

    <asp:ListItem>25</asp:ListItem>

    <asp:ListItem>26</asp:ListItem>

    <asp:ListItem>27</asp:ListItem>

    <asp:ListItem>28</asp:ListItem>

    <asp:ListItem>29</asp:ListItem>

    <asp:ListItem>30</asp:ListItem>

    </asp:DropDownList>

    </EditItemTemplate>

    </asp:TemplateField>

    </Columns>

    </asp:GridView>

    <br />

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:testConnectionString %>"

    ProviderName="<%$ ConnectionStrings:testConnectionString.ProviderName %>"

    SelectCommand="Select prid, team, rank, wins, loses, ties, otl, comment from power_rankings_holder"

    UpdateCommand="UPDATE power_rankings_holder SET rank = ? WHERE prid = ?" >

    <UpdateParameters>

    <asp:Parameter name="rank" />

    <asp:Parameter name="prid" />

    </UpdateParameters>

    </asp:SqlDataSource>

    Friday, January 12, 2007 12:40 PM
  • User49523753 posted

    I figured it out and I am pasting my aspx code here for future people who search and find this thread....

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:testConnectionString %>"

    ProviderName="<%$ ConnectionStrings:testConnectionString.ProviderName %>"

    DeleteCommand="DELETE from power_rankings_holder where prid = ?prid"

    InsertCommand="INSERT into power_rankings_holder rank values(?rank)"

    SelectCommand="SELECT prid, team, rank, wins, loses, ties, otl, comment from power_rankings_holder"

    UpdateCommand="UPDATE power_rankings_holder SET rank = ?rank, comment = ?comment WHERE prid = ?prid" >

    <UpdateParameters>

    <asp:Parameter name="?rank" Direction="Input" />

    <asp:Parameter name="?comment" Direction="Input" Type="string" />

    <asp:Parameter name="?prid" Direction="Input" />

    </UpdateParameters>

    Monday, January 15, 2007 10:43 AM
  • User-337618065 posted

    Hello Horizon!

    I tried your solution but i can't get it to work. I get an error message from Mysql that says:

    ERROR [42S21] [MySQL][ODBC 3.51 Driver][mysqld-5.0.27-community-nt]Unknown column 'NULLUID' in 'where clause' .

     UID is the table column and the name of the bound column in the grid. It seems as ASP converts the "? to a NULL value. Have you got any idea of what my problem is?

     

     

    I figured it out and I am pasting my aspx code here for future people who search and find this thread....

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:testConnectionString %>"

    ProviderName="<%$ ConnectionStrings:testConnectionString.ProviderName %>"

    DeleteCommand="DELETE from power_rankings_holder where prid = ?prid"

    InsertCommand="INSERT into power_rankings_holder rank values(?rank)"

    SelectCommand="SELECT prid, team, rank, wins, loses, ties, otl, comment from power_rankings_holder"

    UpdateCommand="UPDATE power_rankings_holder SET rank = ?rank, comment = ?comment WHERE prid = ?prid" >

    <UpdateParameters>

    <asp:Parameter name="?rank" Direction="Input" />

    <asp:Parameter name="?comment" Direction="Input" Type="string" />

    <asp:Parameter name="?prid" Direction="Input" />

    </UpdateParameters>

    Friday, January 19, 2007 6:09 AM
  • User49523753 posted
    Sounds like you need single tick marks around the ?UID in your query....try that and see if it works.  What data type is UID?
    Wednesday, January 24, 2007 4:08 PM
  • User-337618065 posted
    No, it doesn't work. What development environment do you use?
    Friday, January 26, 2007 3:36 AM
  • User49523753 posted

    I am using Visual Studio 2005 / ASP 2.0 and mySQL 5 I believe.

    I am also using a mySQL .net Connector 5.0 from mysql's site

    Friday, January 26, 2007 1:59 PM
  • User1619048992 posted

    nothing worked for me :(

    any solution?!

    Saturday, February 17, 2007 4:20 PM
  • User-1028604560 posted

    This has been bugging me for a few days now as I too have got into the same problem.  However, the posts in this thread have helped me sort it out and get my GridView working, and this is what I did.

    Although I haven't found direct information on the '@' not working with MySQL queries, it definitely doesn't work for me.  So that leaves the '?' notation and UpdateParameters tags, which works for me with the code below.  Hope this helps someone else.

     

    <asp:SqlDataSource id="<span" class="st">"SqlDataSource1" runat="server" ConnectionString="&lt;%$ ConnectionStrings:myConnectionString %>"
                ProviderName="&lt;%$ ConnectionStrings:myConnectionString.ProviderName %>"
                SelectCommand="SELECT DISTINCT ClientID, Company, Name, Address1, Address2, City, State, ZIP, CountryCode, Email, Phone, Fax, WebSite FROM client ORDER BY Company"
                UpdateCommand="UPDATE client SET Company=?, Name=?, Address1=?, Address2=?, City=?, State=?, ZIP=?, CountryCode=?, Phone=?, Fax=?, WebSite=?, Email=? WHERE ClientID=?"
                DeleteCommand="DELETE FROM client WHERE ClientID=?">
            <UpdateParameters>
                <asp:Parameter type="<span" class="st">"String" Name="Company" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="Name" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="Address1" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="Address2" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="City" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="State" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="ZIP" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="CountryCode" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="Phone" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="Fax" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="WebSite" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="Email" Direction="Input"></asp:Parameter>
                <asp:Parameter type="<span" class="st">"string" Name="ClientID" Direction="Input"></asp:Parameter>
            </UpdateParameters>
            </asp:SqlDataSource>
     
     

     



     

    Tuesday, February 20, 2007 1:52 PM
  • User-1028604560 posted

    Sorry about that I would have thought the "Source Code" button in the WYSIWYG would actually do something nice...here's the direct copy & paste of the code :

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
                ProviderName="<%$ ConnectionStrings:myConnectionString.ProviderName %>"
                SelectCommand="SELECT DISTINCT ClientID, Company, Name, Address1, Address2, City, State, ZIP, CountryCode, Email, Phone, Fax, WebSite FROM client ORDER BY Company"
                UpdateCommand="UPDATE client SET Company=?, Name=?, Address1=?, Address2=?, City=?, State=?, ZIP=?, CountryCode=?, Phone=?, Fax=?, WebSite=?, Email=? WHERE ClientID=?"
                DeleteCommand="DELETE FROM client WHERE ClientID=?">
            <UpdateParameters>
                <asp:Parameter Type="String" Name="Company" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="Name" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="Address1" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="Address2" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="City" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="State" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="ZIP" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="CountryCode" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="Phone" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="Fax" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="WebSite" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="Email" Direction="Input"></asp:Parameter>
                <asp:Parameter Type="string" Name="ClientID" Direction="Input"></asp:Parameter>
            </UpdateParameters>
     </asp:SqlDataSource>

     

    Tuesday, February 20, 2007 1:56 PM
  • User517737281 posted

    Thanks also to previous posters I got this working for both update and delete code is below hope it helps (ODBC connection used).

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"

    ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>"

    SelectCommand="SELECT * FROM name"

    DeleteCommand="DELETE FROM name WHERE id = ?"

    UpdateCommand="UPDATE name SET fname = ? WHERE id = ?"

    OldValuesParameterFormatString="{0}">

    <DeleteParameters>

    <asp:Parameter Name="id" Type="Int32" />

    </DeleteParameters>

    <UpdateParameters>

    <asp:Parameter Name="fname" Type="String" Direction="Input" />

    <asp:Parameter Name="id" Type="Int32" Direction="Input" />

    </UpdateParameters>

    </asp:SqlDataSource>

     

    Tuesday, October 30, 2007 7:42 AM
  • User1867197125 posted

    Mister, Any solution about it ?

    Friday, November 30, 2007 8:20 AM
  • User1345125660 posted

     hi, could you guys guide me on this one?

    <

    asp:SqlDataSource ID="SQL_Panier" runat=server ConnectionString="<%$ ConnectionStrings:Connection to NI %>" ProviderName="<%$ ConnectionStrings:Connection to NI.ProviderName %>" SelectCommand="selectcommand" UpdateCommand="Update Panier SET qtt=@qtt,commentaires=@commentaires WHERE email=?" DeleteCommand="delete from Panier where email=? and code=@code"> <SelectParameters> <asp:SessionParameter Name="?" SessionField="email" /> </SelectParameters>

    </

    asp:SqlDataSource>

     

    the above is the posters original code, now im trying to implement  gridview + mysql implementation and im stuck in the ProviderName property. i have mysql connector 5.0.9 if i am not mistaken, and i have this line in my web.config <add name="ConnString" connectionString="Database=thechoirportal_dev;Data Source=localhost; User Id=root; Password=password"/>. now, i dont know what to put in the ProviderName property. i trie ProviderName="<%$ MySql.Data.MySqlClient%>" and this error shows --> the connection name 'ConnString:MySql.Data' was not found in the applications configuration or the connection is empty. i tried to make it ProviderName="MySql.Data.MySqlClient", now the error is --> Unable to find the  requested .Net Framework Data Provider. It may not be installed. i tried to do this --><%@ import Namespace="MySql.Data.MySqlClient" %> and still a no go. im not sure about the first error because i was able to use it throughout my project, except for this page. anybody please guide me. thank you in advance.

     

    **errors are in bold fonts. 

    Wednesday, January 9, 2008 10:56 AM
  • User-2098791946 posted

    I had this same problem for a long time. Depending the way you define parameters in the UpdateParemeter collection, the update queries do not make it to the MySQL server. I activated the log option to see the queries executed and I realised that the update in some cases make it to the server but with all parameters with value NULL.

    The solution I found to all problems related to updating a MySQL database using ASP.NET in VS.2005 and with Connector/NET 5.1.4 is the following:

    1. The update query must have parameters defined as ?column_name. For example

    update table set column1 = ?column1, column2 = ?column2 where ...

    2. In Visual Studio, for the SqlDataSource UpdateQuery parameters collection you have to add one parameter for each parameter in the update query but the name of the parameter (and this is the key point) must be just the column name without the ?. This has to do with the way .NET assign values on bound fields (in my case I am using a FormView) to the parameter collection of the update query.

    Using this, all my update queries make it to the server with the values in the formview fields.

    I hope this may help. It dit in my case.

    Saludos,

    Saturday, January 12, 2008 2:33 PM
  • User-104177314 posted

    Thank you schido.

    I have been struggling with the update and because of your tip, got it working a few minutes ago.

    Thank you again.

    Sri
     

    Wednesday, January 16, 2008 5:13 AM
  • User-749846800 posted

    FYI: Here is complete reference code of two different ways to get MySQL (v 5.1.22) to work with the ASP 2.0 GridView (update and delete) using the MySQL Connector/ODBC 5.1 or the MySQL Connector/Net 5.1.4:

    Here is how my table is defined in the MySQL catalog:

    CREATE TABLE `tbltest` (
      `testId` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `value1` varchar(45) DEFAULT NULL,
      PRIMARY KEY (`testId`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

    Here are my connection strings in my web.config:

     <configuration><appSettings /><connectionStrings>
       <
    add
           name="csMySQL_ConODBC51"
           connectionString="Dsn=test;uid=test;pwd=secret"
           providerName="System.Data.Odbc" />
       <
    add
          name="csMySQL_ConNet514"
          connectionString="server=127.0.0.1;user id=test;Password=secret;persist security info=True;database=test;pooling=False;logging=True;use old syntax=False;ignore prepare=False"
          providerName="MySql.Data.MySqlClient" />
       </
    connectionStrings>
       ...
    </configuration>

     

    Here is my ASP 2.0 code snippet 1.  Note: the MySQL Connector/ODBC 5.1 connection, the use of only the '?' in the XXXCommands and the lack of the <DeleteParameters> & <UpdateParameters> tags.

    <asp:SqlDataSource

        ID="dsMySQL"

        runat="server"

        ConnectionString="<%$ ConnectionStrings:csMySQL_ConODBC51%>"

        ProviderName="<%$ ConnectionStrings:csMySQL_ConODBC51.ProviderName %>"

        SelectCommand="SELECT testId, value1 FROM tbltest"

        UpdateCommand="UPDATE tbltest SET value1=? WHERE testId=?"

        DeleteCommand="DELETE FROM tbltest WHERE testId=?">

    </asp:SqlDataSource>

     

    <asp:GridView

        ID="gv1"

        runat="server"

        AutoGenerateColumns="False"

        DataKeyNames="testId"

        DataSourceID="dsMySQL">

        <Columns>

            <asp:CommandField ShowEditButton="True" />

            <asp:CommandField ShowDeleteButton="True" />

            <asp:BoundField

                DataField="testId"

                HeaderText="PK Id"

                InsertVisible="False"

                ReadOnly="True"

                SortExpression="testId" />

            <asp:BoundField

                DataField="value1"

                HeaderText="Col 1 Value"

                SortExpression="value1" />

        </Columns>

    </asp:GridView>

     

     

    Here is my ASP 2.0 code snippet 2.  Note: the MySQL Connector/Net 5.1.4 connection, the use of '?' preceding the params in the XXXCommands but NOT in the Name definition of the <DeleteParameters> & <UpdateParameters> tags:

     

    <asp:SqlDataSource

        ID="dsMySQL"

        runat="server"

        ConnectionString="<%$ ConnectionStrings:csMySQL_ConNet514%>"

        ProviderName="<%$ ConnectionStrings:csMySQL_ConNet514.ProviderName %>"

        SelectCommand="SELECT testId, value1 FROM tbltest"

        UpdateCommand="UPDATE tbltest SET value1=?value1 WHERE testId=?testId"

        DeleteCommand="DELETE FROM tbltest WHERE testId=?testId">

     

        <DeleteParameters>

            <asp:Parameter Name="testId" Type="UInt32" />

        </DeleteParameters>

        <UpdateParameters>

            <asp:Parameter Name="testId" Type="UInt32" />

            <asp:Parameter Name="value1" Type="String" />

        </UpdateParameters>

    </asp:SqlDataSource>

     

    <asp:GridView

        ID="gv1"

        runat="server"

        AutoGenerateColumns="False"

        DataKeyNames="testId"

        DataSourceID="dsMySQL">

        <Columns>

            <asp:CommandField ShowEditButton="True" />

            <asp:CommandField ShowDeleteButton="True" />

            <asp:BoundField

                DataField="testId"

                HeaderText="PK Id"

                InsertVisible="False"

                ReadOnly="True"

                SortExpression="testId" />

            <asp:BoundField

                DataField="value1"

                HeaderText="Col 1 Value"

                SortExpression="value1" />

        </Columns>

    </asp:GridView>

     

    Hope the info helps:

     

    Cheers,

    Al Dass

    Wednesday, January 16, 2008 12:11 PM
  • User-28614273 posted

    Thanx Al DASS..your post above really helps me..thanx again man..

    :))

    Sunday, February 17, 2008 10:33 PM
  • User523498593 posted

     Nice Al Dass that was really helpful

    Saturday, May 3, 2008 11:17 AM
  • User593260253 posted

    Like others in this thread I am trying to use a SqlDataSource for displaying and editing data from a Mysql DB.  I can display the data in a Gridview ok.  The SELECT and UPDATE commands are working. But I can't get the DELETE and INSERT commands to work.  I am using a MySQL ODBC 3.51 Driver as per my web host.    

    The DELETE erro is: SQLBindParameter not used for all parameters

    The INSERT error is: Column count doesn't match value count at row 1 [The "id" column is primary but not auto incremented]

    I think I have tried all the variations in this thread.  Any help would be greatly appreciated.  Here is my code for the SqlDataSource.

    <asp:SqlDataSource ID="SDS1"

    runat="server"

    ConnectionString="<%$ ConnectionStrings:MySQL_ODBC %>"

    ProviderName="<%$ ConnectionStrings:MySQL_ODBC.ProviderName %>"

    SelectCommand="SELECT * FROM zen_orders"

    UpdateCommand="UPDATE zen_orders SET name=? WHERE id=?"

    DeleteCommand="DELETE FROM zen_orders WHERE id=?"

    InsertCommand="INSERT INTO zen_orders (id, name) VALUES ('id?, name?')">

    <DeleteParameters>

    <asp:Parameter Name="id" Type="Int32" Direction="InputOutput" />

    <asp:Parameter Name="name" Type="String" Direction="InputOutput" />

    </DeleteParameters>

    <UpdateParameters>

    <asp:Parameter Name="name" Type="String" Direction="Input" />

    <asp:Parameter Name="id" Type="Int32" Direction="Input" />

    </UpdateParameters>

    <InsertParameters>

    <asp:Parameter Name="id" Type="Int32" Direction="Input" />

    <asp:Parameter Name="name" Type="String" Direction="Input" />

    </InsertParameters>

    </asp:SqlDataSource>

     

    Wednesday, July 16, 2008 4:11 PM
  • User-749846800 posted

    I posted a full example (see http://forums.asp.net/p/1015092/2494450.aspx#2494450) for use with ODBC.

    Anyway, I think the major issue is the comment from my post:

    Note: the MySQL Connector/ODBC 5.1 connection, the use of only the '?' in the XXXCommands and the lack of the <DeleteParameters> & <UpdateParameters> tags.

    Wednesday, July 16, 2008 6:26 PM
  • User593260253 posted

    Hi Al Dass.  Thanks for your input.  As I noted, I believe I have tried about all of the options presented in this thread and the code I cited above seemed to work for SELECT and UPDATE. 

     <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

    I think this is probably the version that comes closest to what you have advised:

     <o:p></o:p><asp:SqlDataSource ID="SDS1"<o:p></o:p>        runat="server" <o:p></o:p>        ConnectionString="<%$ ConnectionStrings:MySQL_ODBC %>"<o:p></o:p>        ProviderName="<%$ ConnectionStrings:MySQL_ODBC.ProviderName %>"<o:p></o:p>        SelectCommand="SELECT * FROM zen_orders"<o:p></o:p>        UpdateCommand="UPDATE zen_orders SET name=? WHERE id=?"<o:p></o:p>        DeleteCommand="DELETE FROM zen_orders WHERE id=?"<o:p></o:p>        InsertCommand="INSERT INTO zen_orders (id, name) VALUES (id?, 'name?')"><o:p></o:p>

         </asp:SqlDataSource>

     <o:p></o:p>

    The DELETE, UPDATE, and INSERT do not work.  Here are the errors:

     <o:p></o:p>

    DELETE:  “SQLBindParameter not used for all parameters”

     <o:p></o:p>

    INSERT: “ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''44', 'name?')' at line 1”

     <o:p></o:p>

    UPDATE (Edit): No error message but the word “name” was put into the “id” field for every row in the table (ouch!!).

     

    Wednesday, July 16, 2008 8:29 PM
  • User-749846800 posted

    It's complaining about the data bindings and that part is missing... post your aspx GridView code.  Also, to be sure its not with your setup, have you been able to get the example I posted working on your system?

    Friday, July 18, 2008 4:19 PM
  • User593260253 posted

    Here is the complete page code.  It loads with data ok, but throws the errors as noted previously when I try to update, delete, or insert.  Thanks for your input. Hope we can get this working.

    <%@ Page Language="VB" Debug="true" %>

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

    <script runat="server">

    </script>

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

    <head runat="server">

    <title>Untitled Page</title>

    </head>

    <body>

    <form id="form1" runat="server">

    <div>

    <asp:GridView ID="GridView1" runat="server" DataSourceID="SDS1" AllowPaging="True" AllowSorting="True">

    <Columns>

    <asp:CommandField ShowSelectButton="True" ShowDeleteButton="True" ShowEditButton="True" />

    </Columns>

    </asp:GridView> &nbsp;

    <asp:SqlDataSource ID="SDS1"

    runat="server"

    ConnectionString="<%$ ConnectionStrings:MySQL_ODBC %>"

    ProviderName="<%$ ConnectionStrings:MySQL_ODBC.ProviderName %>"

    SelectCommand="SELECT * FROM zen_orders"

    UpdateCommand="UPDATE zen_orders SET name=? WHERE id=?"

    DeleteCommand="DELETE FROM zen_orders WHERE id=?"

    InsertCommand="INSERT INTO zen_orders (id, name) VALUES (id?, 'name?')">

    </asp:SqlDataSource>

    <br /><br />

    <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AllowPaging="True" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" DataSourceID="SDS1">

    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />

    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />

    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />

    <Fields>

    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />

    </Fields>

    <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />

    <EditRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />

    </asp:DetailsView>

    </div>

    </form>

    </body>

    </html>

    Friday, July 18, 2008 4:41 PM
  • User-83494662 posted

    HI, ASPMatte

     I think this is ODBC driver bug.

    I sugggest to use mysql.net driver (from http://dev.mysql.com/downloads/connector/net/5.1.html) instead of ODBC, because odbc is slow, it doesn't support all features of mysql server. It also comes with integration with Visual studio.

     

    Sunday, July 20, 2008 1:40 PM
  • User593260253 posted

    Thanks for the suggestion.  I downloaded that driver, registered the binaries, and placed them in the bin folder.  I added the namespace to the page and adjusted my previous ODBC connection string, but got this error:

    Compiler Error Message: BC30002: Type 'MySqlCommand' is not defined.

    Here is the relevant code:

    <%@ Page Language="VB" %>

    <%@ Import Namespace="System.Data" %>

    <%@ Import Namespace="Mysql.Data.Mysqlclient" %>

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

    <script runat="server">

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

    Dim cmd As MySqlCommand = New MySqlCommand("Select * from zen_orders", New MySqlConnection("DRIVER={MySQL Connector Net 5.1.6 Driver};SERVER=XXXX.secureserver.net;PORT=3306;DATABASE=XXXX;USER=XXXX;PASSWORD=XXXX;OPTION=0;"))

    Try

    cmd.Connection.Open()

    gvwExample.DataSource = cmd.ExecuteReader()

    gvwExample.DataBind()

    cmd.Connection.Close()

    cmd.Connection.Dispose()

    Catch ex As Exception

    lblStatus.Text = ex.Message

    End Try

    End Sub

    </script>

    Since I registered the namespace and there are no indications of coding errors on the page, I am not sure how I can appropriately define the MySqlCommand.  Any further suggestions?

    Sunday, July 20, 2008 3:42 PM
  • User-749846800 posted

    You have no bound columns in the gridView... hence the errors you are getting (in your prev post).

    Monday, July 21, 2008 8:04 PM
  • User593260253 posted

    Here is my complete page code.  The bound columns were coded into the Gridview in the body of the page:

    <%@ Page Language="VB" %>

    <%@ Import Namespace="System.Data" %>

    <%@ Import Namespace="Mysql.Data.Mysqlclient" %>

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

    <script runat="server">

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

    Dim cmd As MySqlCommand = New MySqlCommand("Select * from zen_orders", New MySqlConnection("SERVER=xxxx.secureserver.net;PORT=3306;DATABASE=xxxx;USER=xxxx;PASSWORD=xxxx;OPTION=0;"))

    Try

    cmd.Connection.Open()

    gvwExample.DataSource = cmd.ExecuteReader()

    gvwExample.DataBind()

    cmd.Connection.Close()

    cmd.Connection.Dispose()

    Catch ex As Exception

    lblStatus.Text = ex.Message

    End Try

    End Sub

    </script>

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

    <head runat="server">

    <title>Untitled Page</title>

    </head>

    <body>

    <form id="form1" runat="server">

    <div>

    <asp:GridView ID="gvwExample" runat="server" AutoGenerateColumns="False" CssClass="basix" CellPadding="4" ForeColor="#333333" GridLines="None" >

    <columns>

    <asp:BoundField DataField="id" HeaderText="ID" />

    <asp:BoundField DataField="name" HeaderText="Name" />

    </columns>

    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

    <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />

    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />

    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />

    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

    <AlternatingRowStyle BackColor="White" />

    </asp:GridView>

    <br /><br />

    <asp:Label ID="lblStatus" runat="server" Text=""></asp:Label>

    <br />

    </div>

    </form>

    </body>

    </html>

     

     

    Tuesday, July 22, 2008 5:21 AM
  • User-749846800 posted

    try adding the DataKeyNames ="id" attribute to the asp:GridView element

    Wednesday, July 23, 2008 1:24 AM
  • User593260253 posted

    Tried it.  Still getting: "Compiler Error Message: BC30002: Type 'MySqlConnection' is not defined."

    Wednesday, July 23, 2008 4:22 AM
  • User-749846800 posted

    Sometimes the VS IDE gets confused... try creating a new connection string and using that instead.

    Thursday, July 24, 2008 3:08 AM
  • User593260253 posted

    Created new connection string. Same error message.

    Thursday, July 24, 2008 8:57 AM
  • User-1952159266 posted

     I know this thread is old, but I stumbled on it during a search for problems I was having with MySQL and GridView.  Thanks to Al Dass, I was able to solve my problems!  I did want to post some additional info in case others run across it.  As I added columns to the GridView and my UPDATE statement, it stopped updating when it came to one column.

    I used  the syntax that Al posted for ODBC 5.1 connection.  However, I did run across a problem with mixed string and int columns (in particular, one that was set to an int(4) type in MySQL).  I had to go into the  properties for that column and set the DataFormatString to a numeric general format value (I know, it seems so obvious now).

    This value is for a time out in some automation I'm working on. It looks like.

                    <asp:BoundField DataField="time_out" DataFormatString="{0:G}" 
                        HeaderText="time_out" SortExpression="time_out" />
    So, if you're mixing strings and INTs, you may need to format the value if you're having problems.

    Sax

     

     

     

    Monday, March 23, 2009 10:50 PM
  • User-749846800 posted

     Hey Sax,

    Thx for the kudos... and also for the info about mixed string/int columns... that bit me a while back and I don't remember how I solved it (I know it was probably not pretty[:$]) but now I do... thx.

     

    Cheers,

     Al Dass

    Tuesday, March 31, 2009 1:27 PM
  • User-1952159266 posted

     What's funny (and frustrating) about this is....I ran into a few days later...AGAIN!  I refreshed the schema of the form in design view and scratched my head for a bit until I realized that I needed to go back and set the formatting.  [:O]  But, GridView and DetailsView are all working and happy together, now.

    Thanks, Al!

    Tuesday, March 31, 2009 1:52 PM
  • User-1202680086 posted

    Master ALDass , a little out of the time , but thank you very much for your previews detailed and methodic post , I found them very useful using VS 2008 with .NET 3.5 , I simply added the following to the defaulfs of the Wizards :

    - DatakeyName is the Gridview command

    - Referr to the MySQL field as id=?id for example.

     Follow this thread for all people who are using VS 2008

    Friday, April 3, 2009 6:55 PM
  • User-1867244284 posted

    I had this same problem for a long time. Depending the way you define parameters in the UpdateParemeter collection, the update queries do not make it to the MySQL server. I activated the log option to see the queries executed and I realised that the update in some cases make it to the server but with all parameters with value NULL.

    The solution I found to all problems related to updating a MySQL database using ASP.NET in VS.2005 and with Connector/NET 5.1.4 is the following:

    1. The update query must have parameters defined as ?column_name. For example

    update table set column1 = ?column1, column2 = ?column2 where ...

    2. In Visual Studio, for the SqlDataSource UpdateQuery parameters collection you have to add one parameter for each parameter in the update query but the name of the parameter (and this is the key point) must be just the column name without the ?. This has to do with the way .NET assign values on bound fields (in my case I am using a FormView) to the parameter collection of the update query.

    Using this, all my update queries make it to the server with the values in the formview fields.

    I hope this may help. It dit in my case.

    Saludos,

    This was a key to solving my problem, which is updating a FormView using the ODBC connection to a remote server MySql database.   The server's MySql version, or my MySql ODBC provider version may be different from what you used.  Anyway, I found I had to modify slightly from your recipe:

    Instead of "?column1" I had to use just the "?" in the SQL UPDATE command.   The key requirement, as you said, is the name of the asp:parameter being just the column name without the ?. 

     Also, I don't know if this was true in your case but the order of the asp:parameter elements has to match the order of the '?" in the UPDATE command.

     Edit: Just realized schido was using the MySql Connector /Net instead of the ODBC connector, which probably explains the differences.

    Saturday, May 2, 2009 1:04 PM
  • User-749846800 posted

     Sorry I never followed up on your last post... did you ever get it solved?

    Sunday, May 3, 2009 7:45 PM
  • User593260253 posted

    I did make some progress using a SqlDataSource which I documented in this thread on 7/18/2008.  Basically I can display data properly in a Gridview and DetailsView and by adding Update Parameters I am now able to edit the data.  But no luck with deleting or inserting.  Here is my latest version of the SqlDataSource code:

    <asp:SqlDataSource ID="SDS1"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:MySQL_ODBC %>"
    ProviderName="<%$ ConnectionStrings:MySQL_ODBC.ProviderName %>"
    SelectCommand="SELECT * FROM zen_orders"
    UpdateCommand="UPDATE zen_orders SET name=? WHERE id=?"
    DeleteCommand="DELETE FROM zen_orders WHERE id=?"
    InsertCommand="INSERT INTO zen_orders (name) VALUES ('name?')">
        <InsertParameters>
            <asp:Parameter Name="name" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="id" Type="Int32" />
        </UpdateParameters>
        <DeleteParameters>
             <asp:Parameter Name="id" Type="Int32" />
        </DeleteParameters>
    </asp:SqlDataSource>

    I have experimented with variations of the InsertCommand.  Since the id field of the table in autoincrement, I have left it out of the insert statement and now data is inserted, but not the data I enter.  The id is autoincremented ok, but the name field is always "name?"  So it is taking the insert value literally rather than as a parameter.  Any help in getting the delete and insert functions working properly would be greatly appreciated.

     

    Tuesday, May 5, 2009 4:02 PM
  • User593260253 posted

    OK.  I just kept trying different things and now have insert, update, and delete working in a Gridview and Detailsview.  Thanks for all your help!

    Here is my code:

    <asp:GridView
        ID="GridView1"
        runat="server"
        DataSourceID="SDS1"
        AllowPaging="True"
        OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
        DataKeyNames="ID"
        AllowSorting="True">
            <Columns>
            <asp:CommandField ShowSelectButton="True" ShowDeleteButton="True" ShowEditButton="True" />
            </Columns>
    </asp:GridView> &nbsp;

    <br /><br />

    <asp:DetailsView ID="DetailsView1"
        runat="server"
        Height="50px"
        Width="125px"
        AllowPaging="True"
        BackColor="#DEBA84"
        BorderColor="#DEBA84"
        BorderStyle="None"
        BorderWidth="1px"
        CellPadding="3"
        CellSpacing="2"
        DataSourceID="SDS1"
        DataKeyNames="ID">
            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
            <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
                <Fields>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
                </Fields>
            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
    </asp:DetailsView>

    <asp:SqlDataSource ID="SDS1"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:MySQL_ODBC %>"
    ProviderName="<%$ ConnectionStrings:MySQL_ODBC.ProviderName %>"
    SelectCommand="SELECT * FROM zen_orders"
    UpdateCommand="UPDATE zen_orders SET name=? WHERE id=?"
    DeleteCommand="DELETE FROM zen_orders WHERE id=?"
    InsertCommand="INSERT INTO zen_orders (name) VALUES(?)">
        <InsertParameters>
            <asp:Parameter Name="name" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="id" Type="Int32" />
        </UpdateParameters>
        <DeleteParameters>
             <asp:Parameter Name="id" Type="Int32" />
        </DeleteParameters>
    </asp:SqlDataSource>
     

     

    Wednesday, May 6, 2009 5:37 PM
  • User593260253 posted

    FYI.  I added some more field to my table and altered my SqlDataSource to accomodate, like this:

    <asp:SqlDataSource ID="SDS1"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:MySQL_ODBC %>"
    ProviderName="<%$ ConnectionStrings:MySQL_ODBC.ProviderName %>"
    SelectCommand="SELECT * FROM zen_orders"
    UpdateCommand="UPDATE zen_orders SET name=?, address=?, city=?, state=? WHERE id=?"
    DeleteCommand="DELETE FROM zen_orders WHERE id=?"
    InsertCommand="INSERT INTO zen_orders (name, address, city, state) VALUES(?, ?, ?, ?)">
        <InsertParameters>
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="address" Type="String" />
            <asp:Parameter Name="city" Type="String" />
            <asp:Parameter Name="state" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="id" Type="Int32" />
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="address" Type="String" />
            <asp:Parameter Name="city" Type="String" />
            <asp:Parameter Name="state" Type="String" />
        </UpdateParameters>
        <DeleteParameters>
             <asp:Parameter Name="id" Type="Int32" />
        </DeleteParameters>
    </asp:SqlDataSource>

     

    Wednesday, May 6, 2009 6:54 PM
  • User2105646563 posted

    Hi all,
    I have problems with DataGrid and SqlDataSource with Mysql.
    Here following my code, the error is: "parameter ?id_riga must be defined" only in DELETE action.

     The sqlcommand text is:

    accessdatasource2.DeleteCommand = "DELETE FROM t_righepreventivi WHERE id_preventivo=?id_preventivo AND id_riga=?id_riga LIMIT 1"

    <asp:GridView ID="GridView1" BorderColor="#CCCCCC" AlternatingRowStyle-BackColor="#cccccc" HeaderStyle-BackColor="#E9EDF7" CellPadding="3" CellSpacing="3" runat="server" AutoGenerateColumns="False" AutoGenerateDeleteButton="true" AutoGenerateEditButton="True" DataSourceID="AccessDataSource2" AllowPaging="True" AllowSorting="True" PageSize="15">

    <Columns>

     

    <asp:BoundField DataField="id_riga" HeaderText="ID Riga" SortExpression="id_riga" />

    </Columns>

    <HeaderStyle BackColor="#E9EDF7" />

    <AlternatingRowStyle BackColor="#CCCCCC" />

    </asp:GridView>

    <asp:SqlDataSource ID="accessdatasource2" runat="server" ConnectionString="Data Source=123.123.123.123;User Id=username;Password=password;Database=123123123;" ProviderName="MySql.Data.MySqlClient">

    <SelectParameters>

    <asp:ControlParameter ControlID="idprev" Name="id_preventivo" Type="Int32" />

    </SelectParameters>

    <UpdateParameters>

    <asp:ControlParameter ControlID="idprev" Name="id_preventivo" Type="Int32" />

    <asp:Parameter Name="id_riga" Type="Int32" />

    </UpdateParameters>

    <DeleteParameters>

    <asp:ControlParameter ControlID="idprev" Name="id_preventivo" Type="Int32" />

    <asp:Parameter Name="id_riga" Type="Int32" />

    </DeleteParameters>

    </asp:SqlDataSource>

     

    I've tried also to lack the names of the parameters but with no success.

     Somebody can help me?

     Thanks

    G

    Sunday, June 7, 2009 3:28 AM
  • User1159696941 posted
    configure your data source and then enter ur connection string through wizard then u must select the table u want to show in grid after that u can enter the query through query builder.. u problem occur den ask me freely
    Tuesday, June 9, 2009 2:23 AM
  • User1799595715 posted

    Hi,


    Hope someone here will help me... I read all the posts of this topic and I try several solutions but the problem still. Here's the post I created :


    http://forums.asp.net/p/1459665/3353049.aspx


    I work with ODBC Connector 3.51 (so no need of updateparameters and the stuff of "?parameter" doesn't work, only "?" mark).


    The only thing I didn't try is to update my ODBC connector or trying with the Connector/NET


    Thank you for the help.

    Tuesday, August 18, 2009 5:00 AM
  • User1799595715 posted

    Ok so I tried with the Connector/Net 5.2.7.0 and nothing more...


    Nobody has an idea ?

    Wednesday, August 19, 2009 2:15 AM
  • User-1261829109 posted

    Al: thank you so much for this article. I have just spent most of my day figuring out how to update  a mysql table with aspx. Was trying first with an older version of the connector (odbc-3.51.27) and I just couldn't get it to work.

    Anyway, I just joined the forum mainly to thank you and also because of the excellent information available here

    Thanks again.

    LF

    Wednesday, November 25, 2009 5:13 PM
  • User-749846800 posted

    Your welcome... but rather then sending flowers or a donation to my favorite charity <smirk/> just be sure to help somebody else out when you "have the know" and they don't.

    Thursday, November 26, 2009 1:12 AM
  • User-1261829109 posted

    Fair enough

    Thursday, November 26, 2009 10:11 AM
  • User-1294383909 posted

    Thanks so what worked for me was

    DataKeyNames="testId" 

    Now i have an update and delete working perfectly with MYSQL as my datasource thanks



    Tuesday, January 26, 2010 6:51 PM
  • User-1611016307 posted

     Yes! I just got it... forgot a datakeyname ;)

     

    I'm trying all day and can't get it to work... I also updated mysql connector to v 5.1

    Any suggestions appreciated!

    My code:

     

    Public Sub GvCustomers_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
                '  set it to true so it will render
                Me.dvCustomerDetail.Visible = True
                '  force databinding
                Me.dvCustomerDetail.DataBind()
                '  update the contents in the detail panel
                Me.updPnlCustomerDetail.Update()
                '  show the modal popup
                Me.mdlPopup.Show()
            End Sub
                   
            Public Sub OdsCustomerDetail_Selecting(ByVal Sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)  'ObjectDataSourceSelectingEventArgs)
                e.Command.Parameters("id").Value = Convert.ToString(Me.gvCustomers.DataKeys(Me.gvCustomers.SelectedIndex).Value)
            End Sub
            
            Public Sub BtnSave_Click(ByVal sender As Object, ByVal e As EventArgs)
                If (Me.Page.IsValid) Then
    
                    '  move the data back to the data object
                    Me.dvCustomerDetail.UpdateItem(False)
                    Me.dvCustomerDetail.Visible = False
    
                    '  hide the modal popup
                    Me.mdlPopup.Hide()
    
                    '  add the css class for our yellow fade
                    ' The control I want to send data to
                    '  The data I want to send it (the row that was edited)
                    '  refresh the grid so we can see our changed
                    scriptManager.GetCurrent(Me).RegisterDataItem(Me.gvCustomers, Me.gvCustomers.SelectedIndex.ToString())
    
                    Me.gvCustomers.DataBind()
                    Me.updatePanel.Update()
                End If
                
            End Sub
            
        
         </script>    
        <style type="text/css">
            TR.updated TD
            {
            	background-color:yellow;
            }
            .modalBackground 
            {
    	        background-color:Gray;
    	        filter:alpha(opacity=70);
    	        opacity:0.7;
            }
        </style>    
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:ScriptManager ID="scriptManager" runat="server" />
            <script type="text/javascript" language="javascript">
                //  attach to the pageLoaded event
                Sys.WebForms.PageRequestManager.getInstance().add_pageLoaded(pageLoaded);        
            
                function pageLoaded(sender, args) {
                    
                    //  the data key is the control's ID
                    var dataKey = '<%= me.gvCustomers.ClientID %>';
                    var updatedRowIndex = args.get_dataItems()[dataKey];
                    
                    //  if there is a datakey for the grid, use it to
                    //  identify the row that was updated
                    if(updatedRowIndex){
                        //  get the row that was updated
                        var tr = $get(dataKey).rows[parseInt(updatedRowIndex) + 1];
                        //  add the 'updated' css class
                        Sys.UI.DomElement.addCssClass(tr, 'updated');
                        
                        //  remove the css class in 1.5 seconds
                        window.setTimeout(function(){
                            Sys.UI.DomElement.removeCssClass(
                                tr,
                                'updated'
                            );                        
                        }, 1500);
                    }
                }
            </script>        
            <asp:SqlDataSource ID="odsCustomerList" runat="server" ConnectionString="<%$ ConnectionStrings:MySQL_ODBC %>" ProviderName="<%$ ConnectionStrings:MySQL_ODBC.ProviderName %>"
                SelectCommand="SELECT * FROM Dagstaat">
            </asp:SqlDataSource>
                
            <asp:SqlDataSource ID="odsCustomerDetail" runat="server" ConnectionString="<%$ ConnectionStrings:MySQL_ODBC %>" ProviderName="<%$ ConnectionStrings:MySQL_ODBC.ProviderName %>" 
                SelectCommand="SELECT * FROM Dagstaat WHERE (id = ?)" OnSelecting="OdsCustomerDetail_Selecting" 
                UpdateCommand="UPDATE Dagstaat SET servicenr=? WHERE id=?" >
                <SelectParameters>
                    <asp:Parameter Name="id" Type="Int32" />
                </SelectParameters> 
                <UpdateParameters>
                    <asp:Parameter Name="servicenr" Type="String" Direction="Input" />
                    <asp:Parameter Name="id" Type="Int32" Direction="Input" />
                </UpdateParameters>
            </asp:SqlDataSource>
    
            <asp:UpdatePanel ID="updatePanel" runat="server" UpdateMode="Conditional">
                <ContentTemplate>            
                    <asp:GridView ID="gvCustomers" 
                        runat="server" 
                        DataSourceID="odsCustomerList"
                        CssClass="datagrid" 
                        GridLines="None" 
                        AutoGenerateColumns="False"
                        OnSelectedIndexChanged="GvCustomers_SelectedIndexChanged" 
                        DataKeyNames="id">
                        <Columns>
    				        <asp:BoundField DataField="id" HeaderText="ID" ReadOnly="true" />
    				        <asp:BoundField DataField="debiteurnr" HeaderText="Deb.#" ReadOnly="true" />
    				        <asp:BoundField DataField="servicenr" HeaderText="Service #" ReadOnly="true" />
    				        <asp:BoundField DataField="start" HeaderText="Start" ReadOnly="true" />
    				        <asp:BoundField DataField="eind" HeaderText="Eind" ReadOnly="true" />
    				        <asp:BoundField DataField="uren" HeaderText="Uren" ReadOnly="true" />                
    				        <asp:BoundField DataField="werkbon" HeaderText="Werkbon" ReadOnly="true" />                
    				        <asp:BoundField DataField="activiteit" HeaderText="Activiteit" ReadOnly="true" />                
    				        <asp:TemplateField>
    					        <ItemTemplate>
    					            <asp:LinkButton ID="btnViewDetails" runat="server" Text="Bewerken" CommandName="Select" />
    					        </ItemTemplate>
    				        </asp:TemplateField>
                        </Columns>					
                    </asp:GridView>
                </ContentTemplate>
            </asp:UpdatePanel>                    
    
    		<asp:Panel ID="pnlPopup" runat="server" CssClass="detail" Width="500px" style="display:none;">
                <asp:UpdatePanel ID="updPnlCustomerDetail" runat="server" UpdateMode="Conditional">
                    <ContentTemplate>
                        <asp:Button id="btnShowPopup" runat="server" style="display:none" />
                		<ajaxToolKit:ModalPopupExtender ID="mdlPopup" runat="server" 
                		    TargetControlID="btnShowPopup" PopupControlID="pnlPopup" 
                		    CancelControlID="btnClose" BackgroundCssClass="modalBackground" 
                		/>
                        <asp:DetailsView ID="dvCustomerDetail" runat="server" DataSourceID="odsCustomerDetail" 
                            CssClass="detailgrid" GridLines="None" DefaultMode="Edit" AutoGenerateRows="false" 
                            Visible="false" Width="100%">
                            <Fields>
                                <asp:BoundField HeaderText="ID" DataField="ID" ReadOnly="true" />
                                <asp:TemplateField HeaderText="Service #">
                                    <EditItemTemplate>
                                        <asp:TextBox ID="txtCompany" runat="server" Text='<%# Bind("servicenr") %>' />
                                        <asp:RequiredFieldValidator ID="rfvCompanyName" runat="server" ControlToValidate="txtCompany" ErrorMessage="Required" Display="Static" SetFocusOnError="true" />
                                    </EditItemTemplate>
                                </asp:TemplateField>                                                                                                           
                            </Fields>
                        </asp:DetailsView>
                        <div class="footer">
                            <asp:LinkButton ID="btnSave" runat="server" Text="Opslaan" OnClick="BtnSave_Click" CausesValidation="true" />
                            <asp:LinkButton ID="btnClose" runat="server" Text="Sluiten" CausesValidation="false" />
                        </div>                    
                    </ContentTemplate>                
                </asp:UpdatePanel>             
            </asp:Panel>


     

    Thursday, March 18, 2010 4:22 PM
  • User-1307629935 posted

    Thanks to Moonshadow's code I solved my problems with deleting rows.

    For those who have the next error using MySql:

    SQLBindParameter not used for all parameters


    In my code everything was in place and correct but I was forgetting

    DataKeyNames="ID"
    inside <asp:GridView ... >


    Thanks Moonshadow. :D




    Tuesday, September 21, 2010 6:12 AM
  • User-1859315695 posted

    Just wanted to give feedack that this solved my problem on how to get the parameter substitution working for me using GridView with mySql database. It took me several hours to stumble into this solution. 

    Here's what didn't work for me:

     <asp:SqlDataSource ID="oldb" runat="server"
          ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
          ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
          DataSourceMode="DataReader"
          DeleteCommandType="Text"
         
          DeleteCommand="Delete from weeklytime where weeklytime.id = ?id">
          <DeleteParameters>
             <asp:Parameter Name="id" DbType="Int32" />
          </DeleteParameters>
    </asp:SqlDataSource>

     

    this got me:  ERROR [42S22] [MySql][OBDC 5.1 Driver][mysqld-5.5.15] Unknown column '2id' in where clause.

    Obviously ?id was interpeted as 2id and not as a parameter with a name id.

    Here's what did work for me:

    (Note: the only difference is in the DeleteCommand)

     <asp:SqlDataSource ID="oldb" runat="server"
          ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
          ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
          DataSourceMode="DataReader"
          DeleteCommandType="Text"
         
          DeleteCommand="Delete from weeklytime where weeklytime.id = ?">
          <DeleteParameters>
             <asp:Parameter Name="id" DbType="Int32" />
          </DeleteParameters>
     </asp:SqlDataSource>

    This solved my problem but it still leaves the question on how to deletes which require multiple DataKeyFields still unanswered.  If someone knows more about the syntax and semantics for more complex substition of key values using mySql, I for one would appreciate you taking the time to document it and sharing that knowledge.

     

    ----------------------

    Just to be thorough, here's a snippet of what my gridview code looked like:

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
         DataSourceID="oldb" DataKeyNames="id">
      <Columns>
         <asp:CommandField ShowDeleteButton="True" />
         <asp:BoundField DataField="id" HeaderText="id" SortExpression="id"
              itemstyle-cssclass="hiddencol" 
              HeaderStyle-CssClass="hiddencol" ReadOnly="true">
         </asp:BoundField>
         <asp:BoundField DataField="name" HeaderText="Customer:Job"
             SortExpression="name" />
         <asp:BoundField DataField="serviceItem" HeaderText="Service Item"
              SortExpression="serviceItem" />
         <asp:BoundField DataField="classCode" HeaderText="L&I Code"
              SortExpression="classCode" />
      </Columns>
    </asp:GridView>

    Troubleshooting hint:  I figured that my problem with delete not working was not due to the gridview but rathere with the datasource  because I was able to set a breakpoint at the event datagrid_DeletingRow.  If you breakpoint there, which is just before the delete occurs, you can see that the javascript _doPostback has all the information to know what control and what row index needed to be deleted.  That means thet the code on the server side simply wasn't doing the sql delete command.  Since the sql delete command is fairly simple, you can easily guess that the parameter substitution wasn't working.

     

    Sunday, October 30, 2011 5:08 PM