What database to use.

I've decided to dabble a little with making a database,
so that i can gain more experience with using VB 2010.

I started out by finding some code that created a ADO(ADOX)
database. I then was searching around on how to create tables
and records for it, but then i read a piece of information that
said that a DAO database is far better to use than a ADO
database, so i scaped the ADO project and created a DAO one.

I got as far as creating a database with code and while searching
for how to add tables and records, i ran across some information
that said that microsoft doesn't even recommend using DAO
any more. They are suppose to be abandoning the access database
and probably going to use SQL.

VB, no matter what versions there are out there, only support
DAO, starting from 1992 db version 1 and ending at 2000 db version
4. I know that is and access 2010 version that MS made but apparently
VB.net wasn't good enough to be able to use that version. At least i
don't think VB 2010 has that particular version in there com objects.

So any way, before i go any further with this, i'd like your opinions
on what database i should be using.

I don't plan on using a database on the internet, so that should leave
out SQL. My plan was to create a database that could handle image
data, like pngs and store it and be able to retrieve it and put it in a
picturebox when called on.

I know Your going to say not to do it. I've read that a lot during my
searches online. It probably won't hold more than 10 pngs at a time
but it will also need to hold string data and numeric data. It shouldn't
get to inflated.

Is there a good database that i can make for doing this with?

I started out by finding some code that created a ADO(ADOX)
database. I then was searching around on how to create tables
and records for it, but then i read a piece of information that
said that a DAO database is far better to use than a ADO
database, so i scaped the ADO project and created a DAO one.
Both DAO (Data Access Objects) and ADO (ActiveX Data Objects) are frameworks / libraries for accessing databases, they aren't databases themselves.

Both can be used to access a range of databases including Access or SQL.

VB, no matter what versions there are out there, only support
DAO, starting from 1992 db version 1 and ending at 2000 db version
4. I know that is and access 2010 version that MS made but apparently
VB.net wasn't good enough to be able to use that version. At least i
don't think VB 2010 has that particular version in there com objects.
VB certainly isn't limited to DAO, VB has supported ADO from version 4 so the choice of VB isn't going to influence this decision in any way.

I don't plan on using a database on the internet, so that should leave
out SQL.
SQL server has nothing specific to do with the internet, it can certainly be accessed from a web application but there is no requirement to do so. SQL Server is perfectly suited to being accessed from any valid client - be it windows, web or other.

So, what are you saying? That i should just use the one that i'm
most comfortable using?

Looking for source code to see how things work, sure is a job in
itself. I've been looking for source code to create a db first and i
found it and used it.

Then i looked around for code to create tables and there is very little
code out there, showing how this is done. If i happen to find some,
then the coding for for SQL.

Why can someone make a program that demonstrates all the needed
aspects of creating a database?

I learned something today. There really aren't that many good programmers
out there on the web. Most of what i found were people asking the same
questions as me and anyone that responded to their questions wrote a
bunch of spaghetti code, thinking they were a programmer.

I tried a lot of that code and i always had errors.

My eyeballs hurt so bad from2 days of solid searching that i have to quit
for a while. I need the rest. LOL

Even Google wasn't my friend.

So, what are you saying? That i should just use the one that i'm
most comfortable using?

Looking for source code to see how things work, sure is a job in
itself. I've been looking for source code to create a db first and i
found it and used it.

Then i looked around for code to create tables and there is very little
code out there, showing how this is done. If i happen to find some,
then the coding for for SQL.


Most programming languages don't have code to create tables or a database. You need to learn SQL in order to create databases.

You want to pick the relational database you want to use. Once you've picked your RDBMS, read up on SQL for that RDBMS. SQL is standardized, but most databases have their own ways of doing some things. SQL is how you will create databases and tables, insert, modify and delete data through your code.

Once you have your database set up, you can connect to it using a ConnectionString. A connectionString is basically a list of data that tells your program everything it needs to access your database, like the type of database, location of database, security, etc. Google would be your best bet once you determine what relational database management system you'll use.

I found some code using ADO that creates a DB. creates a table and adds
four fields to it.

Now i'd like to be able to open that DB and see if the table and fields are
really there before i go any further.

For now, i'd rather stick with DAO and ADO. SQL just seems like over kill
for the little DB that i want to make.

If your programming in .NET then you should try and use it.

If SQL is overkill then you shouldn't be trying to use databases. Unless you specifically mean Microsoft SQL Server, but then that's by the by because the particular database type doesn't make a great deal of difference to the code that is written, only the types that are used.

For example, if I want to connect to an Microsoft SQL database I'd connect via a System.Data.SQLClient.SQLConnection, or if I wanted to connect to an SQLite database I'd connect via a System.Data.SQLite.SQLiteConnection. But both connection objects are based around the System.Data.Common.IDbConnection interface so as far as actually using them in code THEY ARE EXACTLY THE SAME. As are commands, dataadapters, datareaders etc.

The point being that if you can program for a Microsoft SQL Server then you can program for pretty much any database type (as long as the .NET provider is... err.. provided). It's all the same.

Therefore, it's probably worth learning.

My answer to the question of the thread would be SQLite.

I found a way to read the table and fields and found
out that all the code did was to create the database.

I'm surprised that i didn't get any errors when creating
the table and fields, which apparently, i didn't.

I'm so sick of trying to create an ADO or DAO database.
There just isn't enough information out there that covers
these things for VB Express 2010.

I did run across something though about SQL that i hope
you can clear up for me.

I read that any version of VB Express, can not create an SQL
database, unless i do it in design mode, but then the database
is stuck inside the program that i would make with it. Is that true?

I'd rather create the database, with it being in a folder somewhere
outside of the VB environment. Is there a trick to doing this?

Not really sure what you mean by I'm so sick of trying to create an ADO or DAO database. as there is no such thing as an ADO or a DAO database, those are just two technologies for accessing a database.

If you are considering SQL then http://www.microsoft.com/sqlserver/en/us/editions/express.aspx gives access to the free Express edition, you can also download the management tools from there which will allow you to create and manage databases. These databases can then be accessed from a VB application without any problem at all.

PlausiblyDamp:

I never really got into working with databases when i used VB6, so
i really don't know what the difference between DAO & ADO are.
I do know that everyone codes it differently though. It would seem
though that VB.Net requires a slightly different way to code for a
database and there lies my problem. Everyone is using ADO to
open a mdb file, that i thought was made by a DAO database code.
Then i find SQL opening up mdb files and then i really get confused.
If SQL can create, open and edit, all the databases created by DAO &
ADO, then maybe i should just learn SQL, to be on top of the game.

Anyway, I installed MySQL, just to give it a try but i'm not understanding
how to create or connect to a database. I have some SQL code that is suppose
to create a database, if it doesn't exits or open it, if it does exist. The problem
i don't understand is how to point the code toward a database on my hard drive
instead of an online database. Can someone help me with this? Here is the code.


' This is where i want to store a database file C:\Users\MyName\Desktop\Passwords.mdb
Dim strConnect As String = "Server=localhost; Port=3306; User=user; Password=password"
Dim db As New MySqlConnection(strConnect)
Dim SQLQuery As String = ""
Dim cmd As MySqlCommand

'check database and table exists, create if not
db.Open()

SQLQuery = "CREATE DATABASE IF NOT EXISTS dbtest; "
SQLQuery &= "USE dbtest; "
SQLQuery &= "CREATE TABLE IF NOT EXISTS Tracks (ID int not null _
primary key auto_increment, " & ListView1.Columns(0).Text & " varchar(60), _
" & ListView1.Columns(1).Text & " varchar(60), " & ListView1.Columns(2).Text _
& " varchar(60), " & ListView1.Columns(3).Text & " varchar(60), " _
& ListView1.Columns(4).Text _& " varchar(60));"

cmd = New MySqlCommand(SQLQuery, db)
cmd.ExecuteNonQuery()

'insert data
For Each ListItem As ListViewItem In ListView1.Items

SQLQuery = "INSERT INTO tracks(" & ListView1.Columns(0).Text & ", " _
& ListView1.Columns(1).Text & ", " & ListView1.Columns(2).Text & ", " _
& ListView1.Columns(3).Text & ", " & ListView1.Columns(4).Text & "), _
VALUES (@" & ListView1.Columns(0).Text & ", @" & ListView1.Columns(1).Text _
& ", @" & ListView1.Columns(2).Text & ", @" & ListView1.Columns(3).Text & ", @" _
& ListView1.Columns(4).Text & ");"

cmd = New MySqlCommand(SQLQuery, db)
cmd.CommandType = CommandType.Text ' <-- What is the command to enter here?

cmd.Parameters.AddWithValue("@" & ListView1.Columns(0).Text, ListItem.Text)
cmd.Parameters.AddWithValue("@" & ListView1.Columns(1).Text, ListItem.SubItems(1).Text)
cmd.Parameters.AddWithValue("@" & ListView1.Columns(2).Text, ListItem.SubItems(2).Text)
cmd.Parameters.AddWithValue("@" & ListView1.Columns(3).Text, ListItem.SubItems(3).Text)
cmd.Parameters.AddWithValue("@" & ListView1.Columns(4).Text, ListItem.SubItems(4).Text)

cmd.ExecuteNonQuery()

Next

For starters you are using a MySqlConnection (used for connecting to a MySql database) but the comment indicates you are using an Access .mdb file. If it is an Access file then just use an OleDbConnection with a connection string (http://www.connectionstrings.com/access-2007 for examples) that points to the location of the access mdb file.

There's two different scenarios being talked about here.

There's the administration of tables, such as creating them, changing the fields of the table etc and then there's using them, such as querying them, adding rows etc.

The vast majority of programs that people will write will just deal with using the tables. You write a program that queries the data in the database and makes changes to it.

You very rarely have to bother writing the administration stuff. As soon as anything on that side of it changes then whatever uses the database changes (i.e. you delete a table from a database, none of the code that queries that table works).

Generally speaking there's dedicated programs for the administration side. For Microsoft SQL Server you have the SQL Management Studio that lets you create databases, create tables in the database, edit fields in the table etc.

And at the end of the day, most of that administration stuff is just an SQL statement run against the database anyway, so it's more about understanding the syntax of the SQL to do the job in hand if you really wanted to do the code for it, but then a tool has already been created to make it easier so it makes sense to use that instead of trying to reinvent the wheel. But, ultimately, you'll be able to download a tool that will let you create the database you need easily and then you can get on with the code to start using it.

Again, SQLite is the best choice for a simple system, mainly because it doesn't require a database server to work.

You google for "SQLite management" or something like that and you'll find a tool that will let you create the database and tables.

Then you just crack on with writing the code to use that database.

After all the bickering about what database language to use and then me finally
trying to give SQL a chance, are you now saying that SQL can not create a database
or are you just saying that it's a lot easier to have a program, already made, to create
the database for me? I agree but i was wanting to learn to make one from scratch.

Anyway, i thought i had better stick with what MS supplies for using SQL but i don't
know if i referenced the correct object to use.

I referenced an MS ActiveX object and about the class form i put this: "Imports System.Data.SqlClient"
Is that correct?

I did download SQLite and already had it installed and i'll go now and download SQLite management.

When i add a reference to sql though, am i suppose to reference an MS ActiveX Object and the
above the class form put this: "Imports System.Data.SqlClient"?

Thank-you

I referenced an MS ActiveX object and about the class form i put this: "Imports System.Data.SqlClient"
Is that correct?
If you are using .Net then the data access functionality will be found under System.Data.<something>, the code under System.Data.SqlClient is used to access Microsoft Sql Server.

MS ActiveX Data Objects are the older ADO based data access techniques and aren't needed under .Net (apart from legacy compatibility) and don't need to be referenced.

I did download SQLite and already had it installed and i'll go now and download SQLite management.
If you are going to use SQLite then http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki is the relevant .Net page for downloads. Is there a reason why you chose Sqlite over Sql Express?

Is there a reason why you chose Sqlite over Sql Express?

Nope. None at all. Chalk it up to "I HAVE NO IDEA WHAT I'M DOING".

I'm only doing what others are telling me to do, or i wouldn't know what
to even look for. I'll go check out the Sql Express site.

Maybe because i'm using "Imports System.Data.SqlClient ", is the reason
why i'm getting an error then. I'll see what i else i can try and use.

Thanks....

Is SQL Server 2012 the correct program to get?
It gives me five choices of something to download.

1: Local DB
2: Express (Database Only)
3: Express with tools
4: SQL Server Management Studio Express
5: Express with Advanced Services

Got any idea on which one to get?

Come to think of it, i think i came here
already and tried to download option three
but it took about an hour and a half to
download just 14 percent, so i canceled the download.

How could this connect string be changed, to point toward
a database on my pc?

connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"

I won't need the server name, unless i have to put localDb in it's place.
I don't know what the initial catalog is.
Database name could be C:\Photo.mdf
Database isn't password protected, so i won't need a password or user name

All you should need to download is 2 and 4. The SQL Express database server and the management studio.

After installing SQL Express with the defaults, you should be able to connect to a database you've created with the connection string...

connetionString = "Data Source=YourPCName\SQLEXPRESS;Initial Catalog=TheDatabaseYouveCreated"

After installing database server and the management studio, run the management studio and the first thing it will ask for is what to connect to. It should have YourPCName\SQLExpress in the server name field. Whatever you connect to there is the same as the Data Source in your connection string. It will also have the authentication mode to select. You should be able to leave this on Windows Authentication and it will connect using your Windows login to authenticate against the SQL Server. To use Windows Mode authentication you don't specify a username and password in the connection string. Alternatively you can set up users in SQL Server and change the authentication mode to SQL Server and then you can supply a username and password to connect. It's those usernames and passwords that you would specify in the connection string.

By default the server will be installed to only work on Windows Authentication mode. You have to change a setting if you want to allow SQL usernames and passwords to connect. Selecting the properties of the Server, the Security page has that setting.

Once connected to SQL Server the first task will be to create a Database to start putting tables in. Under the Server is a Databases catagory. Expand this and you'll have a couple of other catagories, but no actual databases of your own. So you can right click the Databases and choose "New database...". A new window opens up with various bit and bobs but all you really need to do is give you database a name and click OK. Whatever you enter there as the Database Name is what you put as the Initial Catalogue in the connection string. You can create more than one database in the server.

With the database created, you'll now need to put some tables into the database to use. Under Databases should now be the Database Name you just created. Expand that and one of the groups is Tables. Right click Tables and choose "New Table...". Now you start putting in your columns for the table, giving each column a Name, a Type and whether the field is allowed to be Null or not.

Note that if you right click in the fields pane then one of the menu options is "Generate Change Script". This will show you the SQL script that will create the fields you have specified if you want to learn more about the SQL scripts for creating tables.

You can right click a field or a selection of fields and set the Primary key of the table. You can set up Indexes for the table by right clicking and choosing "Indexes\Keys".

When you've created all your fields for the table, save it (File menu Save Table, right click the page's tab Save Table, close the page and it'll ask you if you want to save it). When saving it, if you haven't given it a name in the properties of the table on the right then it will prompt you for a name for the table. Give the table a name and you now have a Database to connect to and a Table in the database to use.

Hopefully you should now know the connection string to connect to that table and use it.

You didn't mention, what i'd be creating the database with. I've downloaded
and installed everything you suggested and i still haven't gotten any further.

I've used SQLite to create a database but it doesn't save in the mdf format or
any format at all. It just creates the name of the database with no extension

I did find a database online called AdventureWorks.mdf but i can't open that either.

I guess VB 2010 will do good enough to create mdf databases for now but i'd
still have the same problem.

I've got some sample code that is just suppose to open a connection to a database
so i'll post that to see if there is something wrong with the code.


Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Xml.Serialization
Imports System.Xml
Imports System.Windows.Forms
Imports System.Data.OleDb

Private Sub OpenConnection()

Private DbPath As String = "C:\Users\MyPcName\Desktop\Employees"

Dim connetionString As String
Dim cnn As SqlConnection
connetionString = "Data Source=MyPcName\SQLEXPRESS;Initial Catalog=" & DbPath & ";Integrated Security=SSPI"

cnn = New SqlConnection(connetionString)
Try
cnn.Open()
MsgBox("Connection Open ! ")
cnn.Close()
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try
End Sub


I keep getting the "Can not open connection" message.

So, either my connection string is wrong or the database is bad or
in the wrong format.

This is sure a lot of trouble just to create and use a database. I have never
had this many problems with VB6.

I think the whole VB express project needs to be scrapped. It isn't
user friendly at all.

You would use the Express management studio to create the database, that wass one of the things DrPunk suggested you should download.

Why you are trying to use SQLite and expecting it to create a database for use with SQLExpress rather than using SQLExpress though is another matter.

http://www.connectionstrings.com is a great place for help with how connection strings work, the Initial Catalog parameter takes the name of the database and not a path to the database - that is probably why you are getting the error.

I think the whole VB express project needs to be scrapped. It isn't
user friendly at all.

In all honesty you are making this far more difficult than it needs to be - the entire process for creating a SQL database is pretty much

Go to View menu
Select server explorer
In server explorer find the Data Connections node
Right click this node and select "Create new SQL Server database"
Name the database


In fact googling "create sql database vb express" gives the first link of http://msdn.microsoft.com/en-us/library/ms345151(v=sql.90).aspx which walks you through the entire process from start to finish.

I don't have a Server Explorer, i have a Database Explorer.
Is this the same thing? I've already created a Service Based
Database by using that and i still can't connect to it, through
code.

After reading over some of that page that you linked to, i see
that i am in the right place. My problem is that the "Create SQL
Database" is greyed out and i can't access it. Must be why i didn't
see it before.

I've decide to give up on using SQL databases. It is way to much trouble
than i realized. I just want something small and simple, without the need
to connect every database that i create to my server database. before i can
even use it.

I've wasted many hours of my time, downloading all this sql stuff, searching
around for how to use one and still not any closer to even opening a connection
to one, let alone, using one. Then i get the feeling that SQl express didn't even
install correctly, or else, i would be able to create a SQL database.

I'm going back to ADO / DAO or what-ever else is available to me.
If i can't do that then i'll quit with using a database period.

Private DbPath As String = "C:\Users\MyPcName\Desktop\Employees"
When talking to a database server you don't connect to a file. You connect to a database that exists within the database server. It doesn't matter to the client where the file is. The server is configured with where the file is for the database the client says it wants to connect to.

With Access databases (and SQLite) there is no database server which is why you connect to the database with the database's filename.

If you don't want to use SQL server or similar you could still use an Access .mdb file with VB, you could still connect to it using .Net (System.Data.OleDb.OleDbConnection being the class in question) without any problem.

To the experts commenting on this thread. Thank you for taking the time to offer your experiences. It has proven helpful to me.

Like VB Alien I am struggling with building a simple database application. I am an experienced programmer however my area of expertise is machine control so much of the terminology is unfamiliar to me. We have a need to create a very simple DB application running on a PC to store some data and then present the stored data to our customer. The PC is not connected to any network, there is no server, all files will be local to that PC. The application will store process results from various operators and various process parameters. Nothing very complicated but storing, sorting and presenting will be key features.

Does the following make sense:

1)Download SQLite to create the DB and the tables in the DB.
2)The blank database created by SQLite will be placed on the target PC.
3)Use VB.Net express to add records and do simple summaries and queryies for the end user.

Thanks again for your comments.

Rich

Unless there is a specific reason you need Sqlite you may as well just use SQLExpress or even Access as support for accessing both is built into .Net.

I did find out that when someone refers to "Server Explorer"
what they really mean is "Database Explorer". Microsoft even
does this. They call it "Server Explorer/Database Explorer".

When creating an SQL database in VB net, you click on the
menu "Project" and go down to "Add New Item" and click
that. When a new window pops up, click on the "Service Based
Database". This is a SQL database in mdf format. This is according
to microsoft.

I'm currently, taking a mini tutorial on how to create a SQL database.

Walkthrough: Creating a SQL Server Express Database
http://msdn.microsoft.com/en-us/library/ms233763.aspx

OK. I went back to using ADO and i'm using the SQL
language to select field names from tables. It is working
fine but i was wondering if there is some SQL that will
find all of the field names, without having to hard code
them in.

Going from this SQL statement:
"SELECT Title, URL FROM " & lstTables.SelectedItem

In this version, i hard coded the field names in and lstTables.SelectedItem
is the name of the table that is in a list box. The field name go in another
list box.

Is there something like this that i could use instead?
"SELECT All Fields FROM " & lstTables.SelectedItem

NERVER MIND

With a little effort on my part, i found what i was looking for.
This selects all the field names at once from a specific Table.
"SELECT * FROM " & lstTables.SelectedItem

OK. I found some code that populates a listview box. It works
by passing the listview box you are using and a SQL telling the
code what table, you want to populate the listview box with, to
a function call.

I have three tables in the database and the code will only work
for the first table (Books). I was wondering if you could take a
look at the code and maybe see what is going on.

I have two list boxes on the form, one holds the tables in the database
and the second one holds the fields in the database (Columns). This
one doesn't need to be there, i just wanted to learn how to do that.

You have to double click on one of the tables, to populate the listview
box.

I was wondering if you could look at the code and see if you can see
anything wrong with it. I can upload my program, if you wish....

Here is the SQL Search String

' My SQL Search String
' Selects all field names from the selected table
strsql = "SELECT * FROM " & lstTables.SelectedItem


Here is the code i found

Public Sub FillListView(ByRef lvList As ListView, ByRef myData As OleDbDataReader)

'Fill ListView control with data
Dim itmListItem As ListViewItem
Dim shtCntr As Integer
Dim strValue As String

Do While myData.Read
itmListItem = New ListViewItem()
strValue = IIf(myData.IsDBNull(0), "", myData.GetValue(0))
itmListItem.Text = strValue

For shtCntr = 1 To myData.FieldCount() - 1
If myData.IsDBNull(shtCntr) Then
itmListItem.SubItems.Add("")
Else
itmListItem.SubItems.Add(myData.GetString(shtCntr))
End If
Next shtCntr

lvList.Items.Add(itmListItem)
Loop
End Sub


Public Function GetData(ByVal sSQL As String)

Dim TheConnection As OleDbConnection
Dim sqlCmd As OleDbCommand = New OleDbCommand(sSQL)
Dim myData As OleDbDataReader

TheConnection = New OleDbConnection(myConnection)

Try
TheConnection.Open()

sqlCmd.Connection = TheConnection

myData = sqlCmd.ExecuteReader

Return myData
Catch ex As Exception
Return ex
End Try

End Function


To use it

FillListView(Listview1, GetData(strsql))

Firstly, this isn't the best method for getting the fields of a table. If there are a lot of records in the table then it's a waste of resources to query all the data in that table just to get the fields for that table.

You could SELECT TOP 1 (or whatever the syntax is) to reduce the amount of results returned seeing as you aren't bothered about the returned results.

But you'd be better off looking at the GetSchema of the Connection object.

Unsurpisingly, it's quite complicated. The GetSchema is quite a lot to get your head around.

But stick a datagridview on a form and then have the following code run on it.
dim cn as New OleDbConnection(myConnection)
dim dt as DataTable
Try
cn.Open

dt = cn.GetSchema("Columns")

datagridview1.datasource = dt

Catch ex as Exception

messagebox.show(ex.message, "Error")

Finally

cn.close

End Try
You should end up with the datagridview listing all the columns of all the tables, along with lots of other information about about the columns (which you wouldn't be able to find out using the querying data method you're using).

Now, you could try and work out how to use GetSchema to get a particular Table's columns but a simpler method would be to query the DataTable that was returned by GetSchema with all the fields in for just the table we're interested in.

You should see from the above example what the field is that you're interested in querying. For the one I've set up to test here so I know this should work, I've connected to an Microsoft SQL Database and I've got a TABLE_NAME field in the GetSchema's returned DataTable, so I can create a DataView and tell that to only get records where TABLE_NAME = the table I'm interested in.

So, for example, my database has a table tbParts in it, so I could change the code above to only show the fields for tbParts...
dim cn as New OleDbConnection(myConnection)
dim dt as DataTable
dim dv as DataView
Try
cn.Open

dt = cn.GetSchema("Columns")

dv = New DataView(dt)
dv.RowFilter = "TABLE_NAME = 'tbParts'"

datagridview1.datasource = dv

Catch ex as Exception

messagebox.show(ex.message, "Error")

Finally

cn.close

End Try
So that creates a DataView from the DataTable and then set's the RowFilter property of the DataView to be only the rows that I want to see and then sets the datasource of the datagridview to be the dataview instead of the datatable.

You treat the data like any database data. I could put all the fields of tbParts from the DataView above into a listbox using...
dim dr as DataRowView
ListBox1.Items.Clear
For each dr in dv
ListBox1.Items.Add(dr.Item("COLUMN_NAME"))
Next

Note that you could query the schema only once and then change the DataView's RowFilter whenever a new table is selected from the lists. All you'd have to do is change the DataView's RowFilter in the selectedindexchanged event.

Note that GetSchema("Tables") will return you all the tables in a database.

Finally, note how the GetData you've posted doesn't close the connection. It's always worth putting the closing of a connection in the Finally part of a Try which will make sure the connection is closed regardless of an error occurring or not. Nevermind that GetData can return a OleDbDataReader or an Exception. That's horrible. Functions should very rarely return Objects (i.e. no Returns on the end of a function declaration).

Since i'm only concerned with getting the TABLE, i already had
code in place for retrieving that. I'm having no problems with it.
For now, i just click a button to get the TABLE names.


' insert the connect string into the ADODB object
conADO.ConnectionString = myConnection

' Open the connection
conADO.Open()


' Make a catalog for the database.
Dim cat As New ADOX.Catalog

' insert the active connection string from
' ADODB object into the ADOX object
cat.ActiveConnection = conADO

' Clear the listbox
lstTables.Items.Clear()

' List the catalog's tables.
For i As Integer = 0 To cat.Tables.Count - 1
' Make sure we are not loading up the
' ACCESS TABLE or SYSTEM TABLE
If cat.Tables(i).Type = "TABLE" Then
lstTables.Items.Add(cat.Tables(i).Name)
End If
Next i

' Close the connection
conADO.Close()


Once the TABLE names are displayed, i have code in the
SelectedIndexChanged event that gets and displays all the
FIELD names from that selected TABLE.


' insert the connect string into the ADODB object
conADO.ConnectionString = myConnection

' Open the connection
conADO.Open()

' Make a catalog for the database.
Dim cat As New ADOX.Catalog

' insert the active connection string from
' ADODB object into the ADOX object
cat.ActiveConnection = conADO

' Clear the listbox
lstFields.Items.Clear()

' Clear the Listview box
ListView1.Clear()

' Use SQL to select all the field names from selected table name
rs = conADO.Execute("SELECT * FROM " & lstTables.SelectedItem)

' Display field names in list box
' and in the listview columns
For Each FIELD In rs.Fields
lstFields.Items.Add(FIELD.name)
ListView1.Columns.Add(FIELD.name)
Next

' Close the recordset
rs.Close()

' Close the connection
conADO.Close()


The field or column name are already displayed in a listview box
before i even call on any code to populate it with field values.

I'm not looking for only certain data in a TABLE. I want to display
all the data, in that TABLE, in the listview box.

From the code you have just supplied, it only lists the TABLE NAME
or FIELD NAME but not the FIELD values.

I know that GetSchema can get all the data i need, but like you said,
it is really hard to wrap your head around it. I found a lot of code using
it but none of it worked for a listview.

I don't really like the dataview but maybe i'm better off using one for now.

Well, i'll go search some more.

Before i go though, are you aware of any free stand alone programs that
will create a true MS Access 97 or 2000 database?

Apologies, I kind of read the code you'd posted wrong and thought it was doing something else.
I found a lot of code using
it but none of it worked for a listview.
The listview isn't really a data control. The DataGridView's better because you can chuck a table at it and it sorts out all the columns and puts the data in it. The listview can't do that, so you have to add the columns (subitems) to it and then add the rows to it.

But once you know how to handle a listview it's not that difficult to make any database code work that way.

Basically where their code would say Something.DataSource = MyQueried data, you'd have to go For Each Row Of Data, For Each Column Of Data etc. But everything before that Datasource = would still apply.

No, I don't know of any free programs that will create Access databases.

I found this code online, that listed all the code for how to create
a database reader. It showed how it should look and everything.
It's some kind of lesson on how to do it.

I went through all the trouble to make it and it does the same thing
my program does. If a database only has one table in it, things are
great and this program works well, but if you have more than one,
one the first table in the list of tables, will populate the datagrid
box.

I'll attach this program, if you want to look at it.

In the bin/debug folder, i have two databases included but only the
"Books" database has more than one table in it.

Are you saying that example code attached does it (it won't open properly for me so I can't run it)?

Because what you describe is like the dataset has more than one table in it, so everytime you populate the listview from DataSet.Tables(0) it looks at the first table in the dataset but (if the dataset is not cleared) the second query will have created DataSet.Tables(1), the third DataSet.Tables(2) and so on. You could check DataSet.Tables.Count after querying to see if that's the case.

But the code you've attached can't do that, so I'm not sure.

Sorry about the bad upload. I was told before that i need to delete
the object folder and everything in the bin/debug folder, except
for files that my program needed to run. Every since then, i've had
nothing but trouble trying to upload a good working version of my
projects. I think since VB express is now out, this forum needs to
change what can be included in an upload.

But anyway, i did save the link to the place where i got the code
from. You can visit that place, if you like and look at the code
provided, there.

In the mean time, i,ll check into the dataset thing.

I'm running a 64 bit version of VB net. If your running a 32 bit, that
could be the problem to.

=========================================================

Advanced Visual Basic (Visual Studio 2010) - Project 3
Database Snooper - An Access Database browsing utility

http://cislab.moorparkcollege.edu/gcampbell/advVB3-2010.htm

=========================================================

I was told before that i need to delete
the object folder and everything in the bin/debug folder, except
for files that my program needed to run. Every since then, i've had
nothing but trouble trying to upload a good working version of my
projects.
I know you had trouble with this for your TileMapper thread (http://www.xtremevbtalk.com/showthread.php?t=323653) as well,
but it really shouldn't be a problem.
It's compiled binaries (exes and dlls) that the forum is most worried about.
regular data files (.bin, .dat, access files, etc) should be fine.
Are you saying that example code attached does it (it won't open properly for me so I can't run it)?
I don't know what issues DrPunk had,
but I was able to get it to run just fine using Windows7 (64 bit),
and using VB Express Edition 2010 (see attached screenshot below).

Note: I adjust the font down to Arial Narrow, 8 points
(pathing strings can get very long with a deeply nested folder structure on a 4TB drive).

I also changed the following properties for the DataGridView1 control:
AutoSizeColumnsMode: AllCells
AutoSizeRowsMode: AllCells
..so the grid autosizes and doesn't cut off the data display (out of view).

Thanks for that bit of information hDC. I didn't know the datagridview
had an autosize property for each cell.

I was wondering what program DrPunk used to try to open my project.
I know i mentioned enough that i was using VB Express 2010 but i
probably didn't say anything about working in window 7 64 bit.

Since were on the subject of bit versions, i thought i'd share something
i just discovered but maybe you experts already knew about.

A couple of days ago, i was using VB express to try a mess with a ado
database in mdb format and it was a picture database.

When i went to run the program, i kept getting an error message, something
about microsoft oledb jet version 4 was not registered and then the program
would shut down.

I did a little research on that problem and found out that the oledb jet 4 can not
be accessed by a 64 bit program, such as VB express. You will always get that
message when you try to. The work around for that problem is that VB Express
and i imagine all the rest of the Express programs can change the setting in there
VB Express project settings to scale down to running and compiling in x86.

After i did that, i could run the program with no problem. I don't know if MS
is going to make a 64 bit version of oledb jet 4 or not but i think in 2015, they
are going to drop oledb jet 4 completely.

When using Imports, when you Import System.Data.SqlClient,
are we accessing MS SQL?

I finally figured out how to open a sql database in mdf format, so
i thought i'd go a step further and try to load all the table into a
list box.

Problem is, that i'm not sure who's SQL i'm using. Is it the
microsoft SQL?

When i use the Select statement to list all the table names, i keep getting the
same results. The listbox will populate with " System Data DataRowView".

Here is the SQL i'm using:
Dim da As New SqlDataAdapter _
("SELECT a.[Name] as FunctionName FROM sysobjects a WHERE a.xtype = 'U'", SQLConnectionString)

Type "U" is suppose to be the user defined tables but i always come up
with the same results, no matter what select statement i use.

I'm using the NorthWind database to test on.

Here is the entire code i'm using:

Private Function sqlDBConnection() As Boolean

'Create the Connection String
SQLConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & DbName & ";Initial Catalog=Overland;Integrated Security=True;User Instance=True"
'Set the connection string into the SQL connection
SQLConnection.ConnectionString = SQLConnectionString

Try

'Open up the connection
SQLConnection.Open()

sqlDBConnection = True

' --------------------------------------------
' System Data DataRowView
'That name <>'dtproperties' because it listed also some how that kind of a table and i dont want to list it...
Dim da As New SqlDataAdapter("SELECT * FROM sysobjects WHERE xtype = 'u'", SQLConnectionString)

Dim ds As New DataSet("sysobjects")
da.Fill(ds, "sysobjects")
lstTables.DataSource = ds.Tables(0)

'-------------------------------------------


Catch ex As Exception
sqlDBConnection = False

MessageBox.Show("Error Connecting to Database: " & ex.Message)
SQLConnection.Close()
End Try


SQLConnection.Close()
End Function

Yeah, System.Data.SqlClient is for Microsoft SQL Server.

System.Data.SqlClient is the .Net driver for Microsoft Sql Server, if you are using it to connect then you will be connecting to a MS Sql Server.

An alternate (and more recommended) way to get a list of tables is via the INFORMATION_SCHEMA (http://msdn.microsoft.com/en-us/library/ms186778.aspx) e.g.

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

this is preferred because INFORMATION_SCHEMA is an ANSI concept and also sysobjects is under no obligation to remain the same (or even exist between versions)

The reason you are seeing the string System.Data.DataRowView repeatedly is because the listbox is simply calling .ToString() on each item in the DataTable it is bound to, the default implementation for .ToString() under .Net simply returns the name of the class. You could set the DisplayMember (http://msdn.microsoft.com/en-us/library/system.windows.forms.listcontrol.displaymember.aspx) property of the listbox to the name of the field you would like displayed.

Yeah. I've already used that particular SQL statement already
and came up with the same results. Any sql statement that i
use, displays the same thing.

I downloaded several VB Express code sample programs yesterday,
all of them show how to add/delete field values, add/delete tables and
make changes to field values/.

I think i downloaded between 10 to 12 demo code samples and only
2 of them worked, some what. Most of the time, i can't even make a
connection, let alone change data in a database with the rest of them.

The same things happens with any other code samples i download.
They don't want to make a connect to a database.

I think the whole problem lies in the connection string. These are set
according to the programmers server needs but when they get
downloaded by someone else to use, my server needs are different
and the connection is never made. At least, this is what i'm getting
out of it.

Take a look at my connection string again:

"Data Source=.\SQLEXPRESS;AttachDbFilename=" & DbName & _
";Initial Catalog=Overland;Integrated Security=True;User Instance=True


First off, SQLEXPRESS is used as the data source, not MS SQLSERVER.
If any of my programs work at all, it usually uses SQLEXPRESS.

Secondly, look at next set of instructions. It has my database attaching itself
to what i assume, is the server database. Probably Northwind.

Although this does work and i do make a connection, if i change the database
that i want to use after running the program once before hand, the server complains
that i already have one database attached and it can't attach any more. I did find out
though that i need to detach the first one before i attach another one. I just haven't
found out how yet.

Getting back to my original problem, i guess i need to find a better way to
display tables in a listbox then. I'll work on that.

I also need to find a way to use a sql statement that doesn't require my DB
to connect to the server DB before my DB will connect.

A connection string to a sql server is made up of different parts, not all required and some cannot be used together; a quick overview of the ones you are using follows....

Data Source is used to specify the server instance you wish to connect to. This takes to form of <servername>\<instancename> where . can be used to specify the local machine and <instancename> can be omitted if you are connecting to the default instance.
Data Source=.\SQLEXPRESS is therefore trying to connect to the instance named SQLExpress (the default name SQL Express uses) on the local machine.

Initial Catalog is used to specify the name of a database on the instance you are connecting to.

Integrated Security=Trueis telling the application to connect to the SQL Server Instance using your currently logged on credentials.

AttachDbFilename Is used to specify the path to a .mdf file to attach dynamically to an instance of SQL Server rather than requiring an administrator to have previously created or attached a DB to the server.

User Instance=True Is enabling a feature known as User Instances - this will create a new instance of SQL Server for the currently logged on user, this instance will run as the current user rather than as the typical windows service account (NT AUTHORITY\NETWORK SERVICE by default for SQL Server)

If you look at the above explanations then you can see that using a User Instance and a Data Source in the same connection string makes no sense, either you are connecting to a named instance or you require a User Instance.

Similarly if you are using Initial Catalog to connect to a database there is no sense in trying to also use AttachDbFilename to attach a database.

Are you trying to connect to a specific instance of SQL Express? IS the database already attached or is it just a file on disk?

I don't want to connect to my master database at all. What
i have been trying to do all along was to connect and open
any database file that i choose, already on my hard drive.

All the databases that i try to connect to, don't have a password
or user ID. The process, should be simple and straight forward
but i guess i'm not understand how to open sql databases to well.

I'm trying to learn, through what code i have downloaded but even
microsoft code will not open any database on my hard drive.

I keep getting stuff like LOG-IN FAILED.

I need something that will tell either MSSQLSERVER or SQLEXPRESS
where to go to open my database and that's pretty much it.

I'm getting a sense that in order to do that though, i first have to log
in to my master database, before i can open the database that i really
want to open. From the code that i have been finding, this seems to be
the case.

Most of the time, i never get passed the:Example(con.open). The program
halts at that point because of log-in issues. I shouldn't have to put that
information in, unless i'm trying to change something in the master database.

I don't want to have to attach anything either, if that's possible.

The two options of AttachDbFilename and User Instance=true should allow you to do this then, you would simply pass the path to the .mdf file as the parameter for AttachDbFilename.

You are also going to need to specify some credentials for the database though, either a user name and password if the database is configured for that or use integrated security to use your current user account.

OK. I'll try that then but it doesn't make much sense. If other
people can run the code that i've been downloading from them
and it works for them, then why don't it work for me?

Also, do i need to detach it when i'm done with it and what am
i attaching it to?


On a different but similar note, I got two different versions of the
same code that i want to share with you. The only different between
the two, is that in one of them, the code connects to the master database
with no trouble at all. No userID or password is required.

The second one, the only thing that changes is that instead of connecting
to the master database, i try to connect to northwind.mdf. Here is the
first one that works:

This one connects to the master database
and it works.

Imports System.Data.SqlClient
Public Class Form1
Dim con As SqlConnection
Dim DbName As String = "C:\Northwind.mdf"
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI")
Dim cmd As New SqlCommand()
' cmd.CommandText = "PRINT('This raises an InfoMessage event')"
cmd.Connection = con
Try
con.Open()
MsgBox("Database Opened")
Catch ex As Exception
TextBox1.AppendText(ex.Message)
Finally
con.Close()
MsgBox("Database Closed")
End Try
End Sub
End Class



This one has northwind as the database to connect to,
It doesn't work.

Imports System.Data.SqlClient
Public Class Form1
Dim con As SqlConnection
Dim DbName As String = "C:\Northwind.mdf"
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=" & DbName & ";Integrated Security=SSPI")
Dim cmd As New SqlCommand()
' cmd.CommandText = "PRINT('This raises an InfoMessage event')"
cmd.Connection = con
Try
con.Open()
MsgBox("Database Opened")
Catch ex As Exception
TextBox1.AppendText(ex.Message)
Finally
con.Close()
MsgBox("Database Closed")
End Try
End Sub
End Class


This is the error i get:

Cannot open database "C:\Northwind.mdf" requested by the login.
The login failed.
Login failed for user 'MyServerName'.

To answer the problem first - 'c:\Northwind.mdf' is the physical file name and not the logical name the database is registered as with SQL - try a database name of 'Northwind' and see if that works.

Regarding If other people can run the code that i've been downloading from them and it works for them, then why don't it work for me? I am guessing that is because either their systems are setup differently or they are doing slightly different things.

Also if you are using the AttachDbFile then you are effectively dynamically attaching it for that connection, there is no permanent configuration change made and you don't need to detach the database afterwards.

Nope. Now attaching a database isn't working. Once it's attached,
it needs to be detached in order to use that database again. If not,
you get this error message:

Database 'C:\Users\MyComputerName\Desktop\Databases\NORTHWIND.mdf'
already exists. Choose a different database name.
Cannot attach the file 'C:\Northwind.mdf' as database 'Overland'.

I was just trying to open the northwind database at c:\NorthWind But the
server must hold the information about what is attached or not. This sql
statement won't work for anything unless i get the NorthWind database
at 'C:\Users\MyComputerName\Desktop\Databases\NORTHWIND.mdf''
detached first.

Secondly, i think that attach code was meant to create a database because
of the error:

Cannot create file 'C:\Northwind_log.LDF' because it already exists.
Change the file path or the file name, and retry the operation.
Could not open new database 'Overland'. CREATE DATABASE is aborted.
Cannot attach the file 'C:\Northwind.mdf' as database 'Overland'.
File activation failure. The physical file name
"C:\Users\MyComputerName\Desktop\Databases\NORTHWIND_log.LDF" may be incorrect.


I removed the database from C drive and that is when i got that error.

I think i'm just going to have to give up on using SQL databases. I was trying to learn
it because it seems the SQL is the wave of the future, but going on 3 pages now of trying
the learn how to just open a database on a regular bases, and i still haven't gotten anywhere
with it, i'm just going to stop bugging you people and quit.

I thank all that tried to help me, but apparently, i can't seem to grasp it
very well and you efforts were wasted on me.

Thank you all very much....

If the database is already in use by an instance of SQL (this also includes your own application) then the file will be locked and you will be unable to attach it to a second SQL instance. There should be no need to deliberately detach a database from a named instance of SQL.

What connection strings / code did you use to get the two errors in your previous post?

It doesn't even matter any more PlausiblyDamp. I give up on trying
to work with SQL databases.

I just can't seem to grasp the use of command strings. Not only that
but even when i find code online to use, none of it works on my laptop
either. I find that really strange. Isn't this stuff suppose to be portable
enough to use on computers that the program wasn't created on?

Sometimes there is more to programming than finding code on line and just doing a cut and paste into your own program and expecting it to work.

When you are using code found on the internet you need to make a bit of an investment in researching the objects used to decide if they are suitable and what exactly can / needs to be changed. Finding multiple similar "solutions" and randomly cutting and pasting bits from each without understand the principles or objects used will nearly always result in code that is either non-compiling or buggy if it compiles.

The various settings in a connection string are all documented for SQL on MSDN and the documentation makes it pretty clear what each setting does.

Isn't this stuff suppose to be portable
enough to use on computers that the program wasn't created on?When dealing with things like databases there is always the potential for your machine to be configured differently to the machine of the person who wrote the code you are copying, this means you will need to make adjustments for things like database names or file locations.

I give up on trying to work with SQL databases.I have a confession to make - I hate databases.
(I know they are the "bread-and-butter" of the programming world,
so I don't usually make a point of it, though.)

I love graphics programming and really don't like to get involved with the
threads asking database questions.

But really - giving up on SQL and databases in .Net after one thread? :huh: :whoops:
(or midway through your first .Net database programming thread?)

This is the second thread (after your tiles thread (http://www.xtremevbtalk.com/showthread.php?t=323653)) where you seem to have given up
before your project reached completion..
(although the tiles thread still have a few days left before it dies,
so I'm still hopeful you can finish working on some of the suggestions
given in that thread).

Anyway - I'm sensing a bad pattern forming
Do you give up on everything in life so easily?
(I know you do not from having read through all your VB6 posts)

I definitely want to give you some (looks like its needed) encouragement.

Nobody every said learning to program would be without effort.
Sometimes it can be a real grind.
But at least you don't have to totally go the trial-and-error route,
because you have experienced people here on the xvbt forum
who are here to help.

What the people on the forum can't help you with
is a "let's-just-give-up" attitude. :o

That's something you have to get past on your own
if you really want to be a programmer in .Net or any other language.

i can't seem to grasp it very well and you efforts were wasted on me.This is also a bit of "wrong" attitude.
In years to come you don't know how many other newbie .Net programmers
may come upon this thread in their internet searching.
Your "stupid" questions may provide just the answers to issues they were trying to get past.
In your "fumblings" you maybe their best teacher - did you consider that?
Wasted efforts - definitely not!

Instead of just randomly searching around on the internet,
start with the MSDN docs and work out from there:
SQL Commands and Functions (http://msdn.microsoft.com/en-us/library/44xx6c68%28v=vs.80%29.aspx)
SqlConnection Constructor (http://msdn.microsoft.com/en-us/library/9197xfyw%28v=vs.110%29.aspx)
System.Data.SqlClient Namespace
http://msdn.microsoft.com/en-us/library/system.data.sqlclient%28v=vs.110%29.aspx
Retrieving and Modifying Data in ADO.NET (http://msdn.microsoft.com/en-us/library/ms254937%28v=vs.110%29.aspx)

I know the MSDN docs can be really dry (text-heavy),
and somewhat un-graphical.

After going through dozens and dozens of pages on database
specifics, I sometimes think:
Can't the Microsoft documentors
just have one "trunk" list-of-overview-tutorials thread
that maybe references a few illustrations and diagrams?
MSDN has them they're just not always easy to find except through special Google image searching.

However, the above links are only a few of the hundreds
of MSDN SQL links I have bookmarked
(and I deal with SQL less than 1% of the time, but I know it's
important to really dig in and use MSDN fully as a resource,
in addition to asking for help on the forum).

Here's something you may learn only later in life.

One of the secrets to succeeding in life,
(as well as not getting to "brittle" as you get older)
is never stop trying to learn new things.

PlausiblyDamp:

I have gone to MSDN on several occasions. I searched through many articles
concerning SQL and proper ways to use it. I've implemented their code on my
machine and i still can't even open a connection, let alone do anything with the
database.

I noticed that my machine and three different versions of SQL Server Configuration
Manager running at the same time. I had the 2005, 2008 and 2012 versions, so i
uninstalled the first two, with the thought that maybe SQL didn't know which one
to connect to. Now they have no choice but to connect to the 2012 version, or so i
thought. I'm still having the same problem. I can even make a connection. This should
be the easy part of database programming but apparently, it's not so easy for me. Either
i'm that stupid or something is wrong with my machine, that will not allow me to make
a connection. Which one is it?

I've actually downloaded several dozens of complete SQL programs, demonstrating different
uses for using a SQL database and none of them will make a connection either.

The only thing that would get me interested in SQL again, is if someone that programs using
VB Express 2012 on a 64 bit machine, could make me a program that just makes a successful
connection to a provided database. Not northwind or adventureworks but a dummy database,
made just for testing purposes.

I've done everything that i can do, to learn SQL and i'm no further along now than i was
when i first started.

hDC_0

I haven't given up on the map editor yet, if that is what your thinking. Using a
database to store tile sheets, was my next step in it's creation. I wanted to be
able to use more than one tile sheet at a time, like RPG Game Maker does. I
didn't want to have six to ten tile sheets that i had to keep track of on my hard
drive and storing them all in one database, just seemed to make sense.

Like i just told PlausiblyDamp, i've been to MSDN several times and even used
their code in my program, to open a connection to a database and still, i'm having
trouble doing this. So, for now, i quit trying to use SQL. Obviously, either i just
can't seem to learn it or something one my machine is preventing me from make a
connection.

Right now, I'm working with ADO. I can open the database and put it's contents into
a datagridview control. By typing into the datagrid, i can add records and delete them.

Right now, i'm learning how to programically add a new table to the database. I need
to learn how to set a primary key yet to but once i learn these things, i'm going to make
an ADO Database creator and editor. I can't find one online, so i'll make my own.

Once i get really good with that, i'll go back to my map maker and apply what i just
learned to that program.

I'll take a look at the links you just provided but chances are, i've already been there.

Thanks for the pep talk though. I feel a little better about myself now.....

I thought that i would give it another go with code that
i got from MSDN.



Private Sub OpenSqlConnection()
Dim connectionString As String = GetConnectionString()
Using connection As New SqlConnection(connectionString)


Try
connection.Open()

Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try

Console.WriteLine("ServerVersion: {0}", connection.ServerVersion)
Console.WriteLine("State: {0}", connection.State)
End Using
MsgBox("Connection open")

End Sub

Private Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file, using the
' System.Configuration.ConfigurationSettings.AppSettings property
Return "Data Source=(local);Database=AdventureWorks;" _
& "Integrated Security=SSPI;"
End Function


With the Error handing, i just get a message saying that it couldn't
connect with the Adventureworks database.

I, originally got this code without error handling and i got this error:


System.Data.SqlClient.SqlException was unhandled
Class=11
ErrorCode=-2146232060
LineNumber=65536
Message=Cannot open database "AdventureWorks" requested by the login. The login failed.
Login failed for user 'MyPCName'.
Number=4060
Procedure=""
Server=(local)
Source=.Net SqlClient Data Provider
State=1


Like i said before, I'm running on a 64 bit machine. I'm running the code
in 64 bit mode with VB Express 2010. I'm assuming that the database was
created with a 64 bit machine, if that matters.

I gave my computer a name but for some reason, localhost has my pc name differently:
MyComputerName-PC\MyComputerName

As you can see, my computer name is listed twice with -PC after the first one and
a backward slash after that.

I wonder if SQL can read and understand the minus sign and the backward slash?

Do you think that i need to change my pc name or try to change the name in
SQL Server Configuration Manager to just my pc name?

Do you have Microsoft SQL Server installed on your local PC? If so is it the full SQL Server product or is it SQL Express?

When SQL was installed did you go with the defaults during the installation or did you give the instance you were installing a specific name?

If you have the SQL management tools installed can you access the AdventureWorks database via them? If so what user are you logging in to access the DB through the management tools?

I don't have any passwords at all set in the server management app.
I installed the 2012 version of ms sql server express, since it was
free. I know that when i was installing it, i tried giving it a name and
password but it was giving me so much trouble doing that, that i went
with the default install, instead

I did discover that northwind and adventureworks db's were not installed
in the sql express folder, where the master db is, so i just copied and pasted
both of them into that folder. Now, the code above is saying that log-in failed
but at least it sees the database now. I moved one inch with a mile to go yet. lol

Oh. And there is no user name in the server management app. It's a blank textbox.
I do, how-ever have what appears to be a password in the 2 password boxes but
i don't think i put in any password.

I have this program that checks my server for passwords and it is saying that
i don't have any passwords.

I'm currently running with the built in account, so i don't think it's using a password
anyway, but i could be wrong.

Copying the files isn't enough. You need to tell the database server about the databases.

In the Management Studio, in the Object Explorer, right-click the Database's folder and choose Attach. Attach allows you to add existing databases to the server. Under Datbases to Attach click the Add button. Browse to wherever the database file is you want to attach and click OK, then click OK in the Attach Database window.

That should then add the database to the server. Expand the Database's folder and you should have a database name in there. Expand that database and you should be able to see the Tables and everything else associated with that database.

Whatever that name is, that's what your initialcatalog in the connectionstring wants to be. You won't need to specify the filename of the database.

If you can connect to the database server using management studio then your program should be able to connect to it (if the log in window in management studio is set to Windows Authentication then you won't have to specify a username or password in your connectionstring).

In my server management application, there is no object browser, so
i assume that you meant the object browser, inside my VB program.

Yes, that is a choice but right now i'm concentrating on my Access
database. I having temporarily given up on sql. I'll give that one
another shot after i finish my nearly done access program.

My access project is coming along really good but i have stumbled onto
a problem that i can't figure out.

When i'm writing information into a record field, i'm writing it, directly to
a datagridview. That part works great. The problem i'm having is when
i want to save an image in a cell. The image saves alright but it saves it
in the wrong row. If i'm on the first row and save a picture, the picture gets
saved in the second row, where i haven't even put any information into it
yet.

I thought that just by selecting the cell that the picture is to be saved to, it
would automatically be saved in that cell, but it's not.

I even set the datagridview cell to be saved to:
DataGridView1.CurrentCell = DataGridView1(Col, Row)
but that isn't working either. It always saves in the row below the one i
want to save in.

Here's the code for saving the image:

Dim ms As New MemoryStream
Dim con As New OleDbConnection()
Dim cb As OleDbCommandBuilder



DataGridView1.CurrentCell = DataGridView1(Col, Row)

Try
picImage.Image.Save(ms, picImage.Image.RawFormat)

Dim arrayImage() As Byte = ms.GetBuffer
ms.Close() ' Closes the Memory Stream

Dim strQuery As String = "INSERT INTO Employees(Picture) VALUES(@Picture)"
Dim strCon As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & Db_Path & ";"

con.ConnectionString = strCon

Dim objcommand As New OleDbCommand(strQuery, con)
With objcommand
.Parameters.Add(New OleDbParameter("@Picture", SqlDbType.Image)).Value = arrayImage
End With

con.Open()

objcommand.ExecuteNonQuery()

cb = New OleDbCommandBuilder(da)
dt = New DataTable


' Fill the datatable with the data table
da.Fill(dt)

' Bind the datagrid with the datatable
DataGridView1.DataSource = dt

MessageBox.Show("Image Saved Into the DataBase", "Save Successfully", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MsgBox(ex.Message)

End Try

con.Close()


As you can see, there i nothing telling the code what cell to save the image to.
Can the query string do that?

I don't want to get off coarse here with my previous post
but i tried to do as you ask.

First of all, when you access the object browser, it has nothing
to do with databases. This is all system properties and that's it.

I did go to the database explorer though and although you can't
right click it, just like the object browser, i did open it up and
proceeded to make a new SQL connection with a SQL database,
namely "Northwind".

Once i did that, there is a button there that asks you to test your
connection. I did that and it failed to make a connection.

It's saying that it can't find the server. Is there a way to point
VB to the location of the "Master" database? The master db
is the server and for some reason, nothing will connect to it,
not even VB.

At least i now know that it wasn't anything that i did or didn't
do. This is a VB problem...

OK. It was a VB 2010 setup problem. I already had the 2005 and 2008 versions
installed before i installed 2010. Not to long ago, i uninstalled 05 and 08 because
i only wanted to work with one version only of VB.

Uninstalling wasn't the problem though. It has something to do with the framework
that everything runs from. If someone has the 08 version and then installed the 2010
version, both versions would not make a connect to a sql database, or so that is what
i read online from other with the same problem.

What this means is that you have to uninstalled everything and start fresh again, in order
to get make a connection to a sql database with VB. Just uninstalling from the control
panel isn't good enough though. Doing it that way, still leaves behind artifacts of previous
versions, all around the hard drive.

I had to go to MS and download VS2010_Uninstall-RTM.ENU. Only in english for now.
But this program get rid of every little artifact, including the 4.0 net framework.

After i did that, i reinstalled VB. After that i did want you told me to do and went to
database explorer and attached "Northwind" database to the "Master" database. Then
i went to advanced settings and at the bottom, i found the connection string to use and
bing bang boom, i was connected to "Northwind".

Just like with ADO though, you have to include the full path to the database that you
want to connect to but that's included in the SQL connection string.

It still uses the "AttachDbFilename" in the string to.

So now, this puts me one up. Hopefully i can figure out the rest of it now.

In my server management application, there is no object browser, so
i assume that you meant the object browser, inside my VB program.
No, I mean in SQL Server Management Studio.

In my version, i don't have an Object Browser but
the VB IDE will attach a database to, so i'm good to
go now.

I'd probably prefer to attach it a the server end but then,
i probably wouldn't have to put in the full path to the database
in my sql connection string.

In my version, i don't have an Object Browser..In your version of what--
VB.Net 2010 Express edition or
SQL Server 2012 Express Edition?

The VB.Net 2010 express edition does have an object browser (see attached screenshot).

Things are a little more complicated in the SQL Server 2012 Express Edition.
In older versions of the SQLServer
there used to be an Object Browser in the SQL Query Analyzer (http://www.quackit.com/pix/sql_server/tutorial/sql_query_analyzer_3.gif)
(but I'm not sure that came with one of the Express Editions).

The limitations of the SQL Server 2012 Express edition
can be found by looking at the feature charts on this page (http://msdn.microsoft.com/en-us/library/cc645993%28v=SQL.110%29.aspx#Mgmt_tools).

I believe the closest thing to the SQL Query Analyzer is now called the SQL Profiler.
If you look under the "Management Tools" section of the above limitations page
you'll see that the SQL Profiler is not found in the Express Editions.

There is however a 3rd party SQL Profiler that is designed to work with the SQL Server 2012 Express Edition
called "AnjLab SQLProfiler (http://anjlab.com/en/projects/opensource/sqlprofiler)", which has a google code page here (http://sites.google.com/site/sqlprofiler/).

Did you know there is a free set of add-ons for the Microsoft SQL Server 2012 Express edition?
It's called the "Microsoft® SQL Server® 2012 Feature Pack".
Here (http://www.microsoft.com/en-us/download/details.aspx?id=29065) is the download page.

Of course I haven't tried any of these things myself,
(I don't do that much with databases),
but I would be interested in any feedback if anyone else has worked with them..

There is also an MSDN article called "Migrating from Query Analyzer to SQL Server Management Studio (http://technet.microsoft.com/en-us/library/ms174200%28v=sql.110%29.aspx)",
that has a brief procedure designed to "configure SQL Server Management Studio as closely as possible to Query Analyzer"
(although I don't know if you will get you back the Object browser that used to be part of the Query Analyzer).

Yes. I knew about the object browser in VB Net but we were talking
about SQL Server Configuration Manager.

I think that maybe dr.punk and plausibly damp have the full version of
that program, where-as, i only have the express version. I don't think
they are quite the same program.

Since your so good with links, do you think that you can dig up a free version
of a database creator (mdf)?

I know i can make one in the VB environment but i'd rather have a stand alone
program to make them with.

Also, can anyone answer my last question about adding images in a record?

The SQl Express Management studio (http://www.microsoft.com/en-us/download/details.aspx?id=8961) previously mentioned should allow you to create and manage a SQL database with full designer support for tables, views, stored procedures, referential integrity, functions and the creation and management of indexes.

PlausiblyDamp is correct that the free SQl Express Management studio is really the designated tool for creating database MDFs.

Be aware of a number of things regarding mdf files:
1.) There is also a CD imaging file format (similar to .iso files) that ends with a ".mdf" extension --info here (http://filext.com/file-extension/MDF).
2.) The mdf file format used by the SQL Server software is not well documented,
(as regards it's internal file format structure),
and can be easily corrupted, (as noted halfway down in this StackOverFlow thread (http://stackoverflow.com/questions/1972755/how-to-edit-sql-server-primary-database-file-mdf)).
Note: There has been a suggestion on Microsoft Connect to create an "mdfx" file format (http://connect.microsoft.com/SQLServer/feedback/details/554259/create-an-xml-based-database-file-mdfx-format) that would (theoretically)
be a little more open and have an mdf-mdfx interchange tool that also might encourage Microsoft
to document the mdf file format a little better (publically).
I think it's a good idea, but it got the standard Connection suggestions not-a-priority brushoff:
Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue,
it is unlikely that we will actually complete it.
As such, we are closing this suggestion as “won’t fix”.
If you feel that this is worth reconsidering,
feel free to respond to this message and we will take another look.

3.) Here (http://soft.udm4.com/downloading/mdf_database_creator/) is a list of just a few of the software programs out there that promise to try and repair .mdf files
(which is the other reason to think that .mdf files are easily corrupted - otherwise there would be no need for these apps).

However, never let it be said that I am restricting the search for knowledge.
Here's a couple "quick/dirty" how-to articles on alternative approaches:
"Creating a SQL Server database programmatically using VB.NET (http://www.vbdotnetheaven.com/UploadFile/mahesh/CreateSQLDatabase04252005064419AM/CreateSQLDatabase.aspx)".
"How to create a SQL Server database programmatically by using ADO.NET and Visual Basic .NET (http://support.microsoft.com/kb/305079)".

Maybe I should add a few misc (but related) links:
Walkthrough: Creating a SQL Server Express Database (http://msdn.microsoft.com/en-us/library/ms233763%28v=vs.100%29.aspx)
That page has an important link to the Visual Database Tools page (http://msdn.microsoft.com/en-us/library/y5a4ezk9.aspx).
How to: Manage Local Data Files in Your Project. (http://msdn.microsoft.com/en-us/library/ms246989.aspx).
Some MSDN info (http://msdn.microsoft.com/en-us/library/aa174545%28SQL.80%29.aspx) on .mdf, .ndf and .ldf files.

Of course most of the .Net database samples use the Northwind bookstore database
(because it has been around since the earliest days of Access programming),
but I sort of wish some of the database newbies would find
(just for varieties sake) some other sample databases to work with.

Here's a link that describes working with the AdventureWorksLT Database:
How to: Connect to the AdventureWorksLT Database using an .MDF File (http://msdn.microsoft.com/en-us/library/dd469575.aspx).
More info on the database itself can be found here (http://msftdbprodsamples.codeplex.com/wikipage?title=AWLTDocs).

There are several places you can get this free database.
I think the raw database file can be found here (http://msftdbprodsamples.codeplex.com/releases/view/59211).
Other versions can be found off this page (http://msftdbprodsamples.codeplex.com/).

Finally (and I don't want to overwhelm you since I know you are
still trying to "ease" into working with SQL databases),
but have you ever heard of the "Schema View".
If not you may want to take a look at that section in this long article
(with a long title :whoops:):
"Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences (http://sqlblog.com/blogs/jamie_thomson/archive/2012/01/01/implementing-sql-server-solutions-using-visual-studio-2010-database-projects-a-compendium-of-project-experiences.aspx)"

The SQl Express Management studio (http://www.microsoft.com/en-us/download/details.aspx?id=8961) previously mentioned should allow you to create and manage a SQL database with full designer support for tables, views, stored procedures, referential integrity, functions and the creation and management of indexes.

And once again, I'm telling you that my SQL Server Management
does not make databases. I've been all through that program and
there is nothing there at all that has anything to do with making databases.
It's a very striped down version of the paid for program of SQL
Server. Mine is the free express version.

The link you provided is for the 2005 version of the same program
that i got and maybe that version does create databases, i don't know.

It's hasn't been recommended for windows 7 64 bit of which i am running on.

What version of windows are you running? 32 or 64 bit?

I only need one server on my computer, if i install the one you linked
to, then VB will be fighting with sql server to see which one it gets
to access.

hDc_0

I've been checking around about sql databases and i've found out the same
things that you just pointed out. One problem with a mdf file is that they frequently
break down. The company that i used to work for, used an mdf database. It
handled everything the company did, including the payroll. It was always
breaking down, 2 or 3 times a week. They finally got smart and turn to
using oracle. The breakdowns are a lot less frequent now. May twice
a month, if that.....

I was reading an article from a professional database programmer and that is
what he had to say about it. He did say that for businesses, the sql mdf database
is better suited for them but for low end database users, access mdb is the way
to go.

That low end database user, would be me. I only need a database for one program
and then i know it will be a long time before i mess with them again, like you.

So for now, i'm back to using access. I'm getting some much done with access
compared to sql, where i'm just barely able to open an mdf file right now.

I need to sit down and figure out what kind of stuff that i need to put in my
database, and then i need to custom make a database(mdb) creater.

That is why i was asking if anyone knew where i could find a free one.
It would cut down on my programming time.

I am not sure why you seem to be struggling with the express edition of the management studio, I have used both the 2005 and 2008 versions in the past to create and modify databases. I can't remember if I have ever used the 2005 version on 64 bit windows 7 but but the 2008 version seems to work fine.

I only need one server on my computer, if i install the one you linked
to, then VB will be fighting with sql server to see which one it gets
to access. Not entirely sure what you mean by this, if the database is running under sql then your VB app would simply connect to a SQL Express instance and access the database that way.

One problem with a mdf file is that they frequently
break down. The company that i used to work for, used an mdf database. It
handled everything the company did, including the payroll. It was always
breaking down, 2 or 3 times a week. Do you have any hard information about this as I have never worked on a SQL based system where the physical file itself became corrupted, there can certainly be data inconsistencies but that is often the fault of the database design / code rather than the underlying database engine. The fact the company also had a similar problem with Oracle makes me wonder what the underlying problem was...

Here is the version that i am using now:

SQL Server Configuration Manager
Microsoft Corporation
Version: 2011.0110.2100.060

When i clicked on the link to download it,
it said that it was the 2012 version but this
looks like 2011 version, it think.

What-ever it is, it does not create or modify
any database. It's only job is to be a server
and that is it.

I can't get much clearer than that.

Now, in VB, i can create a database and
tables but as far as i know, i can't create
records and put data in them from VB.

I looked around in there but i didn't see
anything in there about creating records
but maybe i over looked something.

This is the closest thing i have right now
to making a database, aside from making
a database creator of my own.

I'm not going to explain myself as far as
my old jobs database problems. All i know
is that they were using microsoft databases
and they kept breaking down.

That is why they went to oracle. They has
far less trouble with oracle than they did
microsofts databases.

This wasn't just one company using the database,
it was a corporation with many of it's companies,
all over america and some over-sea's, accessing
that database and it could not handle the huge flow
of information coming in, i guess.

The SQL Server configuration manager is something entirely different to the Sql Server Management Studio Express.

SQL Server configuration manager is a tool that gives access to the very basic SQL Server configuration (such as port numbers etc.) This tool ships with SQL Server Express which is a Database Engine but doesn't come with any real management tools.

The SQL Server Management Studio however is a separate download which provides management tools for SQL Express, as such it allows you to create, modify, manage, secure and query a SQL server database. Looking back over this thread I can't see a single reference to the Configuration Manager until your previous post - if you want to manage a SQL server then you need the SQL Server Management Studio, I can't think of a clearer way to suggest using the Management studio other than providing a download link to the product and using the product name.

I did say what i was using, a couple of times but i see now, we both were not
seeing what the other was posting. LOL

You were not seeing the word Configuration in my posts and i was seeing Configuration
in your posts.

I now know that what you are using is a different program from what i am using and i
will try and download it.

I went to MS to download SQL Server Management Studio but
it's only a trial version. It won't pay for me to buy it, since i won't
be using it a lot.

http://www.microsoft.com/en-us/download/details.aspx?id=7593