-
Mar 31st, 2014, 02:59 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Read & Update MySQL Using the Same Open Connection
We're trying to use the same connection to do two (2) operations in a MySQL project. We want to read in a record, process some values from it and then UPDATE the record based on the new values. But for some reason, this only reads, but it does not update the database.
Code:
Private Sub ServerForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
dbconn = New MySqlConnection("Data Source=fabeng;user id=my_username;password=xxxx;database=MyDataBase")
sql = "SELECT * FROM getCIO WHERE id='" & My.Application.CommandLineArgs(0) & "'"
Try
dbconn.Open()
dbcomm = New MySqlCommand(sql, dbconn)
dbcomm1 = New MySqlCommand(sqlUpdate, dbconn)
dbReader = dbcomm.ExecuteReader()
While dbReader.Read()
. DO SOME SQL READING AND PROCESS
End While
dbReader.Close()
' NOW TRY TO UPDATE A RECORD USING THE SAME OPEN CONNECTION
Dim sqlUpdate = "UPDATE getCIO SET calcsComplete = 1, field1=" & field1value & ", field2=" & field2value & " WHERE id='" & My.Application.CommandLineArgs(0) & "'"
dbReader.Close()
dbcomm1.CommandText = sqlUpdate
dbcomm1.ExecuteNonQuery()
Catch ex As Exception
MsgBox("Error while reading the database: " & ex.Message)
Finally
dbconn.Close()
End Try
End Sub
-
Mar 31st, 2014, 03:23 PM
#2
Thread Starter
Hyperactive Member
Re: Read & Update MySQL Using the Same Open Connection
Okay, we got this one working. Another developer who specializes in php was assisting with this and he had mixed up some ideas using php vs VB.NET. That's what I get for listening to other developers..... LOL!
-
Mar 31st, 2014, 06:32 PM
#3
Re: [RESOLVED] Read & Update MySQL Using the Same Open Connection
so for those who read the post.. what did you do wrong?
btw.... many of the posters here are developers
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
-
Mar 31st, 2014, 10:52 PM
#4
Re: [RESOLVED] Read & Update MySQL Using the Same Open Connection
I know why it didn't work...
you closed the reader twice... the second dbreader.close throws an exception... it's in the form load event, which in some cases eats any exceptions... specifically in Win7, 64-bit... so the first will run then the reader is closed, and on the second attempt at closing the already closed reader, an exception is thrown and the code stops, kicks out, and meanwhile the app keeps otherwise running along swimmingly.
I bet if you put a break point on the first dbreader.close, then press Ctrl+Alt+E - bring up the exceptions window, and then mark the selections so that it breaks as soon as an exception is thrown... run the app... as soon as it hits the break point, step through the rest of hte code... you'll see one of two things happen... 1) you'll actually see the exception happen as it should on the second dbreader.close call, or (less likely) 2) you'll see the code kick out, unexpectedly, and the form loads.
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|