Posted by: Sourav | June 1, 2015

Load Records from SQL Server in datagridview using VB.Net


Load sql records for data grid

Go to the sqlcon.vb file

Go under the line

 

   Public sqlcommand As SqlCommand

 

And then write

 

Public sqldataadapter As SqlDataAdapter

   Public sqldataset As DataSet

 

Comment out this portion

 

‘Dim r As SqlDataReader = sqlcommand.ExecuteReader

‘           While r.Read

‘             MsgBox(r.GetName(0) & “:” & r(0))

 

   ‘         End While

 

Now to load records for data grid

sqldataadapter = New SqlDataAdapter(sqlcommand)

           sqldataset = New DataSet

           sqldataadapter.Fill(sqldataset)

 

Now our try catch block looks like this

 

Try

           sqlcon.Open()

           sqlcommand = New SqlCommand(query, sqlcon)

 

           ‘Dim r As SqlDataReader = sqlcommand.ExecuteReader

           ‘While r.Read

           ‘MsgBox(r.GetName(0) & “:” & r(0))

 

          ‘End While

           sqldataadapter = New SqlDataAdapter(sqlcommand)

           sqldataset = New DataSet

           sqldataadapter.Fill(sqldataset)

 

 

           sqlcon.Close()

 

       Catch ex As Exception

           MsgBox(ex.Message)

       End Try

 

We can see if any exception occurs the catch will catch the exception and the program will continue after catch statement

 

But the connection might still be open and we need to close this

 

So before end try

 

If sqlcon.State = ConnectionState.Open Then

               sqlcon.Close()

Now on the form load even comment out those lines we have written earlier

‘ If sqlcon.hasconnection = True Then

       ‘MsgBox(“connected”)

       ‘sqlcon.runquery(“select Age from test”)

 

       ‘Else

       ‘MsgBox(“not”)

       ‘End If

Now go to the form in design mode

Drag a tab control

Select tabcontrol1 go to properties,click tabpages click on collection

Select tab page1,select text and change it to query

 

Go to form on design mode

Click on query

 

Drag a textbox on that

Change the textbox property to multiline

Drag a button beside textbox1

Now drag a datagridview for result of the output

Under textbox1

Now double click on the run query button

If TextBox1.Text <> “” Then

 

 

           If sqlcon.hasconnection = True Then

               sqlcon.runquery(TextBox1.Text)

               If sqlcon.sqldataset.Tables.Count > 0 Then

                   DataGridView1.DataSource = sqlcon.sqldataset.Tables(0)

 

               End If

           End If

       Else

 

 

       End If

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: