Got the same problem

Hello.
I use VS2005 and I was trying to make a little program able to connect to a MySQL 5.0.18 database through MyODBC 3.51.12. I can connect to the database and I can load a table in the Datagridview control, but I can't update the table: the wizard didn't generate the Update method. Even if I configure again the tableAdapter, the wizard generates only:
SELECT statement
table mappings
Fill method
Get method
The table is a very simple table: it has 3 columns and the first is a primary key.

If I create an identical table using Access database and make a program that connects through ODBC, I don't have the problem: the wizard generates also the UPDATE, INSERT and DELETE statements.

Where is the difference? What is the problem?

Thankyou so much for the help

Anthony R.

The MyODBC Driver could not generate the UPDATE command. But the MySQLDirect .NET Data Provider for example generates without problems.

Can anybody help?

|||

I am having a huckuva time trying to get this to work as well. If someone else has any ideas, I would love to hear it.

However, I am working on a project that may work. I do not know if I can get to it soon, so I thought I would post the idea to see if someone else tried it -- to see if I am barking up the wrong tree.

I am using VS2005, MySQL 5, and MyODBC 3.51

I am viewing a TableAdapter in the DataSet Designer. I right-click on the TableAdapter and select Properties. Within the properties are the InsertCommand, DeleteCommand, and UpdateCopmmand methods. They are blank, of course, because as we have noticed, they can't automatically generate.

However, by expanding one of the commands (UpdateCommand, for example) we have access to the CommandText, where we can define the MySQL Command needed to update the table. We also have access to the Parameters, where we define the parameters we will pass.

If I get the chance, I will attempt to try this. If anyone else beats me to it, please let me know if it works.

|||

Someone in the Visual Basic General forum may be able to provide some insights as to why these particular drivers are not generating UPDATE commands.

To follow up on mking's theory, there is some good basic info on the TableAdapter class at: http://msdn2.microsoft.com/en-us/library/bz9tthwx.aspx with lots of good links (including http://msdn2.microsoft.com/en-us/library/dex7k4dw.aspx, which walks through the TableAdapter Configuration Wizard). This information should hopefully provide you with enough information to follow up on MKing's idea and generate the appropriate UPDATE statements.

For additional help with the TableAdapter Configuration Wizard, I'd recommend the Visual Basic General forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=32&SiteID=1. The ADO.NET forum might also be able to provide some assistance: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1.

Good luck,

Jason

|||Jason,

Thanks for the tips. However, after some intense drinking, I figured it out. Then I wrote a tutorial about it, which everyone can find on VBMySQl.com.

Got the same problem

Hello.
I use VS2005 and I was trying to make a little program able to connect to a MySQL 5.0.18 database through MyODBC 3.51.12. I can connect to the database and I can load a table in the Datagridview control, but I can't update the table: the wizard didn't generate the Update method. Even if I configure again the tableAdapter, the wizard generates only:
SELECT statement
table mappings
Fill method
Get method
The table is a very simple table: it has 3 columns and the first is a primary key.

If I create an identical table using Access database and make a program that connects through ODBC, I don't have the problem: the wizard generates also the UPDATE, INSERT and DELETE statements.

Where is the difference? What is the problem?

Thankyou so much for the help

Anthony R.

The MyODBC Driver could not generate the UPDATE command. But the MySQLDirect .NET Data Provider for example generates without problems.

Can anybody help?

|||

I am having a huckuva time trying to get this to work as well. If someone else has any ideas, I would love to hear it.

However, I am working on a project that may work. I do not know if I can get to it soon, so I thought I would post the idea to see if someone else tried it -- to see if I am barking up the wrong tree.

I am using VS2005, MySQL 5, and MyODBC 3.51

I am viewing a TableAdapter in the DataSet Designer. I right-click on the TableAdapter and select Properties. Within the properties are the InsertCommand, DeleteCommand, and UpdateCopmmand methods. They are blank, of course, because as we have noticed, they can't automatically generate.

However, by expanding one of the commands (UpdateCommand, for example) we have access to the CommandText, where we can define the MySQL Command needed to update the table. We also have access to the Parameters, where we define the parameters we will pass.

If I get the chance, I will attempt to try this. If anyone else beats me to it, please let me know if it works.

|||

Someone in the Visual Basic General forum may be able to provide some insights as to why these particular drivers are not generating UPDATE commands.

To follow up on mking's theory, there is some good basic info on the TableAdapter class at: http://msdn2.microsoft.com/en-us/library/bz9tthwx.aspx with lots of good links (including http://msdn2.microsoft.com/en-us/library/dex7k4dw.aspx, which walks through the TableAdapter Configuration Wizard). This information should hopefully provide you with enough information to follow up on MKing's idea and generate the appropriate UPDATE statements.

For additional help with the TableAdapter Configuration Wizard, I'd recommend the Visual Basic General forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=32&SiteID=1. The ADO.NET forum might also be able to provide some assistance: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1.

Good luck,

Jason

|||Jason,

Thanks for the tips. However, after some intense drinking, I figured it out. Then I wrote a tutorial about it, which everyone can find on VBMySQl.com.

GoDaddy / MySQL / SQL Server

I don't know if this is unique to GoDaddy: I found the program for working with a MySQL database very intuitive (to the point of compliments). Today I started work with ASP.NET and SQL, and when I went to work with SQL Server (on GoDaddy), it wasn't intuitive, and there was almost nothing on which to click. By chance might there be a tutorial that would help a person who needs to work with SQL Server on a provider like GoDaddy?

This may help...

http://forums.asp.net/thread/1456464.aspx

|||

I'm going through it, and I come to a point where I think it asks me to connect to the server (IP and PORT). I believe GoDaddy doesn't allow this (if someone reads this and I've misinterpreted it, please hollar--I know there is something that GoDaddy forbids regarding connected outside of their system).

If the above is true, does that make it so that the above process won't work (that's what I'm thinking right now.

However, thank you for the above link, because it gave me an idea. GoDaddy has it so that you can run queries. If I had a database with 250 entries and I didn't want to retype them all, I might be able to construct the database through the query box. Thanks! (going to test that out now.)

|||

The thread was started as a request by a newbie for help for someone who is on GoDaddy and they got to the SQL Server, and then they were stuck (what do I do next?) Over time I figured it out, and if you came to this post because you are "stuck", feel free to continue the thread with another question. I'm putting some basic things you want to know below:

Assuming that you've created a database under SQL Server and given it name and a password, and that you clicked on "Open Manager" in the row with the name of your database are now on a page with a name likehttps://mssqladmin.secureserver.net:

You see Quick Help with a bunch of words and icons (this doesn't help you though, those are not links, those are things you will see later).

On the left, you see Connection and Preferences, click Connection. Click the new link 'connection' the comes up underneath.

Put in your user name and password and click Connect.

There should be a new choice between Connection and Preferences on the left and it may have a name like whsql-v03.

Click that. You should then see Databases and Tools, and both will be helpful. Click Tools and then Query Analyser. This box is where you can type a query line that creates a table for your database:

CREATE TABLE mydictionary (word nvarchar(50), definition text)

and if it creates successfully, you will see "The command(s) completed successfully." appear underneath. You now have a table with two fields, word and definition.

Go back to the menu on the left and click Databases. something like DB_123456 comes up and click on that. Underneath you have Tables, Views, Stored Procedures. Click Tables. A list of tables comes up, many that you didn't make, but look for yours at the bottom. Don't click it just yet--instead, mouse over to all the icons on the right in the same row, and click the first one, content.

Look for the word Actions and then click that yellow star/asterisk. The next window gives you text boxes to put in your first entry. Fill them and click Insert.

At this point you have a database. It doesn't have things that are important, like a primary key with auto_increment, but the database will work for the purpose of confirming your first success.

|||

My advice to Godaddy users is to start with getting a simple page working that writes to one table in your new database. That will give you a web.config file with a working database connection and you can move on from there.

The web.config database connection seems a particular issue for newbies, I've put together a page with tips based around going live on GoDaddy with SQL Server, when you've got SQL Express on your local PC.

http://www.salmontraining.com/SqlExpressZone/Articles/why_the_development_config_file_wont_work_on_live_shared_hosting_with_sql_server.aspx

Global Temp Table permissions

I have a VB6 program which has to execute a SQL Server 2005 Stored Procedure
.
The SP creates a global temp table on the data and structure of an input .DB
F
file. I have SYSADMIN rights to the SQL Server. If I run the program, the SP
runs beautifully and creates the GTT. If any of my end users run it, the SP
doesn't give an error, but also doesn't create the GTT. It's got to be a
security issue, but as soon as I take myself off SYSADMIN, I can't create th
e
table, and my users can't create a GTT until I make them a SYSADMIN.
Microsoft can't be serious about this!!!! You can't possibly have to be a
SYSADMIN to create a GTT. Is there anything else I'm overlooking?That does sound strange, but I don't think Microsoft has any constraints lik
e
that. Please verify that you are granting execute premissions to the public
(or any other database role).
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dave" wrote:

> I have a VB6 program which has to execute a SQL Server 2005 Stored Procedu
re.
> The SP creates a global temp table on the data and structure of an input .
DBF
> file. I have SYSADMIN rights to the SQL Server. If I run the program, the
SP
> runs beautifully and creates the GTT. If any of my end users run it, the S
P
> doesn't give an error, but also doesn't create the GTT. It's got to be a
> security issue, but as soon as I take myself off SYSADMIN, I can't create
the
> table, and my users can't create a GTT until I make them a SYSADMIN.
> Microsoft can't be serious about this!!!! You can't possibly have to be a
> SYSADMIN to create a GTT. Is there anything else I'm overlooking?
>|||Dave
Perhaps the sysadmin account has an appropriate permissions on filesystem.
Can you verify it?
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:433C1E5B-20E8-479D-BD8C-B0DD11CFDFF1@.microsoft.com...
>I have a VB6 program which has to execute a SQL Server 2005 Stored
>Procedure.
> The SP creates a global temp table on the data and structure of an input
> .DBF
> file. I have SYSADMIN rights to the SQL Server. If I run the program, the
> SP
> runs beautifully and creates the GTT. If any of my end users run it, the
> SP
> doesn't give an error, but also doesn't create the GTT. It's got to be a
> security issue, but as soon as I take myself off SYSADMIN, I can't create
> the
> table, and my users can't create a GTT until I make them a SYSADMIN.
> Microsoft can't be serious about this!!!! You can't possibly have to be a
> SYSADMIN to create a GTT. Is there anything else I'm overlooking?
>|||Dave (Dave@.discussions.microsoft.com) writes:
> I have a VB6 program which has to execute a SQL Server 2005 Stored
> Procedure. The SP creates a global temp table on the data and structure
> of an input .DBF file. I have SYSADMIN rights to the SQL Server. If I
> run the program, the SP runs beautifully and creates the GTT. If any of
> my end users run it, the SP doesn't give an error, but also doesn't
> create the GTT. It's got to be a security issue, but as soon as I take
> myself off SYSADMIN, I can't create the table, and my users can't create
> a GTT until I make them a SYSADMIN. Microsoft can't be serious about
> this!!!! You can't possibly have to be a SYSADMIN to create a GTT. Is
> there anything else I'm overlooking?
As you can see from this repro, no particular permissions are needed to
create global temp tables:
CREATE LOGIN hans with password = '//)LD((d'
CREATE USER hans
go
execute as login = 'hans'
go
create table ##temp (a int NOT NULL)
go
revert
go
drop table ##temp
drop user hans
drop login hans
There must be something more that you are not telling us. How do you
know that the global temp table is not created? Could you post the
relevant parts of the code?
An aside, I have rarely found global temp tables to be more a source of
confusing than of any real use. The one exception is when you want to export
data from a stored procedure by calling xp_cmdshell to run BCP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||We've been progressively adding permissions to various users and groups as
we've gone along. We have Connect and Execute permissions on the database fo
r
all the users (including Guest).
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> That does sound strange, but I don't think Microsoft has any constraints l
ike
> that. Please verify that you are granting execute premissions to the publ
ic
> (or any other database role).
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Dave" wrote:
>|||It turns out we had Adhoc Queries using xBase Drivers disabled in the
Registry for the SQL Server. In the Registry, when we turned
DisallowAdhocAccess off, everything works fine.
Thanks to everyone for your help.
"Dave" wrote:

> I have a VB6 program which has to execute a SQL Server 2005 Stored Procedu
re.
> The SP creates a global temp table on the data and structure of an input .
DBF
> file. I have SYSADMIN rights to the SQL Server. If I run the program, the
SP
> runs beautifully and creates the GTT. If any of my end users run it, the S
P
> doesn't give an error, but also doesn't create the GTT. It's got to be a
> security issue, but as soon as I take myself off SYSADMIN, I can't create
the
> table, and my users can't create a GTT until I make them a SYSADMIN.
> Microsoft can't be serious about this!!!! You can't possibly have to be a
> SYSADMIN to create a GTT. Is there anything else I'm overlooking?
>