VS 2015 [RESOLVED] How to put SQL database on a server and how to access it?-VBForums
Results 1 to 11 of 11

Thread: [RESOLVED] How to put SQL database on a server and how to access it?

  1. #1

    Thread Starter
    Addicted Member NinjaNic's Avatar
    Join Date
    Dec 2013
    Location
    Earth
    Posts
    228

    Resolved [RESOLVED] How to put SQL database on a server and how to access it?

    Hi, I have many questions regarding SQL databases. I'm very new to this subject and I need to know how to upload (or create) a database on a server and where I can find the connection string. First of all, a database on a server can be accessed with any program with the correct connection string, right? Also, does it update in real-time? (I'm thinking of using Azure but I don't know the quality of the free trial.) And this is some example code I made to manage tables in a local database. Is there anything wrong with it or anything I may need to add? Like I said, I'm new to this and don't really know where to begin. Thanks in advance!
    ~Nic

    vb Code:
    1. Imports System.Data.SqlClient
    2. Public Class Form1
    3.  
    4.     Dim connString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""MY_LOCAL_FILE_PATH\Database1.mdf"";Integrated Security=True"
    5.     Dim myConnection As SqlConnection = New SqlConnection(connString)
    6.     Dim dr As SqlDataReader
    7.  
    8.     Private Sub CreateTable(TName As String)
    9.         myConnection.Open()
    10.         Dim command As String = $"
    11. IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='{TName}' AND xtype='U')
    12.    CREATE TABLE [dbo].[{TName}]
    13.    (
    14.         intRecID Int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    15.         FirstName VARCHAR(50) NOT NULL,
    16.         LastName VARCHAR(50) NOT NULL,
    17.         Email VARCHAR(50) NULL,
    18.         Create_Date Smalldatetime DEFAULT GETDATE()
    19.    )"
    20.         Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
    21.         cmd.ExecuteNonQuery()
    22.         myConnection.Close()
    23.     End Sub
    24.  
    25.     Private Sub AddEntry(TName As String, FName As String, LName As String, Email As String)
    26.         myConnection.Open()
    27.         Dim command As String = $"INSERT INTO {TName} (FirstName, LastName, Email) VALUES ('{FName}', '{LName}', '{Email}')"
    28.         Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
    29.         cmd.ExecuteNonQuery()
    30.         myConnection.Close()
    31.     End Sub
    32.  
    33.     Private Function GetEntry(TName As String, Column As String, ColumnValue As String, ColumnNeeded As String) As String
    34.         myConnection.Open()
    35.         Dim command As String = $"SELECT {ColumnNeeded} FROM {TName} WHERE ({Column} = '{ColumnValue}')"
    36.         Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
    37.         dr = cmd.ExecuteReader
    38.         Dim str As String = ""
    39.         While dr.Read
    40.             str += dr(0).ToString
    41.         End While
    42.         myConnection.Close()
    43.         Return str
    44.     End Function
    45.  
    46.     Private Sub ClearTable(TName As String)
    47.         myConnection.Open()
    48.         Dim command As String = $"DELETE FROM {TName}"
    49.         Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
    50.         cmd.ExecuteNonQuery()
    51.         myConnection.Close()
    52.     End Sub
    53.  
    54.     Private Sub DeleteTable(TName As String)
    55.         myConnection.Open()
    56.         Dim command As String = $"DROP TABLE {TName}"
    57.         Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
    58.         cmd.ExecuteNonQuery()
    59.         myConnection.Close()
    60.     End Sub
    61.  
    62.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    63.  
    64.         CreateTable("T1")
    65.         ClearTable("T1")
    66.         AddEntry("T1", "Dave", "Johnson", "d.johnson@gmail.com")
    67.  
    68.         MsgBox(GetEntry("T1", "FirstName", "Dave", "Email")) ' This retrieves Dave's email address.
    69.  
    70.     End Sub
    71.  
    72. End Class

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,063

    Re: How to put SQL database on a server and how to access it?

    You install SQL Server on a Windows machine in the same way you do any other software. If you don't want to pay for a SQL Server license then you can get SQL Server Express for free. It has some limitations but is still very powerful.

    To connect to a SQL Server database, you create a SqlConnection object with an appropriate connection string and call Open. The connection string specifies where the SQL Server instance is, which database to open, which user to connect as and various other things. You can find more information about connection strings at www.connectionstrings.com.

    The connection string in your current code is specifying that it should connection to a LocalDB instance. LocalDB is a stripped-down database engine intended for development use. When you deploy, you would change that connection string to specify the instance the end user needs to connect to. That's why connection strings are often stored in a config file rather than being hard-coded. That allows the freedom to change connection details after deployment.

    Your connection string is also specifying that, rather than being permanently attached to a SQL Server instance, your database is contained in a local MDF data file that must be attached on demand. That is OK for a single-user application but if you need multiple clients to connect to the same database then you need to attach a database to the instance. That way everyone can always access it, rather than attaching their own database each time they run. There are a few different ways to create such a database and many of them are done via SQL Server Management Studio. You would then use the Initial Catalog attribute in your connection string rather than AttachDbFilename.

    Yes, just like any other RDBMS, changes made by an application to a SQL Server database will be immediately visible to any other clients.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,419

    Re: How to put SQL database on a server and how to access it?

    Quote Originally Posted by NinjaNic View Post
    First of all, a database on a server can be accessed with any program with the correct connection string, right?
    If you mean a server that is within the same network then yes, but if you mean from another network (such as over the internet) then it is such a massive security risk (not just for the database) that most hosting providers wont allow it.

    If it is a different network, the best option is probably to create a web service on the server... your program then connects to the web service (using specific parameters, rather than generating SQL as you do above), which is what actually works with the database and returns data etc as appropriate.

  4. #4

    Thread Starter
    Addicted Member NinjaNic's Avatar
    Join Date
    Dec 2013
    Location
    Earth
    Posts
    228

    Re: How to put SQL database on a server and how to access it?

    Thank you very much to both of you for the detailed answers!

    So, how does SQL Server Express work, and what is the difference from SQL Server Management Studio?
    Only people in my network can access the databases, and my computer/the server must be on and running, correct?
    Last edited by NinjaNic; Nov 17th, 2016 at 08:58 PM.

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,415

    Re: How to put SQL database on a server and how to access it?

    So, how does SQL Server Express work, and what is the difference from SQL Server Management Studio?
    Only people in my network can access the databases, and my computer/the server must be on and running, correct?
    That's a very broad question, it's like asking how does Visual Basic work and what's the difference between VB 2008 Express and VB 2015 pro.

    And yes your machine and the server must be running. That would be a neat trick if they didn't.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,063

    Re: How to put SQL database on a server and how to access it?

    Quote Originally Posted by NinjaNic View Post
    So, how does SQL Server Express work, and what is the difference from SQL Server Management Studio?
    SQL Server is the broad name for a range of Microsoft database technologies. At the core is the SQL Server RDBMS. SQL Server Express is a cut-down edition of that. It provides most of the features that most small applications require and at no cost. If your needs are greater, e.g. replication, then you need to go for one of the paid-for editions. Management Studio is an application for managing SQL Server databases and the like. SSMS can connect to an instance of SQL Server or SQL Server Express and let you add, edit or remove databases attached to it. There is also an Express edition of SSMS, which can connect to any SQL Server instance but, again, provides fewer features.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    Re: How to put SQL database on a server and how to access it?

    With the new edition of SQL Server (2016) SSMS can now be downloaded as a separate installation and installed with out needing an SQL Server license. It is updated on a different schedule than the core database components also (about every month or 2)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  8. #8
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,204

    Re: How to put SQL database on a server and how to access it?

    if you install mssql (and express), you need to make specific settings after installation to be able to connect to the instance over the network.

    your code is in its basic correct but you should use parameterized queries instead of these interpolated strings. most likely you wont create the table from code but deploy a prepared empty database. and you should certainly not use this "GetEntry" of your code to get the values you want.
    to read data you should also look into dataadapters that will deliver a normal DataTable.
    If you use a DataReader like in your GetEntry, you must check if it .HasRows before you do a .Read.
    If you plan a multiuser environment you need Transactions and handle exceptions.

  9. #9

    Thread Starter
    Addicted Member NinjaNic's Avatar
    Join Date
    Dec 2013
    Location
    Earth
    Posts
    228

    Re: How to put SQL database on a server and how to access it?

    Thanks all. Do I need to have SSMS if I have SQL Server Express? (As opposed to not having anything.)

    to read data you should also look into dataadapters that will deliver a normal DataTable.
    If you use a DataReader like in your GetEntry, you must check if it .HasRows before you do a .Read.
    If you plan a multiuser environment you need Transactions and handle exceptions.
    Thank you, but can you give me some examples of this, or at least point me toward the right direction?
    Thanks again,
    ~Nic

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,063

    Re: How to put SQL database on a server and how to access it?

    Quote Originally Posted by NinjaNic View Post
    Thanks all. Do I need to have SSMS if I have SQL Server Express? (As opposed to not having anything.)
    You need SSMS if you want a GUI to manage your database(s). For instance, if you want to restore a database backup or execute SQL scripts to create your initial database then doing so in SSMS is an easy way to do that. If you don't want your users to have to do that as part of your deployment though, you can use SQLCMD instead, which is the SQL Server commandline utility. You can then prepare a batch file to setup the database and just have the user run that or even have it run automatically by your installer.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  11. #11

    Thread Starter
    Addicted Member NinjaNic's Avatar
    Join Date
    Dec 2013
    Location
    Earth
    Posts
    228

    Re: How to put SQL database on a server and how to access it?

    If you don't want your users to have to do that as part of your deployment though, you can use SQLCMD instead
    Hi, and thanks for that information! I've been trying to do that now. The server works on my own computer, and I can connect to the database, but my other computer in the same wifi will not connect to it. I've tried messing around with the connection strings to no avail.

    Right now I have:
    Server=Toshiba-Nic\SQLEXPRESS;Database=NameAndEmail;Trusted_Connection=True;

    And I'm getting error 26 (error locating server/instance).

    This looks right, right? What's going on?
    Thanks!
    ~Nic

    EDIT:

    I realized that I needed to allow a ton of stuff through the firewall. I found a detailed tutorial here. Manual link:
    www.sqlshack.com/how-to-connect-to-a-remote-sql-server

    It works now! Thanks, everyone who helped.
    Last edited by NinjaNic; Nov 20th, 2016 at 02:52 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.