module1.bas

this code helps u to understand the basic thing to connect visual basic with sqlserver. this ll be v

Attribute VB_Name = "Module1"
Option Explicit

Public Declare Function LockWindowUpdate Lib "user32" (ByVal hWndLock As Long) As Long

Public strFormat As String
Public lngIndex As Long
Public lngCursor As Long
Public l As Long
Public strData() As String

Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Public itmx As ListItem
Public BEdt As Boolean
Public info As String   'info="aaa" for product; info="bbb" for sales; info="ccc" for editing sales
Public ctr, ctr1, n1, test As Integer
Public grand As Long
Public cInvent As String
Public vdate As Date

Public Sub open_conn()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & (App.Path & "\mdbInventory.mdb") & ";Persist Security Info=False"
cn.Open
End Sub

Public Sub close_conn()
Set cn = Nothing
Set rs = Nothing
End Sub

Private Function InvNumber(ByVal Number As String) As String
' this function are for those negative integers
    Static i As Integer
    For i = 1 To Len(Number)
        Select Case Mid$(Number, i, 1)
        Case "-": Mid$(Number, i, 1) = " "
        Case "0": Mid$(Number, i, 1) = "9"
        Case "1": Mid$(Number, i, 1) = "8"
        Case "2": Mid$(Number, i, 1) = "7"
        Case "3": Mid$(Number, i, 1) = "6"
        Case "4": Mid$(Number, i, 1) = "5"
        Case "5": Mid$(Number, i, 1) = "4"
        Case "6": Mid$(Number, i, 1) = "3"
        Case "7": Mid$(Number, i, 1) = "2"
        Case "8": Mid$(Number, i, 1) = "1"
        Case "9": Mid$(Number, i, 1) = "0"
        End Select
    Next
    InvNumber = Number
End Function

Public Sub SortNum(ByRef Objname As Object, ByVal ColumnHeader As MSComctlLib.ColumnHeader)
With Objname
''''''''''''''''''converting cursor to our glass while sorting''''''''''''''
    lngCursor = .MousePointer
    .MousePointer = vbHourglass
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    LockWindowUpdate .hWnd
    lngIndex = ColumnHeader.Index - 1
    
    strFormat = String(30, "0") & "." & String(30, "0")
            
    ' Loop through the values in this column. Re-format the values so as they
    ' can be sorted alphabetically, having already stored their visible
    ' values in the tag, along with the tag's original value

    With .ListItems
        If (lngIndex > 0) Then
            For l = 1 To .Count
                With .Item(l).ListSubItems(lngIndex)
                    .Tag = .Text & Chr$(0) & .Tag
                    If IsNumeric(Val(.Text)) Then
                        If CDbl(Val(.Text)) >= 0 Then
                            .Text = Format(CDbl(Val(.Text)), _
                                strFormat)
                        Else
                            .Text = "&" & InvNumber( _
                                Format(0 - CDbl(Val(.Text)), _
                                strFormat))
                        End If
                    Else
                        .Text = ""
                    End If
                End With
            Next l
        Else
            For l = 1 To .Count
                With .Item(l)
                    .Tag = .Text & Chr$(0) & .Tag
                    If IsNumeric(.Text) Then
                        If CDbl(.Text) >= 0 Then
                            .Text = Format(CDbl(.Text), _
                                strFormat)
                        Else
                            .Text = "&" & InvNumber( _
                                Format(0 - CDbl(.Text), _
                                strFormat))
                        End If
                    Else
                        .Text = ""
                    End If
                End With
            Next l
        End If
    End With
            
            ' sort in ascending only
    '.SortOrder = 0
            'sort in both ascending and descending
    .SortOrder = (.SortOrder + 1) Mod 2
    
    .SortKey = ColumnHeader.Index - 1
    .Sorted = True
            
            ' Restore the previous values to the 'cells' in this
            ' column of the list from the tags, and also restore
            ' the tags to their original values
            
    With .ListItems
        If (lngIndex > 0) Then
            For l = 1 To .Count
                With .Item(l).ListSubItems(lngIndex)
                    strData = Split(.Tag, Chr$(0))
                    .Text = strData(0)
                    .Tag = strData(1)
                End With
            Next l
        Else
            For l = 1 To .Count
                With .Item(l)
                    strData = Split(.Tag, Chr$(0))
                    .Text = strData(0)
                    .Tag = strData(1)
                End With
            Next l
        End If
    End With
    ' Unlock the list window so that the OCX can update it
        
    LockWindowUpdate 0&
            
    ' Restore the previous cursor
        
    .MousePointer = lngCursor
    .Sorted = False
End With
End Sub

Public Sub SortAlpha(ByRef Objname As Object, ByVal ColumnHeader As MSComctlLib.ColumnHeader)
With Objname
    ' Sort alphabetically. This is the only sort provided
    ' by the MS ListView control (at this time), and as
    ' such we don't really need to do much here
    '.SortOrder = 0
    .SortOrder = (.SortOrder + 1) Mod 2
    .SortKey = ColumnHeader.Index - 1
    .Sorted = True
'____________________sorting for both ascending and descending alphanumerically__________'


'    If lvwAlpha.SortKey = ColumnHeader.Index - 1 Then
'
'
'       If lvwAlpha.SortOrder = lvwAscending Then
'            lvwAlpha.SortOrder = lvwDescending
'        Else
'            lvwAlpha.SortOrder = lvwAscending
'        End If
'    Else
'        lvwAlpha.SortOrder = lvwAscending
'        lvwAlpha.SortKey = ColumnHeader.Index - 1
'    End If
'________________________________________________________________________________________'

End With
End Sub