Solved

Cant get update to work with GridView and SqlDataSource

Posted on 2007-03-25
14
378 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
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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now