bhermer
asked on
Cant get update to work with GridView and SqlDataSource
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;d
Code as follows
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False
<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"
<asp:BoundField DataField="ShippingBand" HeaderText="ShippingBand" SortExpression="ShippingBa
<asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="MySqlD" runat="server"
ConnectionString = "<%$ ConnectionStrings:****Conn
SelectCommand = "SELECT * FROM Shipping"
DeleteCommand = "DELETE FROM Shipping WHERE ID=@ID;"
UpdateCommand = "UPDATE Shipping SET UpperLimit=@UpperLimit,Low
ProviderName="<%$ ConnectionStrings:****Conn
>
<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>
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;d
Code as follows
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False
<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"
<asp:BoundField DataField="ShippingBand" HeaderText="ShippingBand" SortExpression="ShippingBa
<asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="MySqlD" runat="server"
ConnectionString = "<%$ ConnectionStrings:****Conn
SelectCommand = "SELECT * FROM Shipping"
DeleteCommand = "DELETE FROM Shipping WHERE ID=@ID;"
UpdateCommand = "UPDATE Shipping SET UpperLimit=@UpperLimit,Low
ProviderName="<%$ ConnectionStrings:****Conn
>
<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>
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
you cant do that
if you need a mysql provider
take a look at
http://dev.mysql.com/downloads/connector/net/5.0.html
ASKER
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?
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?
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
it is extremely well documented and easy to use
Good luck
ASKER
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.
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.
ASKER
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?
ASKER
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=lo
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=lo
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
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=lo
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=lo
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
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("
myCommand.Parameters.Add("
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
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("
myCommand.Parameters.Add("
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
ASKER
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.
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.
ASKER
I would like my code as the accepted solution, comment titled:
Author:bhermerDate:03.25.2
Author:bhermerDate:03.25.2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
UpdateCommand = "UPDATE Shipping SET UpperLimit=@UpperLimit,Low
to
UpdateCommand = "UPDATE Shipping SET UpperLimit?,LowerLimit?,Sh
then add your parameters in that order
take a look at this example here
http://www.codeproject.com/aspnet/mysqlinaspnet.asp