Another Connection to SQL DB question 1

[stillwillyboy]

I am trying to “convert” some code in Sam’s Visual Basic 2005 Express book. I am trying to connect to a SQL 2005 Express db. The code in Sam’s is for connecting to Jet. I’ve looked at some of the other connection Q’s and replies on this site, but being a newbie, well….. Here is my code with notes:

Public Class frmConnectToSQLTest

Private m_cnADONetConnection As New System.Data.SqlClient.SqlConnection()

Private m_daDataAdapter As SqlClient.SqlDataAdapter
Private m_cbCommandBuilder As SqlClient.SqlCommandBuilder
Private m_dtClients As New DataTable
Private m_rowPosition As Integer = 0

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

m_cnADONetConnection.ConnectionString = _
"Provider=Microsoft.SQL.2005; data source= C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ClientPayroll.mdf"

'In the line above with “Provider=Microsoft.SQL.2005;” included I Receive the error message telling me that the keyword “Provider” is not supported. When I leave it out, I get past the above code, but then the following line:

m_cnADONetConnection.Open()

‘trips up with the message: “An error has occurred while ‘establishing a connection to the server. When connecting to SQL Server 2005, this failure may be ‘caused by the fact that under the default settings SQL Server does not allow remote connections.”
‘How do I fix this?

‘Code continues:

m_daDataAdapter = New SqlClient.SqlDataAdapter("Select * from dbo.tbl_ClntBasicInfo", m_cnADONetConnection)
m_cbCommandBuilder = New SqlClient.SqlCommandBuilder(m_daDataAdapter)
m_daDataAdapter.Fill(m_dtClients)
Me.ShowCurrentRecord()

End Sub

Private Sub frmConnectToSQLTest_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

m_cnADONetConnection.Close()
m_cnADONetConnection.Dispose()

End Sub

Private Sub ShowCurrentRecord()
If m_dtClients.Rows.Count = 0 Then
txtClientNumber.text = ""
txtLastName.text = ""
txtFirstName.text = ""
Exit Sub
End If

txtClientNumber.text = _
m_dtClients.Rows(m_rowPosition)("ClientNumber").ToString
txtLastName.text = _
m_dtClients.Rows(m_rowPosition)("LastName").ToString
txtFirstName.text = _
m_dtClients.Rows(m_rowPosition)("FirstName").ToString

End Sub
End Class

My server type is Database Engine.
My server name is: B-7M2KXC45F9FT4\SQLEXPRESS
I connect to my DB thru the SQL Server Management Express.
I use Windows Authority.
I believe the SQL Server version is “SQL Server 9.0.1399”
My DB name is “ClientPayroll”
The table name in ClientPayroll is “dbo.tbl_ClntBasicInfo”

After I establish the connection, I then want to be able to enter a number in the Client Number field and have the client’s information (address, phone, etc.) appear on the form.

If you see any other issues, please let me know.

TIA,

Bill

 

[hmckillop]

I dont know much about SQL 2005, but I think its the same as SQL2000 in that you dont actually specify the database file (i.e. the .MDF file), instead you reference the instance e.g.
replace

m_cnADONetConnection.ConnectionString = _
"Provider=Microsoft.SQL.2005; data source= C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ClientPayroll.mdf"

with

m_cnADONetConnection.ConnectionString = "Provider=Microsoft.SQL.2005;Data Source=Servername;Initial Catalog=databasename;Trusted_Connection=No;User Id=sa;Password=sapassword"

obviously replace where I have servername, databasename and userid/password with the name of the server/SQL instance, database name and then SQL credentials.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[stillwillyboy]

I have got connected using the following code. When the fields below are on the opening form (“Start Up”) that is used for the connection, the field data shows up as desired. But that’s not how the program (or most others) will be designed to work. I need to be able to have various forms with client info, job info, timesheets, etc. each form will need its own fields and code.

Does each form in VB need its own connection to the DB or if once the connection is made with any form in the project, the connection is made for all? The same question for queries to add, insert, update, delete, etc.

TIA, Bill
-----------------------

Public Class frmConnectToSQLTest
    Private m_cnADONetConnection As New System.Data.SqlClient.SqlConnection()
    Private m_daDataAdapter As SqlClient.SqlDataAdapter
    Private m_cbCommandBuilder As SqlClient.SqlCommandBuilder
    Private m_dtClients As New DataTable
    Private m_rowPosition As Integer = 0

    Private Sub frmConnectToSQLTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        m_cnADONetConnection.ConnectionString = _
    "Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator"

        m_cnADONetConnection.Open()


        m_daDataAdapter = New SqlClient.SqlDataAdapter("Select * from dbo.tbl_ClntBasicInfo", m_cnADONetConnection)
        m_cbCommandBuilder = New SqlClient.SqlCommandBuilder(m_daDataAdapter)
        m_daDataAdapter.Fill(m_dtClients)
        Me.ShowCurrentRecord()

    End Sub

    Private Sub frmConnectToSQLTest_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        m_cnADONetConnection.Close()
        m_cnADONetConnection.Dispose()

    End Sub

    Private Sub ShowCurrentRecord()
        If m_dtClients.Rows.Count = 0 Then
            txtClientNumber.text = ""
            txtLastName.text = ""
            txtFirstName.Text = ""
            txtMiddleInitial.Text = ""
            Exit Sub
        End If
        txtClientNumber.text = _
        m_dtClients.Rows(m_rowPosition)("ClientNumber").ToString

        txtLastName.Text = _
        m_dtClients.Rows(m_rowPosition)("LastName").ToString

        txtFirstName.text = _
        m_dtClients.Rows(m_rowPosition)("FirstName").ToString

        txtMiddleInitial.Text = _
        m_dtClients.Rows(m_rowPosition)("MiddleInitial").ToString
    End Sub
End Class