ConnectionString, sql server

[jagguy]

I have a sql server DB and want to connect in vb.net.
I can connect to mysql easy enough but i am confused about sql server connection.

where is the database located?

[jmcilhinney]

Go to www.connectionstrings.com for all sorts of connection strings, including SQL Server. It's really not very different to MySQL.

[jagguy]

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

for vb.net i need this.

I have database name ,login,user and password.
Data Source=myServerAddress; ' i dont understand

Initial Catalog=myDataBase; 'my databsae name?

[Deepak Sakpal]

Data Source = machine name.
Initial Catalog = Your Database name.

If you are running your application locally then you can specify (local) in Data Source. If your database is stored on another machine in network then you should specify the machine name of that server.

[jagguy]

i get this error and dont know what to do next.

i havent connected to sql server before from vb.net 2008 express


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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form

      Private da As New SqlDataAdapter

    Private ds As New DataSet
    Private DeptBinder As CurrencyManager
    'Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\andrew visual basic\school7\db1.mdb;")
    Dim cn As New SqlConnection("server=localhost;uid=jagguy;pwd=mexican;database=test1")



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

        LoadDepartments()
        BindControls()

    End Sub

    Private Sub LoadDepartments()


        Dim cmd As New SqlCommand
        Dim count As Integer

        cmd.Connection = cn
        cmd.CommandText = "Select * From table2"

        da.SelectCommand = cmd
        da.Fill(ds, "student")
        
       

    End Sub

    Private Sub BindControls()
   
        DeptBinder = Me.BindingContext(ds, "student")
        txtone.DataBindings.Add("Text", ds, "student.one")
        txttwo.DataBindings.Add("Text", ds, "student.two")
        txtthree.DataBindings.Add("Text", ds, "student.three")

    End Sub

[jmcilhinney]

You simply need to open your eyes. Go to the bottom of the SQL Server 2005 page at that Web site and there's a link to a page that explains every field in a SqlConnection connection string.

[jmcilhinney]

i get this error and dont know what to do next.

i havent connected to sql server before from vb.net 2008 express


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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form

      Private da As New SqlDataAdapter

    Private ds As New DataSet
    Private DeptBinder As CurrencyManager
    'Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\andrew visual basic\school7\db1.mdb;")
    Dim cn As New SqlConnection("server=localhost;uid=jagguy;pwd=mexican;database=test1")



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

        LoadDepartments()
        BindControls()

    End Sub

    Private Sub LoadDepartments()


        Dim cmd As New SqlCommand
        Dim count As Integer

        cmd.Connection = cn
        cmd.CommandText = "Select * From table2"

        da.SelectCommand = cmd
        da.Fill(ds, "student")
        
       

    End Sub

    Private Sub BindControls()
   
        DeptBinder = Me.BindingContext(ds, "student")
        txtone.DataBindings.Add("Text", ds, "student.one")
        txttwo.DataBindings.Add("Text", ds, "student.two")
        txtthree.DataBindings.Add("Text", ds, "student.three")

    End Sub

That's just a generic SqlException message. You need to interrogate the exception object, specifically its Errors collection, to find out what the actual issue was.

[jagguy]

how do i get the errors collection? with try catch block?

i looked at the example code .

look i have to admit i can connect with other databases but i am not sure if i have a vb.net issue or sql server.

i cant see what i have done wrong which is why i am here.

http://msdn.microsoft.com/en-us/libr...ionstring.aspx

[jmcilhinney]

Well, for a start, why would you use a connection string format that doesn't match any of the examples at connectionstrings.com? Isn't that just inviting disaster? Maybe your format is valid but I don't know. Why tempt fate though? Why can't you just copy and paste one of the examples they provide and then edit the field values? The obvious choice to me would be the very first one:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

It just seems to me that you're going out of your way to make this harder than it needs to be.

Apart from that, exceptions are thrown for a reason. If you get an exception thrown then LOOK at it. EXAMINE it. That SqlException would have told you EXACTLY what the issue was had you INVESTIGATED. I don't suppose you even knew there was an Errors property, because you didn't LOOK.

[jagguy]

Dim cn As New SqlConnection("Data Source=localhost;Initial Catalog=test1;User Id=jagguy;Password=mexican;")

looking at the error with View Detail?

i am looking i am not experienced at this database .

let me get the try catch block

[techgnome]

First things first.... do you have SQL Server installed on your PC? If the answer is ANYthing other than "Yes" then odds are, the answer is no. If this is the case, then I suggest this thread http://www.vbforums.com/showthread.php?t=337051 specifically post #3... and even more specifically, the first bullet in the list:

SQL Server 2005 useful info
(contains several links in Post #4, including the Free version of SQL Server 2005, tools for it, and the help files [Books Online])

-tg

[jagguy]

yes it is installed and i have created a database and a view and inserted some data

so far so good!

[techgnome]

try this....

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

  Try
      cn.open
  Catch sqlEx as SQLException
    messagebox.show sqlEx.Message
  End

        LoadDepartments()
        BindControls()

    End Sub

hopefully that will return a more detailed explanation for the connection failure.

-tg

[jagguy]

Try
cn.Open()
Catch sqlEx As SqlException

MessageBox.Show(sqlEx.Message)
End Try



i get
Login failed for user 'jagguy'. The user is not associated with a trusted SQL Server connection.

1)now i have login jagguy ?

[techgnome]

OKay... NOW we are getting somewhere....

When you connected to the database to add your data and create your view... did you use SQL Server Authentication (with a username and a password) or did you use Windows Authentication (where you don't have to enter a username or a password, jsut hit connect)?

-tg

[jagguy]

I can connect and view my database data in vb.net using this

Dim cn As New SqlConnection("Data Source=JAGGUY2-1PD810A\SQLEXPRESS;Initial Catalog=test1;Integrated Security=True;")


However I need to set a password and login on the database.

why did this fail because again with this I get and i want a password and user set as below.

Dim cn As New SqlConnection("Data Source=JAGGUY2-1PD810A;Initial Catalog=test1;User Id=jagguy2;Password=mexican;")

I created a login using SQL Server Authentication (with a username and a login and password as there is no user field).

I Right Click you login 'jagguy2' and click properties. and then set
Server Role as sysadmin,
User Mapping as test1 DB.

It still fails!!!!
Dim cn As New SqlConnection("Data Source=JAGGUY2-1PD810A;Initial Catalog=test1;User Id=jagguy2;Password=mexican;")

[jmcilhinney]

Can you open up Management Studio and connect to the same server using those credentials?

[jagguy]

no i cant connect if i use sql server authentication.

i mean the database has the user and login.
i cant see where i have not done this correctly

[jmcilhinney]

If you can't login then that user name or password is presumably invalid. What EXACTLY does the error message say?

[jagguy]

I have the SQL server configured to do both and i can login into sql authentication with login: jagguy4 and pswd: mexican

sql server is on the same computer
the DB test1 seems set up correclty with login,user and passowrd,
Dim cn As New SqlConnection("Data Source=JAGGUY2-1PD810A;Initial Catalog=test1;User Id=jagguy4;Password=m;")


cn.open() 'error
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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)



tried the below and still didnt work.
is this the most confusing software package i have dealt with?

unless i get help here I cant do this because it just doesnt work for me.

i think this is the 3rd day i have tried to simply connect to sql server.

http://www.datamasker.com/SSE2005_NetworkCfg.htm

[jmcilhinney]

That's just the generic SqlException message. Catch the exception and interrogate it. The Errors property of an SqlException is a collection of SqlError objects, each of which relates to a specific error. That will tell you what actually went wrong.

[jagguy]

its like going around in circles

Try
cn.Open()
Catch sqlEx As SqlException

MessageBox.Show(sqlEx.Message)
End Try


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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

this is a very confusing product

[jagguy]

wait it is working now after i rebooted

Dim cn As New SqlConnection("Data Source=JAGGUY2-1PD810A\SQLEXPRESS;Initial Catalog=test1;User Id=xxxxx;Password=xxxx;")

[ntokzen]

Here I am trying to connect to the sever in the other pc. wen i connect localy it does connect. but when i change the server name and user details which should be pointing to that computer it does not allow that and give me this exception

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. (provider: Named Pipes Provider, error: 5 - Invalid parameter(s) found)

may someone help

[b_boboci]

I have the same problem.

Any idea?

[dminder]

If you are using SQL 2005 Express then yuo will need to enable the named pipes provider in the SQL Server Surface Area Configuration and make sure you have the correct machine name/ ipaddress : port configuration. Here is a link to a forum on this topic: MS Forum Discussion
And possibly this blog:
MS Blog

Good Luck!

D

[techgnome]

Here I am trying to connect to the sever in the other pc. wen i connect localy it does connect. but when i change the server name and user details which should be pointing to that computer it does not allow that and give me this exception

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. (provider: Named Pipes Provider, error: 5 - Invalid parameter(s) found)

may someone help

Question - when you go to the doctor, do you simply say "It hurts?" No, you explain the problem, "I was doing a reverese ollie on my skateboard when I wiped out and smacked my arm on the railing. I think it might be broken."

I have the same problem.

Any idea?

And do you walk in and simply say "Me too." ? I hardly think so, and yet this is exactly what you guys have done. You haven't given us anything to start with. I might as well start asking:
1) Is the computer plugged in?
2) Is the monitor turned on?
3) Is the monitor connected to the computer?

See how ridiculous those questions are? But they are the only ones I can ask based on the information given. You aren't the first ones to post this useless info, which is why you are happening to get the brunt of my frustration on this matter.


Now, if you were to post a little more info .... like what your connection string looks like... or if you are using SQL Server full, or Express.... and if you are connecting to your local machine, or a second machine.... something else that may be of use (maybe, just maybe) is the code you are using to connect...

-tg

[dminder]

tg, don't be silly!! We already know everything they are doing, don't we??

I just gave them some general tips in the hopes that my blind shot in the dark may possibly just hit the mark and not put somebody's eye out in the process......

Ok my silliness is done.

D

[b_boboci]

My.Settings.SetUserOverride("RezervimetConnectionString", "Data Source=" & REGU.GetValue("IP") _
& ";Initial Catalog=Rezervimet;Integrated Security=True;User ID=" _
& REGU.GetValue("UserName") & ";Password=" & REGU.GetValue("Password"))

Using conn As New SqlConnection(My.Settings.RezervimetConnectionString)
conn.Open()
Using cmd As New SqlCommand(FileSQL, conn)
End Using

This is my code.
I'm using SQL Server 2005, in my PC it works. but when i install program in another PC and i give conn string to my pc it doesnt work.

If u want i will give more details.
Thanks man.

[techgnome]

Well, for starters, you need to decide what login method you want.... Windows security (where Integrated Security = SSPI) or SQL Authentication (where you set password AND User ID). Using both doesn't work very well.

Then once you have decided that, then you need to make sure the correct Windows login (if you use Integrated Security) or the SQL Login (if you use SQL Authentication) has been set up for that user.

-tg

[b_boboci]

I use SQL Authentication.

My.Settings.RezervimetConnectionString="Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

How can I set up SQL Authentication for that user?

When I install the software in my PC (in my PC is located Database), everything is OK(connectionstring is like above), but when I install it in another PC in the same LAN does not work. Maybe I must configure ports of PC!!!

Thanks

[techgnome]

No... you need to make sure that the SQL Server can first allow remote connections, and seconfly that the SQL USer is created correctly. If you are connecting with that user/password from your machine OK, then the login is fine. Might want to check out the database faq and turorials, but specifically this posting about SQL Server.

-tg