Guys, I need your help on this one. I have a problem here. There is an exception on my conn.Open.
It said that "SQL Exception was unhandled by user code. Cannot open database requested in login 'MUSIC STORE'. Login fails.
Login failed for user 'IT785P13student'."
Does anyone have any idea what this means?
Greetings everyone, I am attempting to build my first application using Microsofts Sql databases. It is a Windows Mobile application so I am using Sql Server Compact 3.5 with Visual Studio 2008 Beta 2. When I try and insert a new row into one of my tables, the app throws the error message shown in the title of this topic.
'((System.Exception)($exception)).Message' threw an exception of type 'System.NotSupportedException'
My table has 4 columns (i have since changed my FavoriteAccount datatype from bit to Integer)
http://i85.photobucket.com/albums/k71/Scionwest/table.jpg
Account type will either be "Checking" or "Savings" when a new row is added, the user will select what they want from a combo box.
Next is a snap shot of my startup form.
http://i85.photobucket.com/albums/k71/Scionwest/form.jpg
Where it says "Favorite Account: None" in the top panel, I am using a link label. When a user clicks "None" it will go to a account creation wizard, and set the first account as it's primary/favorite. As more accounts are added the user can select which will be his/her primary/favorite. For now I am just creating a sample account when the label is clicked in an attempt to get something working. Below is the code used.
private void lnkFavoriteAccount_Click(object sender, EventArgs e)
{
FinancesDataSet.BankAccountRow account = this.financesDataSet.BankAccount.NewBankAccountRow();
account.Name = "MyBank Checking Account";
account.AccountType = "Checking";
account.Balance = Convert.ToDecimal("15.03");
account.FavoriteAccount = 1;//datatype is an integer, I have changed it since I took the screenshot.
financesDataSet.BankAccount.Rows.Add(account);
//The next three lines where added while I was trying to get this to work.
//I don't know if I really need them or not, I receive the error regardless if these are here or not.
this.bankAccountTableAdapter1.Update(financesDataSet);
this.financesDataSet.AcceptChanges();
refreshDatabase();
}
the refreshDatabase() code is here:
private void refreshDatabase()
{
this.bankAccountTableAdapter1.Fill(this.financesDataSet.BankAccount);
//Aquire a count of accounts the user has
int numAccounts = financesDataSet.BankAccount.Count;
//Loop through each account and see which one is the primary.
for (int num = 0; num != numAccounts; num++)
{
//Works ok in frmMain_Load, but when my lnkFavoriteAccount_click calls this, it throws the error.
if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)
{
//Display the primary account on our home page. User can click the link label & be taken to their account register.
this.lnkFavoriteAccount.Text = this.financesDataSet.BankAccount[num].Name.ToString();
this.lnkFavoriteFunds.Text = this.financesDataSet.BankAccount[num].Balance.ToString();
break;
}
}
}
and my form_load code
private void frmMain_Load(object sender, EventArgs e)
{
refreshDatabase();
}
So, when I click on the lnkFavoriteAccount label, and my new row gets added, the app stops at the following line in my DataSet.Designer
[global:ystem.Diagnostics.DebuggerNonUserCodeAttribute()]
public byte FavoriteAccount {
get {
try {
return ((byte)(this[this.tableBankAccount.FavoriteAccountColumn]));
}
catch (global:ystem.InvalidCastException e) {
//Stops at the following line, this error was caused by 'if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)'
throw new global:ystem.Data.StrongTypingException("The value for column 'FavoriteAccount' in table 'BankAccount' is DBNull.", e);
}
}
set {
this[this.tableBankAccount.FavoriteAccountColumn] = value;
}
}
I have no idea what I am doing wrong, all of the code I used I retreived from Microsofts help documentation included with VS2008. I have tried used my TableAdapter.Insert() method and it still failed when it got to
if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)
in my refreshDatabase() method it still failed.
When I look, the data has been added into the database, it's just when I try to retreive it now, it bails on me. Am I retreiving the information wrong?
Thanks for any help you guys can offer.
Johnathon
Hi,
I got an strange problem with one of my packages.
When running the package in VisualStudio it runs properly, but if I let this package run as part of an SQL-Server Agent job, I got the message "The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown." on my log and the package ends up with an error.
Both times it is exactly the same package on the same server, so I don't know how the debug or even if there is anything I need to debug?
Regards,
Jan
I downloaded a web site from internet and tried to open it in visual web developer express edition but it gave an error and the code and the error was:
CODE- Return CType(Me.GetPropertyValue("Theme"),String)
ERROR -An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
An answer will be very good for me
Hi - I am new to *** forum and could really use some help. I am trying to insert the following values in an sql server 2003 database and I get an exception labeled:System.Data.SqlClient.SqlException: Incorrect syntax near ')'. Source:Line 91: connection.Open();Line 92: int numRowsAffected;Line 93: numRowsAffected= command.ExecuteNonQuery();Line 94: connection.Close();Line 95: Stack Trace:[SqlException (0x80131904): Incorrect syntax near ')'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149 _Default.Button1_Click(Object sender, EventArgs e) in e:Hampton DirectWebsitesLunchOrderRestaurantEditor.aspx.cs:93 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +96 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +116 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72 Source Code: SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LunchOrderTestDBConnectionString1"].ToString());SqlCommand command = new SqlCommand("INSERT INTO Restaurants (Name,Address1,Phone,Fax,Menu_Pages,) VALUES ( @Name,@Address1,@Phone,@Fax,@Menu_Pages)", connection); //@MenuSqlParameter param0 = new SqlParameter( "@Name", SqlDbType.VarChar,50 );param0.Value = TextBox1.Text;command.Parameters.Add( param0 );SqlParameter param1 = new SqlParameter("@Address1", SqlDbType.VarChar, 50);param1.Value = TextBox2.Text;command.Parameters.Add(param1);SqlParameter param2 = new SqlParameter("@Phone", SqlDbType.VarChar, 50);param2.Value = TextBox3.Text;command.Parameters.Add( param2 );SqlParameter param3 = new SqlParameter("@Fax", SqlDbType.VarChar, 50);param3.Value = TextBox5.Text;command.Parameters.Add(param3);//SqlParameter param4 = new SqlParameter("@Menu", SqlDbType.Image);//param4.Value = byte_data;//command.Parameters.Add(param4);SqlParameter param5 = new SqlParameter("@Menu_Pages", SqlDbType.Int, 50);param5.Value = Int32.Parse(TextBox6.Text);command.Parameters.Add(param5);connection.Open();int numRowsAffected;numRowsAffected= command.ExecuteNonQuery();connection.Close();Thank You in advance for all help and suggestions!
View 1 Replies View RelatedI am trying to insert data into a table and I am getting a SQL Exception: System.Data.SqlClient.SqlException {"Incorrect syntax near 'nvarchar'. Must declare the scalar variable "@"."}
This is my subroutine that is executed once the submit button is clicked.
Please advise. Thank you for your time!
1 Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
2 Dim CMLdataSource As New SqlDataSource()
3 Dim commStr As String
4
5 CMLdataSource.ConnectionString = ConfigurationManager.ConnectionStrings("CMLConnectionString1").ToString()
6
7 CMLdataSource.InsertCommandType = SqlDataSourceCommandType.Text
8 commStr = "INSERT INTO SCOs (ProjCode, SCONum, SCORev, DateIssued, DateAssigned, ProjName, CSCI, Description, [IDENT NUMBER], REV, PPRChgAbstract, SourceCodeChange, DocumentChange, "
9 commStr = commStr & "DocChangeRev, ChangeNoticeNum, TechInvest, CDMRep, TestEngineer, SQE, SCCBChair, CDMLibrarian, ECPNum, [CLASS 1], [CLASS 2]) "
10 commStr = commStr & "VALUES (@ProjCode, @SCONum, @SCORev, @DateIssued, @DateAssigned, @ProjName, @CSCI, @Description, @[IDENT NUMBER], @REV, @PPRChgAbstract, @SourceCodeChange, @DocumentChange, "
11 commStr = commStr & "@DocChangeRev, @ChangeNoticeNum, @TechInvest, @CDMRep, @TestEngineer, @SQE, @SCCBChair, @CDMLibrarian, @ECPNum, @[CLASS 1], @[CLASS 2])"
12
13 CMLdataSource.InsertCommand = commStr
14
15 CMLdataSource.InsertParameters.Add("ProjCode", CodeList.Text)
16 CMLdataSource.InsertParameters.Add("SCONum", scoNum.Text)
17 CMLdataSource.InsertParameters.Add("SCORev", ScoRevTextBox.Text)
18 CMLdataSource.InsertParameters.Add("DateIssued", DateIssuedTextBox.Text)
19 CMLdataSource.InsertParameters.Add("DateAssigned", DateAssigTextBox.Text)
20 CMLdataSource.InsertParameters.Add("ProjName", ProjectList.Text)
21 CMLdataSource.InsertParameters.Add("CSCI", CsciTextBox.Text)
22 CMLdataSource.InsertParameters.Add("Description", MediaTextBox.Text)
23 CMLdataSource.InsertParameters.Add("[IDENT NUMBER]", IDTextBox.Text)
24 CMLdataSource.InsertParameters.Add("REV", RevTextBox.Text)
25 CMLdataSource.InsertParameters.Add("PPRChgAbstract", PPRTextBox.Text)
26 CMLdataSource.InsertParameters.Add("SourceCodeChange", SourceCodeTextBox.Text)
27 CMLdataSource.InsertParameters.Add("DocumentChange", DocTitleTextBox.Text)
28 CMLdataSource.InsertParameters.Add("DocChangeRev", RevLetterTextBox.Text)
29 CMLdataSource.InsertParameters.Add("ChangeNoticeNum", ChangeNoticeTextBox.Text)
30 CMLdataSource.InsertParameters.Add("TechInvest", TechInvestTextBox.Text)
31 CMLdataSource.InsertParameters.Add("CDMRep", SwCdmTextBox.Text)
32 CMLdataSource.InsertParameters.Add("TestEngineer", TestEngTextBox.Text)
33 CMLdataSource.InsertParameters.Add("SQE", SqeTextBox.Text)
34 CMLdataSource.InsertParameters.Add("SCCBChair", SccbChairTextBox.Text)
35 CMLdataSource.InsertParameters.Add("CDMLibrarian", CdmLibTextBox.Text)
36 CMLdataSource.InsertParameters.Add("ECPNum", EcpNumTextBox.Text)
37 CMLdataSource.InsertParameters.Add("[Class 1]", Class1TextBox.Text)
38 CMLdataSource.InsertParameters.Add("[Class 2]", Class2TextBox.Text)
39
40 Dim RowsAffected As Integer = 0
41
42 Try
43 RowsAffected = CMLdataSource.Insert()
44 Catch ex As Exception
45 CMLdataSource = Nothing
46 Server.Transfer("dberror.aspx")
47 Finally
48 CMLdataSource = Nothing
49 End Try
50
51
52 If RowsAffected <> 1 Then
53
54 Server.Transfer("dbError.aspx")
55
56 Else
57 Server.Transfer("newConfirm.aspx")
58
59 End If
60
61
62 End Sub
Could somebody please tell me what is wrong with this SQL Statement. I'm trying to run it in SQL Server 2000, and keep getting the error:
System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'GROUP'
but as far as I can see, there isn't anything wrong near "GROUP"..." SELECT b.ColorID, b.ColorName, " +
" MAX(a.ColorID) AS DesignerProductAvailability_ColorID, " +" MAX(a.Quantity) AS DesignerProductAvailability_Quantity, " +
" MAX(a.ProductID) AS DesignerProductAvailability_ProductID " +" FROM DesignerProductAvailability a " +
" INNER JOIN ColorList b " +" ON b.ColorID = DesignerProductAvailability_ColorID " +
" WHERE DesignerProductAvailability_ProductID = @ProductID AND " +" DesignerProductAvailability_Quantity > 0 " +
" ORDER BY b.ColorName ASC " +
" GROUP BY b.ColorName ";
Any help would be greatly appreciated. Thanks in advance!
I am passing the values to a class which has a method to insert the date into database. How to retrive this exception thrown in this method and disply to user.
View 10 Replies View RelatedWe have load-balanced sql server mcs and users will be accessing all the time. We are maintaining user profiles in one mc. Here is the problem. We have a sp which reads profile from one particular mc always. If the mc is down, the sp does not fail gracefully. Instead it causes exception. Is there a way to access sql servers alternatively ie if one server is not responding read data from another sql?
View 1 Replies View RelatedHi all,
I am using MSSQL Server 8.0 , and the driver jtds.jar to connect the database for SQL operation .
I can do all the sql operation .
But after the creation of a trigger for a table , I used the same java program to insert rows into table .
-------The java code I used ---------------------
String connectionString = "jdbc:jtds:sqlserver://localhost:1433/master";
try {
Class.forName(jdbcDriverClass);
Connection con = DriverManager.getConnection(connectionString,userN ame,password);
Statement st = con.createStatement();
String query = "insert into tabname values('11','ROBERT')";
st.executeUpdate(query);
} catch (Exception e) { e.printStackTrace(); }
-------------------------------------------------
and got the exception
////////////////////////////////////////////////////////
java.sql.SQLException: executeUpdate can't return a result set
at net.sourceforge.jtds.jdbc.TdsStatement.executeUpda te(Unknown Source)
////////////////////////////////////////////////////////
What will be the problem here ?
Is it bcos of any time out ?
Please help !!!
Thanks
I already added the the COM Component: Microsoft ADO ext. 2.8 for DLL and Security
Dim jro1 As JRO.JetEngine
jro1 = New JRO.JetEngine
jro1.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C: estDB.mdb;Jet OLEDB:Database Password=test", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Backup estDB.mdb;Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=test")
Error:
Retrieving the COM class factory for component with CLSID {DE88C160-FF2C-11D1-BB6F-00C04FAE22DA} failed due to the following error: 8007007e.
I have just recovered data from a hard drive crash. I installed a fresh copy of Windows Server 2003 with sp2. Have SQL Standard Running with sp2, and VS 2005 standard running sp1.
I have several webs on this server, was able to get most up and running fine, but one web and database cannot make the connection after recovery. I had to reapply permissions and folder security to all webs, and change provided dbo privaledges to network_services. However I am still getting the following error:
Server Error in '/bpe' Application.--------------------------------------------------------------------------------
The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'Fitness1stDb', schema 'dbo'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'Fitness1stDb', schema 'dbo'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'Fitness1stDb', schema 'dbo'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +367 System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +85 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
--------------------------------------------------------------------------------Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
What procedure should I follow to fix this issue?
Does anyone have a code snippet for dumping the actual sql a command would be running?
View 3 Replies View Relatedit gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.public void get_issid(string cse_email, string tech_email, string subject, string issue_details, string response, string solv_date, out int issid)
{
// Establish ConnectionSqlConnection oConnection = GetConnection();
// build the commandSqlCommand oCommand = new SqlCommand("get_issid", oConnection);
oCommand.CommandType = CommandType.StoredProcedure;
// ParametersSqlParameter paracse_email = new SqlParameter("@cse_email", SqlDbType.VarChar, 50);
paracse_email.Value =cse_email;
oCommand.Parameters.Add(paracse_email);
SqlParameter paratech_email = new SqlParameter("@tech_email", SqlDbType.VarChar,50);
paratech_email.Value = cse_email;
oCommand.Parameters.Add(paratech_email);SqlParameter parasubject = new SqlParameter("@subject", SqlDbType.VarChar, 50);
parasubject.Value = subject;
oCommand.Parameters.Add(parasubject);SqlParameter paraissue_details = new SqlParameter("@issue_details", SqlDbType.VarChar, 500);
paraissue_details.Value = issue_details;
oCommand.Parameters.Add(paraissue_details);SqlParameter pararesponse = new SqlParameter("@response", SqlDbType.VarChar, 500);
pararesponse.Value = response;
oCommand.Parameters.Add(pararesponse);SqlParameter parasolv_date = new SqlParameter("@solv_date", SqlDbType.DateTime);
parasolv_date.Value = solv_date;
oCommand.Parameters.Add(parasolv_date);SqlParameter paraissid = new SqlParameter("@issid", SqlDbType.Int);paraissid.Direction = ParameterDirection.Output;
oCommand.Parameters.Add(paraissid);
try
{
oConnection.Open();
oCommand.ExecuteNonQuery();issid = int.Parse(paraissid.Value.ToString());
}catch (Exception oException)
{throw oException;
}
finally
{
oConnection.Close();
}
}
the stored procedure is:
create proc [dbo].[get_issid](@tech_email varchar(50), @cse_email varchar(50),@subject varchar(50),@issue_details varchar(500),@response varchar(500),@solv_date datetime, @issid int output)
as
select @issid=tech_response.issue_id from tech_response,issue_details where tech_response.tech_email=@tech_email and tech_response.cse_email=@cse_email and tech_response.subject=@subject and tech_response.issue_details=@issue_details and response=@response and solv_date=@solv_date and tech_response.issue_id=issue_details.issue_id
requested to help in this
I'm unsure how to handle an SQL Exception correctly when the database is unavailable/offline.
I have my aspx file with the C# code-behind, but all of the SQL stuff is done in a separate code file in the App_Code directory.E.g.
CODE-BEHINDDatabaseModifier.deleteUser(username);
DATABASEMODIFIER.cspublic static void deleteUser(string username){ SqlConnection conn = SqlLogin.SqlConnect; SqlCommand command = new SqlCommand("DELETE FROM <table> WHERE Username = '" + username + "'", conn); conn.Open() command.ExecuteNonQuery(); conn.Close()}
Now, that code works perfectly, however if the database I'm connecting to is offline, an SQLException is thrown and because the SQL is handled in my DatabaseModifier class, I'm not sure how to handle it correctly.If I use a Try/Catch block in my code-behind, it doesn't get thrown because the error occurs in my DatabaseModifier class. If I use a Try/Catch block in my DatabaseModifier class, what can I put in the catch block that will inform the user of the database being offline and/or how can I perform a url redirection?
Any help is greatly appreciated.
Hi All,
Currently i am working on some web application, and facing some exceptions. Actually I am throwing exception from DB functions in some of cases, and then displaying error.aspx (custom error page). It is working fine.
But when i change language of browser from english to any other language, first time my custom error page comes, but afterwards, following error page comes:
Server error in '/sampletest' applicationException of Project.module.myException is thrown.
Please help if any idea for same.
Please also let me know whether i have to kept lots of aspx files for different types of exception or messages.
Thanks In AdvanceArnold
Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs)
Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label)
Try
Catch sqlEx As SqlClient.SqlException
If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then
label2.Visible = True
label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it"
e.Cancel = True
End If
End Try
End Sub Hi, Im using vb.net sql2005 and visual studio 2005
I have 2 tables which have a foregin key relationship. When i try to delete information from within one of my aspx pages it rightly comes up with an application errror, something along the lines of
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_callScore_agentType'. The conflict occurred in database 'Merlin_####', table 'callScores', column 'typeID'.The statement has been terminated.
I have looked around and can see people talking about using a try catch excpetion however i need to know how id implement this using the detailsview1_itemdeleting event. Ive never used this before and havent found a decent tutorial to help.So far i have this code but im stuck as im not sure that this is correct but more importantly what i put in the try method. Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs)
Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label)
Try
(WHAT GOES HERE)
Catch sqlEx As SqlClient.SqlException
If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then
label2.Visible = True
label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it"
e.Cancel = True
End If
End Try
End Sub Your help would be greatly appreciated
Hi over there,I hope this question is not too simple, but I didn't manage to figure out why...I would need an explanation for following issue:I'm reading data from a database (MSSQL) and it the column "PersonBirthday" is DBNull.I wanted to prevent the error (Textbox.Text = DBNull) with an IIF. The thing is I get thistypecast exception:"Conversion from type 'DBNull' to type 'Date' is not valid." This code is NOT working, why? txtPersonBirthday.Text = IIf(IsDBNull(.Item("PersonBirthday")) =
True, String.Empty,
CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd")) When I'm using this code, which is for me obviously the same, just with an if-block it works,and I want to know why - please explain. If IsDBNull(.Item("PersonBirthday")) Then txtPersonBirthday.Text = String.Empty Else txtPersonBirthday.Text = CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd") End IfThanks in advance,cheers,uquandux If IsDBNull(.Item("PersonBirthday")) Then txtPersonBirthday.Text = String.Empty Else txtPersonBirthday.Text = CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd") End If
hi,
I am new to .net and I am trying to create a web application. I am getting following error when I am trying to build and browse a form.
[SqlException: Cannot open database requested in login 'SkyShark'. Login fails.
Login failed for user 'SHIRDIASPNET'.]
please help me out with an answer.
thanks.
siri
Hi
I am continuously getting the error:
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
Line 57: {
Line 58: Connection.Open() ;
Line 59: ThreadResult = ThreadCommand.ExecuteNonQuery() ;
Line 60: }
Line 61:
in the following part of the code :
protected void PostClick(object sender,EventArgs e)
{
SqlConnection Connection ;
SqlCommand ThreadCommand ;
SqlCommand GetThreadCommand ;
SqlCommand MessageCommand ;
SqlDataReader ThreadReader ;
string InsertThread,InsertMessage ;
string GetThread,Message ;
long ThreadID ;
int ThreadResult ;
Connection = new SqlConnection("server=HT; Integrated Security= SSPI; Database=Forums");
Feedback.Text = "" ;
InsertThread = "Insert Into Threads(ThreadName,TopicId) VALUES(@ThreadName,@TopicId)" ;
ThreadCommand = new SqlCommand(InsertThread, Connection) ;
ThreadCommand.Parameters.Add(new SqlParameter("@ThreadName", SqlDbType.VarChar,50)) ;
ThreadCommand.Parameters.Add(new SqlParameter("@TopicId", System.Data.SqlDbType.Int));
ThreadCommand.Parameters["@ThreadName"].Value = ThreadText.Text ;
ThreadCommand.Parameters["@TopicId"].Value = Request.QueryString["TopicId"] ;
try
{
Connection.Open() ;
ThreadResult = ThreadCommand.ExecuteNonQuery() ;
}
catch (System.Data.SqlClient.SqlException excp)
{
if (excp.Errors[0].Number == -105121349)
{
Feedback.Text = "<font color=red>*** There is already a thread with the name "+ThreadText.Text+". Please choose a different name and click Post.</font><br><br>" ;
}
else
{
Feedback.Text = "<font color=red>*** "+excp.Errors[0].Message +"<br><br>" ;
}
}
I would be very grateful for any suggestions.
Hello,
I have a table for which I am iterating through using a foreach loop. On each iteration, I am updating the row with information from an SQL database. During certain cycles of the loop and SQL exception is thrown which I catch and deal with appropriately. However on the next cycle of the loop the SQL exception is still active and always goes into the section which deals with the SQL exception. I have run some tests and the SQL exception should not occur on subsequent loops, but it does.
What I am trying to say is....whenever there is an SQL exception is there anyway to clear it, or set it to null or destroy the SQL exception class. (Destructor perhaps?).
Regards
Dezzy
Hi,
Im Nithyananda working on a project with SQL server 6.0
Im having a stored procedure which inserts into a table .
If I violate the primary key constraint on the table , I get a 2627 error.
I would like to replace this error with my own.Could u tell me how to do it?
Im an ORACLE guy and relatively new to SQL server.
Is it possible for me to propogate the same error message to any front end?
In my case ,Im using VC++ as front end.
Could someone also tell me the way of calling a SQL stored procedure from
VC++?
Regards
Nithyananda
Hello!I am looking for someone who has solved this multi-million people'sproblem. EVERYONE seems to ahve this problem.Im a creating a data set and populating it with a call to a store proc.Its a complex stored proc with the end result as an insert to a temptable. Then I do a select from the temp table - in the store proc.I get the following sqlException error on the following line:DataAdapterName.Fill(DataSetName, "TableName")The error is:Timeout expired. The timeout period elapsed prior to completion of theoperation or the server is not responding.My connectiong string looks like this:<add key="cnITDevWinUser" value="Data Source=server; IntegratedSecurity=SSPI; Initial Catalog=dbname; pooling=false;connectionreset=false;connection lifetime=5;min pool size=1;max poolsize=10;connection timeout=120" />I have admin rights on that db.I have set my command.timeout to 500.If i run this same code in a windows application, it works fine.If I use a DataReader with the same storeProc, it works fine.If I run this same code on a simple selec (hello world), it also worksfine.If I run this store proc in QueryAnalyzer it works fine and is donewithin 6 seconds.If I run this on a different machine it produces the same result.I am using SQL2000 with vb.net in VS2003.I have looked everywhere for the answer. I can't find it anywhere.PLEASE SOMEONE HELP.regards,Stas K.(a.k.a Sorcerdon)
View 4 Replies View RelatedIs there something like exception handling in T-SQL?For example, how to catch an error of convertion at thissample:CREATE PROCEDURE SP@param VARCHAR(50)AS BEGINDELCARE @var INT-- try {SET @var = CONVERT( int, @param)-- } catch (error#245) {-- handle an error right here-- }ENDIt must be invisible for a caller of SP if something wrong inside SP.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies View RelatedHi All
I m new to the sqlserver.In Oracle we can handle exceptions like this
declare
name varchar(20);
begin
select ename into name from emp where eno=&eno;
dbms_output.put_line(name);
exception
when no_data_found then
dbms_output.put_line('No Entry');
end;
/
We will get the message No Entry When corrsponding employee number dosent exists.
In Sqlserver how to handle these things.
Specifically my requirement is with select statement(Like above program)
Waiting for valuable suggestions
Baba
Hi everyone,
I'm trying to figure out, how I can solve following problem. I need to generate an overview of a department (parameter) of their employees which have visited a course (2nd parameter) and which haven't. For this I'm working with following tables:
- Employee Data (incl. Unit Code)
- Participant Table (one employee can have none, one or more entries - for every participation she/he gets one entry)
- Course Table (with details of the course - out of here I need to choose the course)
The participant table is merged to the course table over a course # (primary key in course table). The employee data is merged to the participant table over an employee #.
Which data I can generate until now:
- Participants of the course of the chosen department
- Employees which have never visited a course of the chosen department
and
- Employees which haven't visited the specific course but have visited an other one
---> my problem now, is to get those employees which haven't participated at the specific course but participated in an other one together with the ones which have participated. When I set a filter for the course number "NOT = 224847362" then I receive all those, which have participated in an other course - that's ok - but as mentioned before, an employee can participate in more than just one course. Thus when I make an UNION I get double entries - one for the participation and one for the "participation in an other course".
Can someone help? That would be fantastic!
Cheers,
Swisscougar
Hello,
I am trying to catch an error, but not able to. I am storing the value of @@Error soon after the statement which I believe would generate error. In order to produce the error I have deliberately used a wrong table name. So, the statement breaks but never comes to my error handling code snippet. Instead, it just throws the SQL server error message and quits. What am I doing wrong here? Here is the code snippet. The actual name of the temporary table is #TEMPO_TABLE, but in order to generate the error I have used #TEMPO_. Since this would surely error out, it should go to the label ROLLITBACK. But is not going to that label. Neither is it printing the error number as per the PRINT statement there. It just throws the SQL error and quits the execution. (Or should it NOT??). The Error that it throws it this:
Server: Msg 208, Level 16, State 1, Procedure FEED_PULL_XX, Line 157
Invalid object name '#TEMPO_'.
Can you please let me know whats going wrong here? Thanks a lot in advance.
INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_
SET @ErrNo = @@Error
PRINT '@ErrNo is ' + ltrim(str(@ErrNo)) + '.'
IF @ErrNo <> 0
Begin
PRINT '@ErrNo is ' + ltrim(str(@ErrNo)) + '.'
GOTO ROLLITBACK
End
I have connect the SQL Server 2000 database in PDA Project. But it's showing SQL Exception while connect the database.
My Connection Object details are,
m_strSqlConn = new SqlConnection("user id='sa';password='pftec';data source='dev1';persist security info=False;initial catalog='School' "
);
m_strSqlConn.Open();
Please tell the solution for this.
Thanks & Regards,
Vijayakumar J.
Dear All,
I created a sample test application to implement RDA, after I made all required configuration, I got the following exception after call rda.pull() :
{
Error # 1 of 1
Error Code: -2147024809
Message : An error has occurred on the computer running IIS. Try restarting the IIS server.
Minor sqlError.: 28022
Source : Microsoft SQL Server 2005 Mobile Edition}
Note: rda.submitQuery is executed successfully ..
any ideas
Thanks and regards
I am trying to access a stored proc from windows app (VS 2005 running on windows 2003 server).
The code is
SqlConnection conn = new SqlConnection(sCn);
SqlCommand command = new SqlCommand(sCmd, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "Location");
this.dg.DataSource = ds;
this.dg.DataMember = "Location";
I get the following exception!!!
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Internet
What am I doing wrong?
Thanks
The environment:
Machine1: IBM Records Manager 4.11(IRM) on top of WebSphere Applicaton Server 5.1.0 running on Windows Server 2003. Sql Server 2000 Client and SQL JDBC Driver with SP3 has been installed.
Machine2: SQL Server 2000 Developer Edition with SP3 is installed - Windows XP. IRM uses this remoteSQL Server database
I could login and do certain task through the web client of IBM Records Manager(IRM). But when I try perform create/edit/delete kind of operations, I get the following error at WebSphere end:
9/13/06 11:39:16:742 IST] 6c9f4838 WSRdbXaResour E DSRA0304E: XAException occurred. XAException contents and details are: The cause is : null.
[9/13/06 11:39:16:742 IST] 6c9f4838 WSRdbXaResour E DSRA0302E: XAException occurred. Error code is: UNKNOWN XA EXCEPTION CODE: 0. Exception is: [IBM][SQLServer JDBC Driver]There is Uncommitted local transaction work. Can't start an XA transaction until the local work has been committed or rolled back.
[9/13/06 11:39:16:757 IST] 6c9f4838 XATransaction E J2CA0027E: An exception occurred while invoking start on an XA Resource Adapter from dataSource recordmanager/jdbc/sqlserver/ds01, within transaction ID {XID: formatId(57415344), gtrid_length(39), bqual_length(28), data(00000000000001dc0000000148c3909b0b45226df78cc013b7f774854a82fdaa7365727665723148c3909b0b45226df78cc013b7f774854a82fdaa0000000105c83d07)}: javax.transaction.xa.XAException: [IBM][SQLServer JDBC Driver]There is Uncommitted local transaction work. Can't start an XA transaction until the local work has been committed or rolled back.
at com.ibm.websphere.jdbcx.base.BaseXAResource.start(Unknown Source)
at com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl.start(WSRdbXaResourceImpl.java:927)
at com.ibm.ejs.j2c.XATransactionWrapper.start(XATransactionWrapper.java:1267)
at com.ibm.ws.Transaction.JTA.JTAResourceBase.start(JTAResourceBase.java:164)
at com.ibm.ws.Transaction.JTA.RegisteredResources.startRes(RegisteredResources.java:389)
at com.ibm.ws.Transaction.JTA.TransactionImpl.enlistResource(TransactionImpl.java:1903)
at com.ibm.ws.Transaction.JTA.TranManagerSet.enlist(TranManagerSet.java:494)
at com.ibm.ejs.j2c.XATransactionWrapper.enlist(XATransactionWrapper.java:602)
at com.ibm.ejs.j2c.ConnectionEventListener.interactionPending(ConnectionEventListener.java:745)
at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.processInteractionPendingEvent(WSRdbManagedConnectionImpl.java:1446)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.beginTransactionIfNecessary(WSJdbcConnection.java:329)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareCall(WSJdbcConnection.java:1342)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareCall(WSJdbcConnection.java:1292)
at com.ibm.gre.engine.dao.sqlserver.UtilitySQLDAO.getExtendedAttributesById(UtilitySQLDAO.java:108)
at com.ibm.gre.engine.ejb.business.fileplandesign.RelationshipDefinitionEJBBean.getListByFPViewID(RelationshipDefinitionEJBBean.java:236)
at com.ibm.gre.engine.ejb.business.fileplandesign.EJSLocalStatelessRelationshipDefinitionEJB_eb4df5e6.getListByFPViewID(EJSLocalStatelessRelationshipDefinitionEJB_eb4df5e6.java:151)
at com.ibm.gre.engine.ejb.facade.FilePlanDesignControllerEJBBean.getRelationshipDefinitionListByFPViewID(FilePlanDesignControllerEJBBean.java:189)
at com.ibm.gre.engine.ejb.facade.EJSRemoteStatefulFilePlanDesignControllerEJB_e23bb402.getRelationshipDefinitionListByFPViewID(EJSRemoteStatefulFilePlanDesignControllerEJB_e23bb402.java:239)
at sun.reflect.GeneratedMethodAccessor122.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))
at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
at com.ibm.rmi.util.ProxyUtil$4.run(ProxyUtil.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at com.ibm.rmi.util.ProxyUtil.invokeWithClassLoaders(ProxyUtil.java:701)
at com.ibm.CORBA.iiop.ClientDelegate.invoke(ClientDelegate.java:1084)
at $Proxy5.getRelationshipDefinitionListByFPViewID(Unknown Source)
at com.ibm.gre.engine.ejb.facade._FilePlanDesignControllerEJB_Stub.getRelationshipDefinitionListByFPViewID(_FilePlanDesignControllerEJB_Stub.java:745)
at com.ibm.gre.webclient.businesslayer.ejb.EJBCallAdapter.viewRelationshipDefinitionList(EJBCallAdapter.java:3807)
at com.ibm.gre.webclient.fileplandesign.views.actions.ViewEditAction.greExecute(ViewEditAction.java:135)
at com.ibm.gre.webclient.common.CommonAction.greExecute(CommonAction.java:642)
at com.ibm.gre.webclient.common.CommonAction.execute(CommonAction.java:341)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at com.ibm.gre.webclient.security.plugin.SecurityRequestProcessor.process(SecurityRequestProcessor.java:58)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:974)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:555)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:200)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:276)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:186)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:439)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)
I followed the instructions from one of the article about Microsoft SQL Server 2005 JDBC Driver 1.0 (Note I'm using Microsoft SQL Server 2000 and this driver is compatible with SQL Server 2000): copy sqljdbc_xa.dll to SQL Binn directory and execute the script xa_install.sql. And XA Transaction is enabled on the SQL Server 2000 running machine.
Even after have finished all the steps I still get the same error.
Pls help me on this problem.
Thanks in advance
N.Yogendran
Hi all:
We are using a simple clr function in sql server, which splits input strings by a given delimiter. The code is in €œclr_sql.cs€?. And I wrote a simple stored proc in SQL Server 2005 which calls this function(storedproc.slq). Then I used a test application, which creates 80 threads to call this stored proc continuously. The client code is in client.cs. The TPS can reach to 2000+ in my test. Unfortunately, exception occurs sometimes (about 0.01%). Most of the exceptions are:
System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
And there are a few others:
System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
I wrote a dummy T-Sql stored proc, which returns all the rows from an existing table (60 rows, the same dataset as the test above) directly. It€™s much faster than the clr one, and no exception occurs. And I also wrote a T-Sql stored proc which doing the same task as the clr one and is slower. It doesn€™t fail, neither.
To summarize my problem: exception occurs when using clr sql, but it vanish when using T-SQL. But we need CLR SQL because of the performance is better.
I€™m looking forward to hear from you. Thanks in advanceJ
/* clr sql.cs */
public class KspSplitString
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable InitMethod(String input, char delimiter)
{
if (null == input)
return new string[] { };
return input.Split(new char[] { delimiter });
}
public static void FillRow(Object obj, out SqlString Term)
{
Term = (obj as string).Trim();
}
}
/* storedproc.sql */
ALTER proc [dbo].[spSplitString2] (@keywords nvarchar(max), @delimiter nchar)
as
set nocount on
select * from dbo.fnKspSplitString(@keywords, @delimiter)
/* client.cs */
SqlConnection conn;
public override void Setup()
{
conn = new SqlConnection(connectionString);
conn.Open();
}
public override void Cleanup()
{
conn.Close();
}
public override void Run()
{
try
{
using (SqlCommand command = new SqlCommand(storedproc, conn))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = sqlCommandTimeout;
SqlParameter sqlParameter = command.Parameters.Add("@Keywords", SqlDbType.NVarChar);
sqlParameter.Value = keywords;
sqlParameter = command.Parameters.Add("@Delimiter", SqlDbType.NChar);
sqlParameter.Value = delimiter;
using (SqlDataReader reader = command.ExecuteReader())
{
tblResult = new DataTable("RESULT");
tblResult.Load(reader);
}
}
}
catch (Exception ex)
{
Cleanup();
Setup();
}
}
Hi,
I have followed the technical artical "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual Basic.NET" to create a sample application.
But when I run debug mode and get an exception unexpected as following as doing rad.pull :
Immediate Window:
A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll
Message Box
[Contacts]
Code:
Private Sub RADPullButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RADPullButton.Click
Try
VerifyDatabaseExists()
Dim rda As SqlCeRemoteDataAccess
rda = GetRDAObject()
rda.Pull("Contacts", _
"SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers", _
My.Settings.ServerOleDBNorthwindConnectionString, _
RdaTrackOption.TrackingOnWithIndexes, _
"Contacts_Errors")
Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
Windows.Forms.Cursor.Current = Cursors.Default
End Try
End Sub
Thanks in advance,
JD