Showing posts with label wrong. Show all posts
Showing posts with label wrong. Show all posts

Friday, March 30, 2012

Problem with SQL Statement

Can anyone see anything obviously wrong with this statement? I'm not really
an SQL kind of guy

SELECT ProductID, Title FROM Products WHERE PlatformID LIKE % AND CategoryID
= 'f5e45b55-95de-47f4-a79b-b24969178b52' ORDER BY Title

PlatformID contains a GUID and I thought passing a wildcard would allow all
all records to be selected with the matching CategoryID

Any ideas? I'm assuming I've just formed the SQL incorrectly"Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
news:iju_b.555$1d5.5476581@.news-text.cableinet.net...
> Can anyone see anything obviously wrong with this statement? I'm not
really
> an SQL kind of guy
> SELECT ProductID, Title FROM Products WHERE PlatformID LIKE % AND
CategoryID
> = 'f5e45b55-95de-47f4-a79b-b24969178b52' ORDER BY Title
> PlatformID contains a GUID and I thought passing a wildcard would allow
all
> all records to be selected with the matching CategoryID
> Any ideas? I'm assuming I've just formed the SQL incorrectly

The LIKE is the problem - you can look it up in Books Online - but it
doesn't seem to be necessary here anyway, as your description suggests that
the PlatformID is irrelevant:

SELECT
ProductID, Title
FROM
Products
WHERE
CategoryID = 'f5e45b55-95de-47f4-a79b-b24969178b52'
ORDER BY
Title

If this doesn't give the results you expect, then you'll need to post some
more information about the structure of the table and what your data look
like.

Simon|||Managed to Fix it simon.

I need the PlatformID as users are selecting both the platform and category
from a drop down menu.

1 platform can have many categories

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:403a7398$1_1@.news.bluewin.ch...
> "Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
> news:iju_b.555$1d5.5476581@.news-text.cableinet.net...
> > Can anyone see anything obviously wrong with this statement? I'm not
> really
> > an SQL kind of guy
> > SELECT ProductID, Title FROM Products WHERE PlatformID LIKE % AND
> CategoryID
> > = 'f5e45b55-95de-47f4-a79b-b24969178b52' ORDER BY Title
> > PlatformID contains a GUID and I thought passing a wildcard would allow
> all
> > all records to be selected with the matching CategoryID
> > Any ideas? I'm assuming I've just formed the SQL incorrectly
> The LIKE is the problem - you can look it up in Books Online - but it
> doesn't seem to be necessary here anyway, as your description suggests
that
> the PlatformID is irrelevant:
> SELECT
> ProductID, Title
> FROM
> Products
> WHERE
> CategoryID = 'f5e45b55-95de-47f4-a79b-b24969178b52'
> ORDER BY
> Title
> If this doesn't give the results you expect, then you'll need to post some
> more information about the structure of the table and what your data look
> like.
> Simon|||Andrew Banks (banksy@.nojunkblueyonder.co.uk) writes:
> Can anyone see anything obviously wrong with this statement? I'm not
> really an SQL kind of guy
> SELECT ProductID, Title FROM Products WHERE PlatformID LIKE % AND
> CategoryID
>= 'f5e45b55-95de-47f4-a79b-b24969178b52' ORDER BY Title
> PlatformID contains a GUID and I thought passing a wildcard would allow
> all all records to be selected with the matching CategoryID

First a general piece of advice: when you post a question like this, be
more specific. If there is an error message, please share it. If you
get unexpected results, please say so, and why they are unexpected.

In this particular case, the problem is that you cannot have a mod
operator between LIKE and AND. The percent is presumably meant to be
a wildcard, but in this case you need to quote it:

... LIKE '%' AND ...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Wednesday, March 21, 2012

Problem with simple SQL


I want to view all companyname that starts with A, the sql runs but does not show the output,
What's wrong with the query?, am I missing any quotes or comma. Pls 'elp
Dim myCharacter = "A"
SQL = "SELECTlogo, EmployerID, CompanyName FROM EmployerDetails wherecompanyName like '"&myCharacter & "%'"



Select seems correct! How you are using this in ASP.NET page? Are you getting data into Reader or not?|||Thanks Sreed
I think the problem is the SQL because when I hard code to this
SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like 'A%' "
It works, PLS help Thanks
|||

If you declared myCharacter,SQL as a local string variable, then it should work!

Just to debug after SQL = "..blabla" statement do,

Response.Write(SQL)
Response.End()
and run the page and see what is the output you are getting from SQL, and see if that is giving correct Select or not!

That should help us to find whether it building currect SQL or not!

|||Hi Sreed
This is the full code below , I did the PRINT STUFF and the output is also below
Sub BindDataToGrid(myCharacter)
Response.write("Testing "&myCharacter)
Dim Conn, Conn1 as SqlConnection
Dim mySqlCommand, mySqlCommand2 as SqlCommand
Dim SQL,SQL1,SQL2 ,GetQuestions As String
Dim myDate as date
Dim pic as ArrayList = new ArrayList()
Dim Job_no as Integer

IF myCharacter <> "" THEN
SQL ="SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL & " ' " & myCharacter & " % ' "
Else
SQL ="SELECT logo, EmployerID FROM EmployerDetails order by newID() "
End If
Conn = New SqlConnection(ConfigurationSettings.AppSettings("connString"))
Dim resultsDataSet as New DataSet()
Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(SQL, Conn)

Conn.Open()
myDataAdapter.Fill(resultsDataSet,"EmployerDetails")
mySqlCommand = New SqlCommand(SQL, Conn)
mySqlCommand.ExecuteNonQuery()

Dim myReader As SqlDataReader = mysqlCommand.ExecuteReader()
Dim content
While myReader.Read()
If not (myReader("Logo") is DBNull.Value) Then
If Len(myReader("Logo")) > 0 Then
Dim LOGO = myReader("Logo")
Dim EmployerID =myReader("EmployerID")
Content = "<br><ahref='/JBoard/EmployerJobs.aspx?EMPID="&EmployerID
Content = Content & " '><img src='/JBoard/CompLogo/"&Logo
Content = Content & " ' width='124' height='50'BORDER='1'></a>"
pic.Add(Content)
End If
End If
End While

LogoGrid.DataSource = pic
LogoGrid.DataBind()
Conn.Close()
End Sub

OUTPUT IS
SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like 'M % '
What is wrong with this tiny code, forum help me pls.


|||

SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL &" ' " & myCharacter &" % ' "

This looks like you have extra spaces in there. If you copied and pasted this directly from your code, you're actually querying for anything that starts with a space and then an 'A' and then has a space and then any character and then a space. Drop the spaces in theRED code above and it should work.

|||Hi PD
How do you drop the space, that's why I thought ?

|||Use your keyboard's Delete key to remove the extra spaces around thesingle quote and percentage characters. (A total of 4 spacesshould be deleted.)
That should leave you with the following statement:
SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL &"'" & myCharacter &"%' "
|||

tmorton wrote:

Use your keyboard's Delete key to remove the extra spaces


In a recent technical journal, it suggested that the Backspace key canalso be used to remove characters. I haven't tried it myself, as I fearthat I'm not advanced enough. But obinna123 seems pretty advanced, sohe may want to try that technique to removed the extra spaces.