Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cant get update to work with GridView and SqlDataSource

Posted on 2007-03-25
14
Medium Priority
?
405 Views
Last Modified: 2012-08-14
Hi, I cant get the update command to work on my GridView, I know that the update command is being fired because if I type a manual update statement it works; "UPDATE Shipping SET UpperLimit=500 WHERE ID=1"

SELECT works fine, but when I change a value and update it doesn't change the values. I think I may have completely misunderstood the parameters bit. I am using ODBC with MySQL database, the connection string allows editing:
Driver={MySQL ODBC 3.51 Driver};server=localhost;database=****;user=****;password=****;option=3.


Code as follows


         <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="MySqlD">
                <Columns>
                    <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
                    <asp:BoundField DataField="UpperLimit" HeaderText="UpperLimit" SortExpression="UpperLimit" />
                    <asp:BoundField DataField="LowerLimit" HeaderText="LowerLimit" SortExpression="LowerLimit" />
                    <asp:BoundField DataField="ShippingPrice" HeaderText="ShippingPrice" SortExpression="ShippingPrice" />
                    <asp:BoundField DataField="ShippingBand" HeaderText="ShippingBand" SortExpression="ShippingBand" />
                    <asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" />
                </Columns>
            </asp:GridView>



<asp:SqlDataSource ID="MySqlD" runat="server"
    ConnectionString = "<%$ ConnectionStrings:****ConnectionString %>"
    SelectCommand = "SELECT * FROM Shipping"
    DeleteCommand = "DELETE FROM Shipping WHERE ID=@ID;"
    UpdateCommand = "UPDATE Shipping SET UpperLimit=@UpperLimit,LowerLimit=@LowerLimit,ShippingPrice=@ShippingPrice,ShippingBand=@ShippingBand WHERE ID=@ID"
    ProviderName="<%$ ConnectionStrings:****ConnectionString.ProviderName %>"
  >
    <DeleteParameters>
      <asp:Parameter Name="ID" Type="String" />
    </DeleteParameters>
    <UpdateParameters>
      <asp:Parameter Name="ID" Type="String" />
      <asp:Parameter Name="UpperLimit" Type="String" />
      <asp:Parameter Name="LowerLimit" Type="String" />
      <asp:Parameter Name="ShippingPrice" Type="String" />
      <asp:Parameter Name="ShippingBand" Type="String" />
    </UpdateParameters>
  </asp:SqlDataSource>
0
Comment
Question by:bhermer
  • 6
  • 4
14 Comments
 
LVL 27

Expert Comment

by:Sammy Ageil
ID: 18787720
change this
UpdateCommand = "UPDATE Shipping SET UpperLimit=@UpperLimit,LowerLimit=@LowerLimit,ShippingPrice=@ShippingPrice,ShippingBand=@ShippingBand WHERE ID=@ID"

to
UpdateCommand = "UPDATE Shipping SET UpperLimit?,LowerLimit?,ShippingPrice?,ShippingBand?WHERE ID=?"

then add your parameters in that order
take a look at this example here
http://www.codeproject.com/aspnet/mysqlinaspnet.asp
0
 
LVL 27

Expert Comment

by:Sammy Ageil
ID: 18787725
I just noticed you are using SqlDataSource with a Mysql database?
you cant do that
if you need a mysql provider
take a look at
http://dev.mysql.com/downloads/connector/net/5.0.html
0
 
LVL 4

Author Comment

by:bhermer
ID: 18787744
Hi Sammy,

I read you could use MySQL as a sqldataSource if you used ODBC 3.51.

For the bulk of this site I use the MySQL data connector, it is fast and easy to use (both dataset and reader) but I have some admin pages in the back office part of the site that I dont care about performance as they will be used once in a blue moon, but I would like to take advantage of the built in edit/insert/update capabilities of the GridView control, I know I could write it to use the conenctor, but that means more development time (I have maybe 10 different admin pages to do for various settings for the site) and doing it this way would be easier.  


Do you know for sure that I cannot get this to work with MySQL using an ODBC connection?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Sammy Ageil
ID: 18788707
I seen some posts where they state its been done but I would really recommend using the .Net connector from mysql site.
it is extremely well documented and easy to use

Good luck
0
 
LVL 4

Author Comment

by:bhermer
ID: 18788803
Hi Sammy, I am using the NET connector for 99% of the site, but for my admin pages, for updating a product that has 40 fields for instance, or shipping prices as in my original question, it would be far easier to use the GridView component properly.

I dont suppose you have the links to the posts you have seen? I too have seen a few before posting this question, but haven got it to work?

In fact I cant even get it to pass an SQL command back at all unless I hard code the values in.
0
 
LVL 4

Author Comment

by:bhermer
ID: 18788883
Hi Again Sammy,  Just re-reading this post, are you saying you know a way to use the connector with the Gridview? Or am I going to have to produce template fields and parce the information manually?
0
 
LVL 4

Author Comment

by:bhermer
ID: 18788967
Right, I have a work around, and have just posted a long rely up in a different forum, so I will just paste it here for anyone finding this:

Use a ObjectDataSource instead, this works fine, you can use the connector too, but it is a little harder as you need to write functions in a class file somewhere to accept the SELECT and UPDATE commands,

eg

You have a db called Personals, a table called 'People' with a Primary key called 'ID' and a Field called 'FirstName', to select you would bind the ObjectDataSource SELECT function to the following function :


    Public Shared Function CreateDataSetNames()
        Dim myConnection As New MySqlConnection("server=localhost; user id=root; password=; database=People; pooling=false;")
        myConnection.Open()
        Dim myCommand As New MySqlCommand("SELECT ID,FirstName FROM PEOPLE", myConnection)
        Dim myAdapter As New MySqlDataAdapter(myCommand)
        Dim ds As New DataSet()
        myAdapter.Fill(ds)
        Return ds
    End Function


To update you would make a function that UPDATED, taking your field names as arguements, HAVE TO MATCH I THINK:


    Public Shared Function ExecuteUpdate(ID as integer,FirstName as string)
        Try
            Dim myConnection As New MySqlConnection("server=localhost; user id=root; password=; database=People; pooling=false;")
      dim SQL as string = "UPDATE People SET FirstName = '" & FirstName & "' WHERE ID=" & ID
            Dim myCommand As New MySqlCommand
            myCommand.Connection() = myConnection
            myCommand.Connection.Open()
            myCommand.CommandText = SQL
            myCommand.ExecuteNonQuery()
        Catch ex As Exception
 
            Exit Function
        End Try
    End Function
0
 
LVL 27

Expert Comment

by:Sammy Ageil
ID: 18806031
bhermer,
I personally try my best to avoid using sqlDataSource. it involves mixing data layer with the presentation layer and thats not the best way to do things.
Your code looks Ok to me but I would recoomend changing your sql queryies to use parameters instead the way you have it set up now.
a parameters example

myCommand.Parameters.Add("fNameParam", MySqlDbType.VarChar, 50).Value = FirstName
myCommand.Parameters.Add("idParam", MySqlDbType.Int32).Value = ID

Your sql would look like
Dim Sql as String = "Update People Set FirstName=fNameParam Where ID=idParam"

as far as the data manipulation using the Object Datasource you can see a complete tutorial here
http://www.codeproject.com/useritems/GridviewObjectDataSrc.asp

Hope this get you started


0
 
LVL 4

Author Comment

by:bhermer
ID: 18806261
Hi Sammy,

I did try using parameters, but couldnt get them to work, it would seem there is a bug in the MySQL version currently available that wont allow this to work, I googled this problem first and it would seem that alot of MySQL / ASP.NET 2.0 users are having this problem, hence the Q here!

as for avoiding sqlDatasource, I couldnt agree more for the front end of sites, as I have stated for the main site I use puerly data connectors, but alot of us have to spend hours and hours writing admin pages for sites so customers can change little bits of data maybe once or twice through the sites life time. In these senerios, being able to plug a  datasource straight into a gridview and have edit/insert/update work without the massive amount of design time required to do this through a connector and a custom control mean any side effects of this method are lost on the ears of the people paying by the hour!!!


Not sure how to answer this question, as I would like my code highlited as a possible solution, but also want to acknowledge your efforts in helping.
0
 
LVL 4

Author Comment

by:bhermer
ID: 19186428
I would like my code as the accepted solution, comment titled:

Author:bhermerDate:03.25.2007 at 07:56PM BSTYour Comment
0
 

Accepted Solution

by:
AnnieMod earned 0 total points
ID: 19388969
PAQed with points refunded (500)

AnnieMod
Cleanup Admin
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question