i am making an inquiry kiosk for a certain company and it required to be on a network, with sql server as backend database. is there any way that i can connect to the database which is on the server and fetch data?

Recommended Answers

All 13 Replies

Yes, I lot maybe, and there are lots of tutorial out there. But first there are things you have to consider.. What sql server are you using? What is your front end programming language?

Imports System.Net.Sockets
Imports System.Text
Imports System.Data.SqlClient

Module Connect

    Public Con As SqlClient.SqlConnection
    Public constr As String
    Public usertype As String

    Public Sub main()

        '#Most common way of connecting to server #' 'A'
        constr = "Data Source=yourservername;Initial Catalog=yourdatabase;Integrated Security=True"

        '# Get connection String from app.config#' 'B'
        constr = System.Configuration.ConfigurationManager.ConnectionStrings("PropertyMonitoringSystem.My.MySettings.EISConnectionString").ConnectionString

        Con = New SqlConnection(constr) 'Choose from A or B

        Try
            Con.Open()
            MsgBox("Server Connection is Open ! ")
            Con.Close()
        Catch ex As Exception
            MsgBox("No open connection! ", vbCritical, "Database")
        End Try

    End Sub
End Module

Sample code you can start with.. Be sure to check your remote connections and set ur sql server TCP/IP enabled, and allow this in your firewall inbound rule

Hope this helps

What if only on LAN only?

hat do you mean by LAN only? the code can be used to connect from a server through lan. Just make sure u both enabled the TCP/IP

Hi
This site will give you the connection strings to required to connect to most databases, ConnectionStrings.com.

You need to connect to the database via ADO (in this case using SQL native connections) - I'm assuming as you have posted on a VB.net forum you are using VB.net as your programming language.

If you've never connected to a database before you may wish to lookup the following on Google or the MSDN site for examples:

  • SQLDataAdapter
  • SQLDataReader
  • SQL Command
  • SQL language

As Lethugs pointed out, you will need to allow/ensure that your SQL server accepts incoming TCP/IP connections (you communicate over your LAN using TCP/IP) and if your SQL Servers server has a firewall you will need to allow Traffic through the SQL Port (1433) If you let us know which version of SQL Server you are using (2005, 2008, 2012?) someone will be able to to show you where to check these settings.

hello G_Waddell and Lethugs! thanks for the reply, i am using MSSQL 2008 R2 as my database. how can i make sure that my application will connect to the server computer? thanks a lot! :)

Hi,

On your database server, run the SQL configuration manager. It will be under (depending on Operating System,) Start > Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > Configuration Manager.

When it opens up, under the SQL Server Configuration Manager (Local) expand the SQL Server Network Configuration option then the Protocols for MSSQLServer option. Ensure that the TCP/IP option and Named Pipes option are both set to Enabled

This will allow SQL Server to take incoming connections from your LAN.

Now use a SQLAdaptor or SQLDataReader with the appropriate Connection string and pull back data from it. Or maybe a SQL command and push data out to it...

Nearly forgot, to say if your database server has windows Firewall enabled you will need to open up port 1433 to allow SQL traffic through.

Here's how you add permission to your windows firewall for you sql server:

Start> control panel > Advance Settings > Inbound Rules > New Rule

Select port > Tcp > Specific Local Ports > Then enter 1433 for Sql > Allow Connection > Select Profile then name your new rule (eg. SQL Port)

You may also allow sqlserver browser.exe, this is also neccessary in connecting to your server:

Open control Panel > Allow program or features through windows firewall > Allow another program > then browse to c:\program files (x86 if exists)\microsoft sql server\90\shared then look for sqlbrowser.exe > open then add
Add the 1433 port also

then your done

so the secret is on the firewall setting and connection strings? when i was searching for tutorials, i encounter that component called winsock. will i need to learn that component?

Im not familiar with that component, I myself didn't used any 3rd party component just to connect to my server. Correct firewall and connection string is enough for me. I just don't know with your case.

Try to understand first how to set up the firewall and connection string before using 3rd paryt component for this may give you headache when installing your software to other machines

You don't need winsock for vb.net -- that is for c++ and c. Here is a great tuorial for beginners, I read through it myself and learned a great deal about vb.net. One of the chaptes covers databases, doesn't matter where the database is located. If it is not on your local computer just substitute the computer's ip address that is hosting the database for "localhost" in the tutorial. Nothing more to it than that as far as the program is concerned. Others have mentioned firewall which you will have to set up correctly so that client computers can access the database.

hello lethugs! i am using win7 OS and i went to control panel, i cant seem to find the "advanced settings" you mentioned, is it under a specific category? thanks for all the info guys! :)

i cant seem to find the "advanced settings" you mentioned,

Huh? Did you post that in the right thread? Can you be more specific about who you are talking about?

Hi lexaeterna
Were talking about windows firewall, so I hope after opening control panel, that's what you should opened first before the advanced setting. BTW sorry for not including that on my post :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.