0

I am using a details view and sqldatasource to update information in my database. I keep getting this "Incorrect syntax near 'nvarchar'." Error and from what I have researched this is because there are spaces in my table field names and the table name. Here is some code:

<asp:SqlDataSource ID="sdsProperty" runat="server" 
        ConnectionString="<%$ ConnectionStrings:MasterSQLConnectionString %>" 
        
        
        
        SelectCommand="SELECT * FROM [Property List] WHERE ([Property Code] = @Property_Code)" 
        UpdateCommand="UPDATE [Property List] SET [Property ID] = @PropID, [Property Name] = @PropName, Address = @PropAddress, City = @PropCity, State = @PropState, PostalCode = @PropZip, MainNumber = @PropMainNumber, Backnumber = @PropBackNumber, FaxNumber = @PropFaxNumber, [800Number] = @Prop800Number, EmailAddress = @PropEmail, Units = @PropUnits, DateAquired = @PropDateAquired, Notes = @PropNotes, RegionalID = @PropRegional, DispoDate = @PropDispoDate, AcctID = @PropAcct, Manager = @PropManager, InvestorContact = @PropInvestorContact, InvestorCompany = @PropInvestorCompany, RVPID = @PropRVPID, [Prop Abbr] = @PropAbbr, HR = @PropHR, FormerName = @PropFormerName WHERE ([Property Code] = @pID)">
        <SelectParameters>
            <asp:QueryStringParameter Name="Property_Code" QueryStringField="pID" Type="Int32" />
        </SelectParameters>
        <UpdateParameters>
     <asp:ControlParameter ControlID="DetailsView1$PropID" ConvertEmptyStringToNull="true" Name="PropID" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropName" ConvertEmptyStringToNull="true"  Name="PropName" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropAddress"  ConvertEmptyStringToNull="true" Name="PropAddress" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropCity"   ConvertEmptyStringToNull="true" Name="PropCity" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$ddlState"  ConvertEmptyStringToNull="true"  Name="PropState" PropertyName="SelectedValue" />
     <asp:ControlParameter ControlID="DetailsView1$PropZip"   ConvertEmptyStringToNull="true" Name="PropZip" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropMain"  ConvertEmptyStringToNull="true"  Name="PropMainNumber" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropBack"  ConvertEmptyStringToNull="true"  Name="PropBackNumber" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropFax"  ConvertEmptyStringToNull="true"  Name="PropFaxNumber" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$Prop800"  ConvertEmptyStringToNull="true"  Name="Prop800Number" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropEmail"  ConvertEmptyStringToNull="true"  Name="PropEmail" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropUnits"  ConvertEmptyStringToNull="true"  Name="PropUnits" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropAquired" ConvertEmptyStringToNull="true"   Name="PropDateAquired" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropNotes"  ConvertEmptyStringToNull="true"  Name="PropNotes" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$ddlRegionalManager" ConvertEmptyStringToNull="true"  Type="Int32" Name="PropRegional" PropertyName="SelectedValue" />
     <asp:ControlParameter ControlID="DetailsView1$PropDispo"   ConvertEmptyStringToNull="true" Name="PropDispoDate" PropertyName="text" />

     <asp:ControlParameter ControlID="DetailsView1$ddlManager" ConvertEmptyStringToNull="true"   Type="Int32" Name="PropManager" PropertyName="SelectedValue" />
     <asp:ControlParameter ControlID="DetailsView1$PropInvestorContact"  ConvertEmptyStringToNull="true"  Name="PropInvestorContact" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$PropInvestorCompany"  ConvertEmptyStringToNull="true"  Name="PropInvestorCompany" PropertyName="text" />

     <asp:ControlParameter ControlID="DetailsView1$ddlRVP" ConvertEmptyStringToNull="true"  Type="Int32" Name="PropRVPID" PropertyName="SelectedValue" />
     <asp:ControlParameter ControlID="DetailsView1$PropAbbr"  ConvertEmptyStringToNull="true"  Name="PropAbbr" PropertyName="text" />
     <asp:ControlParameter ControlID="DetailsView1$ddlHR"  ConvertEmptyStringToNull="true"  Type="Int32" Name="PropHR" PropertyName="SelectedValue" />
     <asp:ControlParameter ControlID="DetailsView1$ddlAcct"  ConvertEmptyStringToNull="true" Type="Int32" Name="PropAcct" PropertyName="SelectedValue" />
     <asp:ControlParameter ControlID="DetailsView1$PropFormer"  ConvertEmptyStringToNull="true"  Name="PropFormerName" PropertyName="Text" />
            <asp:QueryStringParameter Name="pID" QueryStringField="pID" Type="Int32" />

        </UpdateParameters>
        
    </asp:SqlDataSource>

I have read that you can use ?Aliases? to fix this issue, but I am unable to find information on how to preform this. I can not rename any of this because this database is utilized by several different applications. Please advise.

3
Contributors
4
Replies
9
Views
6 Years
Discussion Span
Last Post by MichaelWClark
0

UPDATE: I have created a temp table that contains no spaces and changed the code accordingly. This same error is still here. Any and all help is appreciated.

0

Ok... I may be missing something here but...

Nowhere in the code you provided does the term 'nvarchar' exist.

So either the issue is not with the portion you provided (ie: in code-behind or in a stored procedure on the DB end) or the error is being relayed incorrectly by you to us :)

0

You have a type set incorrectly it will say somewhere in your error message

Must declare the scalar variable <@Field>

For example

<asp:QueryStringParameter Name="pID" QueryStringField="pID" Type="Int32" />

could need to be changed to

<asp:QueryStringParameter Name="pID" QueryStringField="pID" />
0

I solved this issue by creating a custom view. The ASP didn't handle spaces in the ROW names well. After I switched to the custom view rather than the table itself all issues were resolved.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.