[timmack]

Hello!

I've been doing a self studies on visual basic and I thought I've learned a lot but still I realized that I still have
something more to learn. I want to learn the basic fundamentals on SQL for its database operations such as
adding,editing, and deleting records. I would say I'm new to SQL because as far as I've used VB, I've been using LINQ(Language Integrated Queries) for database operation. I've been doing a lot of research online on how to start and connect to SQL but I find hard to make it on my own cause all I've got is an error during runtime. I need an assistance on this matter. I just want a complete step by step process on how to connect and starts with the database operations such as add,edit, and delete. Informative links would also be very helful to me as well. All I need is the correct basic steps procedure on how to start with SQL 2005 so I can move on and walkthrough all the way about this topic which I already did when I started to study LINQ. Take note that I'm using Visual Basic 2008 Express Edition under Visual Studio 2008 and I believe that the MS SQL Server 2005 is pre-installed when you install the visual basic. Below is the code I tried to use from what I've got online and all I've got is an error.

****code*****

Imports System.Data.SqlClient     'code above the form load event

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim myConnection As New SqlConnection
        Dim myCommand As New SqlCommand
        Dim ra As Integer
     
        'the filename of my database is strongwind
        myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=strongwind.mdf")
        myConnection.Open()
        myCommand = myConnection.CreateCommand
        'the name of my table is just Table1
        myCommand.CommandText = "INSERT INTO Table1(ID Number, Name, Age, Status) VALUES('" & Trim(TextBox1.Text) & " ','" & Trim(TextBox2.Text) & "','" & Trim(TextBox3.Text) & "','" & Trim(TextBox4.Text) & "')"
        ra = myCommand.ExecuteReader.RecordsAffected

        MsgBox("New records are added.", MsgBoxStyle.Information)
        myConnection.Close()

    End Sub

****error*****

Sql Exception was unhandled: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I don't know if there's something wrong with the connection string or even the code itself. Please correct it.
Thanks for any help that will be contributed...tim mack

This post has been edited by modi123_1: 18 September 2011 - 09:12 AM
Reason for edit:: please use the code tags

[smohd]

First it is not true that SQL Server 2005 is installed when you install Visual Studio. But is installed is the interface between Visual Studio and SQL Server, so you have to install the engine manually.
So install the engine, there you can give your server name, also you can enable sa since by default it is disabled and give it a password...

[Ionut]

Quote

First it is not true that SQL Server 2005 is installed when you install Visual Studio. But is installed is the interface between Visual Studio and SQL Server, so you have to install the engine manually.

Visual studio 2010 and 2008 Professional Edition installs(if the user wants) SQL Server Express edition(the engine).
To check this, look in Services list (Start->run->services.msc). If you have SQLServer there, you have the engine. Express edition installs by default with "SQLExpress" instance name, so your connection string should contain

localhost\SQLExpress
OR
.\SQLExpress

To connect to a MSSQL engine, you have to make sure SQL Browser service is started.
EDIT:
Plus go to www.connectionstrings.com and see the correct connection string for SQLServer 2005.

Under Start->Programs->Microsoft SQL Server 2005 -> Configuration Tools(if you have installed it) -> SQLServer Configuration Manager -> Service check services states (they should be started). Always make changes to your SQL server from here(stopping/ starting the engine, changing the user under the service starts etc).

Edit 2. And use parameterized queries to avoid sql injections

PS: please use code tags [code] [/ code] (without the space in the last tag)

This post has been edited by Ionut: 18 September 2011 - 07:22 AM

[timmack]

Hello Ionut!

Thanks for your response.

I tried to use this connection string but still says unable to open the database.

Dim myConnection As New SqlConnection("Data Source =.\SQLExpress;Initial Catalog=windblow;Integrated Security=true;User Instance=true")

Is there something wrong?

[smohd]

timmack, on 18 September 2011 - 03:50 PM, said:

Hello!
Take note that I'm using Visual Basic 2008 Express Edition under Visual Studio 2008 and I believe that the MS SQL Server 2005 is pre-installed when you install the visual basic.

I wonder if someone uses Express edition. My advice; do what Ionut said, if not installed then you can download an express edition of SQL Server for free from Microsoft website.

[timmack]

Hello Ionut!

I can see here in my SQL Server Configuration Manager that the state of SQL Server Brower is 'stopped'. How do I make it running or started?

[Ionut]

Right click on it, Start

[timmack]

I tried to right-click on it but the start button is disabled. Only the properties button on it is
enabled. What should I do now? I would like to confirm from you also if my connection string is correct.

Dim myConnection As New SqlConnection("Data Source =.\SQLExpress;Initial Catalog=windblow;Integrated Security=true;User Instance=true")

[Ionut]

ok, attach a screenshot with your configuration manager

[trevster344]

Just learn whatever way works first, then work on the smarter methods, don't want to be overwhelmed.

[timmack]

Hi Ionut!

I was able to put the state of SQL Server browser to running but still I get a runtime error.
It says Sqlexception was unhandled.Cannot open database "windblow" requested by the login. The login failed. Login failed for user 'MarkTim-PC\Mark Tim'.

What's going on with this? I was trying to confirm from you if my connection string is correct.

Dim myConnection As New SqlConnection("Data Source =.\SQLExpress;Initial Catalog=windblow;Integrated Security=true;User Instance=true")

[Ionut]

This is an important part of the security. Your connection string is used for integrated security(the user logged on the computer can access the server if it is between the usernames configured on server).
To find out with which user to connect, Go to start->programs->microsoft SqlServer 2005->Sql Server Manangement Studio. Server name .\SQLExpress, Authentification : Windows Authentification.
Go to Security -> Logins and check if your user name (Mark Tim) is listed there. Otherwise, add it.
This is for integrated security.
For SQL Authentification, look for "sa" (super administrator) - this is the built-in adminsitrator of the server and configure its password. Then, go to connectionstrings.com and look after the new connection string(basically, Integrated Security=false;User Id=sa;Password=yourpassword).

[timmack]

Wait, when I go to start>programs>MS SQL Server 2005>Configuration Tools. I don't see the SQL Server Management Studio. When I installed the visual basic 2008 express edition from the visual studio 2008, the MS SQL Server 2005 was automatically installed along with the Visual Basic. I know it is installed because I was able to make a database using LINQ(Language Integrated Queries). Under my SQL Server Configuration Manager>SQL Server 2005 Services, you will see the two items: SQL Server(SQLEXPRESS) and SQL Server Browser respectively. Their states are running and their start mode are automatic.

So are you saying that I can't use the MS SQL Server 2005 Express if I have not installed the SQL Server Management Studio? Do I really need to install that feature in order to connect to SQL 2005?
Why is it using LINQ method you can immediately do the database operations without making any configurations?

[Ionut]

You went to far. Is not under Configration Tools, it is at the same level.
Whatever you use LINQ or ADO.NET, you need a connection string. And to create the correct connection string, you need connectivity info.

Attached image(s)

• 

[timmack]

I would just like to use the default MS SQL Server 2005 Express Edition but using the connection string above would still throw an exception. I could hardly find the solution.