Show Data in DataGrid with VB.Net 2003 and SQLServer 2000

Jx_Man 1 Tallied Votes 2K Views Share

This Code is easy way to load / show data in Datagrid. This Code write in vb.net 2003 and use sqlserver 2000 as database. I use module to connected VB.Net with SqlServer 2000. so the function to connected can be use in every form in this project.

'This Code needed one DataGrid On Form.



'In Module
'Declare outside of class
Imports System.Data
Imports System.Data.SqlClient

Module Koneksi
    Public conn As SqlConnection
    Public Function GetConnect()
        conn = New SqlConnection("server = MyServerName;database = MyDatabaseName;Trusted_Connection = yes")
        Return conn
    End Function
End Module

'In Form
'Declare outside of class
Imports System.Data
Imports System.Data.SqlClient

'Procedure To show data in DataGrid
Private Sub ShowDataGrid()
    Dim conn As SqlConnection
    Dim cmdStudent As New SqlCommand
    Dim daStudent As New SqlDataAdapter
    Dim dsStudent As New DataSet
    Dim dtStudent As New DataTable

    conn = GetConnect()
    Try
		cmdStudent = conn.CreateCommand
		cmdStudent.CommandText = "SELECT * FROM YourTableName"
		daStudent.SelectCommand = cmdStudent
		daStudent.Fill(dsStudent, "YourTableName")
		dgStudent.DataSource = dsStudent
		dgStudent.DataMember = "YourTableName"
		dgStudent.ReadOnly = True
    Catch ex As Exception
        MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
    End Try
End Sub

' Call procedure ShowDataGrid() on event Form Load 
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    ShowDataGrid()  ' Load data to view in data grid
End Sub
' You can change the event to call this procedure like in button pressed and etc..
ITKnight 1 Light Poster

worked nice

kimhanu -3 Newbie Poster

very good
thanks you

chrisdent1986 0 Newbie Poster

hi I'm doing a similar project where I use a VB system to create users for the SQL server.

I created a dataset (I'm assuming this is the same as creating a module?) and I have a text box called txtUsername and one called txtPassword and a command button called cmdCreate

Obviously the user would fill in the text boxes with information on the new user and when they click the command button it will create a new user with those details. Could you possibly modify the above code to do this and perhaps show error messages in the VB display for instance if a user already exists etc etc.

Thanks for your help

Chris

diorbabej 0 Newbie Poster

I received an error when running this :
Error: .NET SqlClient Data Provider: SQL Server does not exist or access denied.

This is what I have for the GetConnect funtion:

Public Function GetConnect()
Dim ServerName As String = "SA1SQL19"
Dim DatabaseName As String = "APPRAISAL"
conn = New SqlConnection("server = ServerName;database = DatabaseName;Trusted_Connection = yes")
Return conn
End Function

Please help! Thank you!

Jx_Man 987 Nearly a Senior Poster Featured Poster

Public Function GetConnect()
Dim ServerName As String = "SA1SQL19"
Dim DatabaseName As String = "APPRAISAL"
conn = New SqlConnection("server = ServerName;database = DatabaseName;Trusted_Connection = yes")
Return conn
End Function

try this :

Public Function GetConnect()
conn = New SqlConnection("server = SA1SQL19;database = APPRAISAL;Trusted_Connection = yes")
Return conn
End Function
cindi_rella 0 Newbie Poster
Lovealious 0 Newbie Poster

In the sample code where is 'dgStudent' defined?

Jx_Man 987 Nearly a Senior Poster Featured Poster

dg student is datagrid..

kentlytears 0 Newbie Poster

This Code is easy way to load / show data in Datagrid. This Code write in vb.net 2003 and use sqlserver 2000 as database. I use module to connected VB.Net with SqlServer 2000. so the function to connected can be use in every form in this project.

Hello! thanks for the code you post it was very helpful.

how can i search record using a textbox key press event and display the data into label box or textbox eg: txtSuppliername.text = rs!Suppliername
lblAddress.text = rs!Address

in vb.net

Ty.

neronero 0 Newbie Poster

Hi, what code i must to write to get strings instead of table. Because in example you save query data directly to table.
In JAVA what i want would look as this

ArrayList<String> VV = new ArrayList<String>(); 
String SQL1 = "SELECT * FROM "+Baza1TABLE+";";
ResultSet RES1 = null;
SQL1.Connect(Baza1IP, Baza1PORT, Baza1USER, Baza1PASS, Baza1BAZA);
try
{
  RES1 = SQL1.SQLquery(SQL1);
  j=0;
  while(RES1.next())
  {
    while( CollumNames.size() > j )
    {
      VV.add(RES1.getString(j+1));
      j++;
    }
    break;
  }
}
catch(SQLException e1)
{
  System.out.println("Error");
  System.exit(0);
}
neronero 0 Newbie Poster

I figured it out.

Public Sub readtables(ByVal baza As String, ByRef combo As ComboBox)
        combo.Items.Clear()
        Dim SQLcommand As String = "select name from " + baza + "..sysobjects where xtype = 'U';"
        adapter = New SqlDataAdapter(SQLcommand, connection)
        dataset1 = New DataSet
        adapter.Fill(dataset1, "2")
        For i As Integer = 0 To dataset1.Tables("2").Rows.Count - 1
            combo.Items.Add(dataset1.Tables("2").Rows(i)(0))
        Next
    End Sub

.Rows(i)(0) the (0) after Rows in 8. line, means which column i want.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.