How to pass CommandText to another MySqlCommand?

Question

The title is a bit furviant, I'll try to explain better. So in my application I've two connection string, one for the local database, and another for the web database. This two database must be updated with the same records. Now in my app when I add a record in the local database I execute a function that pass the MySqlCommand object to another function that use another connection string for the web database. In this second function I need to execute the same operation already performed in the local database. Example code:

Function local database

 Dim query = "INSERT INTO text_app (name, last_name)
                                        VALUES(@namep, @last_namep)"

 Dim MySqlCommand = New MySqlCommand(query, dbCon)

 MySqlCommand.Parameters.AddWithValue("@namep", name.Text)
 MySqlCommand.Parameters.AddWithValue("@last_namep", last_name.Text)

 MySqlCommand.ExecuteNonQuery()

 Sync.SyncOut(MySqlCommand) 'Pass the object to another function

Function web database (SyncOut)

Using dbCon As MySqlConnection = establishWebConnection()

        Try
            dbCon.Open()
            Dim MySqlCommand = New MySqlCommand(query_command, dbCon)

            MySqlCommand.ExecuteNonQuery() 

            Return True

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Using

Now query_command contain the MySqlCommand passed from the local function, dbCon is the new connection object.

When I perform the .ExecuteNonQuery on the SyncOut function I get this error:

Invalid Cast from 'MySqlCommand' type to 'String' type.

What I need is take the .CommandText property contained in the query_command, but I can't access to this property.

What exactly am I doing wrong? How I can achieve this?

Why you can't access that property? It is public read/write so it should be available — Steve
– Steve, Commented Dec 23, 2015 at 15:41
the variable "query_command" contains only: .Equal .GetHashCode .GetType .ToString. NB: I can read the property .CommandText in the function local but not in the web function SyncOut. Maybe I should convert it? — Sandokan
– Sandokan, Commented Dec 23, 2015 at 15:44
The function is declared in another class called "Sync", if you see in my post: Sync.SyncOut — Sandokan
– Sandokan, Commented Dec 23, 2015 at 15:46
Usually this is accompained by an InnerException message that explains better the situation. Look at your MainException.InnerException.Message — Steve
– Steve, Commented Dec 23, 2015 at 15:54

Sandokan · Accepted Answer · 2015-12-23 17:55:59Z

1

The first thing to do is change the name of the variable that represent the MySqlCommand. I can't find any plausible reason to allow a confusion as this to spread along your code. Do not name a variable with the same name of its class even if the language permits, it is very confusing

Dim query = "INSERT INTO text_app (name, last_name)
                        VALUES(@namep, @last_namep)"

Dim cmd = New MySqlCommand(query, dbCon)

and, of course, change every reference to the old name with the new one.

Now in the declaration of SyncOut write

Public Sub SyncOunt(ByVal cmd As MySqlCommand)
   ...
   ' Do not forget to open the connection '
   dbCon.Open()
   Dim aDifferentCommand = cmd.Clone()
   aDifferentCommand.Connection = dbCon  
   ....

edited Dec 23, 2015 at 17:55

Sandokan

1,1053 gold badges19 silver badges31 bronze badges

answered Dec 23, 2015 at 15:40

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sandokan Over a year ago

I need to pass the parameter in your "cmd" 'cause the SyncOut function doesn't know what are the parameters. This function is used by different class that perform a only operation: sync the data in the db online.

Steve Over a year ago

I have no MySql installed at hand here, but you could try to use the Clone method then

Steve Over a year ago

Updated the answer, yes, tested on SqlCommand for Sql Server and it duplicates everything (it makes a deep copy) If MySql version works alike ... do not forget to change the connection of course

Sandokan Over a year ago

Okay but when I do ExecuteNonQuery I get: System.NullReferenceException

Steve Over a year ago

I am unable to see if there is something null here. I have tried in LinqPAD without opening a real connection but everything seems fine here. I can only suggest to start a debug session and check what variable is null (nothing in vb)

|

Collectives™ on Stack Overflow

How to pass CommandText to another MySqlCommand?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related