problem with ADO

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
__________________________________________________________

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

 

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.

 

thank u so much this part worked and the connection came alive
hoping there would be no errors in the other parts