excel 2007 userformda sql server baglantı kodları

[dincay67]

userformdaki listboxa sql serverdan nasıl bilgi aktarabilirim

 

[zafer]

Merhaba

Kodları inceleyiniz.
Gerekli yerleri doldurup çalıştırınız

servername=""
databasename=""
user=""
password=""
 
[SIZE=3][FONT=Times New Roman]Set cnt = New ADODB.Connection[/FONT][/SIZE]
[SIZE=3][FONT=Verdana][SIZE=2]strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=" & servername & ";INITIAL CATALOG=" & databasename & ";"
strConn = strConn & "UID=" & user & ";PWD=" & password[/SIZE][/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]cnt.Open strConn[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Set rst = New ADODB.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]With rst[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman].ActiveConnection = cnt[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman].Open "SELECT * FROM Tabloismi [/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]Do While Not rst.EOF[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]For i=0 To rst.Fields.Count[/SIZE][/FONT]
 
[SIZE=3][FONT=Times New Roman]    ListBox1.AddItem[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           ListBox1.COLUMN(i, x) = rst(i)[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]    Next  [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    rst.MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   x = x + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Loop[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman].Close[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]End With[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]cnt.Close[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Set rst = Nothing[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]Set cnt = Nothing[/SIZE][/FONT]

 

[sarigozoglu]

Merhaba,
Bu kodları userformun "INITALIZE" Bölümüne mi kopyalıyoruz ?

 

[zafer]

Merhaba

Evet, userformun "INITALIZE" Bölümüne kopyalıyoruz.Açılışta listbox'a bilgiler geliyor

 

[sarigozoglu]

Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

servername = "192.168.1.8"
databasename = "PERSONEL"
user = "1"
Password = "1"

strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=" & servername & ";INITIAL CATALOG=" & databasename & ";"
strConn = strConn & "UID=" & user & ";PWD=" & Password



     cnn.Open strConn
     Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cnt
.Open "SELECT SICILNO, ADI, SOYADI FROM PERSONEL1"
 
Do While Not rst.EOF
For i = 0 To rst.Fields.Count
 
    ListBox1.AddItem
           ListBox1.Column(i, x) = rst(i)
 
    Next
    rst.MoveNext
   x = x + 1
 Loop
.Close
End With
UserForm_Initialize_Exit:
     On Error Resume Next
     rst.Close
     cnn.Close
     Set rst = Nothing
     Set cnn = Nothing
     Exit Sub
UserForm_Initialize_Err:
     MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
     Resume UserForm_Initialize_Exit
End Sub

Hata veriyor, ekte gönderdim