connection issues to sql server 2008 from mobile device (Windows mobile 6.5)

Question

I've been searching everywhere on the internet for the following problem but it seems theres not a answer.

Im creating a very simple mobile app in Visual Studio 2008. It should connect to a remote sql database to do a simple read. The sql server is 2008. The physical device is here so i can deploy to it and run on the device itself (not using emulator).

The connection string im using is: (and ive tried different ones)

Data Source=[ServerIP];Initial Catalog = [DatabaseName]; User ID = [ID]; Password = [Password];

Ive altered the paras just to paste here.

The actual code im using to connect to database is:

SqlConnection sqlConnection1 = new SqlConnection("MyConnectionString as above");
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader;
        string SKU = "";
        cmd.Parameters.Add(new SqlParameter("@barcode", Barcode));
        cmd.CommandText = "SELECT SKU, Quantity FROM Catalog.Barcodes WHERE Barcode = @barcode";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection1;

        sqlConnection1.Open();

        reader = cmd.ExecuteReader();
        // Data is accessible through the DataReader object here.
        while (reader.Read())
        {
            SKU = reader.GetString(0);
        }


        sqlConnection1.Close();

The exception is when opening the connection:

SqlException at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, TdsParserState state) at System.Data.SqlClient.SqlInternalConnection.OnError(sqlException exception, TdsParserState state) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning().....System.Data.SqlClient.SqlInternatlConnection.OpenAndLogin().....

Ive ensured that the system.data.sqlclient reference is for the compact framework. C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Devices\Client\System.Data.SqlClient.dll

The code has also been copied into a standard winform and works a treat. The sql server is remotley accessible as it is used daily with the same credentials as ive used. The device is connected to the internet via wifi, and tested by web browsing.

Hopefully ive fully noted what ive done so far and the full situation.

So what's the problem here ?? Baffled me.

Answer 1

I have finally found the answer. For me its a weird one as i would have persumed it do work without it. The problem was with the connectionstring. I was missing the port number 1433 after the server address. It seems this is a requirement of the .net compact framework.

Answer 2

chuck a SQL datasource control onto the page, using the GUI can you connect to the DB and pull some data back ? if so you could save the connection info (web.config) and use it from there using ConfigurationManager.ConnectionStrings

string connStr = ConfigurationManager.ConnectionStrings["mySavedConnectionStringName"].ConnectionString;

Answer 3

I assume your connection is setup wrong. Better setup step by step:

try{
    string connStr = "Data Source=192.168.0.2;Initial Catalog=myDataBase/myInstance;
    Integrated Security=SSPI; User ID=myDomain\myUsername;Password=myPassword;";
    //new connection
    SqlConnection sqlConnection1 = new SqlConnection(connStr);
    sqlConnection1.Open();
    //new sqlCommand
    SqlCommand cmd = new SqlCommand();
    SqlDataReader reader;
    string SKU = "";
    cmd.Parameters.Add(new SqlParameter("@barcode", Barcode));
    cmd.CommandText = "SELECT SKU, Quantity FROM Catalog.Barcodes WHERE Barcode = @barcode";
    cmd.CommandType = CommandType.Text;
    cmd.Connection = sqlConnection1;
    //create reader
    reader = cmd.ExecuteReader();
    // Data is accessible through the DataReader object here.
    while (reader.Read())
    {
        SKU = reader.GetString(0);
    }
    sqlConnection1.Close();
 }catch (Exception ex){
    System.Diagnostics.Debug.WriteLine("Exception in sql code:" + ex.Message);
 }

The connection string has to be altered to your setup. Do not assign the connection to the command BEFORE the connection is opened.

Connection Strings: See https://www.connectionstrings.com/sql-server-2008/

Trusted Connection from a CE device

A Windows CE device is most often not authenticated and logged in to a domain but it is 
possible to use SSPI or trusted connection and authentication from a CE device using 
this connection string.
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
User ID=myDomain\myUsername;Password=myPassword;