Database

Re: Problem - Stored Procedure not found [modified]

16-May-09 9:59

SPs dont just desappear
Probably you created the sp in another DB (maybe the aspnet membership)
You are saying you have 2 DBs, make sure you have 2 connection strings as well and are not trying to access both DB with a single conection string

Dont complicate your life, just recreate the sp in the correct DB

Alexei Rodriguez

NetQuestions16-May-09 19:16

NetQuestions

16-May-09 19:16

Hi Alex,

Actually I Missed the Execute Command after creating a stored procedure.

Thanks,
NetQuestions

modified on Sunday, May 17, 2009 1:37 AM

Try catch in MYSQL Stored procedures

PeriyasamyRamachandran16-May-09 5:03

PeriyasamyRamachandran

16-May-09 5:03

Hi All,

I am new in MYSQL Database. I am using the interface SQLYog tool. I just want simple stored procedure with INSERT query and it should be handled exception handling(Try-catch).

If the records are inserted in a table then the procedure should return 1. If the records are not inserted(because Exception may occured) then the procedure should return 0.

Cau anybody help me?

Please..

Thanks,
Periyasamy Ramachandiran.

Re: Try catch in MYSQL Stored procedures

N a v a n e e t h16-May-09 6:49

N a v a n e e t h

16-May-09 6:49

See this[^] setting handlers. To see the insertion/updation/deletion failure/success status, check row_count(). Something like,

--your insert query
IF (<a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count">row_count()</a>[<a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count" target="_blank" title="New Window">^</a>]> 0) THEN 
 --insertion success
ELSE
 ..failure

Navaneeth

How to use google | Ask smart questions

Re: Try catch in MYSQL Stored procedures

PeriyasamyRamachandran16-May-09 7:06

PeriyasamyRamachandran

16-May-09 7:06

Navaneeth,

Thanks for your reply. Let me try this.

-Periyasamy Ramachandiran.

updating database

piticcotoc15-May-09 22:38

piticcotoc

15-May-09 22:38

I have a database called db on pc1 with 4 tables (table1-4) which already has information in it. On PC2 I have the same datatbase db with 8 tables (4 new tables). How can i add the 4 new tables, and data stored in them, in the database db on pc1 without losing data stored on pc1?

Re: updating database

AlexeiXX316-May-09 13:09

how do i acess ms sql database using pocket pc

16-May-09 13:09

You can use the sql server import and export data wizard

Alexei Rodriguez

ChiSmile15-May-09 19:41

15-May-09 19:41

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

AlexeiXX315-May-09 21:40

15-May-09 21:40

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

ChiSmile16-May-09 7:35

16-May-09 7:35

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

AlexeiXX316-May-09 9:40

16-May-09 9:40

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

AlexeiXX316-May-09 9:44

16-May-09 9:44

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

ChiSmile16-May-09 15:48

16-May-09 15:48

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!!!

AlexeiXX316-May-09 16:39

16-May-09 16:39

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

ChiSmile16-May-09 20:53

16-May-09 20:53

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!

AlexeiXX316-May-09 21:14

16-May-09 21:14

Did you already try the connection string you are using in your mobile app from a windows or web app???

Alexei Rodriguez

ChiSmile17-May-09 7:23

17-May-09 7:23

Nop i'v not tried that, i will do that right away and report back to u.

I appreciate!!

ChiSmile18-May-09 3:01

18-May-09 3:01

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!!

AlexeiXX318-May-09 5:56

18-May-09 5:56

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

ChiSmile18-May-09 13:45