MySql INSERT, UPDATE AND DELETE ineffective from ASP development machine

Zipbang
Zipbang used Ask the Experts™
on
This has to be an easy fix, but I have searched high and low and find anything but an answer:

I have an ASP.NET site setup that is successfully querying records over our intranet via the SELECT command.

I am working from another machine besides the MySQL server machine.  Could this be a rights issue?  

I am just starting to add and edit records using INSERT and UPDATE, the commands execute, but there are no results in the tables.  I try the same command (in the console) on the server hosting the MySQL database and the command works fine.

here is what I have in my .aspx.vb code

Dim sql As String
Dim conStr As String = "Driver={MySQL ODBC 5.1 Driver};server={server.domain.com};uid=root;pwd=password;database=database;port=3306"
    
Dim MyConn As New OdbcConnection(conStr)
sql = "UPDATE table SET notes ='testing' WHERE idprojectid='169'"
Dim cmd As New OdbcDataAdapter(sql, MyConn)

Open in new window


If I do the same thing above with a SELECT command configured to read data, it will work.

Since the UPDATE and INSERT seem to work on the server machine in the console, I am thinking it is an access rights issue when doing this from a web page?

thank  you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I note that you are using the "root" account. By default, for security reasons, it is restricted
to host=localhost, i.e. you need to be logged onto the MySQL server itself in order to be
allowed to do anything with it.

You can confirm this by issuing the command on the MySQL server as root:
   use mysql;
   select user,host from user where user='root'

It is a bad idea to use root as a client account. It is better to set up a different account
for your application with privileges to only work with data in a particular database.

But if you must open up client access to the MySQL server to your root account
(perhaps it is protected by being a tier 2 server with firewalls and you do therefore
have major concern about hacking attempts), you could do this:
  use mysql;
  update user set host='%' where user='root';
  flush privileges;

Author

Commented:
@wolfgang 93

I'm quite sure you have the solution here, but let me be clear as I'm still having no luck.

I created another user account on the server (using Toad) and gave this user rights for INSERT and UPDATE for this database and the same rights in the master list.

I then changed my connection string to have this user and password.

This did not work.

I created a user name like so:    j_smith@%

this did not work, so I tried:  j_smith@server.domain

and this did not work.

Can you see where I am still wrong?

thank you.
As root, you can create a user j_smith with pswd "userpswd" with access allowed from any client
(i.e. host=%):

GRANT USAGE ON *.* TO j_smith@'%' IDENTIFIED BY 'userpswd'

Give j_smith full access to create any objects, update, insert, etc. into the database
(assuming the database already exists), say called "fubar":

GRANT ALL ON fubar.* TO j_smith@'%' WITH GRANT OPTION

The "WITH GRANT OPTION" part of the command gives the user j_smith the right to
give other users access to tables, views, etc. that the user creates in the database.
Do not include this option if you do not want the user to have that privilege.

Author

Commented:
Good job, thanks for the prompt help!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial