Which Is More Expensive? Query Or Exception
Jul 25, 2005
I have a scheduling application that I created that allows users to select several microscopes from a multiple select list and then select users from a datagrid and change their permissions. ie it will add a record to a table that allows them to use the selected microscopes or it will delete the record from the table if it already exists since the table is only two columns and both make the primary key.So right now I'm just deleting the records when a duplicate primary key exception is thrown (although I'm not sure how to return from the catch and continue the rest of my loops to continue adding or deleting permissions.Alternatively I could check the database before executing the insert or delete command and see if that record already exists and avoid the catch/return scenerio. But which is more intensive on the server? Is there a better way to do this that I haven't thought of? Thanks~Cattrah~
View 6 Replies
ADVERTISEMENT
Jul 23, 2005
I need some tips to boost the performance on the following query.The problem is that it times out once in a while, and then again runsnormally in most cases.The clue is to compare a textual value stored as an image data type, but Idon't know if the cast operation could be done in another way.Any tips?SELECT DISTINCT pk_product FROM tbl_product PJOIN tbl_product_content PC ON P.pk_product = PC.fk_productJOIN tbl_content C ON PC.fk_content = C.pk_contentJOIN tbl_content_rel REL ON C.pk_content = REL.fk_contentJOIN tbl_filegroup FG ON REL.fk_filegroup = FG.pk_filegroupJOIN tbl_filegroup_file FF ON fg.pk_filegroup = FF.fk_filegroupJOIN tbl_file F ON ff.fk_file = F.pk_fileWHEREP.fk_product_type = 45 ANDCAST(CAST(f.data AS VARBINARY) AS NVARCHAR(20)) = '1234'/ted
View 1 Replies
View Related
Jan 16, 2008
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
View 1 Replies
View Related
Feb 2, 2004
Hi,
i am experiencing SQl write performance problems on a very shiny server. Got data files on a Raid 1+0, log files on a separate drive, all SCSI, Win2003 server, 6G RAM, 2 Xeon processors. I've created a small benchmarking program and run it on my desktop pc and this 'big' server. Here are the results:
Desktop: SQL server inserts: 78 Seconds, Direct writes to the harddisk(Just write a string to the file 10000 times): 13 seconds
SQLServer: SQL server inserts: 422 Seconds, Direct writes to the harddisk: 16 seconds
So, for some reason, my 'shiny' machine is 6 times slower on writes than my desktop. When i tried comparing the select performance, my shiny server is 10 times faster than my desktop.
Initially i had Raid5 on my server and it had poorer direct write performance but now, direct writes seem to be ok, so, i recon this is a problem related to SQL server.
What can i do to improve the insert performance?
Thanks in advance
View 8 Replies
View Related
Sep 20, 2006
I had an idea to put all my web design settings, css text and web content in the database..This way it would be easy for others to edit remotely. Do you guys think this would have an impact on performance if I do this?
View 7 Replies
View Related
Apr 18, 2008
I've create a bunch of views to expose a logical model of the underlying database of an application server.
To enforce the security control, I've also created a CLR UDF to call the application server's API for security check and audit log.
For example, we have a table, tblSecret, and the view, vwSecret, is,
SELECT
Id,
ParentId,
Description,
SecretData
FROM tblSecret
WHERE udfExpensiveApiCall(Id) = 1
The udfExpensiveApiCall will return 1 if the current user is allowed to access the SecretData else 0. The CLR UDF call is very expensive in terms of execution time and resources required.
Currently, there are millions rows in the tblSecret.
My objective is to tune the view such that when the view is JOINed, the udfExpensiveApiCall will be called the least number of time.
SELECT
ParentId,
SecertData
FROM vwParent
LEFT JOIN vwSecret ON vwSecret.ParentId = vwParent.ParentId
WHERE vwParent.StartDate > '1/1/2008'
AND vwSecret.Description LIKE '%WHATEVER%'
Is there any way to specify the execution cost of the CLR UDF, udfExpensiveApiCall, such that the execution plan will call the UDF while it is absolutely necessary?
Is there any query hint will help?
Any recommendation?
Thanks,
Simon Chan
View 1 Replies
View Related
Aug 10, 2007
I'm relatively new to compact framework and SQL Server Compact so bear with me if I've got an obvious thing I've forgotten.
I've written my own database helper layer. My idea is to generate SQL Insert statements dynamically based on what is in the contents of each object. Peformance however is horrible. I try to do 1800 inserts and it takes about 50 seconds on the device (Release Build, outside of the IDE).
I pass in a list of objects to be inserted which derive from a ModelBase class. ModelBase includes some ORM information (what table the object goes into. what fields are mapped to which properties). I generate one SQLCeCommand object, one sql string (new params for each insert), and am using SqlCeResultResultSets.
What can I do to make this run faster? Thank you
Code Snippet
public bool Insert(List<ModelBase> recs)
{
SqlCeConnection con = new SqlCeConnection(connectionString);
try
{
con.Open();
con.BeginTransaction();
}
catch
{
return false;
}
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = con;
String sqlString = "INSERT INTO [" + recs[0].tableName + "] (";
String sqlString2 = ") VALUES (";
PropertyInfo[] props = recs[0].GetType().GetProperties();
for (int x = 0; x < props.Length; x++)
{
PropertyInfo pi = props[x];
if (recs[0].fieldMap.ContainsKey(pi.Name))
{
sqlString += "[" + recs[0].fieldMap[pi.Name] + "]";
sqlString2 += "@" + pi.Name;
sqlString += ", ";
sqlString2 += ", ";
}
}
sqlString = sqlString.Substring(0, sqlString.LastIndexOf(", "));
sqlString2 = sqlString2.Substring(0, sqlString2.LastIndexOf(", "));
sqlString += sqlString2 + ")";
cmd.CommandText = sqlString;
cmd.CommandType = CommandType.Text;
foreach (ModelBase rec in recs)
{
cmd.Parameters.Clear();
for (int x = 0; x < props.Length; x++)
{
PropertyInfo pi = props[x];
if (rec.fieldMap.ContainsKey((pi.Name)))
{
if (pi.GetValue(rec, null) == null)
cmd.Parameters.AddWithValue("@" + pi.Name, DBNull.Value);
else
cmd.Parameters.AddWithValue("@" + pi.Name, pi.GetValue(rec, null));
}
}
try
{
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.None);
}
catch(Exception e)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
Program.log.Error(e.Message,e);
return false;
}
}
con.Close();
return true;
}
View 7 Replies
View Related
Apr 22, 2015
How do I find out top expensive queries from SQL Server 2008 – Standard edition ?
View 9 Replies
View Related
Jan 31, 2007
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
View 2 Replies
View Related
Jun 29, 2006
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?
View 2 Replies
View Related
Nov 19, 2006
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
View 1 Replies
View Related
May 25, 2007
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 Related
Jul 23, 2007
I 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
View 16 Replies
View Related
Aug 30, 2007
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!
View 13 Replies
View Related
Jun 9, 2008
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 Related
Aug 17, 2000
We 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 Related
Jul 14, 2004
Hi 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
View 3 Replies
View Related
Mar 12, 2007
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.
View 1 Replies
View Related
Apr 6, 2007
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?
View 3 Replies
View Related
Jul 13, 2007
Does anyone have a code snippet for dumping the actual sql a command would be running?
View 3 Replies
View Related
Sep 7, 2007
it 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
View 2 Replies
View Related
Nov 14, 2007
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.
View 3 Replies
View Related
Nov 21, 2007
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
View 1 Replies
View Related
Mar 25, 2008
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
View 12 Replies
View Related
May 29, 2008
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
View 3 Replies
View Related
Apr 11, 2004
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
View 2 Replies
View Related
Jan 28, 2005
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.
View 5 Replies
View Related
Mar 4, 2005
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
View 1 Replies
View Related
Apr 18, 1999
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
View 1 Replies
View Related
May 18, 2006
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 Related
Jul 20, 2005
Is 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 Related
Aug 22, 2007
Hi 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
View 4 Replies
View Related
Jan 8, 2008
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
View 7 Replies
View Related