Go4Expert - problem with ADO

Go4Expert (http://www.go4expert.com/)

- Visual Basic ( VB ) (http://www.go4expert.com/forumdisplay.php?f=22)

- - problem with ADO (http://www.go4expert.com/showthread.php?t=6041)

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 :o
__________________________________________________ ________

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

Re: problem with ADO

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.

Re: problem with ADO

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

Re: problem with ADO

Use code tag (e.g. [ php][/php ] and [ code][/code ]) to make your code easier to read.