[newaudrey]

this is the error message

an attempt to attach an auto-named database for file c:\documents and settings\audrey\my documents\msdn\using sql express from visual basic 6\sql express\northwnd1.mdf failed. a database with the same name exists, or specified file cannot be opended, or it is located on unc share

this is the codes

Dim cn As ADODB.Connection
    Set cn = New Connection
    cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "AttachDBFileName=" & App.Path & "\northwnd1.mdf;Data Source=AUDREY\sqlexpress"
    cn.Open
    
    Dim rs As Recordset
    Set rs = New Recordset
    rs.Open "Select * from orders", cn
    Set MSHFlexGrid1.DataSource = rs

by the way i downloaded the sample code here http://msdn.microsof...(v=sql.90).aspx

what i want is i want to learn programming. i know how to connect ms access and my sql. but i can't make it with ms sql

please help me how to solve it

i tried to solve it but i failed. by the way the database is beside the .vbp files

This post has been edited by Atli: 03 February 2012 - 11:08 PM
Reason for edit:: Updated the title for clarity.

[Atli]

Does the .MDF file you are referencing actually exist?

[newaudrey]

Atli, on 03 February 2012 - 09:29 PM, said:

Does the .MDF file you are referencing actually exist?

ahm yes sir. its beside the .vbp file. i got confused because i downloaded the file from microsoft and i found an error, because what i was thinking is that the source code i want to learn from is correct.

but i changed the sqlserver and database name but i changed it on the file and connection string

[Atli]

OK then, that leaves us with the other alternative:

Quote

a database with the same name exists, or specified file cannot be opended

Try adding a Database=your_db_name_here; parameter to the connection string. I don't have much experience with these MDF files, but as I understand it they are attached to the database only once, after which the database they describe will exist on the SQL Server and an attempt to re-attach the MDF file will fail. Which is why you need to specify the database in the connection string, so that the MDF file is only attached if the connection to the database fails.

This post has been edited by Atli: 03 February 2012 - 10:56 PM

[newaudrey]

sir which part of the connection string?

cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "AttachDBFileName=" & App.Path & "Database=northwnd1.mdf;Data Source=AUDREY\sqlexpress"

justlike that?

[nK0de]

scratch the AttachDBFileName=" & App.Path & "\northwnd1.mdf part for now and set the connection string like this and see if it works.

cn.ConnectionString = "Data Source=AUDREY\sqlexpress;Initial Catalog=northwnd1;Persist Security Info=False;Integrated Security=SSPI"

[Atli]

No, you would leave the parameters that were in there alone, and add the Database parameter. Also, the Database parameter takes the name of the database, not the MDF file.

If that doesn't make sense to you, consider that the connection string is a collection of key-value pairs, separated by semi-colons. You need to make sure each of the key-par values is separated from the others by a ; char.

param1=value1;param2=value2;param3=value3

What you just did is basically this:

param1=value1;param2=valParam3=ue2

[newaudrey]

i have tried this connection string but i got an error

cn.ConnectionString = "Data Source=AUDREY\sqlexpress;Initial Catalog=northwnd;Persist Security Info=False;Integrated Security=SSPI"

cn.Open

multiple-step ole db operation generated errors. check each ole db status value, if available. no work has done

please help me. i just need the correct connection string, but what i really need is that the .mdf file or the database is beside the .vbp file or the exe.

please help me

[maj3091]

From my understanding, Atli is correct with his explanation.

The database will only be attached to the instance of SQLEXPRESS once. If the file isn't already in the Server, it will be attached and connected to. If it is already in the Server, then it will attach to the server instance.

You need to use the management tools and look at the the database that is attached already, to ensure you have the correct name. Just because the file is northwnd1.mdf, the actual database could be called DATA, for example.

So your connection string in that case, would be something like below (substitute your server, file and DBname accordingly).

Quote

Provider=SQLNCLI;Server=ServerName\SQLExpress;AttachDbFilename=PathToMyFile; Database=DBname;Trusted_Connection=Yes;

[newaudrey]

maj3091, on 05 February 2012 - 02:49 AM, said:

From my understanding, Atli is correct with his explanation.

The database will only be attached to the instance of SQLEXPRESS once. If the file isn't already in the Server, it will be attached and connected to. If it is already in the Server, then it will attach to the server instance.

You need to use the management tools and look at the the database that is attached already, to ensure you have the correct name. Just because the file is northwnd1.mdf, the actual database could be called DATA, for example.

So your connection string in that case, would be something like below (substitute your server, file and DBname accordingly).

Quote

Provider=SQLNCLI;Server=ServerName\SQLExpress;AttachDbFilename=PathToMyFile; Database=DBname;Trusted_Connection=Yes;

thanks sir. ill try it. but i dont understand some comments. sorry for that. im really new in peogramming

[BobRodes]

If you post the specific comments that you don't understand, we'll attempt to explain them.

[newaudrey]

BobRodes, on 05 February 2012 - 09:53 AM, said:

If you post the specific comments that you don't understand, we'll attempt to explain them.

sir can u gave me an example of the connection string i need. i can't really make it. or give me some hint sir. specially the checking if the database is already attached, i don't understand some of the comments coz im really a beginner

[maj3091]

You have had examples of connections strings. Did you try any of them?

Using the connection strings specified, YOU don't need to check if it's already attached, the system will do that for you.

So how about, you try the examples and post back specific errors that you have. At this point in time, you have been given all the information people can give you.