SQL Server Xpress 2008

[RobKuwait]

Not sure where (i.e. which forum) to start talking about this. I'm a complete beginner with SQL Server although I have five years of MS Access behind me. Got as far as downloading SQL Server Xpress and vb.net, but not much further. Would welcome a link to a tutorial or something that will get me started, building a school database and front end. Any and all help appreciated.

 

Cheers

Rob

Edited February 2, 2009 by RobKuwait

[SYNACK]

Take a look as SQL Managment Studio Express as this will let you interface with your 2008 server:

This video set should be really helpful:

SQL Server Videos : The Official Microsoft ASP.NET Site

 

This video is good:

ASP.Net: Using SQL Server Management Studio :: Tech Videos, Screencasts, Webinars, Techtalks, Tutorials

 

These may help

SQL Server Management Studio Express tutorial - ASP.NET Forums

Contact Manager - Creating the SQL Database - Tutorial Centre

 

These vids are good from the software side:

Videos : The Official Microsoft ASP.NET Site

 

And as a starter you could simply use Access itself to build the databases by connecting a new Access ADP project to your SQL server.

[RobKuwait]

I'm doing something wrong...

 

I've managed to create a simple database using SQL Server Management Studio (Express). I can create tables and populate them using SQL Server Management Studio. Now I want to start working with my db using VB.NET Express. Problem is, whenever I try to create a new project in VB, I can't connect to my database. I open the Data Sources box, navigate to the .mdf file, and that's as far as I get. Can't see where I'm going wrong.

[ahuxham]

I'm doing something wrong...

 

I've managed to create a simple database using SQL Server Management Studio (Express). I can create tables and populate them using SQL Server Management Studio. Now I want to start working with my db using VB.NET Express. Problem is, whenever I try to create a new project in VB, I can't connect to my database. I open the Data Sources box, navigate to the .mdf file, and that's as far as I get. Can't see where I'm going wrong.

 

Should actually connect to the database over TCP/IP rather than reading from a SQL database file.

 

Using DataReaders and SQL Server in VB .NET

[RobKuwait]

Sorry I'm not getting very far. I think I need to go back a few steps.

 

I don't have a mental picture of this 'connection' bit. I am a complete beginner with SQL Server and VB.NET. All my experience is with MS Access, and this is how I see things (don't laugh):

 

An Access db exists as a mdb file, within which are the tables and the queries that link them and make them do clever stuff, as well as all the fancy forms and reports that the user interacts with. I guess the mdb file is analogous to the server, and each instance of Access used to gain access to it is a client.

 

In moving from Access to SQL Server, I guess what I'm doing is I'm setting up a server with a mdf file on it. I've created a basic mdf (using SQL Server Management Studio) with a single table, just to practise, and I'd like to create a simple VB application to display data from that table, allow me to edit, enter new data, query the data etc. At the moment the stumbling block is getting my VB.NET project to talk to my mdf file. I must confess to be a bit in the dark here.

 

I think I need to create a connection string, in which I identify the server (which is presumably somewhere on my C drive) and the database (likewise), and provide user and password details. Trouble is, although I can find examples of connection strings online, I've no idea which to use or where to put it.

 

Does the SQL server connection string do the same job as the MS Access shortcut I'm used to, which points to 3 things: the MSAccess.exe file, the mdb file itself, and the workgroup security file?

 

Could some kind person walk me through the first few steps? Assume no prior knowledge. Thanks.

[irsprint84]

go to asp.net and look at sql tutorials. Using sql in asp.net is 5x easier than with vb.net forms

[s123]

The thing with SQL server is that you connect to the server first, then tell it which database you want to access rather than access where you just open the database file and start playing with it.

 

A good website for showing what it looks like is here:

 

ConnectionStrings.com - Forgot that connection string? Get it here!

 

This wil tell you what the connection strings look like for each version of SQL server, this page is for 2008:

 

SQL Server 2008 Connection String Samples - ConnectionStrings.com

 

SQL server is quite a bit more complicated than Access as it allows you to set proper security on the database etc, also are you trying to access the database remotely? If so you will need to enable the TCP/IP protocol in the SQL server configuration manager before you are able to connect to the SQL server.

 

Hope this helps a bit, quite hard explaining SQL server in a single post :-) Let me know if there's more info you need.

[ahuxham]

The thing with enterprise grade databases, is they lose the flat file effect.

 

You have to make connect to a database, unlike MS Access, SQL and Mysql run local services on specific ports, you connect to the database and read the information, this means the whole database isn't locked as the file isn't always read, it also allows you to access it multiple times, more than one user can connect to the database.

 

You than structure your queries to match what you want todo, same as MS Access for example

 

SELECT Year, StudentName FROM database

 

This would display all students and their respective years, obviously SQL is more powerful, and there are amplitudes of different instructions you can read from the database.

 

An example of a flat database like MS Access would be Oracle Berkeley DB

 

Here are some links to get a grips with the process:

 

SQL Exercises

[RobKuwait]

Thanks, it is beginning to take shape now. I am off the ground. I can connect to my local test db and make datagrids appear in forms. Yippee!

 

Now to import some real data from my old Access db and start playing around with that. I daresay I'll be back soon.

 

Thanks so far, gents.

[ahuxham]

Thanks, it is beginning to take shape now. I am off the ground. I can connect to my local test db and make datagrids appear in forms. Yippee!

 

Now to import some real data from my old Access db and start playing around with that. I daresay I'll be back soon.

 

Thanks so far, gents.

 

Good luck!

[RobKuwait]

I've got as far as creating a VB project and linking to my database. I can create new queries much the same as Access, using the diagram view and the SQL pane. I can execute the SQL and the results display in another pane. So far so good.

 

In Access I used to be able to save queries. Conceptually they were like tables and could be treated as such in other queries. Any way to do the same or similar?

 

Also I used to use a lot of calculations, to produce exam percentages and the like. They were all in the Access queries. I guess they will have to go somewhere in my VB application.

 

As you can see I've got a lot to learn. I want to end up with queries and calculations, plus a front end to make it easy for technophobic users. Lots of small steps are the best way. Where should I go from here?

[ahuxham]

I've got as far as creating a VB project and linking to my database. I can create new queries much the same as Access, using the diagram view and the SQL pane. I can execute the SQL and the results display in another pane. So far so good.

 

In Access I used to be able to save queries. Conceptually they were like tables and could be treated as such in other queries. Any way to do the same or similar?

 

Also I used to use a lot of calculations, to produce exam percentages and the like. They were all in the Access queries. I guess they will have to go somewhere in my VB application.

 

As you can see I've got a lot to learn. I want to end up with queries and calculations, plus a front end to make it easy for technophobic users. Lots of small steps are the best way. Where should I go from here?

 

Learning the math functions within VB, calculations from variable data. You set the calculation source, calculate and print the data. No small feat at all, you'll get a grasp of it all soon enough, just going to take a while to get everything in place.

 

Another step, would be searching for similar projects of sourceforge and the likes and checking code examples. I'm sure certain functions will show in other applications (*Calculating data percentages etc from a source with variable data)

 

Looking at some examples, a function like:

 

dim input1,input2 as integer
input1=sourcefield1.source 
input2=sourcefield2.source
sourcefield3.source=(input1*input2)/100

 

and the such, calculate the data in the first sourcefield, and the second, and put the data in the third, obviously you have to name the sourcefields, more than likely columna.text or however your data is going to be shown.

Edited July 27, 2009 by ahuxham

[AngryTechnician]

In Access I used to be able to save queries. Conceptually they were like tables and could be treated as such in other queries. Any way to do the same or similar?

 

Also I used to use a lot of calculations, to produce exam percentages and the like. They were all in the Access queries. I guess they will have to go somewhere in my VB application.

I think Views and Stored Procedures might be what you want to look into here.