Hi friends
i've a stored proc (sql 2005) that'll raiseerror statement when something violated.
but my C# application that calls this stored proc does not any throw exception when this happens !!
i remember visual basic used to through an exception for this type of things.
is it different in C# and how do we handle this scenario ?
Thanks for ur ideas.
I am calling a stored procedure that looks like this.
Code:
ALTER PROCEDURE [dbo].[VerifyLogin]
-- Add the parameters for the stored procedure here
@Email nvarchar (100),
@Password nvarchar (200)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Count int
DECLARE @Accounts int
SET @Accounts = (Select Count(*) from Users Where UPPER(Email) = UPPER(@Email))
IF @Accounts = 0
BEGIN
RAISERROR('E-mail address not found.', 10, 1)
END
ELSE
BEGIN
SET @Count = (Select Count(*) from Users Where UPPER(Email) = UPPER(@Email) and Password = @Password)
IF @Count > 0
select 1 as Verification
ELSE
select 0 as Verification
END
END
When I enter a login that does not exist, the error is not thrown in .net. Tracing the code shows normal execution. What am I doing wrong?
I have an issue with the error message that is being raised in a stored procedure:
RAISERROR('Error inserting Value %d into Order_Header_Reference_Code_XREF for Order_Header_ID %d and Reference_Code_ID %d', 10, 1, @Value, @OrderHeaderID, @ReferenceCodeID)
Here is the result after executing my procedure with "@Value = 'test'"
Error inserting Value 114994016 into Order_Header_Reference_Code_XREF for Order_Header_ID 10801 and Reference_Code_ID 1
114994016 should say 'test' right?
Does anyone see an issue with my raiseerror statement?
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 need the T-SQL statement on how to raise a custom error message for a check contraint @au_id '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9]' when ever a client enters the wrong SSN format such as 575-444865. I used the t-sql statement below but does not work when running an insert on the authors table ; au_id column.
CREATE TRIGGER trg_datavalidate
ON authors
FOR INSERT
AS
DECLARE @au_id varchar(11)
DECLARE @err INT
SELECT @err = @@ERROR
from inserted
IF @err = 547
BEGIN
PRINT 'Au_id needs to be in the SSN format xxx-xx-xxxx!. Error Number:'
+ CAST(@err AS VARCHAR)
RAISERROR ('au_id must be in the format of xxx-xxxxxx where x is a number.', -- Message text.
16, -- Severity.
1 -- State.
);
ROLLBACK TRANSACTION
END
I'm writing:
View 7 Replies View RelatedI want to execute some insert statements within a stored procedure and commit those changes regardless of any raiseerror that occurs later in the stored procedure. My difficulty is that I am forced to use raiseerror with severity 16 in order to send a message through the powerbuilder application interface (compiled vendor code). I have tried save points but can't get that to save my insert and still present an error to the user about something else that happens later.
here is an example
BEGIN procedure
Insert something and save it even if error is raised below
RaiseError('you made a mistake and need to do this.',16,-1)
END procedure
Will raiserror within a procedure make a auto rollback
I have a SP like
Create procedure test
as
begin tran
insert into test values (1,'one')
raiseerror('%s',16,1,'Value Inserted')
commit tran
will the above SP make the insertion or not?.if not,pls tell me how to make the value insert into the table still
raiserror is needed.
Thanks in advance
HI, I have a lookup that find a specific row. If that row does not exist, I need to create a new one. If it exists, I need to raise an error and trap it with the "on_error" event of the dataflow in order to log it. Is there a way to raise an error and specify the error message from the dataflow? I was thinking using the conditional split and verify if the value returned from the lookup (by set the error config to ignore the error) is not or not. But how can I raise an error when the value is not null?
Thank you,
Ccote
I can't believe I can't find this in books online...
I have a transformation script component and I have a case where I want the object to completely fail if it is encountered. How do I code that? All my searching BOL could find is how to redirect rows to an error output which is not what I need in this case.... I'd think it would be something simple like Me.RaiseError("Something really bad happened") but I haven't found it ...
I was asked if you can do "raise error" from the server side. I can do it from the client side but never had to do it server side. Is it possible at all, I can't find any documents on it. I'm using 6.5, and 7.0....
View 1 Replies View RelatedHello,
We are migrating the SP from 2000 -> 2005, in my previous verriosn they are using WITH LOG option to store the error messges in to the Applicatin log and System log.When migrated to 2005 and I tried to run that i got error messgae like "Only System Administrator can specify WITH LOG option for RAISERROR command.".
Is there any possbility that without granting admin access to the developers that we can run this stored proceedure.
My DBAs dont want to provide the admin access to all developers to execute this SPs is there any work around for this problem.
please help me out.
suggest me if there any other ways to handle this situation.
I hope i will be answered by some one.
Thanks
Thanks
-----------------
Ram MCP
cos RAISERROR isnt callable, but I really want to indicate a non-fatal warning to be passed back to the client (C++ code)
View 2 Replies View RelatedI am in process of building a website where the user can upload files and then those files are loaded in to a sql server database. I am using some sprocs to scrub the data and then insert them into the production database.. And in my sprpc before and after updating or inserting a record or scrubing... i am returning the count by raising an error. or returning the rownumber where the error occured.. is there any way i can get the raise error part or whatever error i get while scrubing the data and relay it back to the user in a user freindly way or in a text file... or the best thing is can i open smalll window where i can show them what processing is goin on and alert them if there are any errors...
Any help will be appreciated.
Regards
Karen
Hey guys,
I created a script task in my package that checks for filenames of files that are to be processed by the package.
for example, File.txt contains a header row of "File.txt|03252006"
what my script should do is to check whether or not the actual filename matches the header record inside..
If File.txt has a header record of "readme.txt|03252006" then the script should raise an alert or notification but not fail the package..
I am lost here.. I have no idea how to access the SSIS package's events/alerts from within my script task..
my code looks something like this:
'Get Filename from File Connection String
Dim strFile_Name As String = strFile_Conn.Substring(strFile_Conn.LastIndexOf("") + 1, strFile_Conn.Length - (strFile_Conn.LastIndexOf("") + 1))
'Get Filename from header
Dim ReadHeader As StreamReader = New StreamReader(strHeader)
Dim HeaderFileName As String = ReadHeader.ReadLine()
ReadHeader.Close()
HeaderFileName = HeaderFileName.Substring(0, HeaderFileName.IndexOf("|"))
If strFile_Name <> HeaderFileName Then
<I don't know what to put here I should raise an alert or notification that the file identification failed.>
End If
Thanks in advance guys, I hope someone would be able to help me
Kervy
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
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?
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