• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

Incorrect syntax near 'nvarchar' - Details view page.

I have created a simple detail view pages and I get the following error. I have   attached the code of my page with a preview of the error.

The details view is link from a gridview where I can perform an edit and update on my details view.
Here is the code of my page.
 
<%@ Page Title="Pharmaceutical Maintenance/ Add Edit" Language="vb" AutoEventWireup="false" MasterPageFile="~/MasterPage.master" CodeBehind="AddEditPharmacy.aspx.vb" Inherits="IWD.PharmacyMaintenance" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
   
        Search Pharmaceutical
   
 
    <p><span style="font-size:12.0pt;font-family:&quot;Times New Roman&quot;;
mso-fareast-font-family:&quot;Times New Roman&quot;;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA">Add Edit Pharmacy</span></p>
    <p>
        <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" 
            DataKeyNames="PhamaceuticalID" DataSourceID="SqlDataSource1" Height="50px" 
            Width="125px">
            <Fields>
                <asp:BoundField DataField="Company ID" HeaderText="Company ID" 
                    SortExpression="Company ID" />
                <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" 
                    SortExpression="CompanyName" />
                <asp:BoundField DataField="Address" HeaderText="Address" 
                    SortExpression="Address" />
                <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
                <asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
                <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
                <asp:BoundField DataField="Phone Extension" HeaderText="Phone Extension" 
                    SortExpression="Phone Extension" />
                <asp:BoundField DataField="Fax" HeaderText="Fax" SortExpression="Fax" />
                <asp:BoundField DataField="Website Address" HeaderText="Website Address" 
                    SortExpression="Website Address" />
                <asp:BoundField DataField="Reapply Interval" HeaderText="Reapply Interval" 
                    SortExpression="Reapply Interval" />
                <asp:BoundField DataField="Type of App Acceptable" 
                    HeaderText="Type of App Acceptable" SortExpression="Type of App Acceptable" />
                <asp:BoundField DataField="Type of ReApp Acceptable" 
                    HeaderText="Type of ReApp Acceptable" 
                    SortExpression="Type of ReApp Acceptable" />
                <asp:BoundField DataField="Type of Return" HeaderText="Type of Return" 
                    SortExpression="Type of Return" />
                <asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
                <asp:BoundField DataField="PhamaceuticalID" HeaderText="PhamaceuticalID" 
                    ReadOnly="True" SortExpression="PhamaceuticalID" />
                <asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:SeniorPrescriptionConnectionString1 %>" 
            DeleteCommand="DELETE FROM [Pharmaceutical] WHERE [PhamaceuticalID] = @PhamaceuticalID" 
            InsertCommand="INSERT INTO [Pharmaceutical] ([Company ID], [CompanyName], [Address], [City], [State], [Zip], [Phone], [Phone Extension], [Fax], [Website Address], [Reapply Interval], [Type of App Acceptable], [Type of ReApp Acceptable], [Type of Return], [Notes], [PhamaceuticalID]) VALUES (@Company_ID, @CompanyName, @Address, @City, @State, @Zip, @Phone, @Phone_Extension, @Fax, @Website_Address, @Reapply_Interval, @Type_of_App_Acceptable, @Type_of_ReApp_Acceptable, @Type_of_Return, @Notes, @PhamaceuticalID)" 
            SelectCommand="SELECT * FROM [Pharmaceutical] WHERE ([PhamaceuticalID] = @PhamaceuticalID)" 
            UpdateCommand="UPDATE [Pharmaceutical] SET [Company ID] = @Company_ID, [CompanyName] = @CompanyName, [Address] = @Address, [City] = @City, [State] = @State, [Zip] = @Zip, [Phone] = @Phone, [Phone Extension] = @Phone_Extension, [Fax] = @Fax, [Website Address] = @Website_Address, [Reapply Interval] = @Reapply_Interval, [Type of App Acceptable] = @Type_of_App_Acceptable, [Type of ReApp Acceptable] = @Type_of_ReApp_Acceptable, [Type of Return] = @Type_of_Return, [Notes] = @Notes WHERE [PhamaceuticalID] = @PhamaceuticalID">
            <SelectParameters>
                <asp:QueryStringParameter Name="PhamaceuticalID" 
                    QueryStringField="PhamaceuticalID" Type="Int32" />
            </SelectParameters>
            <DeleteParameters>
                <asp:Parameter Name="PhamaceuticalID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="Company_ID" Type="Int32" />
                <asp:Parameter Name="CompanyName" Type="String" />
                <asp:Parameter Name="Address" Type="String" />
                <asp:Parameter Name="City" Type="String" />
                <asp:Parameter Name="State" Type="String" />
                <asp:Parameter Name="Zip" Type="String" />
                <asp:Parameter Name="Phone" Type="String" />
                <asp:Parameter Name="Phone_Extension" Type="String" />
                <asp:Parameter Name="Fax" Type="String" />
                <asp:Parameter Name="Website_Address" Type="String" />
                <asp:Parameter Name="Reapply_Interval" Type="String" />
                <asp:Parameter Name="Type_of_App_Acceptable" Type="String" />
                <asp:Parameter Name="Type_of_ReApp_Acceptable" Type="String" />
                <asp:Parameter Name="Type_of_Return" Type="String" />
                <asp:Parameter Name="Notes" Type="String" />
                <asp:Parameter Name="PhamaceuticalID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="Company_ID" Type="Int32" />
                <asp:Parameter Name="CompanyName" Type="String" />
                <asp:Parameter Name="Address" Type="String" />
                <asp:Parameter Name="City" Type="String" />
                <asp:Parameter Name="State" Type="String" />
                <asp:Parameter Name="Zip" Type="String" />
                <asp:Parameter Name="Phone" Type="String" />
                <asp:Parameter Name="Phone_Extension" Type="String" />
                <asp:Parameter Name="Fax" Type="String" />
                <asp:Parameter Name="Website_Address" Type="String" />
                <asp:Parameter Name="Reapply_Interval" Type="String" />
                <asp:Parameter Name="Type_of_App_Acceptable" Type="String" />
                <asp:Parameter Name="Type_of_ReApp_Acceptable" Type="String" />
                <asp:Parameter Name="Type_of_Return" Type="String" />
                <asp:Parameter Name="Notes" Type="String" />
                <asp:Parameter Name="PhamaceuticalID" Type="Int32" />
            </InsertParameters>
        </asp:SqlDataSource>
    </p>
    <p>&nbsp;</p>
</asp:Content>

Open in new window

Here is what happen when i test my application.
I added a screen cast also. yguyon28-458456.flv
0
yguyon28
Asked:
yguyon28
  • 3
3 Solutions
 
sammySeltzerCommented:
This line is incorrect:

           DeleteCommand="DELETE FROM [Pharmaceutical] WHERE [PhamaceuticalID] = @PhamaceuticalID"

It should be:

           DeleteCommand="DELETE FROM [Pharmaceutical] WHERE [PhamaceuticalID] =" @PhamaceuticalID

assuming that PhamaceuticalID is an integer
0
 
sammySeltzerCommented:
same with these 2:

            SelectCommand="SELECT * FROM [Pharmaceutical] WHERE ([PhamaceuticalID] = @PhamaceuticalID)"

            UpdateCommand="UPDATE [Pharmaceutical] SET [Company ID] = @Company_ID, [CompanyName] = @CompanyName, [Address] = @Address, [City] = @City, [State] = @State, [Zip] = @Zip, [Phone] = @Phone, [Phone Extension] = @Phone_Extension, [Fax] = @Fax, [Website Address] = @Website_Address, [Reapply Interval] = @Reapply_Interval, [Type of App Acceptable] = @Type_of_App_Acceptable, [Type of ReApp Acceptable] = @Type_of_ReApp_Acceptable, [Type of Return] = @Type_of_Return, [Notes] = @Notes WHERE [PhamaceuticalID] = @PhamaceuticalID">
0
 
yguyon28Author Commented:
So this code bellow is incorrect:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:SeniorPrescriptionConnectionString1 %>"
            DeleteCommand="DELETE FROM [Pharmaceutical] WHERE [PhamaceuticalID] = @PhamaceuticalID"
            InsertCommand="INSERT INTO [Pharmaceutical] ([Company ID], [CompanyName], [Address], [City], [State], [Zip], [Phone], [Phone Extension], [Fax], [Website Address], [Reapply Interval], [Type of App Acceptable], [Type of ReApp Acceptable], [Type of Return], [Notes], [PhamaceuticalID]) VALUES (@Company_ID, @CompanyName, @Address, @City, @State, @Zip, @Phone, @Phone_Extension, @Fax, @Website_Address, @Reapply_Interval, @Type_of_App_Acceptable, @Type_of_ReApp_Acceptable, @Type_of_Return, @Notes, @PhamaceuticalID)"
            SelectCommand="SELECT * FROM [Pharmaceutical] WHERE ([PhamaceuticalID] = @PhamaceuticalID)"
            UpdateCommand="UPDATE [Pharmaceutical] SET [Company ID] = @Company_ID, [CompanyName] = @CompanyName, [Address] = @Address, [City] = @City, [State] = @State, [Zip] = @Zip, [Phone] = @Phone, [Phone Extension] = @Phone_Extension, [Fax] = @Fax, [Website Address] = @Website_Address, [Reapply Interval] = @Reapply_Interval, [Type of App Acceptable] = @Type_of_App_Acceptable, [Type of ReApp Acceptable] = @Type_of_ReApp_Acceptable, [Type of Return] = @Type_of_Return, [Notes] = @Notes WHERE [PhamaceuticalID] = @PhamaceuticalID">
            <SelectParameters>
0
 
sammySeltzerCommented:
Your updateCommand, DeleteCommand, and SelectCommand have incorrect syntaces.

Delete should be either:
DeleteCommand="DELETE FROM [Pharmaceutical] WHERE [PhamaceuticalID] =" & @PhamaceuticalID
OR
DeleteCommand="DELETE FROM [Pharmaceutical] WHERE [PhamaceuticalID] =" & @PhamaceuticalID & " "

Select should be either:
WHERE [PhamaceuticalID] =" & @PhamaceuticalID>
OR
WHERE [PhamaceuticalID] = " & @PhamaceuticalID & " ">

Update should be either:
WHERE [PhamaceuticalID] = " & @PhamaceuticalID>
OR
WHERE [PhamaceuticalID] = " & @PhamaceuticalID & " ">

Your issue, at least at first glance, is in the WHERE clause

All of these assume that PhamaceuticalID is an integer as stated earlier.

if it is nvarchar, then cast them with single quotes like:

WHERE [PhamaceuticalID] = '" & @PhamaceuticalID & "' ">

Do this for all of them.

Once you fix this, if there are any other errors, they will become evident.







0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now