Incorrect syntax near '='. 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 '='.
og hvis jeg har indhold i textbox1(@Artist) så får jeg denne fejl:
Invalid column name 'Test'. 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: Invalid column name 'Test'.
Er der nogen der umidelbart kan se hvad jeg gør galt..?
Jeg tror ikke at det du forsøger kan lade sig gøre. du kan lave din sql sætning sådan her:
SELECT Artist.Artist, Album.Album, Genre.Genre, Song.SongTitle, Song.Rating, Song.Counter, Song.SongId FROM Album INNER JOIN Song ON Album.AlbumId = Song.AlbumID INNER JOIN Artist ON Song.ArtistID = Artist.Artistid INNER JOIN Genre ON Song.GenreID = Genre.GenreId WHERE Artist.Artist =COALESCE(@Artist,Artist.Artist)
Ideen idet det her er at jeg har 4 textboxe at skal søge mellem,
og hvis der kun er indhold i 2 af dem, så skal den kun bruge de 2 i min WHERE streng.
Jeg har godt læst om den der COALESCE før, er det ikke noget med at den tager det første IKKE NULL object..?
men det ville (tror jeg ihvertfald ikke) kunne fungere når jeg nu har 4 textboxe, og det skal fungere på den måde at de textboxe som er skrevet i skal "AND" med hinanden..!
COALESCE tager null: SELECT Artist.Artist, Album.Album, Genre.Genre, Song.SongTitle, Song.Rating, Song.Counter, Song.SongId FROM Album INNER JOIN Song ON Album.AlbumId = Song.AlbumID INNER JOIN Artist ON Song.ArtistID = Artist.Artistid INNER JOIN Genre ON Song.GenreID = Genre.GenreId WHERE Artist.Artist =COALESCE(NULL,Artist.Artist)
ok.. prøver lige at skrive den som jeg det færdige resultat skulle se ud som..
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Artist_CodeConnectionString %>" SelectCommand="DECLARE @SQL varchar(1000) SET @Artist_Temp = @Artist SET @SQL = 'SELECT Artist.Artist, Album.Album, Genre.Genre, Song.SongTitle, Song.Rating, Song.Counter, Song.SongId FROM Album INNER JOIN Song ON Album.AlbumId = Song.AlbumID INNER JOIN Artist ON Song.ArtistID = Artist.Artistid INNER JOIN Genre ON Song.GenreID = Genre.GenreId '
IF @Artist IS NOT NULL OR @Album IS NOT NULL OR @Genre IS NOT NULL OR @SongTitle IS NOT NULL BEGIN SET @SQL = @SQL + 'WHERE ' END IF @Artist IS NOT NULL BEGIN SET @SQL = @SQL + 'Artist.Artist = ' + @Artist +' ' END IF @Album IS NOT NULL BEGIN SET @SQL = @SQL + 'Album.Album = ' + @Album + ' ' IF @Genre IS NOT NULL BEGIN SET @SQL = @SQL + 'Genre.Genre = ' + @Genre + ' ' END IF @SongTitle IS NOT NULL BEGIN SET @SQL = @SQL + 'Song.SongTitle = ' + @SongTitle + ' ' END
Problemet er de felt typer du har valgt. Har du behov for at der kan stå mere end 8000 tegn i nogen af felterne ? Hvis ikke så lav dem om til varchar.
ellers skal du bruge like:
SELECT Artist.Artist, Album.Album, Genre.Genre, Song.SongTitle, Song.Rating, Song.Counter, Song.SongId FROM Album INNER JOIN Song ON Album.AlbumId = Song.AlbumID INNER JOIN Artist ON Song.ArtistID = Artist.Artistid INNER JOIN Genre ON Song.GenreID = Genre.GenreId WHERE Artist.Artist LIKE COALESCE(@Artist,Artist.Artist) AND Album.Album LIKE COALESCE(@Album ,Album.Album) AND Genre.Genre LIKE COALESCE(@Genre,Genre.Genre) AND Song.SongTitle LIKE COALESCE(@SongTitle ,Song.SongTitle)
får du resultater med bare: SELECT Artist.Artist, Album.Album, Genre.Genre, Song.SongTitle, Song.Rating, Song.Counter, Song.SongId FROM Album INNER JOIN Song ON Album.AlbumId = Song.AlbumID INNER JOIN Artist ON Song.ArtistID = Artist.Artistid INNER JOIN Genre ON Song.GenreID = Genre.GenreId
du skal bruge samme sql. Prøv lige med: SELECT Artist.Artist, Album.Album, Genre.Genre, Song.SongTitle, Song.Rating, Song.Counter, Song.SongId FROM Album INNER JOIN Song ON Album.AlbumId = Song.AlbumID INNER JOIN Artist ON Song.ArtistID = Artist.Artistid INNER JOIN Genre ON Song.GenreID = Genre.GenreId WHERE Artist.Artist LIKE COALESCE(@Artist+'%',Artist.Artist) AND Album.Album LIKE COALESCE(@Album+'%' ,Album.Album) AND Genre.Genre LIKE COALESCE(@Genre+'%',Genre.Genre) AND Song.SongTitle LIKE COALESCE(@SongTitle+'%' ,Song.SongTitle)
prøv med: SELECT Artist.Artist, Album.Album, Genre.Genre, Song.SongTitle, Song.Rating, Song.Counter, Song.SongId FROM Album INNER JOIN Song ON Album.AlbumId = Song.AlbumID INNER JOIN Artist ON Song.ArtistID = Artist.Artistid INNER JOIN Genre ON Song.GenreID = Genre.GenreId WHERE Artist.Artist LIKE COALESCE('%'+@Artist+'%',Artist.Artist) AND Album.Album LIKE COALESCE('%'+@Album+'%' ,Album.Album) AND Genre.Genre LIKE COALESCE('%'+@Genre+'%',Genre.Genre) AND Song.SongTitle LIKE COALESCE('%'+@SongTitle+'%' ,Song.SongTitle)
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.