Download 100% FREE Office Document APIs for .NET
DotNetFunda.com
Online: 837
Home > Forums > ASP.NET >

Incorrect syntax near 'nvarchar'. Using FormView in asp.net

Posted by Mandlaa under ASP.NET on 11/7/2013 | Points: 10 | Views : 12270 | Status : [Member] | Replies : 10
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
I want to update data fromdatabase using FormView,

Getting following error


ERROR:

Incorrect syntax near 'nvarchar'.

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: Incorrect syntax near 'nvarchar'.

Source Error:

MYTABLE:

USE [users]
GO

/****** Object: Table [dbo].[Payment5] Script Date: 11/07/2013 16:04:02 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Payment5](
[BusinessName] [nvarchar](500) NULL,
[Address] [nvarchar](500) NULL,
[Order date] [nvarchar](200) NULL,
[Plan] [nvarchar](500) NULL,
[NFC Tablets] [nvarchar](500) NULL,
[First Payment] [nvarchar](500) NULL,
[Due On] [nvarchar](500) NOT NULL,
[Subsequent Monthly Payment] [nvarchar](500) NULL,
[Due On Every Month] [nvarchar](500) NULL,
[ClientId] [nvarchar](50) NULL,
CONSTRAINT [PrimaryKey_1de9522e-ac4b-45b5-91e1-feaa4f4413de] PRIMARY KEY CLUSTERED
(
[Due On] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

ASPXPAGE CODE:



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Mandlaa on: 11/7/2013 [Member] Starter | Points: 25

Up
0
Down 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm3.aspx.cs" Inherits="WebApplication9.WebForm3" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:stgdbConnectionString %>" DeleteCommand="DELETE FROM [Payment5] WHERE [Due On] = @Due_On" InsertCommand="INSERT INTO [Payment5] ([BusinessName], [Address], [Order date], [Plan], [NFC Tablets], [First Payment], [Due On], [Subsequent Monthly Payment], [Due On Every Month], [ClientId]) VALUES (@BusinessName, @Address, @Order_date, @Plan, @NFC_Tablets, @First_Payment, @Due_On, @Subsequent_Monthly_Payment, @Due_On_Every_Month, @ClientId)" SelectCommand="SELECT * FROM [Payment5]" UpdateCommand="UPDATE [Payment5] SET [BusinessName] = @BusinessName, [Address] = @Address, [Order date] = @Order_date, [Plan] = @Plan, [NFC Tablets] = @NFC_Tablets, [First Payment] = @First_Payment, [Subsequent Monthly Payment] = @Subsequent_Monthly_Payment, [Due On Every Month] = @Due_On_Every_Month, [ClientId] = @ClientId WHERE [Due On] = @Due_On">
<DeleteParameters>
<asp:Parameter Name="Due_On" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="BusinessName" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="Order_date" Type="String" />
<asp:Parameter Name="Plan" Type="String" />
<asp:Parameter Name="NFC_Tablets" Type="String" />
<asp:Parameter Name="First_Payment" Type="String" />
<asp:Parameter Name="Due_On" Type="String" />
<asp:Parameter Name="Subsequent_Monthly_Payment" Type="String" />
<asp:Parameter Name="Due_On_Every_Month" Type="String" />
<asp:Parameter Name="ClientId" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="BusinessName" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="Order_date" Type="String" />
<asp:Parameter Name="Plan" Type="String" />
<asp:Parameter Name="NFC_Tablets" Type="String" />
<asp:Parameter Name="First_Payment" Type="String" />
<asp:Parameter Name="Subsequent_Monthly_Payment" Type="String" />
<asp:Parameter Name="Due_On_Every_Month" Type="String" />
<asp:Parameter Name="ClientId" Type="String" />
<asp:Parameter Name="Due_On" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>

</div>
<asp:FormView ID="FormView1" runat="server" DataKeyNames="Due On" DataSourceID="SqlDataSource1">
<EditItemTemplate>
BusinessName:
<asp:TextBox ID="BusinessNameTextBox" runat="server" Text='<%# Bind("BusinessName") %>' />
<br />
Address:
<asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />
<br />
Order date:
<asp:TextBox ID="Order_dateTextBox" runat="server" Text='<%# Bind("[Order date]") %>' />
<br />
Plan:
<asp:TextBox ID="PlanTextBox" runat="server" Text='<%# Bind("Plan") %>' />
<br />
NFC Tablets:
<asp:TextBox ID="NFC_TabletsTextBox" runat="server" Text='<%# Bind("[NFC Tablets]") %>' />
<br />
First Payment:
<asp:TextBox ID="First_PaymentTextBox" runat="server" Text='<%# Bind("[First Payment]") %>' />
<br />
Due On:
<asp:Label ID="Due_OnLabel1" runat="server" Text='<%# Eval("[Due On]") %>' />
<br />
Subsequent Monthly Payment:
<asp:TextBox ID="Subsequent_Monthly_PaymentTextBox" runat="server" Text='<%# Bind("[Subsequent Monthly Payment]") %>' />
<br />
Due On Every Month:
<asp:TextBox ID="Due_On_Every_MonthTextBox" runat="server" Text='<%# Bind("[Due On Every Month]") %>' />
<br />
ClientId:
<asp:TextBox ID="ClientIdTextBox" runat="server" Text='<%# Bind("ClientId") %>' />
<br />
<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update" Text="Update" />
&nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
<InsertItemTemplate>
BusinessName:
<asp:TextBox ID="BusinessNameTextBox" runat="server" Text='<%# Bind("BusinessName") %>' />
<br />
Address:
<asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />
<br />
Order date:
<asp:TextBox ID="Order_dateTextBox" runat="server" Text='<%# Bind("[Order date]") %>' />
<br />
Plan:
<asp:TextBox ID="PlanTextBox" runat="server" Text='<%# Bind("Plan") %>' />
<br />
NFC Tablets:
<asp:TextBox ID="NFC_TabletsTextBox" runat="server" Text='<%# Bind("[NFC Tablets]") %>' />
<br />
First Payment:
<asp:TextBox ID="First_PaymentTextBox" runat="server" Text='<%# Bind("[First Payment]") %>' />
<br />
Due On:
<asp:TextBox ID="Due_OnTextBox" runat="server" Text='<%# Bind("[Due On]") %>' />
<br />
Subsequent Monthly Payment:
<asp:TextBox ID="Subsequent_Monthly_PaymentTextBox" runat="server" Text='<%# Bind("[Subsequent Monthly Payment]") %>' />
<br />
Due On Every Month:
<asp:TextBox ID="Due_On_Every_MonthTextBox" runat="server" Text='<%# Bind("[Due On Every Month]") %>' />
<br

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: vishalneeraj-24503 on: 11/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Mandlaa,

Can you write a code behind part, in which action, you are getting an error?

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 11/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Remove the square brackets of column names while binding

Order date:<asp:TextBox ID="Order_dateTextBox" runat="server" Text='<%# Bind("Order date") %>' />
Reference:
http://stackoverflow.com/questions/11801373/incorrect-syntax-near-nvarchar-must-declare-scalar-variable-near-num

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Mandlaa on: 11/7/2013 [Member] Starter | Points: 25

Up
0
Down
I am not write any code manually,
I am using formview and sqldatasource control,
When i am updating the data getting this error

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 11/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
This link will give the solution
http://stackoverflow.com/questions/1269828/incorrect-syntax-near-nvarchar-in-update-asp-net-gridview?rq=1


Remove each square brackets of columns while binding
Example,

Text='<%# Bind("[Due On]") %>

should be 
Text='<%# Bind("Due On") %>



reference: http://stackoverflow.com/questions/1269828/incorrect-syntax-near-nvarchar-in-update-asp-net-gridview?rq=1

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Mandlaa on: 11/7/2013 [Member] Starter | Points: 25

Up
0
Down
@ Bandi
I am trying the Remove the square brackets of column names while binding
Getting the following error
Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: A call to Bind was not well formatted. Please refer to documentation for the correct parameters to Bind.

Source Error:

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: vishalneeraj-24503 on: 11/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Instead on Bind, you can also use Eval as
Text = '<%# Eval("Due On")%>

//or you can use
Text='<%#DataBinder.Eval(Container.DataItem,"Due On") %>'

//or
Text = '<%#Container.DataItem("Due On") %>'

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: vishalneeraj-24503 on: 11/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Other way of doing, please alter your Table,and rename Column as
Due_on
Order_date
NFC_Tablets
Subsequent_Monthly_Payment
Due_On_Every_Month

Now you can use as
Text = '<%# Bind("Due_On")%>

Text = '<%# Eval("Due_On")%>

//or you can use
Text='<%#DataBinder.Eval(Container.DataItem,"Due_On") %>'

//or
Text = '<%#Container.DataItem("Due_On") %>'

and do same for all columns.

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 11/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Solution1:
Refer the following link ( provided solution for same issue)

The issue in your code is the column names with spaces

http://adillis.com/Support/Forum/tabid/86/forumid/2/threadid/44/scope/posts/Default.aspx

Solution2:
Alter table by altering column names [Order date] ,[NFC Tablets] , [First Payment],
[Due On] , [Subsequent Monthly Payment] , and [Due On Every Month] as
Order_date ,NFC_Tablets, First_Payment, Due_On, Subsequent_Monthly_Payment, and Due_On_Every_Month

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 11/7/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer
http://forums.aspfree.com/net-development-11/formview-not-updating-122481.html
http://support.microsoft.com/kb/264687/en-us

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts