7 Replies - 6049 Views - Last Post: 22 November 2009 - 10:59 PM Rate Topic: -----

#1 KenMitchell  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 14-November 09

Using mySqlCommandBuilder with VB.net

Post icon  Posted 14 November 2009 - 09:07 AM

I am having a problem getting the mySqlCommandBuilder to work. It does not appear that it is creating the Update/Insert/Delete commands. I am using Visual Basic 2008 Express Edition.

When I run the code in debug and set a breakpoint at the "myDataAdapter.Update(myDataSet, myTableName)" line and use "Watch" to examine the "myadapter" parameters, the "UpdateCommand", "InsertCommand" and "DeleteCommand" show as "Nothing". The "SelectCommand" shows the correct "SELECT" statement.

Here are the contents of the "watch":


- myDataAdapter {MySql.Data.MySqlClient.MySqlDataAdapter} MySql.Data.MySqlClient.MySqlDataAdapter
AcceptChangesDuringFill True Boolean
AcceptChangesDuringUpdate True Boolean
Container Nothing System.ComponentModel.IContainer
ContinueUpdateonerror False Boolean
DefaultSourceTableName "Table" String
DeleteCommand Nothing System.Data.Common.DbCommand
FillLoadOption OverwriteChanges {1} System.Data.LoadOption
InsertCommand Nothing MySql.Data.MySqlClient.MySqlCommand
MissingMappingAction Passthrough {1} System.Data.MissingMappingAction
MissingSchemaAction Add {1} System.Data.MissingSchemaAction
ReturnProviderSpecificTypes False Boolean
- SelectCommand {MySql.Data.MySqlClient.MySqlCommand} MySql.Data.MySqlClient.MySqlCommand
CommandText "SELECT id_vet, surname_vet, given_vet, DAYOFMONTH(date_birth_vet) AS BDay, MONTH(date_birth_vet) AS BMonth, YEAR(date_birth_vet) AS BYear, DAYOFMONTH(date_death_vet) AS DDay, MONTH(date_death_vet) AS DMonth, YEAR(date_death_vet) AS DYear, vet_photo_vet, name_rnk, company_vet, unit_vet, Vstate.name_stt as UnitState, name_brn, vet_info_vet, DAYOFMONTH(date_enlist_vet) AS EDay, MONTH(date_enlist_vet) AS EMonth, YEAR(date_enlist_vet) AS EYear, DAYOFMONTH(date_parole_vet) AS PDay, MONTH(date_parole_vet) AS PMonth, YEAR(date_parole_vet) AS PYear, id_cem, name_cem, street_cem, name_cty, city_cem, Cstate.name_stt as CemState, lot_vet, grave_vet, section_vet, latitude_vet, longitude_vet, grave_photo_vet, is_head_vet, is_marker_vet, is_cross_vet, is_verify_vet, grave_info_vet FROM county_cty, rank_rnk, branch_brn, (state_stt AS Cstate JOIN cemetery_cem ON idstate_cem = Cstate.id_stt JOIN veteran_vet ON idcem_vet = id_cem) JOIN state_stt AS Vstate ON idstate_vet = Vstate.id_stt WHERE(idcounty_cem = id_cty) AND idrank_vet = id_rnk AND idbranch_vet = id_brn AND id_vet = 1;" String
CommandTimeout 30 Integer
CommandType Text {1} System.Data.CommandType
+ Connection {MySql.Data.MySqlClient.MySqlConnection} MySql.Data.MySqlClient.MySqlConnection
Container Nothing System.ComponentModel.IContainer
DesignTimeVisible True Boolean
IsPrepared False Boolean
LastInsertedId -1 Long
+ Parameters {MySql.Data.MySqlClient.MySqlParameterCollection} MySql.Data.MySqlClient.MySqlParameterCollection
Site Nothing System.ComponentModel.ISite
Transaction Nothing System.Data.Common.DbTransaction
UpdatedRowSource Both {3} System.Data.UpdateRowSource
Site Nothing System.ComponentModel.ISite
+ TableMappings {System.Data.Common.DataTableMappingCollection} System.Data.Common.DataTableMappingCollection
UpdateBatchSize 1 Integer
UpdateCommand Nothing System.Data.Common.DbCommand





The dataset gets filled with the correct information. I do not get any errors or exceptions but the database is not updated.

Sorry for the long "SELECT" statement! What am I missing?


Imports MySql.Data.MySqlClient
Imports System.Data

	Private Function UpdateAdapter(ByVal myDataSet As DataSet, ByVal myTableName As String) As DataSet

		Dim myConn As New MySqlConnection(myConnString)
		Dim myDataAdapter As New MySqlDataAdapter()

		'Query to get Vet info for specified Vet ID
		SQL = "SELECT id_vet, surname_vet, given_vet, " _
		   & "DAYOFMONTH(date_birth_vet) AS BDay, " _
		   & "MONTH(date_birth_vet) AS BMonth, " _
		   & "YEAR(date_birth_vet) AS BYear, " _
		   & "DAYOFMONTH(date_death_vet) AS DDay, " _
		   & "MONTH(date_death_vet) AS DMonth, " _
		   & "YEAR(date_death_vet) AS DYear, " _
		   & "vet_photo_vet, " _
		   & "name_rnk, company_vet, unit_vet, " _
		   & "Vstate.name_stt as UnitState, name_brn, vet_info_vet, " _
		   & "DAYOFMONTH(date_enlist_vet) AS EDay, " _
		   & "MONTH(date_enlist_vet) AS EMonth, " _
		   & "YEAR(date_enlist_vet) AS EYear, " _
		   & "DAYOFMONTH(date_parole_vet) AS PDay, " _
		   & "MONTH(date_parole_vet) AS PMonth, " _
		   & "YEAR(date_parole_vet) AS PYear, " _
		   & "id_cem, name_cem, street_cem, name_cty, city_cem, Cstate.name_stt as CemState, " _
		   & "lot_vet, grave_vet, section_vet, " _
		   & "latitude_vet, longitude_vet, grave_photo_vet, " _
		   & "is_head_vet, is_marker_vet, is_cross_vet, is_verify_vet, " _
		   & "grave_info_vet " _
		   & "FROM county_cty, rank_rnk, branch_brn, " _
		   & "(state_stt AS Cstate " _
		   & "JOIN cemetery_cem ON idstate_cem = Cstate.id_stt " _
		   & "JOIN veteran_vet ON idcem_vet = id_cem) " _
		   & "JOIN state_stt AS Vstate ON idstate_vet = Vstate.id_stt " _
		   & "WHERE(idcounty_cem = id_cty) " _
		   & "AND idrank_vet = id_rnk " _
		   & "AND idbranch_vet = id_brn " _
		   & "AND id_vet = " & Me.strVetID.Text & ";"

		myDataAdapter.SelectCommand = New MySqlCommand(SQL, myConn)
		Dim cb As MySqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter)

		myConn.Open()

		myDataSet = New DataSet
		myDataAdapter.Fill(myDataSet, myTableName)

		' Code to modify data in DataSet here 

		myDataAdapter.Update(myDataSet, myTableName)

		myConn.Close()

	End Function


This post has been edited by KenMitchell: 14 November 2009 - 09:46 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Using mySqlCommandBuilder with VB.net

#2 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Using mySqlCommandBuilder with VB.net

Posted 16 November 2009 - 02:09 AM

Hi,

To Start off with, the MySqlDataAdapter has 4 commands.Select, Insert, Update and Delete. You would have to specify the SQL Code for each one in your case.

		Dim daTest As New SqlDataAdapter
		dsTest.SelectCommand = "SELECT * FROM ..."
		daTest.InsertCommand="INSERT INTO ..."
		daTest.UpdateCommand="UPDATE ..."
		daTest.DeleteCommand="DELETE FROM ..."



BUT...

since you are using joins in your Select Command, it will most probably not be possible to use the other commands as updates on joins is not possible.

You can try and make different statements that your select, but i am not sure if it will work. (I do not use them in that way, MOST PROBABLY NOT AS THE COLUMNS FROM YOUR DATASET ARE MAPPED TO THE SQL STATEMENTS.....)
Was This Post Helpful? 0
  • +
  • -

#3 KenMitchell  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 14-November 09

Re: Using mySqlCommandBuilder with VB.net

Posted 16 November 2009 - 06:23 AM

I guess I am confused on how the MySQLCommandbuilder works. From what I read in the Connector/.NET documentation, I thought if would automatically create the required INSERT/UPDATE/DELETE statements based on the SELECT statement associated with the MySqlDataAdapter (myDataAdapter).

Should my example code work as shown if my SELECT statement was more generic?

		SQL = "SELECT id_vet, surname_vet, given_vet " _
		   & "FROM veteran_vet " _
		   & "WHERE id_vet = " & Me.strVetID.Text & ";"


Was This Post Helpful? 0
  • +
  • -

#4 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Using mySqlCommandBuilder with VB.net

Posted 16 November 2009 - 06:50 AM

Hi,

It will work if you use "Typed Data Sets" (they will create all the querries for the DataAdapter).

In your case its "untyped" - meaning you will have to supply the code.

Typed Datasets are a little different but with a bit of practice you will see they require less code from the programmer.

BUT: I can not explain the Typed Datasets here as my post will take quite a long time to write.

Basically, google "Typed DataSets Tutorials for VB.Net".

Oh and the MySQLConnector is compatible with Typed DataSets.

Hope it explains the use of the DataAdapters a little more....
Was This Post Helpful? 0
  • +
  • -

#5 KenMitchell  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 14-November 09

Re: Using mySqlCommandBuilder with VB.net

Posted 18 November 2009 - 03:55 PM

Hi motcom,

Thanks for your help. I think I have narrowed down my problem to a databinding issue. In the code below if I modify the textbox text {Me.strVetNotes.Text = "command"} then the data in the database will not be changed. If I modify the data in the dataset directly {myDataSet.Tables(myTableName).Rows(0).Item("vet_info_vet") = "Command"}, then it will be changed. I have tried everything I can think of and scoured the net but can't figure out what I am doing wrong. For some reason the data in the dataset does not appear to change when textbox changes.

		Dim myConn As New MySqlConnection(myConnString)
		Dim myDataAdapter As New MySqlDataAdapter()

		'Query to get Vet info for specified Vet ID
		SQL = "SELECT id_vet, vet_info_vet " _
		   & "FROM veteran_vet " _
		   & "WHERE id_vet = " & Me.strVetID.Text & ";"

		myDataAdapter.SelectCommand = New MySqlCommand(SQL, myConn)
		Dim cb As MySqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter)

		myConn.Open()

		Dim myDataSet = New DataSet

		myDataAdapter.Fill(myDataSet, "vetData")

		Me.strVetNotes.DataBindings.Clear()
		Me.strVetNotes.DataBindings.Add("Text", myDataSet, "vetData.vet_info_vet")
		
		' Code to modify data in DataSet here
		'Me.strVetNotes.Text = "command"	 '!!! This does not work
		myDataSet.Tables(myTableName).Rows(0).Item("vet_info_vet") = "Command"	  '!!!!! This works
		
		' Without the MySqlCommandBuilder this line would fail.
		myDataAdapter.Update(myDataSet, "vetData")

		myConn.Close()


Was This Post Helpful? 0
  • +
  • -

#6 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Using mySqlCommandBuilder with VB.net

Posted 19 November 2009 - 01:22 AM

Ok,

You are close...

So what you need to do is bind the dataset to your controls..

This is an example and you should put it into your formload event after you fill the dataset

Me.TextBox1.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet1.MyTable "CompanyName", True))



The "Text" means its of datatype "Text". The "CompanyName" is the column in the "MyTable" in the "DataSet1"

In your case bind it to your controls.

(Just a note when you use the Typed Dataset. You would not have to do it in code. You would just bind it to the TextBoxes' Bindings Property...)


Hope this helps
;)
Was This Post Helpful? 1
  • +
  • -

#7 KenMitchell  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 14-November 09

Re: Using mySqlCommandBuilder with VB.net

Posted 21 November 2009 - 08:39 AM

Hi motcom,

I finally got it working. Thanks for keeping me pointed in the right direction. The last part I found was that the dataset was not changing. This caused the UPDATE to not be executed.

I found that by adding :

		MsgBox(myDataSet.HasChanges().ToString)



just before the UPDATE and it came back as "FALSE". So I added the EndEdit() just before the UPDATE and everything worked then.

Here is my complete code. Feel free to make comments. Thanks for your help.

		Dim myConn As New MySqlConnection(myConnString & ";Allow Zero Datetime=True;")
		Dim myDataAdapter As New MySqlDataAdapter()
		Dim testBindingSource As New BindingSource()

		Query to get Vet info for specified Vet ID
		SQL = "SELECT id_vet, vet_info_vet " _
		   & "FROM veteran_vet " _
		   & "WHERE id_vet = " & Me.strVetID.Text & ";"

		myDataAdapter.SelectCommand = New MySqlCommand(SQL, myConn)
		Dim cb As MySqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter)

		myConn.Open()

		Dim myDataSet = New DataSet

		myDataAdapter.Fill(myDataSet, myTableName)
		testBindingSource.DataSource = myDataSet.Tables(myTableName)

		Me.strVetNotes.DataBindings.Clear()
		Me.strVetNotes.DataBindings.Add("Text", testBindingSource, "vet_info_vet")

		' Code to modify data in DataSet here
		Me.strVetNotes.Text = "Text that is to be changed."

		' Without the MySqlCommandBuilder this line would fail.
		testBindingSource.EndEdit()
		myDataAdapter.Update(myDataSet, myTableName)

		myConn.Close()


Was This Post Helpful? 0
  • +
  • -

#8 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Using mySqlCommandBuilder with VB.net

Posted 22 November 2009 - 10:59 PM

Hi,

Jip, its all ok,

I would add "Me.Validate" just before you call "EndEdit".

;)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1