problem with ADO

Discussion in 'Visual Basic ( VB )' started by mad_girl, Aug 25, 2007.

  1. mad_girl

    mad_girl New Member

    Joined:
    Aug 14, 2007
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    0
    hey experts, need some help here
    the prob is this is my first time using visual basic and ADO so i got some errors in my codes which i can't work out
    i'm sending the code now and i hope u tell me what i did wrong :eek:
    __________________________________________________________

    Code:
    Dim adoConn As ADODB.Connection
    Dim cmd As ADODB.Command
    Private Sub Adodc1_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    
    End Sub
    
    Private Sub addUser_Click()
    Dim nou As Integer
    cmd = New ADODB.Command
    cmd.ActiveConnection = adoConn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "info"
    cmd.Parameters.Append cmd.CreateParameter("id", adInteger, adParamInput, Val(refId.Text))
    cmd.Parameters.Append cmd.CreateParameter("numOfUsers", adInteger, adParamOutput)
    cmd.Execute
    nou = cmd("numOfUsers")
    If (nou = 3) Then
        MsgBox "sorry,can't add more than 3 users under one referrer"
    End If
    
    End Sub
    
    Private Sub Form_Load()
    Dim constr As String
    adoConn = New ADODB.Connection
    adoConn.Provider = "SQLOLEDB.1"
    adoConn.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=new;Data Source=np:MOHAMMED\SQLEXPRESS;Initial File Name=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\nEw.mdf"
    adoConn.Open
    
    End Sub
     
    Last edited by a moderator: Aug 26, 2007
  2. jwshepherd

    jwshepherd New Member

    Joined:
    Aug 13, 2007
    Messages:
    135
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    VP Technology Corporation
    Location:
    Texas
    Home Page:
    http://www.officialhackers.com
    Your subs are private and therefore the data connections will need to be recreated in each subroutine.

    create a global varialbe names
    Code:
    global G_CONNECT  as string
    global G_SERVER_NAME As String
    global G_SERVER_ADDRESS As String
    global G_DB_NAME As String
    global G_DB_USER As String
    global G_DB_PASSWORD As String
    
    I usually read the variables in from an INI file so that if I have to changed servers it's a snap
    Code:
    G_SERVER_NAME="server"
    G_SERVER_ADDRESS="192.168.1.5"
    G_DB_NAME="db_MOHAMMED\SQLEXPRESS"
    G_DB_USER="sa"
    G_DB_PASSWORD="password"
    G_CONNECT = "Driver={SQL Server};Server=" & G_SERVER_NAME & ";Address=" & G_SERVER_ADDRESS & ";Network=DBMSSOCN;Database=" & G_DB_NAME & ";Uid=" & G_DB_USER & ";Pwd=" & G_DB_PASSWORD
    Then in each sub use the following
    Code:
    Private sub AddUser_click()
    Dim mysql As String
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    mysql = "select * from employees where status='A' ORDER BY LASTNAME ASC;"
    conn.Open G_CONNECT
    rs.Open mysql, conn, 3, 3, 1
    
    Do While Not rs.EOF
        debug.print rs.fields("name").value
        rs.MoveNext
        Loop
        
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
    end sub
    
    Good Luck and if you need more help just ask.
     
  3. mad_girl

    mad_girl New Member

    Joined:
    Aug 14, 2007
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    0
    thank u so much this part worked and the connection came alive
    hoping there would be no errors in the other parts
     
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,337
    Likes Received:
    377
    Trophy Points:
    83
    Use code tag (e.g. [ php][/php ] and [ code][/code ]) to make your code easier to read.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice