Insertion in mysql

[mundano]

Hi,

I am new to mysql through vb.net and have a problem when inserting data in mysql: when i have 2 different inserts, the code inserts the first value twice. The code is the following:

Imports MySQLDriverCS

Public Class Form1
    Dim DBCon As MySQLConnection
    Dim myCommand As New MySQLCommand
    Dim myReader As MySQLDataReader
    Dim SQL, SQL2 As String


    Private Sub oBotonInsertar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles oBotonInsertar.Click

        DBCon = New MySQLConnection(New MySQLConnectionString("127.0.0.1", "net", user, pass, 3306).AsString)

            SQL = "INSERT INTO datos (cadena) VALUES ('THIS IS VALUE 1')"
            SQL2 = "INSERT INTO datos (cadena) VALUES ('THIS IS VALUE 2')"

            DBCon.Open()

            myCommand.Connection = DBCon
            myCommand.CommandText = SQL
            myCommand.ExecuteNonQuery()
            myCommand.CommandText = SQL2
            myCommand.ExecuteNonQuery()

            DBCon.Close()
            DBCon.Dispose()
        End If

    End Sub
End Class

In the form there is a button named oBotonInsertar that performs the insertion in response to the event onClick.

So the thing is that, after running the code, the string 'THIS IS VALUE 1' is inserted twice.

Can anyone give me a hint on the problem?

Many thanks in advance.

 

[cjard]

Are you using mySQL because you have to (compatibility with an existing system) or because it's free?

 

[mundano]

Hi,

I am using mysql because it just suits our needs and it is free. Anyway, the problem is solved, though it seems a bit unelegant to me: each time i want to insert some values i have to do the following:

        Dim myCommand As MySQLCommand

        Try

            myCommand = New MySQLCommand(SQL, DBCon)
            myCommand.ExecuteNonQuery()
            myCommand.Dispose()
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try

I think it is unelegant because i have to do the instantiation every time and not change the sql and just execute it.

Thanks.

 

[cjard]

Hi,

I am using mysql because ... it is free

It's quite possibly the most expensive free database I've ever used. If you cant quite understand my curious logic here, tell me; would you make a 25 mile trip to a petrol station whose fuel was 2p/litre cheaper than your local one? Yes you got your fuel cheaper, but you took 2 hours out of your day (worth what.. £50?) and drove 50 miles (cost what.. £5 ?) to save £3 on fuel. Its the sort of thing my dad would do; getting a cheap kitchen from a company 75 miles away but having to go back 5 times over the course of 2 months because doors were wrong pattern, didnt fit, both left handed.. that sort of thing. http://www.askpatty.com/page.php?ID=302&t=Man_vs._Woman_Oil_Change

The amount of messing around you'll do writing bad code and trying to figure out what mysql is doing, you'd be far better off purchasing a database that integrates with visual studio. Oracle and SQLServer come in free versions that are very usable for small scale or proof of concept, and you can pay to upgrade them to full blown later. The amount you'll pay will be less than the (extra time spent * your daily pay rate) getting mySql to work.. Balance that up against SQLServer being X hundreds of dollars, but there;s a full suite of video tutorials from Microsoft that will tell you everything about how to use it with Visual Studio to get your app built.. Its a hard pill to swallow but sometimes, spending money saves you an incredible amount of money.

Anyway, the problem is solved, though it seems a bit unelegant to me: each time i want to insert some values i have to do the following:

        Dim myCommand As MySQLCommand

        Try

            myCommand = New MySQLCommand(SQL, DBCon)
            myCommand.ExecuteNonQuery()
            myCommand.Dispose()
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try

I think it is unelegant because i have to do the instantiation every time and not change the sql and just execute it.

Thanks.

Hmm, yes.. I would suspect that the DB subsystem is caching your query when the adapter is created.. I understand the spirit of what youre trying to do, but it's a bit of a fallacy; recreating a data adapter takes nanoseconds, so your hope of some massive time saving simply by reusing the object isnt worth as much as you think. You'd be far better off writing a parameterized query in the first place (see the PQ link in my sig) because then the database can plan the execution of the query based on what it knows to be variable. Changing the SQL text does NOT make a query parameterized, does NOT help the database engine plan anything and the type of SQL you have written there is generally regarded as the slowest, most insecure form of SQL possible. PQ has more info.

 

[mundano]

Thanks for your advice, in our case it should be oracle since we have to run the db both in windows and linux. It is a very simple db, it only consists of a single table with five fields (and one index) and there are not updates or deletions. In this case, do you think paying for a license is worth it?

Thanks.

 

[cjard]

OracleExpress is free and certainly more than capable of running your requirement?

 

[mundano]

Ok, thanks for your help. We'll give it a thought.