problem with ADO

mad_girl's Avatar, Join Date: Aug 2007
Go4Expert Member
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
__________________________________________________ ________

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)
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"

End Sub

Last edited by shabbir; 26Aug2007 at 08:16.. Reason: Code block
jwshepherd's Avatar, Join Date: Aug 2007
Ambitious contributor
Your subs are private and therefore the data connections will need to be recreated in each subroutine.

create a global varialbe names
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
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
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
Set rs = Nothing
Set conn = Nothing
end sub
Good Luck and if you need more help just ask.
mad_girl's Avatar, Join Date: Aug 2007
Go4Expert Member
thank u so much this part worked and the connection came alive
hoping there would be no errors in the other parts
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Use code tag (e.g. [ php][/php ] and [ code][/code ]) to make your code easier to read.