Solved

Cant get update to work with GridView and SqlDataSource

Posted on 2007-03-25
14
384 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
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
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 27

Expert Comment

by:Sammy
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
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

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!

Question has a verified solution.

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

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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