VB .net connection to MS SQL Server 2000

I've been playing around with Vb.net connecting to a SQL server 2000. I feel like the connection could be improved. I'm just not sure how. I am still new to VB so go slow for me and detailed for me.



Public Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

con.ConnectionString = ConnectionString
'call pickfield sub (starts the SQL Query generation)
PickFields()


da = New SqlDataAdapter(sqlQuery, con)
'con.ConnectionTimeout = 0
con.Open()
'Command.commandtimeout = 0
'still getting timeout on large (timewise) narrative searches
da.SelectCommand.ExecuteNonQuery()

Try
da.Fill(ds, p)
con.Close()
DataGridView1.DataSource = ds.Tables(p)
Catch ex As Exception
MessageBox.Show(ex.ToString)

End Try
'opens the results tab
TabControl1.SelectTab(2)
'MsgBox("databse close")
End Sub

Also I'm having issues with a timeout when I run very time consuming queries. This is the error "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated."
I know that this is probably not the right place to ask for help about that but if anyone has advice please let me know.

Forgot to past this part.
Dim con As New SqlConnection
'tried setting connection timeout on connection string but not working to stop timeout
Dim ConnectionString As String = "Data Source=database;Initial Catalog=table;timeout = 0;User ID=****;Password=*****"

First up, I'd recommend creating any short-lived, disposable objects with a Using block. By short-lived, I mean that the object gets created in a method and then not used again after that method completes. By disposable I mean any object whose type implements the IDisposable interface. Your SqlConnection and SqlDataAdapter are examples of such objects:

vb.net Code:

Using connection As New SqlConnection("connection string here"),
      adapter As New SqlDataAdapter("SQL query here", connection)
    '...
End Using

Any object created by the Using statement gets disposed by the End Using statement. Disposing a database connection also means closing it, so you never have to explicitly close a connection created that way.

That said, you don't have to explicitly close your connection anyway because you don't have to explicitly open it. The Fill and Update methods of a data adapter will automatically open and close the connection if it's not already open. You'd only explicitly open a connection when working with data adapters if you want to call Fill and or Update on more than one, to avoid closing and re-opening in between.

Also, there's no point calling ExecuteNonQuery because, for one thing, you are executing a query and, secondly, calling Fill executes the query and populates the DataTable with the result set. ExecuteNonQuery is for SQL statements that don't produce a result set, i.e. not SELECT statements. Also, you only use Fill and Update with data adapters. ExecuteNonQuery is for discrete commands, e.g. inserting a single record directly.

As for your timeout, the ConnectionTimeout is of no help. That only relates to opening the connection, which is not your issue. Your issue is executing the query, so it's the CommandTimeout of the SelectCommand of your data adapter that you need to set.

Finally, unless you want to pass multiple DataTables around or you want DataRelations between multiple tables, using a DataSet is pointless. If all you have is one DataTable then just create a DataTable and forget the DataSet.