No data available after an Insert operation VB.NET - Windows Forms and Web Service

Question

VB.NET and ASMX Web Service. I am still having the same trouble of getting no data after an insert operation.

Here is my insert code:

Try
    'create a MySqlCommand to represent the query
    If sqlConn.State = ConnectionState.Closed Then
        sqlConn = New MySqlConnection(conStr)
        sqlConn.Open()
    End If

    Dim myCommand As MySqlCommand = New MySqlCommand(strQuery, sqlConn)
    If myCommand.ExecuteNonQuery() <> 0 Then
        Result = True
    End If
Catch ex As Exception
    Throw New ApplicationException("Error-luckie's server 2 " & ex.Message)
Finally
    sqlConn.Close()
    sqlConn.Dispose()
End Try

The strQuery will be a simple insert statement.

The code for retrieving data as a dataset is as follows

Public Function ExecuteQuery(ByVal strQuery As String) As DataSet
    Dim ds As DataSet = New DataSet 'create a DataSet to hold the results

    Try
        'create a MySqlCommand to represent the query
        If sqlConn.State = ConnectionState.Closed Then
            sqlConn = New MySqlConnection(conStr)
            sqlConn.Open()
        End If

        Dim sqlcmd As MySqlCommand = sqlConn.CreateCommand()
        sqlcmd.CommandType = CommandType.Text
        sqlcmd.CommandText = strQuery

        'create a MySqlDataAdapter object
        Dim sqlDa As MySqlDataAdapter = New MySqlDataAdapter
        sqlDa.SelectCommand = sqlcmd

        Try
            'fill the DataSet using the DataAdapter
            sqlDa.Fill(ds, "Results")
        Catch ex As Exception
            Throw New ApplicationException("Error-luckie's server 1 " & ex.Message)
        End Try

    Catch ex As Exception
        Throw New ApplicationException("Error-luckie's server 2 " & ex.Message)
    Finally
        sqlConn.Close()
        sqlConn.Dispose()
    End Try

    Return ds
End Function

I am Inserting data from one thread and retrieving data from another (not the same instance of connection is used).

This works perfect if I retrieve data after a 15 minutes gap.

But I want to get the result at once. Please help.

Why do you need nested `Try-Catch` blocks? You may catch different types of exceptions using the same block. — Vivek Jain, Jul 12 '13 at 02:48
Make a stored procedure that takes input to insert record and return the table as a result. You should get the data in a single attempt. — NeverHopeless, Jul 12 '13 at 02:52

score 0 · Answer 1 · answered Jul 12 '13 at 04:29

0

I would change the method as below

Public Function ExecuteQuery(strQuery As String) As DataSet
    Dim ds As New DataSet()

    Try
        Using con = New MySqlConnection(conStr)  'using statements...
            Using cmd = New MySqlCommand(strQuery, con) 'using statements...
                Using sqlDa = New MySqlDataAdapter(cmd) 'using statements...
                    sqlDa.Fill(ds, "Results")
                End Using
            End Using
        End Using
    Catch ex As Exception
        Throw New ApplicationException("Error-luckie's server 1 " + ex.Message)
    End Try

    Return ds
End Function

Public Function ExecuteNonQuery(strQuery As String) As Boolean
    Try
        Using con = New MySqlConnection(conStr) 'using statements...
            Using cmd = New MySqlCommand(strQuery, con) 'using statements...
                con.Open()
                If cmd.ExecuteNonQuery() > 0 Then ' I have change the condition 
                    Return True
                End If
            End Using

        End Using
    Catch ex As Exception
        Throw New ApplicationException("Error-luckie's server 2 " + ex.Message)
    End Try
    Return False
End Function

answered Jul 12 '13 at 04:29

Damith

62,401
13
102
153

Thanks for all your comments.No. Still the same way. I changed all my code to above and tried. yet same result. How i know so fast is I will rebuild my web service and check again it works. as soon as i insert a new data row, it doesn't return new once and I am going to check the same behaviour at my web site too. I am working with Local host. – Luckie Jul 12 '13 at 05:16
@Luckie are you check for new records from directly calling web service or from your application? if you test from your application can you update the question with which event and how you call and display data? – Damith Jul 12 '13 at 05:25
From my application and the function ExecuteQuery in my question will have to give me a dataset using a simple query like _SELECT SQL_NO_CACHE MAX(id_field) AS VALUE FROM invoices_ I tried SQL_NO_CACHE here too. – Luckie Jul 12 '13 at 05:52
Hi everyone. No one got any answer for the trouble yet! I'm stuck. Please say something. Just checked with the mysql server. The Same Query return values in the query window. So this must me something to do with the Mysql Connector? – Luckie Jul 14 '13 at 12:21
No but I tried Stopping and starting the mysql server. It worked as soon as server restarter (not the machine. just mysql server). – Luckie Jul 14 '13 at 12:51
Found after playing with .Net connector (it was 5.0.9). With the latest update, It started working. Thanks for all the help. – Luckie Jul 15 '13 at 01:55

No data available after an Insert operation VB.NET - Windows Forms and Web Service

1 Answers1