Error In SQL Statement

Hi, I m Trying TO use A sql insert Query but it showing an error

i m trying to insert value in Filed Name PNR from Str.text and Coresspond Field Name PNR1 valuse is 1 less than from pnr.text and PNR1 is a Auto Number Field

my code for insert query is

Dim q1As OleDb.OleDbCommand =New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)

and Error is Shown by browser is as follows

Server Error in '/WebApplication1' Application.

---

Missing semicolon (;) at end of SQL statement.

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.OleDb.OleDbException: Missing semicolon (;) at end of SQL statement.

Source Error:

Line 466: con.Open()Line 467: Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)Line 468: q1.ExecuteNonQuery()Line 469: con.Close()Line 470: End Sub

Source File:C:\Inetpub\wwwroot\WebApplication1\2.aspx.vb Line:468

Stack Trace:

[OleDbException (0x80040e14): Missing semicolon (;) at end of SQL statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66 WebApplication1._2.Button2_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\WebApplication1\2.aspx.vb:468 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277

---

Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

I have to ask this, even though it seems so simple. Since the error says the query is missing a semi-colon at the end of the SQL statement, did you add a semi-colon and test it?

Jeff

|||

Is this SQL Server or Access?

In any case, your syntax is incorrect. Using a WHERE clause on an INSERT statement is invalid.

Also, use Parameters instead of concatenating UI-supplied text to SQL statements which will be executed.

|||

Try modify you q1 definition.

does you pnr contain number and you would like to subtract 1 from it?

you have to cast you pnr. to integer next subtract 1 and next convert result to string and insert it into query or do it this way:

Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1=(" & pnr.Text & "-1) ",

Thanks

Error Handling SQL 2000

Hello,

Could it be possible to catch into a variable any type of sql server error?

I have an stored procedured that executes as a part of a cycle one stored procedured many times (one for each branch). I need to make the stored procedured to continue even when I have an error in one of the executions (one of the procedures of a branch).

For example, I execute this as a part of a While Statement

Exec @.return_status = @.sp_name @.Link, @.Historia, @.begindate, @.enddate

If @.return_status <> 0
Begin Print ' /* Exito */'
..... update

End
Else
Begin Print '/* Error */'
....
EXEC master.dbo.xp_sendmail
@.recipients = @.mail_recipient,
@.subject = @.mail_subject,
@.message = @.mail_query

End

... But when I get an error like this, the stored just ends the cycle and the stored procedured.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Authentication failed.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Wish you can help me, thank you

I wish I could help you too. In SqL Server 2000 the only thing you can do is check the value of the error number in the @.@.error system function (used like a variable). So, you may be able to do something like this:

Exec @.return_status = @.sp_name @.Link, @.Historia, @.begindate, @.enddate

If @.@.error <> 0 or @.return_status <> 0

But it could stop the procedure, and it will return the error to the client for handling. In 2005 you have TRY...CATCH, but even that isn't flawless with some errors. For an excellent reference on 2000 error handling, check this article: http://www.sommarskog.se/error-handling-II.html

|||

and don't forget the first in the series:

http://www.sommarskog.se/error-handling-I.html

---

http://www.elsasoft.org

|||Thank you Louis