Adriano72

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



Re: Visual Basic Express Edition sql server Local or Remote?

Martin Xie - MSFT

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






Re: Visual Basic Express Edition sql server Local or Remote?

Adriano72

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.





Re: Visual Basic Express Edition sql server Local or Remote?

Adriano72

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





Re: Visual Basic Express Edition sql server Local or Remote?

Martin Xie - MSFT

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






Re: Visual Basic Express Edition sql server Local or Remote?

abasilis

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