-
Feb 2nd, 2012, 04:30 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Inserting Dataset to MySQL
I know this is simple, but for some reason... this isnt working for me
Im just simply trying to take my whole dataset and put it into my premade mysql tables.
Code:
Dim sqlConn As New MySqlConnection("server=192.168.0.109;User Id=****;password=****;database=taxcert")
Dim taxDS As New DataSet("Tax")
Dim commands As MySqlCommandBuilder
Private Sub btnStartInput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartInput.Click
Call inputorders()
End Sub
Public Sub inputorders()
Dim ordersadapt As New MySqlDataAdapter("SELECT * FROM Orders", sqlConn)
ordersadapt.InsertCommand = New MySqlCommand("INSERT INTO Orders (*) VALUES *", sqlConn)
ordersadapt.Update(taxDS, "Orders")
End Sub
-
Feb 2nd, 2012, 05:13 PM
#2
Re: Inserting Dataset to MySQL
First things first, the whole point of using a command builder is to build the action commands (INSERT, UPDATE, DELETE) for you, so you need decide whether you're going to build the InsertCommand yourself or not. Either provide the SELECT statement and let the command builder build the InsertCommand based on that or else don't use a command builder, don't provide the SELECT statement and create the InsertCommand yourself.
If you go with the second option then you have to create a valid INSERT statement. There are no wildcards in INSERT statements. You have to provide all the values and, unless you provide a value for every column in the same order they appear in the table definition, you have to provide all the columns too. I suggest that you follow the CodeBank link in my signature and check out my Retrieving & Saving Data thread. One of the code examples shows how to insert multiple records into a database using a DataTable and data adapter. It shows how to build an appropriate INSERT statement with a parameter for each column.
-
Feb 3rd, 2012, 09:57 AM
#3
Thread Starter
Hyperactive Member
Re: Inserting Dataset to MySQL
I'm trying to avoid the full INSERT statement because my table has about 150+ columns, as for not using it and just the SELECT and Update command, I can't get this to work either.
Code:
Public Sub inputorders()
Dim ordersadapt As New MySqlDataAdapter("SELECT * FROM Orders", sqlConn)
ordersadapt.Update(taxDS, "Orders")
MsgBox(ordersadapt.Update(taxDS, "Orders"))
End Sub
the MsgBox shows "0" ? So its not importing any rows?
Last edited by thebuffalo; Feb 3rd, 2012 at 11:04 AM.
-
Feb 4th, 2012, 04:55 AM
#4
Re: Inserting Dataset to MySQL
Why are you calling Update twice?
-
Feb 4th, 2012, 11:44 AM
#5
Thread Starter
Hyperactive Member
Re: Inserting Dataset to MySQL
Originally Posted by jmcilhinney
Why are you calling Update twice?
I'm not, i was using the msgbox to see the changes of the update, which didn't work.
-
Feb 4th, 2012, 08:14 PM
#6
Re: Inserting Dataset to MySQL
Um, yes you are. Did you look at you code?
Code:
Public Sub inputorders()
Dim ordersadapt As New MySqlDataAdapter("SELECT * FROM Orders", sqlConn)
ordersadapt.Update(taxDS, "Orders")
MsgBox(ordersadapt.Update(taxDS, "Orders"))
End Sub
I've highlighted the first call in red and the second call in blue. You are displaying the result of the second call but not the result of the first call. You should be calling Update once and once only and, if you want to display the result of that first call, display the result of that first call.
-
Feb 6th, 2012, 11:20 AM
#7
Thread Starter
Hyperactive Member
Re: Inserting Dataset to MySQL
So using the msgbox to show the results calls it too? I didn't know that, either way I still cant get this to work for some reason.
-
Feb 6th, 2012, 02:43 PM
#8
Re: Inserting Dataset to MySQL
Yes, every time you use Update, it "resolves" it. That actually works for everything... if you call myString.Length for example, it processes the string length EVERY time you call it. If you need to use the string length multiple times, it's best to set it to a variable and from that point on, call the variable since it doesn't process it every time.
Does taxDS have the same schema as your "Orders" table; i.e. was it generated from a similar data adapter and then edited?
-
Feb 6th, 2012, 03:25 PM
#9
Thread Starter
Hyperactive Member
Re: Inserting Dataset to MySQL
Yeah taxDS was generated by the dataadapter i used to pull the "Orders" table
-
Feb 6th, 2012, 05:43 PM
#10
Re: Inserting Dataset to MySQL
I've already told you what to do. Call Update once. If you want to display the result then display the result of that one call. The critical point is whether that result is zero or not.
-
Feb 7th, 2012, 10:10 AM
#11
Thread Starter
Hyperactive Member
Re: Inserting Dataset to MySQL
I only called it once and it still doesn't affect the database
My current SQL table is empty, i dont know if this affects anything but here is my code again
Code:
Imports System.Data.OleDb
Imports MySql.Data.MySqlClient
Public Class Backup
Dim accConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Aj\Desktop\TaxCert_be.accdb")
Dim sqlConn As New MySqlConnection("server=192.168.0.109;User Id=ar***n;password=t****;database=taxcert")
Dim ordersadapter As New OleDbDataAdapter("SELECT * FROM Orders Where Status = 'Waiting Payment' OR Status = 'Cancelled'", accConn)
Dim taxDS As New DataSet("Tax")
Dim commands As MySqlCommandBuilder
Private Sub Backup_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Public Sub loadevents()
Dim eventsadapter As New OleDbDataAdapter("SELECT * FROM Events INNER JOIN Orders On Events.Control_Number = Orders.Control_Number WHERE Orders.Status = 'Waiting Payment' OR Orders.Status = 'Cancelled'", accConn)
eventsadapter.Fill(taxDS, "Events")
End Sub
Public Sub loadfnf()
Dim fnfadapter As New OleDbDataAdapter("SELECT * FROM [FNF Taxes] INNER JOIN Orders On [FNF Taxes].ControlNumber = Orders.Control_Number WHERE Orders.Status = 'Waiting Payment' OR Orders.Status = 'Cancelled'", accConn)
fnfadapter.Fill(taxDS, "FNFTaxes")
End Sub
Public Sub inputorders()
Dim ordersadapt As New MySqlDataAdapter("SELECT * FROM Orders", sqlConn)
ordersadapt.Update(taxDS, "Orders")
End Sub
Private Sub btnFillData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFillData.Click
Try
ordersadapter.Fill(taxDS, "Orders")
Call loadevents()
Call loadfnf()
Catch er As Exception
End Try
If taxDS.Tables("Events").Rows.Count > 10 And taxDS.Tables("FNFTaxes").Rows.Count > 10 Then _
MsgBox("Ready for Input.", MsgBoxStyle.OkOnly) _
Else MsgBox("Fail.", MsgBoxStyle.OkOnly)
End Sub
Private Sub btnStartInput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartInput.Click
Call inputorders()
End Sub
End Class
Last edited by thebuffalo; Feb 7th, 2012 at 02:39 PM.
-
Feb 8th, 2012, 10:43 AM
#12
Thread Starter
Hyperactive Member
Re: Inserting Dataset to MySQL
Bump.. why isn't my update command working?
-
Feb 8th, 2012, 07:43 PM
#13
Re: Inserting Dataset to MySQL
Consider this. Let's say that you have a DataAdapter. You call Fill on that to retrieve records into a DataTable. Now, if you were to call Update on that same DataAdapter and pass that same DataTable, what would you expect to happen? Would you expect all the records to be inserted into the database? Of course not, because they are not new records.
The same applies if you use a different DataAdapter. None of the rows in the DataTable are new so none of them will be inserted. The DataAdapter does NOT look at the database to determine whether the records are new; it looks at the DataTable. The rows in the DataTable need to be flagged as new in order to be inserted when you call Update. That means that their RowState needs to be Added, but in your case it is Unchanged.
When you call Fill on a DataAdapter, it first retrieves all the data and adds rows to the DataTable. Those rows all have their RowState set to Added, as do all rows just added to the DataTable. These rows do not represent new data though, so the adapter calls AcceptChanges on the DataTable. That sets all RowStates to Unchanged, to indicate that the DataTable is currently in the same state as the database. As you then add, edit and delete records, those rows will have a RowState of Added, Modified and Deleted. It's those rows that will be acted upon when you call Update.
So, what you need is for AcceptChanges not to be called when you call Fill so that all rows have a RowState of Added, indicating that they are new rows, so that they can be inserted when you call Update. To do that, you set the AcceptChangesDuringFill property of the first DataAdapter to False.
-
Feb 9th, 2012, 09:12 AM
#14
Thread Starter
Hyperactive Member
Re: Inserting Dataset to MySQL
Okay that worked for getting it to run the update but, I get an exception saying "Update requires a valid InsertCommand when passed DataRow collection with new rows.". So in the end I still have to make a InsertCommand with all of the column names and values?
Thanks for the help
Can I use the MysqlCommandBuilder? if so, how does this statement work:
Code:
Dim commands As MySqlCommandBuilder
commands.GetUInsertCommand()
And would I GetInsertCommand or Update?
Last edited by thebuffalo; Feb 9th, 2012 at 09:17 AM.
-
Feb 9th, 2012, 10:34 AM
#15
Re: Inserting Dataset to MySQL
Examples of loading and saving data:
Code:
Private ds As New DataSet
Private Sub LoadData()
Using mcon As New MySqlConnection("connectionString goes here")
Dim da As New MySqlDataAdapter("SELECT * FROM MyTable", mcon)
mcon.Open()
da.Fill(ds)
mcon.Close()
End Using
End Sub
Private Sub SaveData()
Using mcon As New MySqlConnection("connectionString goes here")
Dim da As New MySqlDataAdapter("SELECT * FROM MyTable", mcon)
Dim cb As New MySqlCommandBuilder(da)
mcon.Open()
da.Update(ds)
mcon.Close()
End Using
End Sub
-
Feb 9th, 2012, 11:27 AM
#16
Thread Starter
Hyperactive Member
Re: Inserting Dataset to MySQL
Wooooo! Thanks to the command builder syntax you just gave me I got the fill working to mysql. Now I jut need to make a progress for for the input and make the load call for the other 2 tables, Thanks!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|