Can't connect to a SQL Server 2008 Database using VB 2010 code

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
<span style="font-family:Times New Roman; font-size:small
<div style="padding:0in 0in 1pt; border:medium medium 1.5pt none none solid currentColor currentColor windowtext
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt First off, my apologies for being a complete Newb!<span style="

<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt I have a SQL Express database that I use quite successfully to track my investments in several accounts.<span style="
I built it myself, itâs quite functional, and Iâm happy with it.<span style="
The problem is that there are multiple account tables and updating them can be tedious in SQL Server, if multiple purchases and sales in multiple accounts have occurred in a month.<span style="
Tedium can lead to errorsâ
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt So, Iâd like to try my unskilled hand at writing a VB program that assists the process.<span style="

<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt My skill level in VB is somewhere between nil and very little.<span style="
My skill level in SQL is modest, but I can get by.<span style="
I also have access to help in the SQL department.<span style=" So, the SQL end of things is actually OK.<span style="
I may have issues with SQL Server as its set up on my computer, however.<span style="
(This is a private computer, Iâm the admin (LOL) and I may not have permissions set properly.)
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt I tried to build a very simple program that does nothing but connect to a test database named âtestâ and then changes one row entry with a button.<span style="
Needless to say, it does not work, or Iâd not be wasting your time.<span style="
The code, which is more or less adapted from a VB book I picked up, appears below:
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt SQL Server version: SQL Server 2008 R2
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt Visual Basic version: Visual Basic 2010 Express
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="color:blue; font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="color:blue; font-family:Consolas; font-size:9.5pt Public<span style="font-family:Consolas; font-size:9.5pt
<span style="color:blue Class <span style="color:#2b91af DB_Explorer_form
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue Private m_cnConnect <span style="color:blue
As <span style="color:blue New SqlClient.<span style="color:#2b91af SqlConnection
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue Private m_daDataAdapt <span style="color:blue
As SqlClient.<span style="color:#2b91af SqlDataAdapter
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue Private m_cbCommandBuild <span style="color:blue
As SqlClient.<span style="color:#2b91af SqlCommandBuilder
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue Private m_dtStocks <span style="color:blue
As <span style="color:blue New <span style="color:#2b91af DataTable
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue Private m_rowPosition <span style="color:blue
As <span style="color:blue Integer = 0
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue Private <span style="color:blue Sub Button1_Click(<span style="color:blue ByVal sender
<span style="color:blue As System.<span style="color:#2b91af Object,
<span style="color:blue ByVal e <span style="color:blue As System.<span style="color:#2b91af EventArgs)
<span style="color:blue Handles Button1.Click
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue Dim m_rwStocks <span style="color:blue As
<span style="color:#2b91af DataRow = m_dtStocks.Rows(0)
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:green change the stock name in first row-- test of functionality
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
m_rwStocks(0) = <span style="color:#a31515 "Sammy"
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue End <span style="color:blue Sub
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue Private <span style="color:blue Sub DB_Explorer_form_FormClosed(<span style="color:blue ByVal sender
<span style="color:blue As <span style="color:blue Object, <span style="color:blue
ByVal e <span style="color:blue As System.Windows.Forms.<span style="color:#2b91af FormClosedEventArgs)
<span style="color:blue Handles <span style="color:blue Me.FormClosed
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
m_cnConnect.Close()
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
m_cnConnect.Dispose()
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue End <span style="color:blue Sub
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue Private <span style="color:blue Sub DB_Explorer_form_Load(<span style="color:blue ByVal sender
<span style="color:blue As System.<span style="color:#2b91af Object,
<span style="color:blue ByVal e <span style="color:blue As System.<span style="color:#2b91af EventArgs)
<span style="color:blue Handles <span style="color:blue MyBase.Load
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
m_cnConnect.ConnectionString = _
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:#a31515 "Data Source=MIGHTYMOUSESQLEXPRESS;Initial catalog=c:Program Files (x86)Microsoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLDATAtemp.mdf;User ID=MyComputerMyWindowsID;Password=MyWindowsPassword;"
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
m_cnConnect.Open()
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal; text-indent:0.5in <span style="font-family:Consolas; font-size:9.5pt <span style=" m_daDataAdapt =
<span style="color:blue New SqlClient.<span style="color:#2b91af SqlDataAdapter(<span style="color:#a31515 "select * FROM Table_3",<span style="
m_cnConnect)
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
m_cbCommandBuild = <span style="color:blue New SqlClient.<span style="color:#2b91af SqlCommandBuilder(m_daDataAdapt)
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
m_daDataAdapt.Fill(m_dtStocks)
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9.5pt <span style="
<span style="color:blue End <span style="color:blue Sub
<span style="font-family:Times New Roman; font-size:small
<div style="padding:0in 0in 1pt; border:medium medium 1.5pt none none solid currentColor currentColor windowtext
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; padding:0in; border:currentColor; line-height:normal
<span style="color:blue; font-family:Consolas; font-size:9.5pt End<span style="font-family:Consolas; font-size:9.5pt
<span style="color:blue Class
<span style="font-family:Times New Roman; font-size:small
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 10pt <span style="font-family:Calibri; font-size:small
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 10pt <span style="font-size:small <span style="font-family:Calibri When debugged, the debugger returns the following error message:
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 10pt <span style="font-family:Calibri; font-size:small
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9pt âA first chance exception of type System.Data.SqlClient.SqlException occurred in System.Data.dllâ
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9pt I suspect that either the connection string is wrong, or SQL Server permissions are not allowing a connection.<span style="
Iâve tried a number of different strings found at http://www.connectionstrings.com/ <span style="font-family:Consolas; font-size:9pt <span style="color:#0000ff www.connectionstrings.com <span style="font-family:Consolas; font-size:9pt .<span style="
Help?!
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9pt Thanks for any and all help.
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9pt (Feel free to laughâ)
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9pt
<span style="font-family:Times New Roman; font-size:small
<p style="margin:0in 0in 0pt; line-height:normal <span style="font-family:Consolas; font-size:9pt Peter
<span style="font-family:Times New Roman; font-size:small <hr class="sig Peter

View the full article
 
Back
Top