I have a cursor that loops through some records.
Upon an exception being caught (i.e. when inserting a record, it violates integrity constraint), I would like to do my normal exception handling routine and then continue with the loop to the next record.
Is this possible? If so, how do I implement this behavior?
Thanks.
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
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,
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
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
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 need help please....
I am using exception handling and Transaction handling combined.
The code is written to delete a record from 2 different table.
The purpose of using exception and transaction handling combined is to
make sure that the record is deleted in BOTH table. If there is error
while deleting a record from one of the tables, it will rollback the
database to the original and will stop deleting any record.
However, The Transaction handling is not working. When error occurs
while deleting a record on table A, the code still delete a record from
one table B. The transaction.RollBack() doesn't work. This is my code:
Try
Conn
= New
SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Conn.Open()
Trans = sConn.BeginTransaction
For Each row In GridView1.Rows
Dim sql As String = "SELECT * FROM Member
Using Cmd As New SqlCommand(sql, Conn, Trans)
SqlDataSource1.DeleteParameters("MemberID").DefaultValue =
DropDownListMember.SelectedValue
SqlDataSource1.Delete()
SqlDataSource2.DeleteParameters("CarID").DefaultValue =
DropDownListCar.SelectedValue
SqlDataSource2.Delete()
End Using
End If
Next
sTrans.Commit()
Label1.Text = "deleted."
Catch ex As Exception
If sTrans IsNot Nothing Then
sTrans.Rollback()
End If
Label1.Text = "error"
Return
Finally
If sConn IsNot Nothing Then
sConn.Close()
End If
End Try
soo much thanks in advanced!!!
Hi guys,
Does any one know how to detect when a SQL transaction has been rolled back in either a windows application or ASP.NET. My Transactions always run but when they are rolled back Visual Basic does not pick up any errors in the 'Try Catch SqlException'. Does any one know a way round this. Sorry for the lack of code. Im writing this post on a friends PC but i wil put up my code as soon as possible. Thanks in advance
Matt
Hi,
I'm trying to do exception handling in a stored procedure, such as IF (@@ERROR <> 0) execute and insert on to an error log table. I have set the volatile @@ERROR global to a variable, but the proc is still throwing a primary key exception; hence, it's not being trapped. My question is why isn't my exception being handled?
Here's my code, your help is much obliged. (my error proc is below the caller)
CREATE PROCEDURE [dbo].[INSERT_STATS_NO_GROUP_PROC]
@stat_type_id int,
@stat_delimited_file_id smallint,
@time_interval datetime,
@call_volume int,
@Err int OUTPUT
AS
SET @Err = 0
DECLARE @Error int
BEGIN TRANSACTION
INSERT INTO DAILY_SUMMARY_STATISTICS
(
stat_type_id,
stat_delimited_file_id,
ssda_customer_id,
time_interval,
call_volume
)
VALUES
(
@stat_type_id,
@stat_delimited_file_id,
NULL,
@time_interval,
@call_volume
)
SET @Error = @@ERROR
IF(@Error <> 0)
BEGIN
GOTO abort
END
COMMIT TRANSACTION
abort:
ROLLBACK TRANSACTION
EXECUTE SET_ERROR_TO_LOG_PROC @SQLErrorID = @Error,@ObjectName = 'INSERT_STATS_NO_GROUP_PROC'
SET @Err = @Error
/*ERROR PROC*/
CREATE PROCEDURE [dbo].[SET_ERROR_TO_LOG_PROC]
@SQLErrorID int,
@ObjectName varchar(50)
AS
DECLARE @SQLErrDescription varchar(200)
SELECT @SQLErrDescription = description FROM master..sysmessages WHERE error = @SQLErrorID
BEGIN TRANSACTION
INSERT INTO [dbo].[OSAUTOREPORTS_ERROR_LOG]
(
error_id,
error_description,
object_name
)
VALUES
(
@SQLErrorID,
@SQLErrDescription,
@ObjectName
)
COMMIT TRANSACTION
Thanks Russ
can I handle exception in SQL functions? how??
I am tring to migrate the Database from Oracle 9i to SQL server 2005 with SSMA which converts function in one procedure and wrapper function because of exception handling in Oracle.
in my package i'm using a for each loop container in order to process all files in a certain folder. everything works fine but of course when i start the service and there are no files available the package waits. is there a way to simply say skip the for each loop or semething like that?
View 2 Replies View RelatedI am trying to handle exceptions using try catch with remote database.
I am writing the following code which works fine if login locally but when I am trying to do the same on linked server its not being caught in try..catch block.
The procedure I am writing to raiserror
create proc CustomError
as
RAISERROR ('db error', 16, 1);
I am calling the above procedure in local database using following code
BEGIN TRY
exec CustomError
END TRY
BEGIN CATCH
select ERROR_MESSAGE() as ERROR_MESSAGE;
END CATCH;
GO
and it works perfectly. I am able to catch the error in catch block.
and i m getting the following result
db error
but when i am trying to do the same on linked server the code doesn't take me to catch block. I am getting following error while executing the code
go
BEGIN TRY
exec [192.168.0.50].[BM].dbo.CustomError
END TRY
BEGIN CATCH
select ERROR_MESSAGE();
END CATCH;
GO
where [192.168.0.50] is a linked server name and BM is the database name and custom error is my sp which is on remote server.
its giving me following error
Msg 50000, Level 16, State 1, Procedure CustomError, Line 11
db error
I'm creating a class that will wrap a SqlException and will offer the developer a way of determining how to proceed in handling the exception by offering a suggestion of retrying, revalidating the data, and/or calling the entire process a no go.
One of the ways i've come up with to accomplish this is by using the severity (SqlException.Class) to assist in making a suggestion to the developer.
I'm hoping to get some feedback from the developers who use this forum if they feel severity is or is not the way to go, and also to offer any other ways of accomplishing that they can come up with.
Thanx
AJ
I'm trying to get to grips with the formview control and have a problem regarding where an exception is raised if SQL server raises an error when an insert is taking place. I am new to both asp.net & c#
I have put the following code in the SqlDataSource2_Inserted & Formview1_Inserted events as a way of determining where the exception is raised:
if (e.ExceptionHandled){ string exceptionmessage = e.Exception.Message; lblStatus.Text = exceptionmessage;}
These two events are the only one's that have the ExceptionHandled and Exception properties.
I run the code with data that will raise a duplicate key error from SQL Server but the error is not being caught by either of these two handlers. All I get is a page that starts with
Server Error in '/SourceCode' Application.
Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated.
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: Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated....
Is there somewhere else I should be looking to handle this sort of thing? I thought using the databound controls would make life easier but I'm actually finding it more of a pain than coding everything myself.
I have a For Each Loop which selects files with a certain naming convention, performs a data transformation on each one, loading it into a SQL Server db.
I want to add error handling so that if a file fails processing at any step of the loop, error handling moves the file to a new directory and picks up the next file and continues processing.
I tried playing around with error handling at the For Each Loop level. But when the process failed on a file (with an incorrect format), the file couldn't be moved because it was "locked by another process."
Is this possible within a For Each Loop? If not, what other approach could I take?
Hopefully this is an easy question:
Inside of a for each loop (looping through an ADO record set of objects to import) I have a data flow task (along with many other processes).... if the dataflow task suceeds I log success in a table. If it errors I want it to fail the dataflow task (which will fire off my Event Handler for that data flow and log the failure, email etc) BUT I want it to continue the loop - I can't seem to figure out how to get the data flow object not to fail the whole loop. If any other objects inside the foreach, other than the data flow, fail I would like the whole loop to fail. Also if possible (but not a requirement) I would like it to have a threshold where if the data flow fails X variable times it will fail the package.
I am having difficulty how to not fail the loop when the import data fails..... just looking for a simple "on error next" type logic for that specific object in the foreach but not the rest. Thanks in advance for the help/advice.
I am building a generic job site and well I have hit a speed bump. I need to store resumes in the database to be searched on. Well what is the best way to store these full text resumes? so that they can be easily searched on?
SQL 2005
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
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies View RelatedI have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.
I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.
Any solution to this? anyone experienced anything similar
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,
2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.
Thanks in advance.
I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...
My Lookp table is like the following...
Srn srctable desttable
1 SRC1 DEST1
2 SRC2 DEST2
3 SRC3 DEST3
Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....
How do I run the pacakge for each of the rows... ..............................
Hi,
I am executing script like this. How to check for the errors if "master..xp_cmdshell @bcpCommand" fails. Is there any way to verify that BCP is completed successfully
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'E:TestBCPOut.txt'
SET @bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -U -P'
EXEC master..xp_cmdshell @bcpCommand
Thanks in Advance,
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 Related