Client / Server

[DAWNIE]

Hi, I'm currently having problem with my proj. And really need helps..

I am currently in a proj that need to create an application that is able to hav Client (PC 1)'s data store to Server (PC 2 and it is also where the database is being stored.)

PC 1's Code:

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim connAdd As New SqlConnection _
("data source=ifcdu1-ws23\sqlexpress; initial catalog=PatientInfo;" & _
"integrated security=true")
Dim mySQL As String
connAdd.Open()
mySQL = "insert into pInfo (patientName, address, contactNo, gender, condition) values ('" & Trim(txtPName.Text) & "','" & Trim(txtAddress.Text) & "','" & Trim(txtContact.Text) & "','" & Trim(cbGender.Text) & "','" & Trim(txtCondition.Text) & "' ) "

Dim cmdAdd As New SqlCommand(mySQL, connAdd)
cmdAdd.ExecuteNonQuery()
connAdd.Close()

End Sub
End Class

* Application is using VB, Database is using MS SQL Server.
FYI: changing datasource to PC 1's name and by transfering the database to PC 1, it is able to do a peer-to peer connection.

What should I do to Server PC 2 or Client PC 1? Any configuration or do I hav to change any coding to make it works?

thank lots (:

[Teme64]

What is the exact error message? And at what point it comes.

You may try this

Try
  connAdd.Open()
Catch ex As Exception
    Debug.WriteLine(ex.Message)
End Try
.
.
.
Try
  cmdAdd.ExecuteNonQuery()
Catch ex As Exception
    Debug.WriteLine(ex.Message)
End Try

to get error message(s) and post them here.

Since you're dealing with two computers it may be in the connection. By this I mean firewall blocking the connection or misconfigured SQL Server. Can you also tell which SQL Server version and OS version you're using. And you do have a (software) firewall between those computers?

[DAWNIE]

There isnt any error message. The application does work but whatever I have type in my form (which the data suppose to be store in PC 2.), but the data did not store into PC 2's database. but if the database and application is within 1 PC, the data will be able to save.

SQL Server version: MS SQL Server 2005; OS version: Microsoft Windows Xp Profeeional Version 2002 SP3; MS VB version: MS Visual Studio 2005.

How do I know whether is there any firewall blockage or I had misconfigured?

[Teme64]

So

Try
  cmdAdd.ExecuteNonQuery()
Catch ex As Exception
    Debug.WriteLine(ex.Message)
End Try

does not give any error? If you open the connection

Try
  connAdd.Open()
Catch ex As Exception
    Debug.WriteLine(ex.Message)
End Try

and check the connection object's properties, they are ok? Have stepped through the code or used breakpoints to make sure that the code is actually executed?

If you have done all of the above and you're sure there's nothing wrong (like connection string) then this goes a bit OT from VB.NET.

I attached a document I wrote for a customer (SQL Server 2005 and Win XP firewall), how to configure and/or check necessary settings. Notice: it's written in Finnish, so try to follow the pictures (I can translate some parts if needed).

[DAWNIE]

I got the error msg stated: "Unhandled exception has occurred in your application............... A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

The SQL connection that u attached is only to be configure on the server? btw I have wrote some query that i dont understand in the MS Words doc and they are in red.

[Teme64]

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

You can't access to the computer where the SQL Server resides or there's some misconfiguration (you do have checked that you're accessing right SQL Server instance, haven't you?).

I translated the document to English (which is even worse English I write here in the forum), added answers to your comments and questions and added a troubleshooting chapter I had in the original document.

The SQL connection that u attached is only to be configure on the server?

Yes, you have to make the changes to the configuration(s) in the computer where the SQL Server instance resides. And that's why your code works locally, it doesn't use network connection or/and doesn't get blocked by firewall.

HTH

[DAWNIE]

I have already read the translated document and have configured on the Server's PC with what I have understand but I stil having some queries. I have attached my queries below. Hopefully can get your help. Thks

[Teme64]

I tried my best to answer the questions. See the attachment.

At this point the next question is, do you “see” the remote PC? I mean, does ping <server IP-address> or ping -a <server’s name> work?

[DAWNIE]

ping <server IP-address> or ping -a <server’s name> work does not work. It show request timed out. Packet sent = 100% loss

The IP address in the server (command prompt) have the different IP address as in the TCP/IP Properties. So do i still leave both as it is or do I need to change any of it??

I have attached some printscreen in the file ipquery.

[Teme64]

do i still leave both as it is or do I need to change any of it??

They seem to be ok.

I still think it's the firewall blocking the traffic. See the attachment again.

[DAWNIE]

now it can ping to each other with firewall off. what should i debug or do next?

I'm on Major Project for Sch =)

Attachment below

[Teme64]

First, the Server PC's firewall may still block the connection. I previously posted how to add an exception to Win XP firewall. Whatever firewall you have, you have to make sure it allows traffic through TCP port 1433 i.e. add an exception to firewall rules if needed. You could, however, keep the firewall off until you get the connection to work.

Second,

"ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."

message means that you have to check the connection string.

Dim connAdd As New SqlConnection _
("data source=ifcdu1-ws23\sqlexpress; initial catalog=PatientInfo;" & _
"integrated security=true")

Put the Server PC's IP address as the data source and keep the firewall still off. Does it help?

Also, use

Try
   connAdd.Open
Catch ex As Exception
   MessageBox.Show(ex.Message, "Error Open", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

to trap connection open errors, and

Try
  cmdAdd.ExecuteNonQuery()
Catch ex As Exception
   MessageBox.Show(ex.Message, "Error Query", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

to trap errors from cmdAdd.ExecuteNonQuery()

[DAWNIE]

I get this prompt (attachment below) but don't know what had happen to it. Need help.

Thks

[Teme64]

Sorry for taking so long to answer.

The error message comes from connAdd.Open() and the latter one because you didn't manage to establish a connection to the database.

I may have already sent this link: Connection strings for SQL Server 2005. See if you find any help from there.

One thing to try (although usually not recommended) is to use Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False; syntax in the connection string instead of the trusted connection. See if that works. If (and when) you get that to work, switch back to trusted connection and make sure that your application (the account it runs on) can make a login to your server PC (i.e. Windows login).

[DAWNIE]

it's ok for the late reply =)

For the SQL Server, I'm running on Window Authentication and it is unable to have userID and password. it jus can connect which is default userID with no password.

attached are the errors and some are repeated errors. I dont what went wrong or what should I change.

[DAWNIE]

I have this problem when I try to test connection after configurating of enable remote connection.

below is the attachment of the printscreen =)

[DAWNIE]

hi, I have manage to solve the problem. it is due to administrator p/w. I manage to get administrator p/w n run the sql under administrator so it works. Thanks for helping.

Best Regards,
Dawn.Lee

[Teme64]

Great!!!

This is not just solving problems. This is as much learning myself something new ;)

[DAWNIE]

Is a learning for both of us and the rest =)

I also learn alot from you and really thks alot for your help. Will need more help nxt time cos connection is jus the starting.

Thanks
Best Regards,
Dawn.Lee