We help IT Professionals succeed at work.

Visual Basic MySql INSERT Question

Jack_Jones
Jack_Jones asked
on
Medium Priority
572 Views
Last Modified: 2012-05-11
Not sure but I can't see to get it to write a value. I have tried for over 3hrs. It connects and just don't INSERT or eve UPDATE the value. Hope to kill this headaque thanks experts.

MySqlConnection = New MySqlConnection()

        MySqlConnection.ConnectionString = "server=  ; Port=  ; user id=  ; password=  ; database=  ;"

        Try
            MySqlConnection.Open()
        Catch ex As Exception

        End Try

        Dim myadapter As New MySqlDataAdapter
        Dim sqlquary = "SELECT * FROM accounts WHERE login = '" & Login.UsernameTextBox.Text & "';"
        Dim sqlquery2 = "INSERT INTO accounts WHERE login = '" & Login.UsernameTextBox.Text & "' AND Online = '" & "1" & "';"
        Dim command As New MySqlCommand


        command.Connection = MySqlConnection
        command.CommandText = sqlquary
        myadapter.SelectCommand = command

        Dim mydata As MySqlDataReader

        mydata = command.ExecuteReader
        If mydata.HasRows = 0 Then
            MySqlConnection.Close()
        Else

            MySqlConnection.Close()
            MySqlConnection.Open()
            Dim registerfinal As New MySqlDataAdapter
            command.Connection = MySqlConnection
            command.CommandText = sqlquery2
            'start quary
            registerfinal.SelectCommand = command
            myData = command.ExecuteReader()
           MySqlConnection.Close()
        End If

Open in new window

Comment
Watch Question

Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
"INSERT INTO accounts WHERE login = '" & Login.UsernameTextBox.Text & "' AND Online = '" & "1" & "';"


That doesn't make any sense....

If you are inserting into a table, there is no WHERE clause.

It should be something like...

Insert into tablename(field) VALUES('value')
select value from tablename where fieldx='x'

If you want to UPDATE...that's different..

UPDATE Tablename SET Fieldname=value WHERE fieldx='xvalue'

Author

Commented:
I think UPDATE is what i was trying to acheive.

Author

Commented:
Is there away to shrink down what I have, or can you help with the code. I am trying to follow your example but I guess it's just been a long day here working on this.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
There is certainly room for a lot of improvement in your code. What are you trying to do? You dont need a dataadapter to run an update query. So you second code segment can be simplified from

            MySqlConnection.Close()
            MySqlConnection.Open()
            Dim registerfinal As New MySqlDataAdapter
            command.Connection = MySqlConnection
            command.CommandText = sqlquery2
            'start quary
            registerfinal.SelectCommand = command
            myData = command.ExecuteReader()
           MySqlConnection.Close()

to


            MySqlConnection.Open()
            command.Connection = MySqlConnection
            command.CommandText = "Update ..."
            'start quary
            command.ExecuteNonQuery()
           MySqlConnection.Close()
           command.Dispose

Author

Commented:
Basically I have a table in the DB that I want to update to a 1 or 0.

db: accounts  table in db = Online, when the program is logged into I will have it update Online to a 1, and then when they exit it will updated it to a 0. This worked awhile ago but new hdd after old one crashed hardcore I couldn't retreive my code so I had to start over =\

Author

Commented:
Anyone have an idea around this? Even a reference to the commands would help!

Author

Commented:
I think this might work, but I don't want to run it and take a chance at loosing or messing up my db. So if you could look and let me know thanks.

 conn = New MySqlConnection("server= ;Port= ; user id= ; password= ; database= ")
        Dim username As Boolean = True
        Dim sqlquery As String = "UPDATE accounts SET online='" & "Online" & "' WHERE login='" & username.Text & "';"
        Dim data As MySqlDataReader
        Dim adapter As New MySqlDataAdapter
        Dim command As New MySqlCommand
        conn.Open()
        command.CommandText = sqlquery
        command.Connection = conn
        adapter.SelectCommand = command
        data = command.ExecuteReader
        data.Close()
        conn.Close()

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Well after you said it would work and I felt better knowing an experts opinion is very vaild. It worked and madet he modifications you recommended. Thanks a ton!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Glad to help :-)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.