• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

Visual Basic MySql INSERT Question

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

0
Jack_Jones
Asked:
Jack_Jones
  • 6
  • 3
1 Solution
 
Ron MalmsteadInformation Services ManagerCommented:
"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'
0
 
Jack_JonesAuthor Commented:
I think UPDATE is what i was trying to acheive.
0
 
Jack_JonesAuthor 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.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
CodeCruiserCommented:
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
0
 
Jack_JonesAuthor 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 =\
0
 
Jack_JonesAuthor Commented:
Anyone have an idea around this? Even a reference to the commands would help!
0
 
Jack_JonesAuthor 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

0
 
CodeCruiserCommented:
The query is fine. But as I said and showed above, you dont need a datareader/dataadapter when running a Update, Insert, or Delete query. These are only required when using a Select query.

So you will have

MySqlConnection.Open()
            command.Connection = MySqlConnection
            command.CommandText = "UPDATE accounts SET online='" & "Online" & "' WHERE login='" & username.Text & "';"
            'start quary
            command.ExecuteNonQuery()
           MySqlConnection.Close()
           command.Dispose
0
 
Jack_JonesAuthor 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!
0
 
CodeCruiserCommented:
Glad to help :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now