Visual Basic connecting to MS SQL Server

[Maddleman]

is there any way to use Visual Basic and MSSQL without using ODBC?
please let me know.

[Anu]

Hi,

This is to help you..... I found this script in the website DEATHMOON@HOME.COM.
I have given 4 files with the filenames and the content of that.

If you have any doubts Please ask VB Experts
All the best !!!!!


Filename: adotest.vbp

Type=Exe
Reference=*G{00020430-0000-0000-C000-000000000046}#2.0#0#C:WINNTSYSTEM32STDOLE2.TLB#OLE Automation
Reference=*G{00000201-0000-0010-8000-00AA006D2EA4}#2.1#0#C:Program FilesCommon Filessystemadomsado15.dll#Microsoft ActiveX Data Objects 2.1 Library
Form=frmADOTest.frm
Startup="frmADOTest"
Command32=""
Name="adoTest"
HelpContextID="0"
CompatibleMode="0"
MajorVer=1
MinorVer=0
RevisionVer=0
AutoIncrementVer=0
ServerSupportFiles=0
VersionCompanyName="x"
CompilationType=0
OptimizationType=0
FavorPentiumPro(tm)=0
CodeViewDebugInfo=0
NoAliasing=0
BoundsCheck=0
OverflowCheck=0
FlPointCheck=0
FDIVCheck=0
UnroundedFP=0
StartMode=0
Unattended=0
Retained=0
ThreadPerObject=0
MaxNumberOfThreads=1

Filename: adotest.vbw

frmADOTest = 44, 44, 536, 492, Z, 22, 22, 514, 470, C


Filename: mssccprj.scc

[SCC]
SCC=This is a source code control file
[adoTest.vbp]
SCC_Project_Name=this project is not under source code control
SCC_Aux_Path=<This is an empty string for the mssccprj.scc file>


Filename :frmADOtest.frm

VERSION 5.00
Begin VB.Form frmADOTest
BorderStyle = 3 &#39;Fixed Dialog
Caption = &#34;DSN-Less Connection&#34;
ClientHeight = 1665
ClientLeft = 45
ClientTop = 330
ClientWidth = 3225
LinkTopic = &#34;Form1&#34;
MaxButton = 0 &#39;False
MinButton = 0 &#39;False
ScaleHeight = 1665
ScaleWidth = 3225
ShowInTaskbar = 0 &#39;False
StartUpPosition = 3 &#39;Windows Default
Begin VB.TextBox txtSQLStatement
Height = 285
Left = 120
TabIndex = 4
Text = &#34;Type your SQL Statement Here&#34;
Top = 840
Width = 3015
End
Begin VB.TextBox txtDBName
Height = 285
Left = 120
TabIndex = 3
Text = &#34;Type your Database Name Here&#34;
Top = 480
Width = 3015
End
Begin VB.TextBox txtServerName
Height = 285
Left = 120
TabIndex = 2
Text = &#34;Type your SQL Server Name Here&#34;
Top = 120
Width = 3015
End
Begin VB.CommandButton Command2
Caption = &#34;Execute SQL&#34;
Enabled = 0 &#39;False
Height = 375
Left = 1800
TabIndex = 1
Top = 1200
Width = 1275
End
Begin VB.CommandButton Command1
Caption = &#34;Open Database&#34;
Height = 375
Left = 120
TabIndex = 0
Top = 1200
Width = 1275
End
End
Attribute VB_Name = &#34;frmADOTest&#34;
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

&#39; PLEASE E-MAIL DEATHMOON@HOME.COM IF YOU HAVE
&#39; ANY QUESTIONS / SUGGESTIONS ON THIS CODE.


&#39; MUST REFERENCE
&#39; MICROSOFT ACTIVEX DATA OBJECTS 2.1 LIBRARY

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim Qy As New ADODB.Command
Dim sql As String
Dim ConnectionString As String

&#39;Const ConnectionString = &#34;driver={SQL Server};&#34; & _
&#34;server=PlaceServerNameHere;uid=;pwd=;database =DatabaseNameHere&#34;

Private Sub GetConnectionString()
ConnectionString = &#34;driver={SQL Server};&#34; & _
&#34;server=&#34; & Me.txtServerName.Text & &#34;;&#34; & _
&#34;uid=;pwd=;&#34; & _
&#34;database=&#34; & Me.txtDBName.Text & &#34;;&#34;
End Sub


Private Sub Command1_Click()

GetConnectionString

With cn
&#39; Establish DSN-less connection
.ConnectionString = ConnectionString
.ConnectionTimeout = 10
&#39;.Properties(&#34;Prompt&#34 = adPromptNever
&#39; This is the default prompting mode in ADO.
.Open
End With

MsgBox &#34;Connected&#34;
Me.Command2.Enabled = True

End Sub

Private Sub Command2_Click()
&#39;sql = &#34;SELECT * FROM employee WHERE login_id=&#39;deathmoon&#39;&#34;
sql = Me.txtSQLStatement.Text

rs.Open sql, cn, adOpenDynamic, adLockReadOnly

MsgBox rs.Fields(0) & &#34; &#34; & rs.Fields(1)

rs.Close
cn.Close
End Sub




-Anu


------------
Maddleman at 8/3/01 5:08:50 PM


is there any way to use Visual Basic and MSSQL without using ODBC?
please let me know.