I consider myself a very patient guy with a whole lot of determination to get things working within say, a program i'm working on. However, I feel that I am only wasting precious time that I could be using to actually gain knowledge in something rather than hitting my head against a wall accomplishing nothing. SO.. I'm going to pretend the 11 hours I spent today trying to fix this problem was not wasted, as long as im able to fix it.
I'm new to databases and am working on a database program. It was written in vb6 and im trying to rewrite it in vb.net. I've completely stripped the program down from all the unneeded fancy addons and now just have the needed code to run it. The program retreives the records, but does not populate these records onto a datagrid. Google has failed me on this one, so I really need some personal help. Thanks.

Heres my Form code (Form1)

    Imports ADS_SQL_TEST_VBNET
    Public Class Form1

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Module1.Main()
    End Sub

    Public Sub cmdSQLBound_Click()
        sWhichDataType = "SQL"
        Call SetGridSize("SQL Table Bound.")
    End Sub

    Public Sub optYes01ph_Click()
        BuildTable()         'sets sFields and sQuery
        cmdSQLBound_Click()
    End Sub

    End Class

Heres my Module code (Module1)

Imports System.Data
Imports System.Data.SqlClient
Imports ADS_SQL_TEST_VBNET.Form1
Imports ADS_SQL_TEST_VBNET.Class1

 Public Module Module1

    Public rs As ADODB.Recordset
    Public db As ADODB.Connection

    Public dgvDataGrid As DataGridView
    Public DataGridRef As New DataGridView
    Public sWhichDataType, sSource, sTable, sFields, sQuery, sFilter, sOrderBy, sSelectedIndex, sStoreId    
    Public lblCount As Object
    Public Ctrl1, Ctrl2, Freq, Overhead As Decimal
    Public l, x As Integer
    Public A, I As Long


    Public Sub Main()

      Form1.optYes01ph_Click()

    End Sub


    Public Sub BuildTable()
        If sTable = "yes01ph" Or sTable = "" Then
            sTable = "yes01ph"
            sFields = "store_id,control_no,first_name,last_name,company,address,city,State,zip,phone,total,stat_code,Year,make_id,model_id,engine_id,contact,phone1,phone2,phone3,phone4,invoice_no,ready_proc,summary,note"
            sOrderBy = "control_no"
        Else
            sFields = "store_id,ml_id,first_name,last_name,company,address,city,state,zip,phone,last_upd,address2,phone2,ref_count,comments,fobs_key,quest,lockedby,rmte_stat,serv_upd,serv_id,psm_use,psm_points"
            sOrderBy = "ml_id"
        End If
        BuildFilter()
    End Sub

    Public Sub BuildFilter()
        sFilter = " AND control_no > 1 AND control_no < 500000"
        BuildQuery()
    End Sub

    Public Sub BuildQuery()

        sQuery = "SELECT " & sFields & " FROM " & sTable & " WHERE Store_ID Like 'XXX' " & sFilter & " Order By " & sOrderBy
        sQuery = Replace(sQuery, "'XXX'", "'" & Trim(UCase("250")) & "'")
    End Sub



    Public Sub SetGridSize(ByVal sMessage As String)

        Dim lblTableType As String
        lblTableType = "  " & sMessage
        Form1.dgvDataGrid.Text = "Data populated using: " & UCase(sMessage)

        DataGridRef.ColumnCount = 99
        DataGridRef.RowCount = 1

        DataSetRef.LoadDataBound()
    End Sub
    End Module

Heres my data layer (class1)

    Public Class Class1

    Public Sub LoadDataBound()

        rs = GetData()
        If Not rs.EOF Then
            dgvDataGrid.DataSource = rs '     Bind the Datagrid to the recordset
            dgvDataGrid.Refresh()
        Else
            MsgBox("No Records Found in DataGrid!")
        End If
    End Sub

    Public Function GetData() As ADODB.Recordset
        sWhichDataType = UCase(Trim(sWhichDataType))

        sSource = "development"
        GetData = OpenSQL()
    End Function

    Public Function OpenSQL() As ADODB.Recordset
        db = New ADODB.Connection : rs = New ADODB.Recordset ' Initialize Connection object & RecordSet object

        With db
            .CursorLocation = ADODB.CursorLocationEnum.adUseClient
            .Mode = ADODB.ConnectModeEnum.adModeReadWrite
            .ConnectionString = "Provider=SQLOLEDB.1;Password=pacesql;Persist Security Info=True;User ID=sa;Initial Catalog=speedwrench;Data Source=" & sSource
            .Open() ' "Provider=SQLOLEDB.1;Data Source=development", "sa", "pacesql", -1
        End With

        OpenSQL = db.Execute(sQuery)
    End Function

    End Class

I think the problem lies within the LoadDataBound() Sub routine, but I can't say for sure since I was unable to fix this myself. Thank you so much to whoever takes the time to read all of this and sorry if it's currently sloppy and is loaded with un-used methods or vars... I've completely BUTCHERED this thing trying to get it to work.

hello !
well there is not enough time to read your all code , it is better to always ask question to the point , as far as i understand you simply populate the grid from your database using vb.net , if yes then do like this .

import system.data
import system.data.sqlclient

'then just do this at the button click event
dim con as new sqlconnection("your Connection string here")
dim dt as new datatable
con.open()
dim da as new sqldataadapter("select * from table",con)
da.fill(dt)
datagridview1.datasource = dt
con.close()

this is simple code to populate your grid using vb.net , as i typed this code here so there may be some errors or spelling mistakes , so sorry in advance , and one more thing , DO NOT POST SINGLE QUESTION TWISE.

Regards

Muhammad Waqas Aslam

Ohh....I have posted in ur other duplicate post....

pasting it again........

To just populate ur datagrid with the values from database u can use the below code

'Open connection
        Dim myCommand1 As New SqlCommand
        With myCommand1
            .CommandText = "SELECT * from Table"
            .CommandType = CommandType.Text
            .Connection = Connection
        End With
        Dim dt As New DataTable
        dt.Load(myCommand1.ExecuteReader)
        If dt.Columns.Count > 0 Then
            With DataGridView1
                .DataSource = dt
            End With
        End If
'Close connection

Hope it helps.

This article has been dead for over six months. Start a new discussion instead.