VB statements to connect to SQL Express.

Originally Posted by brosner

DoKoll,

Here is a sample of code I'm using to connect with an MS Access database. I need VB code statements to replace the MS Access VB code to connect to SQLExpress.

The code below is nothing exciting. Just loading a form and requesting a person to sign in. Not a problem sharing it.

Thanks in advance for any help you might provide.

Brosner

Sample Code:

Public Class Main_Menu


Inherits System.Windows.Forms.Form

'******* Begin Special Declarations *******
Dim PortMandb As New OleDb.OleDbConnection
Dim da As OleDb.OleDbDataAdapter
Dim dsNewRow As DataRow

Dim RIATable As New DataSet, WorkStationID As New DataSet, Navigate As New DataSet
Dim DefaultDirectories As New DataSet
'******* End Special Declarations *******

'******* Begin Form Open Declarations *******
Dim Default_Directory_Settings As New DefaultDirectorySettingsWindow ' Form
'******* End Form Open Declarations *******


'******* Begin Common String Declarations *******
Dim Defaulta As String, MyValue, WS_ID As String, RIA As String
Dim MyValue1 As String, ChkNull As String, RepID As String
'******* End Common String Declarations *******

'******* Begin Common Integer Declarations *******
Dim ws3 As Integer, LenRep As Integer, ri As Integer, nas As Integer
Dim inc01 As Integer, a As Integer, b As Integer
'******* End Common Integer Declarations *******

'******* Begin Message Box Declarations *******
Dim msg As String, Title As String, style As MsgBoxStyle, Response As MsgBoxResult
Dim sql As String
'******* End Message Box Declarations *******


Private Sub Main_Menu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"

PortMandb.Open()

sql = "Select WorkStation_ID From WorkStation_ID"
da = New OleDb.OleDbDataAdapter(sql, PortMandb)
PortMandb.Close()

da.Fill(WorkStationID, "WorkStationID")
ws3 = WorkStationID.Tables("WorkStationID").Rows.Count

613: If ws3 = 0 Then
msg = "Enter PortMan Administrator's Id." ' Set prompt.
Title = "PortMan Administrator's Id Required" ' Set title.
Defaulta = "" ' Set default.
MyValue = InputBox(msg, Title, Defaulta) ' , xpos, ypos)

If MyValue = "" Then
'Me.Close()
MsgBox("You Must Enter Server WorkStation Name. Otherwise You Are Not Be Able To Enter PortMan.", , "WARNING")
GoTo 613
Else
msg = MyValue & " Is The Sign In Name You Entered." & Chr(10) & Chr(10) & "Is This Correct?"
style = vbYesNo 'vbOKCancel 'vbYesNo + '+ vbMsgBoxHelpButton
Title = "WARNING"
Response = MsgBox(msg, style, Title)
If Response = 7 Then ' 1= OK 2= Cancel 6= Yes 7= No
GoTo 613
End If
WS_ID = MyValue

614: msg = "Enter Representative Identification (Rep Id.)" ' Set prompt.
Title = "Representative Identification Required" ' Set title.
Defaulta = "" ' Set default.
MyValue1 = InputBox(msg, Title, Defaulta) ' , xpos, ypos)
ChkNull = IsDBNull(MyValue1)
If ChkNull = True Or Len(MyValue1) < 1 Then
MsgBox("You Must Enter Representation Identication", , "NOTICE")
GoTo 614
End If
RepID = MyValue1
msg = MyValue1 & " Is The Representative Identification You Entered." & Chr(10) & Chr(10) & "Is This Correct?"
style = vbYesNo 'vbOKCancel 'vbYesNo + '+ vbMsgBoxHelpButton
Title = "WARNING"

Response = MsgBox(msg, style, Title)
If Response = 7 Then ' 1= OK 2= Cancel 6= Yes 7= No
GoTo 614
End If

LenRep = Len(RepID)
If LenRep < 1 Or LenRep > 3 Then
MsgBox("Representative Identification Most Be Three (3) Characters Or Less. Please Try Again.", , "NOTICE")
GoTo 614
End If

PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"
PortMandb.Open()
''PortMandb = "Provider=SQLOLEDB.1;Data Source = SQLServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

sql = "Select * From RIA_Table WHERE RIA = '" & RepID & "'" ' OR RIA IS NOT NULL"
da = New OleDb.OleDbDataAdapter(sql, PortMandb)
PortMandb.Close()

da.Fill(RIATable, "RIATable")
ri = RIATable.Tables("RIATable").Rows.Count
If ri = 0 Then
Dim cb0 As New OleDb.OleDbCommandBuilder(da)
dsNewRow = RIATable.Tables("RIATable").NewRow()
dsNewRow.Item("RIA") = RepID
RIATable.Tables("RIATable").Rows.Add(dsNewRow)
da.Update(RIATable, "RIATable")
End If
''PortMandb = "Provider=SQLOLEDB.1;Data Source = SQLServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"
PortMandb.Open()
sql = "Select * From WorkStation_ID"
da = New OleDb.OleDbDataAdapter(sql, PortMandb)
PortMandb.Close()
da.Fill(WorkStationID, "WorkStationID")

Dim cb As New OleDb.OleDbCommandBuilder(da)
dsNewRow = WorkStationID.Tables("WorkStationID").NewRow()
dsNewRow.Item("WorkStation_ID") = WS_ID
WorkStationID.Tables("WorkStationID").Rows.Add(dsN ewRow)
da.Update(WorkStationID, "WorkStationID")

Dim WSFile As String = "C:\WSFile.txt"
Dim oWriter As New System.IO.StreamWriter(WSFile)
oWriter.Write(MyValue)
oWriter.Close()

PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"
PortMandb.Open()
sql = "Select WorkStation_Name, RIA, Time_On, Width, Height, RadioButton32, RadioButton25, RadioButton18, RadioButton20, Combo22 From Navigate"
da = New OleDb.OleDbDataAdapter(sql, PortMandb)
PortMandb.Close()
da.Fill(Navigate, "Navigate")

Dim cb1 As New OleDb.OleDbCommandBuilder(da)
dsNewRow = Navigate.Tables("Navigate").NewRow()
dsNewRow.Item("WorkStation_Name") = WS_ID
dsNewRow.Item("RIA") = RepID
dsNewRow.Item("Time_On") = DateString & " " & TimeString
dsNewRow.Item("Width") = 7550
dsNewRow.Item("Height") = 3450
dsNewRow.Item("RadioButton32") = 0
dsNewRow.Item("RadioButton25") = 0
dsNewRow.Item("RadioButton18") = 0
dsNewRow.Item("RadioButton20") = 0
dsNewRow.Item("Combo22") = 0
Navigate.Tables("Navigate").Rows.Add(dsNewRow)
da.Update(Navigate, "Navigate")

PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"
PortMandb.Open()
sql = "Select * From Default_Directories"
da = New OleDb.OleDbDataAdapter(sql, PortMandb)
PortMandb.Close()
da.Fill(DefaultDirectories, "DefaultDirectories")

Dim Array(13) As String
Dim xa As Integer

Array(0) = "Account_Directory" : Array(1) = "Balance_Directory"
Array(2) = "PortMan Helpfile" : Array(3) = "Daily Downloads"
Array(4) = "Deleted Accountse" : Array(5) = "Order_Directory"
Array(6) = "PortMan Export Folder" : Array(7) = "Position_Directory"
Array(8) = "Security_Directory" : Array(9) = "Transaction History Records Destination"
Array(10) = "Transaction History Records Source" : Array(11) = "Transaction_Directory"

For xa = 0 To 11 Step 1
Dim cb2 As New OleDb.OleDbCommandBuilder(da)
dsNewRow = DefaultDirectories.Tables("DefaultDirectories").Ne wRow()
dsNewRow.Item("WorkStation_Name") = WS_ID
dsNewRow.Item("Directory_Name") = Array(xa)
dsNewRow.Item("Directory_Path") = ""
DefaultDirectories.Tables("DefaultDirectories").Ro ws.Add(dsNewRow)
da.Update(DefaultDirectories, "DefaultDirectories")
Next
End If
End If




End Sub

Originally Posted by brosner

DoKoll,

Thanks for the info.

brosner