sql server Local or Remote?
Hi, a friend of mine has asked to me to implement a software working thought the lan, with two or tree client and one server.
so I had read that with vb 2005 express works only in local mode, but just few minutes ago I had found an hint that suggest to modify some parameters in the sql server managment in order to work in TCP/IP mode waiting answers from the 1433 port.
If it's correct it was exceptional for me, there is someone could give me some tips if the information I had found was correct?
thanks.
PS: there are changes to do in the connection string? maybe to set the data source to remote server, or the code run the same setting the database source in the database connection in VB2005?
thanks
Hi Adriano,
In VB Express, connection to a remote database is not supported and you must do it in code.
Dim con As SqlConnection = New SqlConnection("Data Source=ServerName\SQLEXPRESS;Database=DatabaseName;User ID=UserName;password=password;Persist Security Info=True;Trusted_Connection=yes")
Code Snippet
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As SqlConnection = New SqlConnection("Data Source=Martin\SQLEXPRESS;Database=ShopDB.mdf;User ID=Martin;password=Shanghai!123;Persist Security Info=True;Trusted_Connection=yes")
Dim cmd As SqlCommand = New SqlCommand("select * from Goods where Codebar= " & TextBox1.Text.ToString().Trim(), con)
con.Open()
Dim sdr As SqlDataReader = cmd.ExecuteReader()
While (sdr.Read())
ListBox1.Items.Add(sdr("Item").ToString())
ListBox2.Items.Add(sdr("Codebar").ToString())
ListBox3.Items.Add(sdr("Cost").ToString())
End While
sdr.Close()
End Sub
End Class
In addition, here are various connection strings to SQL Server 2005, they are may be helpful to you.
http://www.connectionstrings.com/?carrier=sqlserver2005
Regards,
Martin
Thanks!
but it means that i have to build my db thought the sql managment program
not with the sql database wizard on vb 2005 express edition ?
it's not the same using the wizard for db connection in vb 2005 express to set the
db location using the remote server name instead of locahost\sqlexpress
I mean I could look for server on the lan, in my workgroup group of connected PCs.
when I deploy my software how can I localize my db on the newer pc where the program will be installed
I need to install the sql server express edition also in local pc, or its enought the connection string that point on the server db ( which means an IP address and a server name and a door on Wxp to open to leave data works)
I have found an article (it's in Italian http://6of9.blogspot.com/2005/11/database-server-microsoft-sql-2005.html ) that say I can use the 1433 door to comunicate with the server from my client PCs
but it's not explained very well.
It's enought instead of the hostname\sqlexpress set the data source as IP number\sqlexpress. so effectively I don't know the name of the server I have to use for register my db
It's difficult for me to understand how it works, and for conseguence how to implement the structure of my program
in function of the location of database.
and for the last (I hope) it's possible if you know to use dhcp for dinamic IP on Pc clients.
I know I'm asking too much, it probably is too big for me, but I'll try if it works.
Thanks again Martin, so much.
Ok! I've read the article you mentioning before, I've found the IPconnection string
You are right, it has been very helpfull, and also it names the 1433 address port
I think I can make the damned program to my friend.
But for now Thanks Martin, I go to create forms.
Bye
Hi Adriano,
Thank you for your quick feedback!
 Adriano72 wrote: |
| but it means that i have to build my db thought the sql managment program not with the sql database wizard on vb 2005 express edition ? | |
In VB Express, you can connect to existing databases and manage them, but you can't create new database and configure Database Security.
You can install Microsoft SQL Server Management Studio Express in the official website.
http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en
| Adriano72 wrote: |
| when I deploy my software how can I localize my db on the newer pc where the program will be installed. | |
You need to install SQL Server Management Studio on your Database Server, and create your database or attach an existing database, note that please don't detach your database.
And then, once you specify Server Name or Server IP like this, you only need to specify the database name instead of full file path.
Data Source=ServerName\SQLEXPRESS;Database=myDataBase;User ID=myUsername;password=myPassword;Persist Security Info=True;Trusted_Connection=yes
Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
| Adriano72 wrote: |
| it's possible if you know to use dhcp for dinamic IP on Pc clients. | |
Please check these references.
Sql Server 2005 and dhcp
Configure my computer to use a DHCP obtained IP address along with a static IP address
Thanks,
Martin
If I understand what are you trying to do is that you want to connect your app to a remote server, no matter wich is the name of the server, just like when you do with a ODBC.
First Create an UDL File?
http://www.devx.com/vb2themax/Tip/18590
After you create the udl file, open it, in the first TAB "Provider" Select "SQL Native client", in the second TAB "Connection" STEP 1 write SERVERNAME\SQLEXPRESS or .\SQLEXPRESS if the database is local. STEP 2 "Use Windows NT Integrate Security" or enter user SA and Password with "Specify Username and Password Option" (and Click Allow Saving Password) The Problem of this is that the UDL is a text file, so if anybody opens it will see the password for sa User.
And STEP 3 Select your database from the list or Write it down and Press "Test Connection Button"
There is a code here to test the connection with a UDL File.
Imports System.Data.OleDb
Public Class Form1
Private Sub btnconn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnconn.Click
Dim cn As New OleDbConnection()
cn.ConnectionString = "File Name=C:\Temp\test.udl"
cn.Open()
If
cn.State = ConnectionState.Open Then
MessageBox.Show(
"Connection opened successfully")Else
MessageBox.Show(
"Connection could not be established")End
If
cn.Close()
cn =
Nothing
End
Sub
End Class
