?
Solved

Cant get update to work with GridView and SqlDataSource

Posted on 2007-03-25
14
Medium Priority
?
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

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…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

801 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