By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,097 Members | 1,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,097 IT Pros & Developers. It's quick & easy.

VB statements to connect to SQL Express.

P: 13
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
Mar 27 '07 #1
Share this Question
Share on Google+
6 Replies


Dököll
Expert 100+
P: 2,364
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
Mar 28 '07 #2

Dököll
Expert 100+
P: 2,364
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 :-)
Mar 28 '07 #3

P: 13
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:

Expand|Select|Wrap|Line Numbers
  1. Public Class Main_Menu
  2.  
  3.  
  4.     Inherits System.Windows.Forms.Form
  5.  
  6.     '*******   Begin Special Declarations   *******
  7.     Dim PortMandb As New OleDb.OleDbConnection
  8.     Dim da As OleDb.OleDbDataAdapter
  9.     Dim dsNewRow As DataRow
  10.  
  11.     Dim RIATable As New DataSet, WorkStationID As New DataSet, Navigate As New DataSet
  12.     Dim DefaultDirectories As New DataSet
  13.     '*******   End Special Declarations   *******
  14.  
  15.     '*******   Begin Form Open Declarations   *******
  16.     Dim Default_Directory_Settings As New DefaultDirectorySettingsWindow  '  Form
  17.     '*******   End Form Open Declarations   *******
  18.  
  19.  
  20.     '*******   Begin Common String Declarations   *******
  21.     Dim Defaulta As String, MyValue, WS_ID As String, RIA As String
  22.     Dim MyValue1 As String, ChkNull As String, RepID As String
  23.     '*******   End Common String Declarations   *******
  24.  
  25.     '*******   Begin Common Integer Declarations   *******
  26.     Dim ws3 As Integer, LenRep As Integer, ri As Integer, nas As Integer
  27.     Dim inc01 As Integer, a As Integer, b As Integer
  28.     '*******   End Common Integer Declarations   *******
  29.  
  30.     '*******   Begin Message Box Declarations   *******
  31.     Dim msg As String, Title As String, style As MsgBoxStyle, Response As MsgBoxResult
  32.     Dim sql As String
  33.     '*******   End Message Box Declarations   *******
  34.  
  35.  
  36.     Private Sub Main_Menu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  37.  
  38.  
  39.         PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"
  40.  
  41.         PortMandb.Open()
  42.  
  43.         sql = "Select WorkStation_ID From WorkStation_ID"
  44.         da = New OleDb.OleDbDataAdapter(sql, PortMandb)
  45.         PortMandb.Close()
  46.  
  47.         da.Fill(WorkStationID, "WorkStationID")
  48.         ws3 = WorkStationID.Tables("WorkStationID").Rows.Count
  49.  
  50. 613:    If ws3 = 0 Then
  51.             msg = "Enter PortMan Administrator's Id."    ' Set prompt.
  52.             Title = "PortMan Administrator's Id Required"    ' Set title.
  53.             Defaulta = ""    ' Set default.
  54.             MyValue = InputBox(msg, Title, Defaulta)           '  , xpos, ypos)
  55.  
  56.             If MyValue = "" Then
  57.                 'Me.Close()
  58.                 MsgBox("You Must Enter Server WorkStation Name. Otherwise You Are Not Be Able To Enter PortMan.", , "WARNING")
  59.                 GoTo 613
  60.             Else
  61.                 msg = MyValue & " Is The Sign In Name You Entered." & Chr(10) & Chr(10) & "Is This Correct?"
  62.                 style = vbYesNo 'vbOKCancel   'vbYesNo +  '+ vbMsgBoxHelpButton
  63.                 Title = "WARNING"
  64.                 Response = MsgBox(msg, style, Title)
  65.                 If Response = 7 Then '   1= OK  2= Cancel  6= Yes 7= No
  66.                     GoTo 613
  67.                 End If
  68.                 WS_ID = MyValue
  69.  
  70. 614:            msg = "Enter Representative Identification (Rep Id.)"    ' Set prompt.
  71.                 Title = "Representative Identification Required"    ' Set title.
  72.                 Defaulta = ""    ' Set default.
  73.                 MyValue1 = InputBox(msg, Title, Defaulta)           '  , xpos, ypos)
  74.                 ChkNull = IsDBNull(MyValue1)
  75.                 If ChkNull = True Or Len(MyValue1) < 1 Then
  76.                     MsgBox("You Must Enter Representation Identication", , "NOTICE")
  77.                     GoTo 614
  78.                 End If
  79.                 RepID = MyValue1
  80.                 msg = MyValue1 & " Is The Representative Identification You Entered." & Chr(10) & Chr(10) & "Is This Correct?"
  81.                 style = vbYesNo 'vbOKCancel   'vbYesNo +  '+ vbMsgBoxHelpButton
  82.                 Title = "WARNING"
  83.  
  84.                 Response = MsgBox(msg, style, Title)
  85.                 If Response = 7 Then '   1= OK  2= Cancel  6= Yes 7= No
  86.                     GoTo 614
  87.                 End If
  88.  
  89.                 LenRep = Len(RepID)
  90.                 If LenRep < 1 Or LenRep > 3 Then
  91.                     MsgBox("Representative Identification Most Be Three (3) Characters Or Less. Please Try Again.", , "NOTICE")
  92.                     GoTo 614
  93.                 End If
  94.  
  95.                 PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"
  96.                 PortMandb.Open()
  97.                 ''PortMandb = "Provider=SQLOLEDB.1;Data Source = SQLServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
  98.  
  99.                 sql = "Select * From RIA_Table WHERE RIA = '" & RepID & "'"  '  OR RIA IS NOT NULL"
  100.                 da = New OleDb.OleDbDataAdapter(sql, PortMandb)
  101.                 PortMandb.Close()
  102.  
  103.                 da.Fill(RIATable, "RIATable")
  104.                 ri = RIATable.Tables("RIATable").Rows.Count
  105.                 If ri = 0 Then
  106.                     Dim cb0 As New OleDb.OleDbCommandBuilder(da)
  107.                     dsNewRow = RIATable.Tables("RIATable").NewRow()
  108.                     dsNewRow.Item("RIA") = RepID
  109.                     RIATable.Tables("RIATable").Rows.Add(dsNewRow)
  110.                     da.Update(RIATable, "RIATable")
  111.                 End If
  112.                 ''PortMandb = "Provider=SQLOLEDB.1;Data Source = SQLServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
  113.  
  114.                 PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"
  115.                 PortMandb.Open()
  116.                 sql = "Select * From WorkStation_ID"
  117.                 da = New OleDb.OleDbDataAdapter(sql, PortMandb)
  118.                 PortMandb.Close()
  119.                 da.Fill(WorkStationID, "WorkStationID")
  120.  
  121.                 Dim cb As New OleDb.OleDbCommandBuilder(da)
  122.                 dsNewRow = WorkStationID.Tables("WorkStationID").NewRow()
  123.                 dsNewRow.Item("WorkStation_ID") = WS_ID
  124.                 WorkStationID.Tables("WorkStationID").Rows.Add(dsNewRow)
  125.                 da.Update(WorkStationID, "WorkStationID")
  126.  
  127.                 Dim WSFile As String = "C:\WSFile.txt"
  128.                 Dim oWriter As New System.IO.StreamWriter(WSFile)
  129.                 oWriter.Write(MyValue)
  130.                 oWriter.Close()
  131.  
  132.                 PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"
  133.                 PortMandb.Open()
  134.                 sql = "Select WorkStation_Name, RIA, Time_On, Width, Height, RadioButton32, RadioButton25, RadioButton18, RadioButton20, Combo22 From Navigate"
  135.                 da = New OleDb.OleDbDataAdapter(sql, PortMandb)
  136.                 PortMandb.Close()
  137.                 da.Fill(Navigate, "Navigate")
  138.  
  139.                 Dim cb1 As New OleDb.OleDbCommandBuilder(da)
  140.                 dsNewRow = Navigate.Tables("Navigate").NewRow()
  141.                 dsNewRow.Item("WorkStation_Name") = WS_ID
  142.                 dsNewRow.Item("RIA") = RepID
  143.                 dsNewRow.Item("Time_On") = DateString & " " & TimeString
  144.                 dsNewRow.Item("Width") = 7550
  145.                 dsNewRow.Item("Height") = 3450
  146.                 dsNewRow.Item("RadioButton32") = 0
  147.                 dsNewRow.Item("RadioButton25") = 0
  148.                 dsNewRow.Item("RadioButton18") = 0
  149.                 dsNewRow.Item("RadioButton20") = 0
  150.                 dsNewRow.Item("Combo22") = 0
  151.                 Navigate.Tables("Navigate").Rows.Add(dsNewRow)
  152.                 da.Update(Navigate, "Navigate")
  153.  
  154.                 PortMandb.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Data\MS Visual Studio Projects\MS Access\PortMan_VB_NET.mdb"
  155.                 PortMandb.Open()
  156.                 sql = "Select * From Default_Directories"
  157.                 da = New OleDb.OleDbDataAdapter(sql, PortMandb)
  158.                 PortMandb.Close()
  159.                 da.Fill(DefaultDirectories, "DefaultDirectories")
  160.  
  161.                 Dim Array(13) As String
  162.                 Dim xa As Integer
  163.  
  164.                 Array(0) = "Account_Directory" : Array(1) = "Balance_Directory"
  165.                 Array(2) = "PortMan Helpfile" : Array(3) = "Daily Downloads"
  166.                 Array(4) = "Deleted Accountse" : Array(5) = "Order_Directory"
  167.                 Array(6) = "PortMan Export Folder" : Array(7) = "Position_Directory"
  168.                 Array(8) = "Security_Directory" : Array(9) = "Transaction History Records Destination"
  169.                 Array(10) = "Transaction History Records Source" : Array(11) = "Transaction_Directory"
  170.  
  171.                 For xa = 0 To 11 Step 1
  172.                     Dim cb2 As New OleDb.OleDbCommandBuilder(da)
  173.                     dsNewRow = DefaultDirectories.Tables("DefaultDirectories").NewRow()
  174.                     dsNewRow.Item("WorkStation_Name") = WS_ID
  175.                     dsNewRow.Item("Directory_Name") = Array(xa)
  176.                     dsNewRow.Item("Directory_Path") = ""
  177.                     DefaultDirectories.Tables("DefaultDirectories").Rows.Add(dsNewRow)
  178.                     da.Update(DefaultDirectories, "DefaultDirectories")
  179.                 Next
  180.             End If
  181.         End If
  182.  
  183.     End Sub
Mar 30 '07 #4

Dököll
Expert 100+
P: 2,364
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
Mar 31 '07 #5

P: 13
DoKoll,

Thanks for the info.

brosner
Apr 1 '07 #6

Dököll
Expert 100+
P: 2,364
DoKoll,

Thanks for the info.

brosner
Quite welcome, let me know how it turns out...
Apr 3 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.