Web Development

Ask something
Discussion Locked

Connecting to SQL via Visual Basic

0 helpful
5 replies

by ncovell

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
What references and syntax do I need to connect to SQL via Visual Basic. I am experienced connecting to MS Access, and therefore understand the use of recordsets (.edit, .update, etc.), but I am having a lot of trouble making the transition to SQL server. Ideally, I'd like to use .NET, but am baffled by the new Dataset, etc. objects and how to navigate them. Someone save me! I am looking fondly at the old SET myDB = string & SET myRST = myDB.openrecordset(string) and wondering, "Is SQL thatmuch more complex?!"

5 replies

by d.thomsen

Hello,

here now the VB oldstyle (ADO):

At first, add a reference to the "Microsoft ActiveX Data Objects 2.x Library"

sub foo()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

'create the connection object an open the connectionSet conn = New ADODB.Connection

conn.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=yourdatabase;Data Source=yourserver")

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM sysusers", conn 'use the connection

While Not rs.EOF
Debug.Print rs(0)
rs.MoveNext
Wend

'free the resources
rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

end sub

For the .NET Stuff have a look at the SqlClientConnection (=ADODB.Connection), SqlClientCommand (=ADODB.Command) and the SqlClientDataReader (in many ways similar to the ADODB.Recordset) in the SDK.

HTH and Best Regards,
Daniel

by ncovell

Thanks a bunch! Also, I can't seem to edit the SQL data. When I try !Name="Boris", I get a runtime error 3251. I have tried all the cursortypes. It's not a datatype error and I don't use .Edit, right? Here is my code:

Dim connAs ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection

conn.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=procare;Data Source=SQLCLUSTER;User Id=natec;Password=delta5")

Set pcRST = New ADODB.Recordset

strSQL = "select Phone1, Phone2, patientprofileid " & _
"from patientprofile " & _
"where Phone1 like '616%' " & _
"or Phone2 like '616%'"
pcRST.Open strSQL, conn, adOpenDynamic

With pcRST
Do While Not .EOF
pcP1 = !Phone1
pcP1Short = Left(pcP1, 6)
' .Edit
pcP1 = "269" & Right(pcP1, 7)
!Phone1 = pcP1 'ERRORS HERE
.Update
Loop
.Close
End With

by d.thomsen

Hi again,

You have to specify a Locktype as the fourth parameter of the Open method.

for example:
rs.Open sql, conn, cursortype, adLockPessimistic

Daniel

This discussion is closed to new replies.