Incorrect Syntax near WHERE Clause

<asp:SqlDataSource ID="productsUpdate" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAccountType %>" SelectCommand="SELECT [ProductID], [ProductName], [ImageTitle], [ImageData], [ImageMimeType] FROM [Products] WHERE [ProductID] = @.ProductID" UpdateCommand="UPDATE [Products] SET [ImageTitle] = Null, [ImageData] = Null, [ImageMimeType] = Null WHERE [ProductID] = @.ProductID" InsertCommand="INSERT INTO [Products] ([ImageTitle], [ImageData], [ImageMimeType]) VALUES (@.ImageTitle, @.ImageData, @.MimeType) WHERE ([ProductID] = @.ProductID)"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ProductID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="ImageTitle" /> <asp:Parameter Name="ImageData" Type="string" /> <asp:Parameter Name="MimeType" Type="string" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ImageTitle" Type="String" /> <asp:Parameter Name="ImageData" /> <asp:Parameter Name="MimeType" Type="String" /> </InsertParameters> </asp:SqlDataSource>

Hi Guys, new to development using visual studio and vb.net 2.0. and SQL express. Have a small problem.

I have an update command which im using to change the values of a blob image table to NULL so that i can then insert a new image into the table. This works fine. My problem is that my insert statement has an error and i cant figure this out. if i dont add the where clause it works fine but inserts the new image into the table without the product data, i want to insert the new image into the existing product information.

The error message i keep getting is "incorrect syntax near where"

Any help would be greatly appreciated
Thanks

Hi,

Does the following columns ]

[ImageTitle] , [ImageData], [ImageMimeType] are declared as "Allow NULLS" in the DB Design ?

|||

You need to add the following to your UpdateParameters:

<asp:ControlParameter ControlID="GridView1" Name="ProductID" PropertyName="SelectedValue"
 Type="Int32" />

|||

Yes the columns allow Null values,

Ill try the other solution and mark it if its the answer

Thanks for your replies

|||

INSERTS don't have WHERE clauses. INSERT is for adding a row to a table. UPDATE is for changing the contents of rows.

|||

Please remove the WHERE clause from InsertCommand.

InsertCommand="INSERT INTO [Products] ([ImageTitle], [ImageData], [ImageMimeType]) VALUES (@.ImageTitle, @.ImageData, @.MimeType)"

|||

Ok im going to change my approach and rephrase this whole thing to a question :)

I have a table called products which holds the product name ,price, size, bla bla, and an image, mimetype and image title, i need to update this image incase it ever needs changing, i simply want the user to be able to select a new image and then overwrite/update over the original image, i had 2 controls on this page within a details view control, one was a bound text box for the title, one was a file upload control to select the new image, however when i tried this out it kept coming up with a message that it couldnt convert varbinary into varchar so i tried this approach instead.

Any help you can provide would be greatly appreciated

Regards

|||

Hi,

Since your new question is not directly related to the original <syntax near WHERE Clause> issue, it would be best if you open up a new thread for the new question. In this way, our discussion here will not deviate too much from the original issue. This will make answer searching in the forum easier and be beneficial to other community members as well.Thank you for your understanding.