[RESOLVED] connecting to a SQL server database

[steve_rm]

Hello,
Using VS 2005 & SQL 2005

This is my first time in programming a PDA, i want to connect to a SQL server. both are on my computer.

The code below works fine for a windows application, but when I try and implement it on a PDA application it comes up with "SQL Server does not exist or access denied" This code does work as it has been tested in a windows form.

Is it best to connect to a XML Web Service and return the ds? So the code below will be in the web service. I am not sure about this as this my first time.

Can anyone offer any direction?

Private Sub btnIncidents_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIncidents.Click
        Dim cmd As New SqlCommand()
        Dim cnn As New SqlConnection()
        Dim da As New SqlDataAdapter()
        Dim ds As New DataSet

        Try
            cnn.ConnectionString = "server=(local); database=serviceMaster; integrated security=true"
            'server=steve01\ssd01; database=serviceMaster; integrated security=true
            cnn.Open()

            cmd.Connection = cnn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT IncidentID, company, contact, email, subject FROM Incidents"

            da.SelectCommand = cmd
            da.Fill(ds)

            grdIncidents.DataSource = ds.Tables(0).DefaultView

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

Thanks in advance,

Steve

[Strider]

hey steve...

ok im a little confused... are you trying to connect to sql server from the mobile device directly or are you using a webservice to connect to sql server get the data and return it too the device...

if you want to connect directly form the mobile device your connection string will need to contain the server name/ip

Dim l_connString As String
l_connString = "Server=10.1.2.180;database=northwind;user id=sa;password=;"

[steve_rm]

Hello Strider,

I am trying to connect directly to the sql server from the PDA application. This is using the pocket PC 2003 SE emulator.

I haven't implemented a Web Service for this, just wondering if that would be best as it will be less overhead (I think).

The connection string for connecting to the sql server. I am not sure about this but does it not need the ip and port number. e.g. 168.56.43.6:1433

Thanks for any advice you can give me,

Steve

[Strider]

depending on your needs you may need to specify the port, for example having mutiple instance of msde on one machine or else having certain ports firewalled.

connecting directly to sql server the device will need to have a constant connection to the network such a WIFI or VPN/VLAN over GPRS and all the securities associated with that. You will also have to be careful with user rights and permission in sql server

using webservices u can log all activity to file/event manager. Again you will need to have access to the internet for the webservices, but you have to worry less about firewalls as webservices run over http.
For security you can use SSL on the webservice and even pass username & password to send to the webservices for additional security..

You may also wanna have a look at RDA(Remote Data Access) And Merge Replication especially if dealing with large amounts of data and working disconnected from the network

[steve_rm]

Hello Strider,

Thanks for the advice.

Seems strange that I am connecting to the local host. There should be a connection.

Do you know any good tutorials for remote data access and merge replication. I would like to look into this further.

Thanks,

Steve

[Strider]

"Seems strange that I am connecting to the local host. There should be a connection."
- The device has no idea what localhost is hence you need the IP Address/HostName

SQL Server CE 2.0 Merge Replication Using .NET Compact Framework
http://msdn.microsoft.com/SQL/defau...ication_net.asp
http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/

SQL Server CE Remote Data Access (RDA)
http://www.sqljunkies.com/tutorial/...3cd598cf46.scuk
http://www.ondotnet.com/pub/a/dotne...e20.html?page=1

[techgnome]

"steve01\ssd01" is not localhost.... it's your PC.... and the PDA isn't going to know anything about how to connect to that. So now you're probably thinking, "but I am running it on the PC, when I run it through the emulator." When you run it through the emulator, it acts like it's on a PDA. So, it isolates it from the rest of the PC. As a result, the SQL Server doesn't exist to it. In addition to the links above, look up SQL Everywhere - it's a mobile, compact version of SQL Server designed for mobile products.

-tg

[steve_rm]

Hello,
Hope you can help one last time, just want to show you what i have done.

I have tried many things to make this work. I want to populate a datagrid on my pocket PC from a sql server database. Using VS 2005 and SQL Server 2005.

I have tried making a direct connection using from the pocket pc emulator SE 2003 to sql server. My code below.

Dim cmd As New SqlCommand()
        Dim cnn As New SqlConnection()
        Dim da As New SqlDataAdapter()
        Dim ds As New DataSet

        Try
            cnn.ConnectionString = "data source=192.168.10.1\ssd1; initial catalog=serviceMaster; user id=sa; pwd=a123"

            cnn.Open() ' Errror Message hereSystem.Data.SqlClient.SqlException = {"Specified SQL server not found: 192.168.10.1\ssd1"}

            cmd.Connection = cnn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT IncidentID, company, contact, email, subject FROM Incident"

            da.SelectCommand = cmd
            da.Fill(ds)
            grdIncidents.datasource = ds.tables(0).defaultview

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

This above code was fully tested on a window form and populated the grid without errors.

Next I decided to connect to a web service my code below.

 Dim WS_Incidents As New localhost.Service()
        Dim ds As New DataSet

        Try
            ds = WS_Incidents.IncidentData() 'Error message "could not establish a connection to the network"
            Me.DataGrid1.DataSource = ds.Tables(0).DefaultView

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

The above web service has also be tested by populating a grid on a web form. worked with any error.

All this is being done local on my computer, and using the emulator for the pocket PC SE 2003.

I would rather prefere the second option of connection through a web service as it uses http and can go through firewalls. I would be grateful for any help in this situation as this is urgent for me to solve this problem.

Thanks for any code examples in advance,

Steve

[Strider]

What is the url of the webservice. Again make sure you dont specify it as localhost

open internet explorer on the emulator and try to browse the Web Service URL
http:\\<MACHINE_IP>\<PROJECTNAME>\<WEBSERVICENAME>.asmx

[steve_rm]

Hello Strider,

Thanks for your patients.

the URL of the webservice is this: http://localhost:2080/WS/Service.asmx

That is clicking on the web reference and the properties of the web reference URL

I have tried putting this into the pocket PC, but running it and selecting internet explorer. and putting this into it: http://192.168.10.1/steve01/WS/Service.asmx

It can up with a message "Unable to connect: You have no modern entries created, and no network card present.

With the code I have done above, can you see anything else I am doing wrong. Must be something simple.

Thanks for your patients, this is my first time.

Steve

[Strider]

If you cant browse it on Internet Explorer on the device then it wont work on the app you are developing...
Seems there is configuration issue...

Now i havent used VS2005 i only have VS2003, and i mainly use Windows CE (emulator & device) dont think i ever used Pocket PC emulator only device...

Maybe check the IP Address of the Emulator i think i must be on the same subnet as your PC something like 192.168.XXX.XXX....

Can you ping on the Pocketpc emulator? maybe try pinging your pc.
Generally you should be able to get to the internet using IE on pocketpc

[techgnome]

Unless the emulator can emulate Bluietooth or WiFi somehow, it just ain't gonna work.The Emulator is essentialy unaware of the network (correctly so). So, some how it needs to be made aware.... either simulating WiFI or BT.

-tg

[steve_rm]

Hello,

Thanks again. I must be doing something wrong, and I am not sure what I even doing now.

I did try and go to www.google.com on the pocket pc. but that came up with the same error message as above.

Any ideas, I just want to get this working.

Steve

[Strider]

The emulator can connect to a network, well i've had no problems in VS2003 for 3 years now... unless they got rid of it in VS2005 which is hardly unlikely...


But anyway i recommend when you create ur solution do it for windows ce.net device as this allows your to deploy to windows ce & pocket pc.....

windows ce.net emulator has more functionality than the pocket pc does, such as ping and ipconfig

there must be some config or even security issue goin on but you should be able access the net

[steve_rm]

Thanks for your patients Strider,

In VS 2005, I went to new project, selected smart device, pocket PC 2003, and selected the device application project. there is windows CE 5.0. I am so new at this, I am not sure what the difference is.

I think you could be right about configuration, but not sure how to change this.

Thanks,

Steve

[Strider]

sure give windows ce 5.0 a go and see if you can do what you are trying to do...

Windows CE is just another version of the OS for mobile devices... Windows CE has a more windows feel about it instead of the "pretty" interface for windows mobile.

[steve_rm]

Hello Strider,

I have tried this, when I add a web reference. I look for reference in the current solution. It finds this: http://localhost:2030/WS/Service.asmx

I change this to http://steve1/WS/Service.asmx
Steve1 is the name of my computer.

but i still get the same error message, cannot establish a connection to the network.

I thought this might solve the problem.

Steve

[steve_rm]

Just another thing i have tried.

http://steve1/WS/Service.asmx
I have put this into a web browser, firefox and get the following message.

Server Error in '/' Application.
The resource cannot be found.
Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.

Requested URL: /WS/Service.asmx

I also replaced steve1 with my ip address, still the same error message.

I hope i am close in solving this.

Steve

[steve_rm]

Hello

I have used activeSync to try and solve this.

I don't have an actual PDA this is all running from the VS 2005.

This is what i did:
start the application | clicked pocket 2003 | deploy
Once deployed with success
tool | device eumulator manager | right click pocket PC 2003 | cradle
Started activeSync | file | connections | left the defaults | connect
connected as guest

Then I went back to my emulator and click on the button to get data from the web service. And get the same problem.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Imports FirstPDA.steve1


Public Class Form1

    Private Sub btnIncidents_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIncidents.Click
        Dim WS_Incidents As New steve1.Service()

        Dim ds As New DataSet

        Try
            ds = WS_Incidents.getIncidentData()'cannot estiblish a connection with the network

            Me.grdIncidents.DataSource = ds.Tables(0).DefaultView
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

But i have resolved some issues. I can browse to my service.asmx page. by both using the computer name and ip address. i.e. http://steve1/WS/Service.asmx or http://1.1.1.1/WS/Service.asmx

the web reference I am using is http://steve1/WS/Service.asmx

When the program is running i can enter a website on the pocket pc emulator itself and see the website. i.e. www.google.com displays

The web service is working ok as i can recieve all the data from the database.

Any help to get this to work and i would be most grateful.

I replyed to the forum as above.

Thanks,

Steve

[Strider]

I'd recommend coding it like this below


Private Sub btnIncidents_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIncidents.Click
Dim WS_Incidents As steve1.Service()
Dim ds As DataSet

Try
WS_Incidents = New steve1.Service()
WS_Incidents.URL = "http://......................."

ds = WS_Incidents.getIncidentData()'cannot estiblish a connection with the network

Me.grdIncidents.DataSource = ds.Tables(0).DefaultView
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

[steve_rm]

Hello again strider,

I still having the same problem. The same error message "cannot estiblish a connection with the network"

This time I add my web reference and used the IP address of the computer instead of the computer name, and change the this in the web reference properties as well to have the ip address.

It also produced the same error. I used the code below and still get the same error.

There is one more thing, when i try and connect to the internet from the pda emulator it comes up with this message. "unable to connect: you have no modem entries created, and no network card present."

Current I have DSL and 2 computers on the same workgroup that are seperated by a switch. Not sure if that would cause a problem. I have also directly placed the network cable in my computer, instead of going though the hub.

I hope you can help, as this a major problem and slowing me down.

Thanks for you patients,

Steve

 
Private Sub btnIncidents_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIncidents.Click
        Try
            Dim ds As New DataSet
            Dim ws As New pdaRef.Service()
            ws.Url = "http://1.1.1.1/Incidents/Service.asmx"
            ds = ws.getIncidentData()

            Me.DataGrid1.DataSource = ds.Tables(0).DefaultView

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

below is the stack trace, not sure if this would be helpful
at System.Net.HttpWebRequest.finishGetResponse()
at System.Net.HttpWebRequest.GetResponse()
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse()
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse()
at System.Web.Services.Protocols.SoapHttpClientProtocol.doInvoke()
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke()
at CodeRedPDA.pdaRef.Service.getIncidentData()
at CodeRedPDA.Form1.btnIncidents_Click()
at System.Windows.Forms.Control.OnClick()
at System.Windows.Forms.Button.OnClick()
at System.Windows.Forms.ButtonBase.WnProc()
at System.Windows.Forms.Control._InternalWnProc()
at Microsoft.AGL.Forms.EVL.EnterMainLoop()
at System.Windows.Forms.Application.Run()
at CodeRedPDA.Form1.Main()

[steve_rm]

On the emulator do the following. this will resolve the issue of connection.
File | Configure | Network | check -> Host-only networking

Steve