PPC connection with MSSQL Express Edition

[Oss24]

I've the following VB code to connect to MSSQL 2005 express edition:

Imports System.data.sqlclient
Imports System.Data.SqlServerCe

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim CONStr As String = "Server=192.168.1.5;database=pos;user id=sa;password=123"
Dim ccon As New SqlConnection(CONStr)
Try
ccon.Open()
MsgBox(ccon.State)
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub
End Class

tried to write the port 1433 after I configured the sql setting to accept TCP/IP port 1433 and delete 0 from dynamic.
I wrote many connection string like:
"data source=192.168.1.5,1433\Sqlexpress;initial catalog=pos;user id= sa;password=123"
or
"data source=192.168.1.5\Sqlexpress;initial catalog=pos;user id= sa;password=123"
or
"data source=Mycom_name\Sqlexpress;initial catalog=pos;user id= sa;password=123"

the error message was (sql exception error) and another one was (a native exception has occurred in xyz.exe

what is the problem?

[Strider]

have you tried the following:

Try

sqlConn = New SqlConnection
sqlConn.ConnectionString = "Persist Security Info=False;Integrated Security=False;Server=<IPADDRESS\INSTANCE_NAME>,<PORT_NUMBER>;initial catalog=<DATABASE_NAME>;user id=<USER_NAME>;password=<PASSWORD>;"
sqlConn.Open()

Catch Ex As SqlClient.SqlException

End Try

[Oss24]

Thanks for Reply.
but the same error message.

[petevick]

Hi
try it without the port number, or use the correct one, as I am pretty sure SqlExpress doesn't use 1433 as that is used by SqlServer

The string that works for me is:
Persist Security Info=False;Integrated Security=False;Server=ACERDUO\Sql2005;initial catalog=Waiter;user id=sa;password=xx;

Pete

[Oss24]

Hi,
First of all I'd like to thank you about your great reply.
but the problem as it is and I guess this problem from the emulator and I tried the device direct (Active sync Symbol PPC through Wifi).
(note: the network is running without any problem by ping all2all )
I tried this code with the emulator and the device but the error message result was different also I tried the code in Normal Windows application and it successed.

The same Problem is till now found?

Thanks
Waiting for solution..

[petevick]

Hi,
sorry - don't understand?

Are you saying it doesn't work in the emulator, or doesn't work on the device?

Pete

[Oss24]

Hi
not working in both

[petevick]

HI,
in the try catch to
Try
... open code
Catch Ex as SqlCeException
displaySQLCeErrors(Ex)
End Try

and use this...
Public Sub DisplaySQLCEErrors(ByVal ex As SqlCeException)
Dim I As Int32
Dim strWork As String
strWork = "SqlCE Error " & vbCrLf
For I = 0 To ex.Errors.Count - 1
strWork = strWork & "Index #" & I & vbCrLf & "Error:" & ex.Errors(I).ToString()
Next
MsgBox(strWork, MsgBoxStyle.Information, "SQL Error")
End Sub

What error do you get?

[Oss24]

sqlCe Error
Index #0
Error:System.data.sqlclient.SqlError:invalid connection

[petevick]

Hi,
what connection string have you got now, and is this on the device or the emulator?

Pete

[Oss24]

Hi,
The Connection string is :
data source=192.168.1.5\Sqlexpress;initial catalog=pos;user id=sa;password=123 on the device.

Oss24

[petevick]

So you haven't used the string suggested by Strider or myself?

Persist Security Info=False;Integrated Security=False;Server=192.168.1.5\SqlExpress;initial catalog=pos;user id=sa;password=123;

Pete

[Oss24]

Hi,
The Code:

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 Ccon As New SqlConnection("Persist Security Info=False;Integrated Security=False;Server=192.168.1.5\SqlExpress;initial catalog=pos;user id=sa;password=123;")
Try
Ccon.Open()

Catch ex As SqlException
DisplaySQLCEErrors(ex)
End Try

End Sub
Public Sub DisplaySQLCEErrors(ByVal ex As SqlException)
Dim I As Int32
Dim strWork As String
strWork = "SqlCE Error " & vbCrLf
For I = 0 To ex.Errors.Count - 1
strWork = strWork & "Index #" & I & vbCrLf & "Error:" & ex.Errors(I).ToString()
Next
MsgBox(strWork, MsgBoxStyle.Information, "SQL Error")
End Sub
End Class

the result is:
sqlCe Error
Index #0
Error:System.data.sqlclient.SqlError:
server not found:
192.168.1.5\sqlexpress

[Oss24]

Dear Pete,
First of all thanks a lot
Kindly, review the steps with me :

1. I configured the SQL Server Express Edition to accept the TCP/IP (enable)
2. Deleted for all Dynamic ports (remove Zeros) and assign the port 1433 for all - set to yes
3. restart the sql service.
4. connected through the ssmse (192.168.1.5\sqlexpress,1433 ) (user =sa & password = 123) so the connection succeeded
5. also tried to connect by computer name\sqlexpress and it succeeded
6. connected through another Desktop computer in my lab (succeeded)
7. Ping the Sql server computer from the PPC (succeeded )
8. tried to connect by many connection strings syntax (fail)
9. tried to connect using the emulator (fail)
10. in the ending I installed another MS Sql Server standard edition and tried to connect by the ppc device& the emulator but the result was the same.

may be I forgot some thing or it's a bad luck or .... !!!
P.S: I Know the the TCP Port 1433 isn't necessary when Sql Server Browser service running.
also I connected through the sqlcmd command line (succeeded)
What is the Problem.
Thanks

[petevick]

Hi,
I am now at a bit of a loss, as you appear to have tried everything.

I am still confused by port 1433 as that is what 2000 uses, although I use 2000 and 2005 on my system, so that may be different. Sql2005 uses 1125 and Express uses 1025 on my system.


Will check this out further when I get chance, as have just tried connecting to SqlExpress using this string and it fails, whereas connecting to Sql2005 works

Pete

[petevick]

Ok, after a bit of digging, try this (or similar)
Dim Ccon As New SqlConnection("Persist Security Info=False;Integrated Security=False;Server=beehive25,1068;initial catalog=Sales_Data;user id=sa;password=polaris;")

This was gleaned from http://netcf2.blogspot.com/2005/12/accessing-sql-server-express-from.html

It would appear you don't need to quote the instance

Pete