how to I connect the SQL server using VB coding?

[dormingo]

hi guys, I'm just a beginner learn about the VB programming. Can somebody tell me how to connect to SQL server using the VB coding only and how to retrieve the field value from the tables? Thank you. ^_^

 

[Nagrom]

To make it easy I'll give an example using a DSN (ODBC datasource name defined on the computer). You can also connect in other ways using OLEDB directly, but this is fairly simple so we'll start here.

Let's assume you have a database table "MyTable" with a field "MyField" that holds a string value.

First you'd have to set a reference to the "Microsoft ActiveX Data Objects 2.x Library" (look for msado15.dll in the path description). If you don't know how to do that, just click "Project" in the menu at the top and then click "References" in the context menu. Scroll until you see the entry for the reference listed above and select it. Now you can use ADODB objects.

Next you'll declare your variables:

' Declare variables
Dim objConn_MyDB As ADODB.Connection
Dim objRS_MyDB As ADODB.Recordset
Dim strMyField As String
Dim strSQL As String

Instantiate your ADODB connection object:

' Create the ADODB connection and set the appropriate properties for them
Set objConn_MyDB = New ADODB.Connection
Set objRS_MyDB = New ADODB.Recordset

With objConn_MyDB
.ConnectionString = "MyODBCDatasourceName"
.CommandTimeout = 30 ' These are seconds
.Open
End With

Now that your connection to the DB is open, build your SQL string:

strSQL = "select MyField from MyTable (nolock)"

Now create your recordset object using the connection object "Execute" method:

Set objRS_MyDB = objConn_MyDB.Execute(strSQL)

Now you get the value in MyField for the current record like this:

strMyField = objRS_MyDB("MyField")

There are also other things to consider - like how you might handle if you got multiple records. In that case you could loop like this:

Do While Not objRS_MyDB.EOF
strMyField = objRS_MyDB("MyField")
' Do some processing here
objRS_MyDB.MoveNext
Loop

Also, don't forget to close your recordset and connection when you're done!

objRS_MyDB.Close
objConn_MyDB.Close

There's a lot more that can go into this, but this should get you started.

 

[dormingo]

thank you guy..I'm appreciate..

 

[Nagrom]

Not a problem at all - we all have to start some time.