VB statements to connect to SQL Express.

[brosner]

Hi,

I am currently developing an application using Visual Studio Express and SQL Express. I connected an SQL Express database to Visual Studio Express via the new connection wizard. No problem. Now I'm trying to access tables within an SQL Express database using VB. I can't seem to find the statements to use to do that.

I asked this question before in a different manner and I'm afraid I was misunderstood.

If possible, please share with me the statement(s) I need to insert into my VB code.

Thanks for any help you can provide.

brosner

[Dököll]

Greetings, brosner!

Please forgive the passiveness in my reply. You may need to add what you have working thus far so one can take a look. No need to post all of your code, but at least where you are. How's that for a deal?

Dököll

[Dököll]

Also, if your previous post included a code, you can search for it, reply to your own post in order to push it forward for all to see, and have a good look. I think that's permitted :-)

[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(dsNewRow)
                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").NewRow()
                    dsNewRow.Item("WorkStation_Name") = WS_ID
                    dsNewRow.Item("Directory_Name") = Array(xa)
                    dsNewRow.Item("Directory_Path") = ""
                    DefaultDirectories.Tables("DefaultDirectories").Rows.Add(dsNewRow)
                    da.Update(DefaultDirectories, "DefaultDirectories")
                Next
            End If
        End If
 
    End Sub

[Dököll]

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

Hey Brosner!

I found you an excellent tutorial. Watch the video. Went through it myself. Very helpful. See what it does for you.

http://msdn.microsoft.com/vstudio/ex...default.aspx#2

Good luck!

Dököll

[brosner]

DoKoll,

Thanks for the info.

brosner

[Dököll]

DoKoll,

Thanks for the info.

brosner

Quite welcome, let me know how it turns out...