1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    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,285
    Likes Received:
    364
    Trophy Points:
    83
    Use code tag (e.g. [ php][/php ] and [ code][/code ]) to make your code easier to read.
     

Share This Page