Load data from database Ms Sql server problem

[mangore]

I Create a Ms sql server database called MyDBs and try to made it as movable, attachement database by do this way in Microsoft SQL Server Management:
Script database as - CREATE TO - File
I took the file Of DataBase(MyDBs) and put it in Debug folder in my project.
and try this code to load the data from database:
and it showed me this error: The type initializer for 'OOP__CreatProject.Module4' threw an exception.
Imports System.Data.SqlClient

Module Module4
    Dim MyConnection As SqlConnection = New SqlConnection("Provider=SQLOLEDB.1; " & "Data Source=" & Application.StartupPath & "\MyDBs.sql")
    Public Sub Load_DBmsqldata()
        Dim sql As String = " Select Name,Tel,Addr from Table1 "
        Dim dp As New SqlDataAdapter(sql, MyConnection)
 
        Dim Table1 As New DataTable
        dp.Fill(Table1)
        Form1.DataGridView1.DataSource = Table1
    End Sub
 
End Module

[sparbag]

Hello Mangore

From an initial view I would remove the space before the Select in the SQL statement. The space at the end I doubt will have consequences, but if you don't need it then why have it.

Kind regards

Steve

[jmcilhinney]

Your connection string is wrong. You're using an OleDb connection string with SqlCDlient. You need to use a SqlClient connection string. Go to www.connectionstrings.com to find the correct connection string format.

[mangore]

Like this:

Dim MyConnection As SqlConnection = New SqlConnection( "Data Source=.\SQLExpress;" &  "User Instance=true;"&  "Integrated Security=true;"& "AttachDbFilename=|DataDirectory|MyDBs.sql;")

[jmcilhinney]

That's better but there are still a couple of issues. First, while it won;t stop it working, what is the string concatenation for? Why are you joining together multiple string literals when you could just use one string literal?

The other issue will stop it working. The |DataDirectory| place-holder does NOT include a termination slash. That means that, when using it in a connection string, you need to add the slash yourself between it and the file name.

[mangore]

I tried this way and it showed me this error:
An attempt to attach an auto-named database for file C:\Users\Nader\Desktop\OOP- CreatProject\OOP- CreatProject\bin\Debug\MyDBs.sql failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Dim MyConnection As SqlConnection = New SqlConnection("Data Source=.\SQLExpress;" & "Integrated Security=true;" & "AttachDbFilename=|DataDirectory|MyDB.sql;")

[jmcilhinney]

Perhaps you should read post #5 and perhaps you should do what it says.

[mangore]

Perhaps you should read post #5 and perhaps you should do what it says.

I'm sorry I can't understand it cleary

[mangore]

i tried this was it showed me same error:

Dim MyConnection As SqlConnection = New SqlConnection("Data Source=.\SQLExpress; Integrated Security=true; AttachDbFilename=|DataDirectory|\MyDBs.sql;")

[szlamany]

What is this?

AttachDbFilename=|DataDirectory|\MyDBs.sql

What are these odd characters in front of and at the end of...

|DataDirectory|

Have you ever tried to browse to that location on your computer???

And what error are you getting anyway? Put your code in a TRY/CATCH construct and report the actual error back here...

[mangore]

Have you ever tried to browse to that location on your computer???

And what error are you getting anyway? Put your code in a TRY/CATCH construct and report the actual error back here...

This code coonection succeedd with me when connect to database from Ms sql management studio express.

Dim MyConnection As SqlConnection = New SqlConnection("Data Source=NADER-PC\SQLEXPRESS;Integrated Security=true;Initial Catalog=MyDBs")

but when I took the database and put it in the debug folder in my project. it shoed me the error
The error in the pic after use Try . with Message.show...

[jmcilhinney]

What is this?

AttachDbFilename=|DataDirectory|\MyDBs.sql

What are these odd characters in front of and at the end of...

|DataDirectory|

Have you ever tried to browse to that location on your computer???

And what error are you getting anyway? Put your code in a TRY/CATCH construct and report the actual error back here...

The |DataDirectory| part is correct. |DataDirectory| is resolved at run time to one of three places:

1. For ClickOnce apps it resolves to a special directory created by ClickOnce to store application data files.
2. For ASP.NET apps it resolves to App_Data.
3. For other apps it resolves to the same directory as the app was run from, i.e. the same as Application.StartupPath.

What I didn't notice was the fact that the file extension was ".sql" rather than ".mdf". The first thing to do is to determine whether you need to use AttachDbFileName or InitialCatalog. How EXACTLY did you create the database? Did you open SQL Server Management Studio and create a new database there, or did you add a Service-based Database item directly to your project in VS?

[mangore]

The |DataDirectory| part is correct. |DataDirectory| is resolved at run time to one of three places:

1. For ClickOnce apps it resolves to a special directory created by ClickOnce to store application data files.
2. For ASP.NET apps it resolves to App_Data.
3. For other apps it resolves to the same directory as the app was run from, i.e. the same as Application.StartupPath.

What I didn't notice was the fact that the file extension was ".sql" rather than ".mdf". The first thing to do is to determine whether you need to use AttachDbFileName or InitialCatalog. How EXACTLY did you create the database? Did you open SQL Server Management Studio and create a new database there, or did you add a Service-based Database item directly to your project in VS?

I created the database on the SQL Server Management Studio express . and then: Script database as - CREATE TO - File. then took the file MDBs.sql and put it in the folder Debug of my project.
the pic's below show the ms ql and the place of the file of database(MyDBs)
by the way do I have to change the file extension ".sql" to ".mdf".?

[szlamany]

oops - I'll drop out of this one - didn't know how the || (pipe or pike?) characters worked for connection strings...

[mangore]

oops - I'll drop out of this one - didn't know how the || (pipe or pike?) characters worked for connection strings...

No I don't know . and I can't understand what do you mean

[szlamany]

I meant that I made a mistake mentioning the || characters - JMC pointed that out to me.

I'll let him finish helping you...

[jmcilhinney]

If you have created the database in Management Studio then it has nothing specific to do with your project. The database is permanently attached to the SQL Server instance so you must use the Initial Catalog property in the connection string and specify just the database name.

That SQL file in your project is basically useless as it is. It is NOT a database so you cannot connect to it. It is a SQL script that you execute against the SQL Server instance. As it is, all it will do is create an empty database with the same name. It will not create any tables or anything else. If you want a script to create your entire database, tables and all, then you need to right-click the database, select Tasks and then generate the appropriate script(s).

[mangore]

If you have created the database in Management Studio then it has nothing specific to do with your project. The database is permanently attached to the SQL Server instance so you must use the Initial Catalog property in the connection string and specify just the database name.

That SQL file in your project is basically useless as it is. It is NOT a database so you cannot connect to it. It is a SQL script that you execute against the SQL Server instance. As it is, all it will do is create an empty database with the same name. It will not create any tables or anything else. If you want a script to create your entire database, tables and all, then you need to right-click the database, select Tasks and then generate the appropriate script(s).

I have some question
1- do I have to install sql server in any pc I wan to use my DataBase
2- why script?

[jmcilhinney]

1- do I have to install sql server in any pc I wan to use my DataBase

There has to be a SQL Server instance available if you want to use a SQL Server database. There might already be an instance on the target machine or another machine on the same network. If not then you need to install it somewhere. If you're using ClickOnce publishing or a Setup project then SQL Server Express can be installed automatically by checking a box.

2- why script?

Your question is not clear, which is why it's a good idea not to just provide two words. Do you mean why is it a good idea to provide a script? If so then because that file can be executed, either by your code or the user manually, to create the database and build the schema. If you mean something else then please ask the question again clearly.