How to connect VB to SQL server

[nigel_chong98]

Can you show me the command line for connecting to SQL server through VB using ODBC and ADO. Thanks.

[lenin]

You can connect by a variety of methods. you can either use a connectio string, either using a SDN or not, or you can use a data environemnt. Below is an example with comments of connecting, and running a command against a SQL Server database, using parameterised command objects.


Public Function getDetails(ByVal num As Long) As ADODB.Recordset

' *=========================================================================*
' * Funtion to take a detail number and return a recordset of results *
' * This is done by using the value passed ino the function as a parameter *
' * for the command object parameters collection, appending this to the *
' * collection, and executing a stored procedure in SQL server. *
' * *
' * Set up as a reusable COM component for N - Tier Application. *
' *=========================================================================*


Dim comm_getDetails As ADODB.Command
Set comm_getDetails = New ADODB.Command

Dim lrs_getDetails As ADODB.Recordset
'
' Declare Parameter
'
Dim lp_getDetails_Param As ADODB.Parameter

'
' Define Parameter as part of comm_Details parameter object
'
Set lp_getDetails_Param = comm_getDetails.CreateParameter("queryno", adInteger, adParamInput)

'
' Append to the parameters Collection
'
comm_getDetails.Parameters.Append lp_getDetails_Param

'
' name the stored procedure ( Part of USD SP's )
'
comm_getDetails.CommandText = "usp_vw_Lookup_QueryDetail"

'
' Tell Environment it is a SP
'
comm_getDetails.CommandType = adCmdStoredProc

'
' Set the Active Connection
'
comm_getDetails.ActiveConnection = connectionString

'
' Assign value from function var to parametrer collection
'
comm_getDetails.Parameters("queryno").Value = num

'
' Execute the command object
'
Set lrs_getDetails = comm_getDetails.Execute

'
' Return Values
'
Set getDetails = lrs_getDetails

End Function

Public Sub makeConnection()

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
'
' Implement the DSN
'
conn.Open connectionString

End Sub


This is a very broad subject, so if you require any more specifics let me know.

Lenin.

[Bebe]

Set conn = New ADODB.Connection
'
' Implement the DSN
'


What does this code look like; could I get an example?

[Bebe]

of the dsn part that is?

[Glenn]

The easiest way to setup a DSN is through the Control Panel ODBC applet. You can also use the SQL OLE DB provider instead of using ODBC. The only thing that is different is the connection string. The easiest way to build a connection string is to drop an adodc control onto a form, build the connection (using the property pages) and the copy the connection string into code. You can then delete the adodc control from your form. This also shows you what kind of things are in the connection string.

Hope this helps

[JasonGS]

Reference Microsoft ActiveX Data Objects 2.x Library in your project and do this...

Dim adoConn As New ADODB.Connection
adoConn.Open "Driver={SQL Server}; Server=SQLSERVER; Database=sqldbname", txtUsername, txtPassword

[Edneeis]

No offense but didn't that post by Bebe sound like something Yoda would say,

"of the dsn part that is?"

Doesn't it sound like,

"away put your weapons I mean you no harm"

Sorry, but I had to mention that!