[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Visual Basic MySql INSERT Question

Posted on 2011-04-28
10
Medium Priority
?
557 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

0
Comment
Question by:Jack_Jones
  • 6
  • 3
10 Comments
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35487878
"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
 
LVL 1

Author Comment

by:Jack_Jones
ID: 35488494
I think UPDATE is what i was trying to acheive.
0
 
LVL 1

Author Comment

by:Jack_Jones
ID: 35488510
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35490442
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
 
LVL 1

Author Comment

by:Jack_Jones
ID: 35495117
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
 
LVL 1

Author Comment

by:Jack_Jones
ID: 35496739
Anyone have an idea around this? Even a reference to the commands would help!
0
 
LVL 1

Author Comment

by:Jack_Jones
ID: 35496828
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 35497077
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
 
LVL 1

Author Closing Comment

by:Jack_Jones
ID: 35498489
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35501350
Glad to help :-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month18 days, 19 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question