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.OleDbConn ection
Dim da As OleDb.OleDbData Adapter
Dim dsNewRow As DataRow

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

'******* Begin Form Open Declarations *******
Dim Default_Directo ry_Settings As New DefaultDirector ySettingsWindow ' 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.EventArg s) Handles MyBase.Load


PortMandb.Conne ctionString = "PROVIDER=Micro soft.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.OleDbData Adapter(sql, PortMandb)
PortMandb.Close ()

da.Fill(WorkSta tionID, "WorkStationID" )
ws3 = WorkStationID.T ables("WorkStat ionID").Rows.Co unt

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 + '+ vbMsgBoxHelpBut ton
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 = "Representa tive Identification Required" ' Set title.
Defaulta = "" ' Set default.
MyValue1 = InputBox(msg, Title, Defaulta) ' , xpos, ypos)
ChkNull = IsDBNull(MyValu e1)
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 + '+ vbMsgBoxHelpBut ton
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("Represe ntative Identification Most Be Three (3) Characters Or Less. Please Try Again.", , "NOTICE")
GoTo 614
End If

PortMandb.Conne ctionString = "PROVIDER=Micro soft.Jet.OLEDB. 4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_ VB_NET.mdb"
PortMandb.Open( )
''PortMandb = "Provider=SQLOL EDB.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.OleDbData Adapter(sql, PortMandb)
PortMandb.Close ()

da.Fill(RIATabl e, "RIATable")
ri = RIATable.Tables ("RIATable").Ro ws.Count
If ri = 0 Then
Dim cb0 As New OleDb.OleDbComm andBuilder(da)
dsNewRow = RIATable.Tables ("RIATable").Ne wRow()
dsNewRow.Item(" RIA") = RepID
RIATable.Tables ("RIATable").Ro ws.Add(dsNewRow )
da.Update(RIATa ble, "RIATable")
End If
''PortMandb = "Provider=SQLOL EDB.1;Data Source = SQLServer;Use Procedure for Prepare=1;Auto Translate=True; Packet Size=4096"

PortMandb.Conne ctionString = "PROVIDER=Micro soft.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.OleDbData Adapter(sql, PortMandb)
PortMandb.Close ()
da.Fill(WorkSta tionID, "WorkStationID" )

Dim cb As New OleDb.OleDbComm andBuilder(da)
dsNewRow = WorkStationID.T ables("WorkStat ionID").NewRow( )
dsNewRow.Item(" WorkStation_ID" ) = WS_ID
WorkStationID.T ables("WorkStat ionID").Rows.Ad d(dsNewRow)
da.Update(WorkS tationID, "WorkStationID" )

Dim WSFile As String = "C:\WSFile. txt"
Dim oWriter As New System.IO.Strea mWriter(WSFile)
oWriter.Write(M yValue)
oWriter.Close()

PortMandb.Conne ctionString = "PROVIDER=Micro soft.Jet.OLEDB. 4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_ VB_NET.mdb"
PortMandb.Open( )
sql = "Select WorkStation_Nam e, RIA, Time_On, Width, Height, RadioButton32, RadioButton25, RadioButton18, RadioButton20, Combo22 From Navigate"
da = New OleDb.OleDbData Adapter(sql, PortMandb)
PortMandb.Close ()
da.Fill(Navigat e, "Navigate")

Dim cb1 As New OleDb.OleDbComm andBuilder(da)
dsNewRow = Navigate.Tables ("Navigate").Ne wRow()
dsNewRow.Item(" WorkStation_Nam e") = 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").Ro ws.Add(dsNewRow )
da.Update(Navig ate, "Navigate")

PortMandb.Conne ctionString = "PROVIDER=Micro soft.Jet.OLEDB. 4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_ VB_NET.mdb"
PortMandb.Open( )
sql = "Select * From Default_Directo ries"
da = New OleDb.OleDbData Adapter(sql, PortMandb)
PortMandb.Close ()
da.Fill(Default Directories, "DefaultDirecto ries")

Dim Array(13) As String
Dim xa As Integer

Array(0) = "Account_Direct ory" : Array(1) = "Balance_Direct ory"
Array(2) = "PortMan Helpfile" : Array(3) = "Daily Downloads"
Array(4) = "Deleted Accountse" : Array(5) = "Order_Director y"
Array(6) = "PortMan Export Folder" : Array(7) = "Position_Direc tory"
Array(8) = "Security_Direc tory" : Array(9) = "Transactio n History Records Destination"
Array(10) = "Transactio n History Records Source" : Array(11) = "Transaction_Di rectory"

For xa = 0 To 11 Step 1
Dim cb2 As New OleDb.OleDbComm andBuilder(da)
dsNewRow = DefaultDirector ies.Tables("Def aultDirectories ").NewRow()
dsNewRow.Item(" WorkStation_Nam e") = WS_ID
dsNewRow.Item(" Directory_Name" ) = Array(xa)
dsNewRow.Item(" Directory_Path" ) = ""
DefaultDirector ies.Tables("Def aultDirectories ").Rows.Add(dsN ewRow)
da.Update(Defau ltDirectories, "DefaultDirecto ries")
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...