>Hi Eric,
>
>Updating, Inserting data from VB.
>
>This Code let me connect to the SQL server. I can read data from SQL Server, but addnew,update dosen't work. How can I do insert and update records?
>
>
>Dim conn As New ADODB.Connection
>Dim cmd As New ADODB.Command
>Dim rs As ADODB.Recordset
>Dim sConnString As String
>Dim iCtr As Integer
>
>'connection string for SQl server
>sConnString = "Provider=SQLOLEDB.1;user ID=qcafsl;password=magic;Initial Catalog=EmpAnnual;Data Source = Intranet;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
>conn.Open sConnString
>Set cmd.ActiveConnection = conn
>
>cmd.CommandText = "SELECT * FROM Assessment"
>cmd.CommandType = adCmdText
>Set rs = cmd.Execute
The Command object normally return a read-only recordset. Instead of the Command object, you can directly use the more flexible Recordset object.
Your code should then look like this:
Dim conn As New ADODB.Connection
Dim rs As new ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer
'connection string for SQl server
sConnString = "Provider=SQLOLEDB.1;user ID=qcafsl;password=magic;Initial Catalog=EmpAnnual;Data Source = Intranet;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
conn.Open sConnString
rst.CursorLocation = adUseClient
rst.Open "SELECT * FROM Assessment", conn, adOpenStatic, adLockOptimistic
Éric Moreau, MCPD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Moer inc.
http://www.emoreau.com