|
Pls help me,
i designed a database with ms sql and intend to access it using a mobile device.but at the moment am using the emulator in Visual studio 2008 professional to test the app but it cant access the database, which is running on my system.Am using vb.net for the coding given below, but sqlException keeps on coming out:
Private Sub mnuLoadSqlServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuLoadSqlServer.Click
Dim cn As System.Data.SqlClient.SqlConnection
Dim cmd As System.Data.SqlClient.SqlCommand
Dim da As System.Data.SqlClient.SqlDataAdapter
Try
' Establish a connection to the SQL Server database.
cn = New System.Data.SqlClient.SqlConnection("Data Source=CHINEDUM\SQLEXPRESS;Initial Catalog=medihealth;Integrated Security=SSPI;User ID=CHINEDUM\CHINEDU;Password= ;")
cmd = New System.Data.SqlClient.SqlCommand(txtSelect.Text, cn)
da = New System.Data.SqlClient.SqlDataAdapter(cmd)
' Is this table being added to the DataSet? If not destroy and
' recreate the DataSet.
If Not chkAddToDataSet.Checked Then
ds = Nothing
ds = New System.Data.DataSet
End If
' Add the new table.
Dim dt As DataTable = New System.Data.DataTable(txtTableName.Text)
da.Fill(dt)
ds.Tables.Add(dt)
' Display the DataSet.
DisplayData()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Sub DisplayData()
Dim dt As System.Data.DataTable
' Clear the ComboBox control.
cmbTables.Items.Clear()
' Load the ComboBox with a list of available tables.
For Each dt In ds.Tables
cmbTables.Items.Add(dt.TableName.ToString)
Next
' Finally, trigger the displaying of the first table.
cmbTables.SelectedIndex = 0
End Sub
Note: I've tried to use my ip address as the data source, but cant still get through
|
|
|
|
|
nedusmile wrote: but sqlException keeps on coming out
And whats the exception??
But anyways, it could be that you are gettin the error that sql couldnt stablish a connection to the server, it could be that under default settings...
If so, you need to configure your sql server to accept remote connections
Alexei Rodriguez
|
|
|
|
|
Thanks Alexie, but i did enabled the TCP/IP config of my sql server,
is there any other thing that i am lacking as am new to mobile application, I dont just know where to start.
more explanation, article and links will be deeply appreciated.
Thank you very much for your time.
Nedusmile
|
|
|
|
|
Whats the exception??
What version of sql are you using??
Do you really think that someone is gonna help you like that??
Besides... thins is not a mobile development forum
Alexei Rodriguez
|
|
|
|
|
nedusmile wrote: ' Establish a connection to the SQL Server database.
cn = New System.Data.SqlClient.SqlConnection("Data Source=CHINEDUM\SQLEXPRESS;Initial Catalog=medihealth;Integrated Security=SSPI;User ID=CHINEDUM\CHINEDU;Password= ;")
Use the IP with the port number that the server is listening to
Example 192.168.1.65:1212
Also, dont use integrated security, use a userid and a password
And please, tell me the damn exception, i couldn sleep last night :p
Alexei Rodriguez
|
|
|
|
|
Forgive my inexperience, Alexie. the Exception error looks something like this
system.Data.sqlClient.sqlException:sqlException
at
system.Data.sqlClient.sqlConnection.OnError()
at
system.Data.sqlClient.sqlInternalConnection.OnError()
at
system.Data.sqlClient.TdsParser.ThrowExceptionAndWarning()
at
system.Data.sqlClient.TdsParser.Connect()
at
system.Data.sqlClient.sqlInternalConnection.OpenAndLogin()
I now modified the parameters as u suggested:
Private Sub mnuLoadSqlServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuLoadSqlServer.Click
Dim cn As System.Data.SqlClient.SqlConnection
Dim cmd As System.Data.SqlClient.SqlCommand
Dim da As System.Data.SqlClient.SqlDataAdapter
Try
' Establish a connection to the SQL Server database.
cn = New System.Data.SqlClient.SqlConnection("Data Source=192.168.1.130,1212;Initial Catalog=medihealth;User ID=CHINEDUM\CHINEDU;Password= administrator;")
cmd = New System.Data.SqlClient.SqlCommand("SELECT * from LAB where Patient_ID=100054201", cn)
da = New System.Data.SqlClient.SqlDataAdapter(cmd)
' Is this table being added to the DataSet? If not destroy and
' recreate the DataSet.
If Not chkAddToDataSet.Checked Then
ds = Nothing
ds = New System.Data.DataSet
End If
' Add the new table.
Dim dt As DataTable = New System.Data.DataTable(txtTableName.Text)
da.Fill(dt)
ds.Tables.Add(dt)
' Display the DataSet.
DisplayData()
Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
End Try
End Sub
End Class
Any other thing i should try?
Thanks a lot!!!
|
|
|
|
|
nedusmile wrote: cn = New System.Data.SqlClient.SqlConnection("Data Source=192.168.1.130,1212;Initial Catalog=medihealth;User ID=CHINEDUM\CHINEDU;Password= administrator;")
Youd better be kidding with the port number
I said 1212 is a example, is your sql server really configured to listen to port 1212??
Anyways, there could be many reasons for not being able to connect to sqlserver from your device or emulator, is it even cradled?, does it have a connection to the pc?, can you see the pc form the device or emulator?
If you want to make sure your connection string is correct, make a win or web app and try to connect with that connection string
You need to learn to read and to use google or something
You cant even spell my name, its the third time you spell it wrong
Alexei Rodriguez
|
|
|
|
|
yes all the things u specified have been configured, i can c my pc on the emulator
the port num of server is configured to listen to that given in the code.
by the way, sory for misspelling ur name, Alexei.I have googled my eyes to any possible site that could be of any help. The connection string is from "The Definitive Guide to the .NET Compact Framework" written by Larry Roof and Dan Fergus.
I have also visited connectionstrings.com and tried some of the ones given for this purpose.
Am still lost, Thanx for ur help though!
|
|
|
|
|
Did you already try the connection string you are using in your mobile app from a windows or web app???
Alexei Rodriguez
|
|
|
|
|
Nop i'v not tried that, i will do that right away and report back to u.
I appreciate!!
|
|
|
|
|
Hi Alexei, when i tried the following string on my desktop with windows application, it connected to the database. However, it failed when I inserted my ip address and also failed on the mobile application.
Dim cn As System.Data.SqlClient.SqlConnection
Dim cmd As System.Data.SqlClient.SqlCommand
Dim da As System.Data.SqlClient.SqlDataAdapter
'Dim conStr As String
cn = New SqlClient.SqlConnection()
Try
' Establish a connection to the SQL Server database.
cn = New System.Data.SqlClient.SqlConnection("server=CHINEDUM\SQLEXPRESS;Initial Catalog=C:\DOCUMENTS AND SETTINGS\CHINEDU\MY DOCUMENTS\MEDIHEALTH.MDF;Integrated Security=SSPI;User ID=CHINEDU;Password=handsome ;")
'cn = New 'System.Data.SqlClient.SqlConnection("server=192.168.2.101,1212;'database=C:\DOCUMENTS AND SETTINGS\CHINEDU\MY DOCUMENTS\MEDIHEALTH.MDF;User 'ID=CHINEDU;Password= handsome;")
on the desktop when i used the my Ip, the error says i cant because under the default setings SQL server 2005 does not allow remote connection and that no connection could be made because the target machine has actively refused it. But am sure i have made the necessary config on the server and on my system to allow remote connection.
On the mobile app, the error message is still same.
Do you think there is any other thing i can do or am i just a dummy! cos u've tried to assist me but i cant still figure out. I felt the sol could be simple, but that has not been the case.I dont know if the case is unique to me.
Would gladly welcome any more suggestions!!
|
|
|
|
|
nedusmile wrote: when i used the my Ip, the error says i cant because under the default setings SQL server 2005 does not allow remote connection
When you use an ip, it simulates a remote connection, maybe you havent configured the server yet
Try this: Configure SQl Express to allow remote connections[^]
Also, i think the database must be attached to sqlexpress
Alexei Rodriguez
|
|
|
|
|
All these processes i have followed and even did second time, yet doesn't work.Do you think i should instal a vitrual machine, so that the emulator can use its adapter for network communication.
Am getting more frustrated.Yes the database is already attached to sql server.
|
|
|
|
|
nedusmile wrote: Do you think i should instal a vitrual machine, so that the emulator can use its adapter for network communication.
I dont think installing VM is necesary for this
Your problem is not the emulator
Your problem is configuring your sqlexpress to accept remote connections, once you do that, you then worry about connecting from the emulator
Keep on searching, im sure the anwer is there
Alexei Rodriguez
|
|
|
|
|
Yes u r right Alexei, the problem was from my sql server.I stumbled on a piece that taught me how to really configure it and it works perfectly now.
Again, Thanks a lot for ur time.
U are the best!!!!.
see links that helped me, may be another person might be having the same issue like i had:
<a href="http://netcf2.blogspot.com/2005/12/accessing-sql-server-express-from.html">
|
|
|
|
|
Glad to hear that
Thats the same post that helped me a lot when i was trying to connect to sql2000 from pocket pc
Accessing SQL Server Express from the emulator (or PDA) [^]
If you see the comments, the third and fifth are mine
Unfortunatelly i searched for that post to give it to you but i coulnt find it again, i tought it had been deleted or something because if you see the date, it is like 2 or more years old
Alexei Rodriguez
|
|
|
|
|
true, u said!
but do u know when i uninstalled the microsoft virtual machine that i installed in the course of setting up the emulator, and tried to run the emulator again, i got a message saying that i should install a virtual machine.so i think virtual machine is also vital for complete set up.I had to re-install the virtual machine.
|
|
|
|
|
nedusmile wrote: MessageBox.Show(ex.Message)
Don't use Exception.Message
use Exception.ToString(), it provides much more and very useful information.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
I now used the Exception call u mentioned but i dont know if the message generated will be of any help to u in assisting me.hope it does as I did report the exception message on my replied post to Alexie.
Thanks again!!
|
|
|
|
|
How New_Id() Function Work in Back Ground i.e how it random ally Generate the Number
when used with order by clause. then what is critiera For Random Generate Number.
Dinesh Sharma
|
|
|
|
|
newid() doesn't actually generate random numbers, it generates GUIDs, which have a random part to them, and serve their purpose well for random ordering.
NEWID()
--------
- NEWID() generates GUID's instead of random numbers which is of type in a uniqueidentifier.
Try this:
SELECT NEWID() AS 'NEWID'
Output:
NEWID
-----
259ED1EE-AE35-41D2-9FCA-9FA9538AE5FF
- NEWID is guaranteed to be unique
RAND()
------
- Returns float value between 0 to 1.
Try this:
SELECT RAND() AS 'RANDOM NUMBER'
Output:
RANDOM NUMBER
------------
0.0431138996610296
- RAND() is not guaranteed to be unique
- SQL Server estimation plan says, sorting with NewId() is more time
consuming than Rand(). Rand() number generation is based on time factor
If we sort records with Rand(),the records will get same rand number and
result will be the same as previous.
e.g.
SELECT RAND() RANDOMCOLUMNS,COL1
FROM MYTABLE
ORDER BY RANDOMCOLUMNS
Output:
RANDOMCOLUMNS COL1
--------------------------------------------------------
0.227066653916067 1
0.227066653916067 2
0.227066653916067 3
0.227066653916067 4
As you can make out that the "RANDOMCOLUMNS" values are same through out.
Because random generation factor does not change during record selection.
Whereas, the same query with NEWID() will give the following
RANDOMCOLUMNS COL1
--------------------------------------------------------
B953F9A9-BE22-4651-8669-5CC6A5269911 4
50293D75-4139-4A75-883F-81DAA91ED4E6 1
0A9B1D56-68FD-4060-AA6B-89791DAF8924 3
4C3A2765-4EA2-493B-8AA9-EBA414D7AAC2 2
As you can make out that, unique GUID's has been created in this case.
So in a simple query, doing a simple "ORDER BY RAND()" will not randomly
order the results at all.In this context,NEWID() is much better, though
not more efficient.
For more information on "Generate random sets in SQL Server with NewID()", you can go through this tutorial
http://articles.techrepublic.com.com/5100-10878_11-6089823.html#[^] by by Arthur Fuller.
More on RAND()
http://msdn.microsoft.com/en-us/library/ms177610.aspx[^]
and NEWID()
1)
http://msdn.microsoft.com/en-us/library/aa276822(SQL.80).aspx[^]
2)http://www.sqlmag.com/Article/ArticleID/97032/sql_server_97032.html[^]
3)http://www.sqlmag.com/Article/ArticleID/99807/sql_server_99807.html[^]
N.B.~RAND() sorts on the constant (per SELECT)
NEWID() sorts on a dynamic (per row)
That is why if you look carefully, the COL1 values has been changed in
the 2nd case while it is same in the first.
For information about how are GUIDs sorted by SQL Server, look here
http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx[^]
Hope this helps.
Niladri Biswas
modified on Sunday, July 5, 2009 8:04 AM
|
|
|
|
|
I need to execute the stored procedure recursively..but i am getting an error there is limit of 32 to execute it repeatedly.... how to handle this problem...Any solution
|
|
|
|
|
It becomes a design issue, 32 is the depth of the proc stack, you need to redesign the way you are processing the data, possibly move it to the client or change the way the proc is called.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why in the world would you switch from an iterative version to a recursive one?
So not only are you incurring more memory overhead, but let's not forget you are running this *IN* SQL SERVER
Try not to use recursive function
Anyway, could you please send the stored proc so that we can have a look on that and tell you a suggestive approach!
Regards,
Niladri Biswas
Niladri Biswas
|
|
|
|
|
You replied to my reply - you may want to move this to the OP so as to get/give some feedback
Never underestimate the power of human stupidity
RAH
|
|
|
|