Andrew
asked on
Insert Record error?
Hello, can someone please help me to understand why I am getting this error when trying to insert a record into a SQL DB using a FormView?
TIA,
Andrew
TIA,
Andrew
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:
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): Incorrect syntax near 'nvarchar'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2073502
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064460
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +215
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +394
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +407
System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +89
System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation) +377
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +612
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +112
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +125
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +169
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +9
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.237
<%@ Page Title="" Language="VB" MasterPageFile="~/Site.master" AutoEventWireup="false" CodeFile="InsertRecord.aspx.vb" Inherits="InsertRecord" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
<h2>Insert New Record</h2><br />
<asp:FormView ID="FormView1" runat="server" CellPadding="4" DataKeyNames="VID"
DataSourceID="SqlDSInsertRec" ForeColor="#333333">
<EditItemTemplate>
VID:
<asp:Label ID="VIDLabel1" runat="server" Text='<%# Eval("VID") %>' />
<br />
Family:
<asp:TextBox ID="FamilyTextBox" runat="server" Text='<%# Bind("Family") %>' />
<br />
FID:
<asp:TextBox ID="FIDTextBox" runat="server" Text='<%# Bind("FID") %>' />
<br />
SubFamily:
<asp:TextBox ID="SubFamilyTextBox" runat="server"
Text='<%# Bind("SubFamily") %>' />
<br />
Genus:
<asp:TextBox ID="GenusTextBox" runat="server" Text='<%# Bind("Genus") %>' />
<br />
GID:
<asp:TextBox ID="GIDTextBox" runat="server" Text='<%# Bind("GID") %>' />
<br />
SubGenus:
<asp:TextBox ID="SubGenusTextBox" runat="server"
Text='<%# Bind("SubGenus") %>' />
<br />
Species:
<asp:TextBox ID="SpeciesTextBox" runat="server" Text='<%# Bind("Species") %>' />
<br />
Isolate:
<asp:TextBox ID="IsolateTextBox" runat="server" Text='<%# Bind("Isolate") %>' />
<br />
Abbreviation:
<asp:TextBox ID="AbbreviationTextBox" runat="server"
Text='<%# Bind("Abbreviation") %>' />
<br />
AccessionA:
<asp:TextBox ID="AccessionATextBox" runat="server"
Text='<%# Bind("AccessionA") %>' />
<br />
GIA:
<asp:TextBox ID="GIATextBox" runat="server" Text='<%# Bind("GIA") %>' />
<br />
AccessionB:
<asp:TextBox ID="AccessionBTextBox" runat="server"
Text='<%# Bind("AccessionB") %>' />
<br />
GIB:
<asp:TextBox ID="GIBTextBox" runat="server" Text='<%# Bind("GIB") %>' />
<br />
NC#:
<asp:TextBox ID="NC_TextBox" runat="server" Text='<%# Bind("[NC#]") %>' />
<br />
Geography:
<asp:TextBox ID="GeographyTextBox" runat="server"
Text='<%# Bind("Geography") %>' />
<br />
Year2007A:
<asp:TextBox ID="Year2007ATextBox" runat="server"
Text='<%# Bind("Year2007A") %>' />
<br />
Year2007B:
<asp:TextBox ID="Year2007BTextBox" runat="server"
Text='<%# Bind("Year2007B") %>' />
<br />
Year2008A:
<asp:TextBox ID="Year2008ATextBox" runat="server"
Text='<%# Bind("Year2008A") %>' />
<br />
Year2008B:
<asp:TextBox ID="Year2008BTextBox" runat="server"
Text='<%# Bind("Year2008B") %>' />
<br />
Year2009A:
<asp:TextBox ID="Year2009ATextBox" runat="server"
Text='<%# Bind("Year2009A") %>' />
<br />
Year2009B:
<asp:TextBox ID="Year2009BTextBox" runat="server"
Text='<%# Bind("Year2009B") %>' />
<br />
Year2010A:
<asp:TextBox ID="Year2010ATextBox" runat="server"
Text='<%# Bind("Year2010A") %>' />
<br />
Year2010B:
<asp:TextBox ID="Year2010BTextBox" runat="server"
Text='<%# Bind("Year2010B") %>' />
<br />
Year2011A:
<asp:TextBox ID="Year2011ATextBox" runat="server"
Text='<%# Bind("Year2011A") %>' />
<br />
Year2011B:
<asp:TextBox ID="Year2011BTextBox" runat="server"
Text='<%# Bind("Year2011B") %>' />
<br />
<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True"
CommandName="Update" Text="Update" />
<asp:LinkButton ID="UpdateCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<InsertItemTemplate>
Family:
<asp:TextBox ID="FamilyTextBox" runat="server" Text='<%# Bind("Family") %>' />
<br />
FID:
<asp:TextBox ID="FIDTextBox" runat="server" Text='<%# Bind("FID") %>' />
<br />
SubFamily:
<asp:TextBox ID="SubFamilyTextBox" runat="server"
Text='<%# Bind("SubFamily") %>' />
<br />
Genus:
<asp:TextBox ID="GenusTextBox" runat="server" Text='<%# Bind("Genus") %>' />
<br />
GID:
<asp:TextBox ID="GIDTextBox" runat="server" Text='<%# Bind("GID") %>' />
<br />
SubGenus:
<asp:TextBox ID="SubGenusTextBox" runat="server"
Text='<%# Bind("SubGenus") %>' />
<br />
Species:
<asp:TextBox ID="SpeciesTextBox" runat="server" Text='<%# Bind("Species") %>' />
<br />
Isolate:
<asp:TextBox ID="IsolateTextBox" runat="server" Text='<%# Bind("Isolate") %>' />
<br />
Abbreviation:
<asp:TextBox ID="AbbreviationTextBox" runat="server"
Text='<%# Bind("Abbreviation") %>' />
<br />
AccessionA:
<asp:TextBox ID="AccessionATextBox" runat="server"
Text='<%# Bind("AccessionA") %>' />
<br />
GIA:
<asp:TextBox ID="GIATextBox" runat="server" Text='<%# Bind("GIA") %>' />
<br />
AccessionB:
<asp:TextBox ID="AccessionBTextBox" runat="server"
Text='<%# Bind("AccessionB") %>' />
<br />
GIB:
<asp:TextBox ID="GIBTextBox" runat="server" Text='<%# Bind("GIB") %>' />
<br />
NC#:
<asp:TextBox ID="NC_TextBox" runat="server" Text='<%# Bind("[NC#]") %>' />
<br />
Geography:
<asp:TextBox ID="GeographyTextBox" runat="server"
Text='<%# Bind("Geography") %>' />
<br />
Year2007A:
<asp:TextBox ID="Year2007ATextBox" runat="server"
Text='<%# Bind("Year2007A") %>' />
<br />
Year2007B:
<asp:TextBox ID="Year2007BTextBox" runat="server"
Text='<%# Bind("Year2007B") %>' />
<br />
Year2008A:
<asp:TextBox ID="Year2008ATextBox" runat="server"
Text='<%# Bind("Year2008A") %>' />
<br />
Year2008B:
<asp:TextBox ID="Year2008BTextBox" runat="server"
Text='<%# Bind("Year2008B") %>' />
<br />
Year2009A:
<asp:TextBox ID="Year2009ATextBox" runat="server"
Text='<%# Bind("Year2009A") %>' />
<br />
Year2009B:
<asp:TextBox ID="Year2009BTextBox" runat="server"
Text='<%# Bind("Year2009B") %>' />
<br />
Year2010A:
<asp:TextBox ID="Year2010ATextBox" runat="server"
Text='<%# Bind("Year2010A") %>' />
<br />
Year2010B:
<asp:TextBox ID="Year2010BTextBox" runat="server"
Text='<%# Bind("Year2010B") %>' />
<br />
Year2011A:
<asp:TextBox ID="Year2011ATextBox" runat="server"
Text='<%# Bind("Year2011A") %>' />
<br />
Year2011B:
<asp:TextBox ID="Year2011BTextBox" runat="server"
Text='<%# Bind("Year2011B") %>' />
<br />
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
CommandName="Insert" Text="Insert" />
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</InsertItemTemplate>
<ItemTemplate>
VID:
<asp:Label ID="VIDLabel" runat="server" Text='<%# Eval("VID") %>' />
<br />
Family:
<asp:Label ID="FamilyLabel" runat="server" Text='<%# Bind("Family") %>' />
<br />
FID:
<asp:Label ID="FIDLabel" runat="server" Text='<%# Bind("FID") %>' />
<br />
SubFamily:
<asp:Label ID="SubFamilyLabel" runat="server" Text='<%# Bind("SubFamily") %>' />
<br />
Genus:
<asp:Label ID="GenusLabel" runat="server" Text='<%# Bind("Genus") %>' />
<br />
GID:
<asp:Label ID="GIDLabel" runat="server" Text='<%# Bind("GID") %>' />
<br />
SubGenus:
<asp:Label ID="SubGenusLabel" runat="server" Text='<%# Bind("SubGenus") %>' />
<br />
Species:
<asp:Label ID="SpeciesLabel" runat="server" Text='<%# Bind("Species") %>' />
<br />
Isolate:
<asp:Label ID="IsolateLabel" runat="server" Text='<%# Bind("Isolate") %>' />
<br />
Abbreviation:
<asp:Label ID="AbbreviationLabel" runat="server"
Text='<%# Bind("Abbreviation") %>' />
<br />
AccessionA:
<asp:Label ID="AccessionALabel" runat="server"
Text='<%# Bind("AccessionA") %>' />
<br />
GIA:
<asp:Label ID="GIALabel" runat="server" Text='<%# Bind("GIA") %>' />
<br />
AccessionB:
<asp:Label ID="AccessionBLabel" runat="server"
Text='<%# Bind("AccessionB") %>' />
<br />
GIB:
<asp:Label ID="GIBLabel" runat="server" Text='<%# Bind("GIB") %>' />
<br />
NC#:
<asp:Label ID="NC_Label" runat="server" Text='<%# Bind("[NC#]") %>' />
<br />
Geography:
<asp:Label ID="GeographyLabel" runat="server" Text='<%# Bind("Geography") %>' />
<br />
Year2007A:
<asp:Label ID="Year2007ALabel" runat="server" Text='<%# Bind("Year2007A") %>' />
<br />
Year2007B:
<asp:Label ID="Year2007BLabel" runat="server" Text='<%# Bind("Year2007B") %>' />
<br />
Year2008A:
<asp:Label ID="Year2008ALabel" runat="server" Text='<%# Bind("Year2008A") %>' />
<br />
Year2008B:
<asp:Label ID="Year2008BLabel" runat="server" Text='<%# Bind("Year2008B") %>' />
<br />
Year2009A:
<asp:Label ID="Year2009ALabel" runat="server" Text='<%# Bind("Year2009A") %>' />
<br />
Year2009B:
<asp:Label ID="Year2009BLabel" runat="server" Text='<%# Bind("Year2009B") %>' />
<br />
Year2010A:
<asp:Label ID="Year2010ALabel" runat="server" Text='<%# Bind("Year2010A") %>' />
<br />
Year2010B:
<asp:Label ID="Year2010BLabel" runat="server" Text='<%# Bind("Year2010B") %>' />
<br />
Year2011A:
<asp:Label ID="Year2011ALabel" runat="server" Text='<%# Bind("Year2011A") %>' />
<br />
Year2011B:
<asp:Label ID="Year2011BLabel" runat="server" Text='<%# Bind("Year2011B") %>' />
<br />
<asp:LinkButton ID="EditButton" runat="server" CausesValidation="False"
CommandName="Edit" Text="Edit" />
<asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False"
CommandName="Delete" Text="Delete" />
<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False"
CommandName="New" Text="New" />
</ItemTemplate>
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
</asp:FormView>
<asp:SqlDataSource ID="SqlDSInsertRec" runat="server"
ConnectionString="<%$ ConnectionStrings:PlantssDNAVirusesConnectionString %>"
DeleteCommand="DELETE FROM [PlantssDNA] WHERE [VID] = @VID"
InsertCommand="INSERT INTO [PlantssDNA] ([Family], [FID], [SubFamily], [Genus], [GID], [SubGenus], [Species], [Isolate], [Abbreviation], [AccessionA], [GIA], [AccessionB], [GIB], [NC#], [Geography], [Year2007A], [Year2007B], [Year2008A], [Year2008B], [Year2009A], [Year2009B], [Year2010A], [Year2010B], [Year2011A], [Year2011B]) VALUES (@Family, @FID, @SubFamily, @Genus, @GID, @SubGenus, @Species, @Isolate, @Abbreviation, @AccessionA, @GIA, @AccessionB, @GIB, @column1, @Geography, @Year2007A, @Year2007B, @Year2008A, @Year2008B, @Year2009A, @Year2009B, @Year2010A, @Year2010B, @Year2011A, @Year2011B)"
SelectCommand="SELECT * FROM [PlantssDNA]"
UpdateCommand="UPDATE [PlantssDNA] SET [Family] = @Family, [FID] = @FID, [SubFamily] = @SubFamily, [Genus] = @Genus, [GID] = @GID, [SubGenus] = @SubGenus, [Species] = @Species, [Isolate] = @Isolate, [Abbreviation] = @Abbreviation, [AccessionA] = @AccessionA, [GIA] = @GIA, [AccessionB] = @AccessionB, [GIB] = @GIB, [NC#] = @column1, [Geography] = @Geography, [Year2007A] = @Year2007A, [Year2007B] = @Year2007B, [Year2008A] = @Year2008A, [Year2008B] = @Year2008B, [Year2009A] = @Year2009A, [Year2009B] = @Year2009B, [Year2010A] = @Year2010A, [Year2010B] = @Year2010B, [Year2011A] = @Year2011A, [Year2011B] = @Year2011B WHERE [VID] = @VID">
<DeleteParameters>
<asp:Parameter Name="VID" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="Family" Type="String" />
<asp:Parameter Name="FID" Type="Int32" />
<asp:Parameter Name="SubFamily" Type="String" />
<asp:Parameter Name="Genus" Type="String" />
<asp:Parameter Name="GID" Type="Int32" />
<asp:Parameter Name="SubGenus" Type="String" />
<asp:Parameter Name="Species" Type="String" />
<asp:Parameter Name="Isolate" Type="String" />
<asp:Parameter Name="Abbreviation" Type="String" />
<asp:Parameter Name="AccessionA" Type="String" />
<asp:Parameter Name="GIA" Type="String" />
<asp:Parameter Name="AccessionB" Type="String" />
<asp:Parameter Name="GIB" Type="String" />
<asp:Parameter Name="column1" Type="String" />
<asp:Parameter Name="Geography" Type="String" />
<asp:Parameter Name="Year2007A" Type="String" />
<asp:Parameter Name="Year2007B" Type="String" />
<asp:Parameter Name="Year2008A" Type="String" />
<asp:Parameter Name="Year2008B" Type="String" />
<asp:Parameter Name="Year2009A" Type="String" />
<asp:Parameter Name="Year2009B" Type="String" />
<asp:Parameter Name="Year2010A" Type="String" />
<asp:Parameter Name="Year2010B" Type="String" />
<asp:Parameter Name="Year2011A" Type="String" />
<asp:Parameter Name="Year2011B" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="Family" Type="String" />
<asp:Parameter Name="FID" Type="Int32" />
<asp:Parameter Name="SubFamily" Type="String" />
<asp:Parameter Name="Genus" Type="String" />
<asp:Parameter Name="GID" Type="Int32" />
<asp:Parameter Name="SubGenus" Type="String" />
<asp:Parameter Name="Species" Type="String" />
<asp:Parameter Name="Isolate" Type="String" />
<asp:Parameter Name="Abbreviation" Type="String" />
<asp:Parameter Name="AccessionA" Type="String" />
<asp:Parameter Name="GIA" Type="String" />
<asp:Parameter Name="AccessionB" Type="String" />
<asp:Parameter Name="GIB" Type="String" />
<asp:Parameter Name="column1" Type="String" />
<asp:Parameter Name="Geography" Type="String" />
<asp:Parameter Name="Year2007A" Type="String" />
<asp:Parameter Name="Year2007B" Type="String" />
<asp:Parameter Name="Year2008A" Type="String" />
<asp:Parameter Name="Year2008B" Type="String" />
<asp:Parameter Name="Year2009A" Type="String" />
<asp:Parameter Name="Year2009B" Type="String" />
<asp:Parameter Name="Year2010A" Type="String" />
<asp:Parameter Name="Year2010B" Type="String" />
<asp:Parameter Name="Year2011A" Type="String" />
<asp:Parameter Name="Year2011B" Type="String" />
<asp:Parameter Name="VID" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
</asp:Content>
InsertCommand="INSERT INTO [PlantssDNA] ([Family], [FID], [SubFamily], [Genus], [GID], [SubGenus], [Species], [Isolate], [Abbreviation], [AccessionA], [GIA], [AccessionB], [GIB], [NC#], [Geography], [Year2007A], [Year2007B], [Year2008A], [Year2008B], [Year2009A], [Year2009B], [Year2010A], [Year2010B], [Year2011A], [Year2011B]) VALUES (@Family, @FID, @SubFamily, @Genus, @GID, @SubGenus, @Species, @Isolate, @Abbreviation, @AccessionA, @GIA, @AccessionB, @GIB, @column1, @Geography, @Year2007A, @Year2007B, @Year2008A, @Year2008B, @Year2009A, @Year2009B, @Year2010A, @Year2010B, @Year2011A, @Year2011B)"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
from the capture image you showed
ASKER
Gotcha, I changed the column name in the DB to NC_No and it seems to be working now. I guess that's what I get for naming it that way in the first place...
Thanks for your patient help.
Thanks for your patient help.
ASKER
InsertCommand="INSERT INTO [PlantssDNA] ([Family], [FID], [SubFamily], [Genus], [GID], [SubGenus], [Species], [Isolate], [Abbreviation], [AccessionA], [GIA], [AccessionB], [GIB], [NC#], [Geography], [Year2007A], [Year2007B], [Year2008A], [Year2008B], [Year2009A], [Year2009B], [Year2010A], [Year2010B], [Year2011A], [Year2011B]) VALUES (@Family, @FID, @SubFamily, @Genus, @GID, @SubGenus, @Species, @Isolate, @Abbreviation, @AccessionA, @GIA, @AccessionB, @GIB, @column1, @Geography, @Year2007A, @Year2007B, @Year2008A, @Year2008B, @Year2009A, @Year2009B, @Year2010A, @Year2010B, @Year2011A, @Year2011B)"