Solved

VB.net ODBC MySQL - HELP

Posted on 2006-11-26
6
936 Views
Last Modified: 2007-11-27
I'm very new to vb.net so hopefully someone with some real experience can help.

Here's what I have done so far:
My installations are:   MySQL version 5.0.24a-community-nt;   visual studio 2005;  The MySQL ODBC driver 3.51

With MySQL on my local server (W2000 server) and vb.net on my development computer (Win XP) both computers have the same ODBC connector installed.

I then proceeded to work with the data Sources in vb.net and managed to get my database through the ODBC into vb.net as a DataSource. There is a bug in VB.Net that creates SQLs for all the tables in your DB like as follows: "SELECT * FROM `truwest`..`orders`" I went through all the SQLs and removed the double dots and everything seemed good. I could now drop a table from my data source onto a form and I would have a data table. I could also use the same data source to bind fields on my form to controls on the form. I had just two records in my DB table to test with.

There is just one line in the form that attaches the table (not counting all the supporting code you don't need to look at too closely that it adds into a different source file) which looks like this and is placed in the form_load event:

            Me.OrdersTableAdapter.Fill(Me.DStruWest.orders)

Where "DStruWest" is just the name of my DataSet. "Orders" is a table in the DataSet that represents an orders table in the DB. Now, the first time I started my form everything worked fine (by the way, I only have one control bound right now it's just a text field.) Then I closed it, opened it again and got this error:    "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Now I know with just two lines in the DB table there is no constrains that could be a problem. I also made sure that I didn't add any in the DStruWest.xsd file since you can define relationships there.

Then I started tracking down the error and it came to be that one line above. And now it gets fun. I highlighted the portion in the brackets "Me.DStruWest.orders" and viewed it through Shift+F9. Everything came up and it looked fine.  However, when I checked the Err.LastDLLError I found that a different error was created, the error being: "Invalid flags" ( I used the the following to see the errors: errMsg = New Win32Exception(Marshal.GetLastWin32Error()).Message) ).

Now I just used Shift+F9 again and checked for the error. Now there wasn't an error! I then let the whole line run, that is the [ Me.OrdersTableAdapter.Fill(Me.DStruWest.orders) ] and no error this time.

When I stop the debugging and restart the project, the error reappears. Ok, that's it, it's beyond me to even begin to figure out what's going on while I'm still grasping vb.net yet alone something funky like this.

Now, if I just let the error occur and then continue with the code as it if never happened, then my form comes up and I can navigate back and forth between the two records just fine. But that's not safe I know, I have to find out why this error is occurring and fix it or I'll just have headaches later that will be even harder to track down.

If anyone has any ideas I would really appreciate it, I'm at a "grasping at straws" stage right now.

As a thought there is a bunch of options on the ODBC Connecter for MySQL, perhaps someone know about whether some of these need to be set with vb.net. They are:
Tab 1:  Don't Optimize Column Width; Return Matching Row; Allow Big Result; Use Compressed Results; Change BIGINT Columns To Int; Safe;
Tab 2:  Don't Prompt Upon Connect; Enable Dynamic Cursor; Ignore # Table Name; User Manage Cursors; Don't User Set Locale; Pad Char To Full Length; Don't Cache Result(forward only cursors);
Tab 3:  Return Table names For SQLDescribeCol; Ignore Space After Function Names; Force use Of named Pipes; No Catalog (exp); Read Options From my.cnf; Disable Transactions; Force use of Forward Only Cursors;

I turned on tracing in the ODBC connector but the SQL trace doesn't seem to work (doesn't create a file of SQL's sent) and the other trace gives results that doesn't help me much like the following:
truWest.vshost  7a0-f54      EXIT  SQLDriverConnectW  with return code 0 (SQL_SUCCESS)
            HDBC                03FD1B00
            HWND                00000000
            WCHAR *             0x74329A38 [      -3] "******\ 0"
            SWORD                       -3
            WCHAR *             0x74329A38
            SWORD                        2
            SWORD *             0x00000000
            UWORD                        0 <SQL_DRIVER_NOPROMPT>
0
Comment
Question by:RegProctor
  • 4
  • 2
6 Comments
 
LVL 13

Accepted Solution

by:
newyuppie earned 500 total points
ID: 18014409
before you do the fill, try adding this line

DStruWest.EnableConstraints = FALSE
Me.OrdersTableAdapter.Fill(Me.DStruWest.orders)
DStruWest.EnableConstraints = TRUE
0
 
LVL 1

Author Comment

by:RegProctor
ID: 18017143

Hi, I think you meant: Me.DStruWest.EnforceConstraints = False

Now I get these errors which at least say a little more:

A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll
The specified procedure could not be found

0
 
LVL 1

Author Comment

by:RegProctor
ID: 18018241
Okay, I've made progress.  I went back to look at the DB and the tables wouldn't show.  That's the first time I have never seen before (using phpMyAdmin to look at it).

Now my only question is, how can the database get corrupted like that when all you are doing is read it from through the ODBC? Anyway I'll post an update after I rebuild the database and test for the error again.

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:RegProctor
ID: 18023841
Well, I'll just have to put this down to one of those unexplainable things in that I'll probably never know how my DB could get corrupted in the way it did. VB is talking with MySQL nicely now.
0
 
LVL 1

Author Comment

by:RegProctor
ID: 18023861
Thanks newyuppie. It wasn't the answer but it helped me get there.
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 18025131
hi regproctor, im glad you could work it out. thanks for the points
NY
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Expando 4 46
Disable and re-enable a dynamic System.Timers.Timer 6 39
Visual Studio 2013 Shortcut (VB) 4 33
Vb.net dynamic formulas in runtime 11 61
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

937 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

15 Experts available now in Live!

Get 1:1 Help Now