General discussion

Locked

connecion of VB with Sql server 2000

By nizam8194 ·
i don't know how o connec vb with sql server via code.send me the code. all are welcome

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by bantisk In reply to connecion of VB with Sql ...

you need to create a DSN. you can create DSN programatically too.
Then Create a ADODB Connection object. and use Open()method to setup the connection.
create recordset object ans supply the query.
you can easily find the code on www.vbcode.com

Collapse -

by cmiller5400 In reply to connecion of VB with Sql ...

Which version of VB are you using?

Collapse -

by bogdincescu In reply to connecion of VB with Sql ...

Here's how I do to connect to an Oracle database through VBA code in an .xls.
Connecting to Mess-SQLServer should be pretty much the same, the only difference should be in the "Provider", that should be designated as the right one for SQLServer.

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "Provider=MSDAORA.1;Password=" & xl_user_pass & ";User ID=" & xl_user_name & ";Data Source=" & db_name
If Err.Number <> 0 Then
MsgBox Err.Number & ": " & Err.Description, vbExclamation + vbOKOnly, "Error connecting to database!"
On Error GoTo 0
Exit Sub
End If
On Error GoTo 0

Collapse -

by RAHope In reply to connecion of VB with Sql ...

This is how I would do it using VB.net 2005.

Option Explicit On
Option Strict On
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
Dim cnStr As String = "Data Source=DEVELOPMENT\SQL2005;Initial Catalog=AdventureWorks;Integrated Security=True"
'just replace my cnStr parameters Data Source=DEVELOPMENT\SQL2005;--> the name of your SQL2000 instance
'and the Initial Catalog=AdventureWorks;--->Initial Catalog=yourDatabase.........
Dim cn As New SqlConnection(cnStr)
Try
cn.Open()
lblSqlCon.Text = "The SQL connection is: " & cn.State.ToString
'do something with code then always remember to close out your connection with
'cn.Close()
'kill it by .dispose, .close, set to nothing, gc.collect overkill or redundant probably
'I put the SQLexception first that way I know prior to any other general exception
'that my method for connecting failed. ie specific error----> general error...
Catch ex As SqlException
lblSqlCon.Text = "Sql Exception Error thrown!: " & ex.Message.ToString
If Not cn Is Nothing Then
cn.Dispose()
cn.Close()
cn = Nothing
GC.Collect()
End If
Catch ex As Exception
lblSqlCon.Text = "General Exception Error thrown!: " & ex.Message.ToString
If Not cn Is Nothing Then
cn.Dispose()
cn.Close()
cn = Nothing
GC.Collect()
End If

Finally
If Not cn Is Nothing Then
cn.Dispose()
cn.Close()
cn = Nothing
GC.Collect()
End If
End Try
End Sub
End Class

Collapse -

by glennsco In reply to connecion of VB with Sql ...

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
Persistable = 0 'NotPersistable
DataBindingBehavior = 0 'vbNone
DataSourceBehavior = 0 'vbNone
MTSTransactionMode = 0 'NotAnMTSObject
END
Attribute VB_Name = "CSQLConnection"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

Function Connect(ByVal strServerName As String, _
ByVal strDatabaseName As String) As ADODB.Connection

Dim strConnect As String
Dim cnnDatabase As ADODB.Connection

On Error GoTo Error_Handler

strConnect = "Provider=SQLOLEDB.1;"
strConnect = strConnect & "Integrated Security=SSPI;"
strConnect = strConnect & "Persist Security Info=False;"
strConnect = strConnect & "Initial Catalog=" & strDatabaseName & ";"
strConnect = strConnect & "Data Source=" & strServerName & ";"

Set cnnDatabase = New ADODB.Connection

'Debug.Print strConnect

With cnnDatabase
.ConnectionString = strConnect
.ConnectionTimeout = 0
.CommandTimeout = 0

.Open
End With

Set Connect = cnnDatabase

Exit Function

Error_Handler:

Call ErrorHandler(Err.Number, Err.Description, "CSQLConnection::Connect")

End Function

Private Sub ErrorHandler(ByVal lngErrorNumber As Long, _
ByVal strErrorDescription As String, _
ByVal strClassAndProcedure As String)

Err.Raise strClassAndProcedure & ": ", lngErrorNumber, " " & strErrorDescription
End Sub

Collapse -

by puneettandon In reply to connecion of VB with Sql ...

Do on ur own. Sala........

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums