Can’t get SQL statement to work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can’t get SQL statement to work

Author

Topic

joopdog
Starting Member

4 Posts

Posted - 2006-02-15 : 11:36:25

I’m using VB.NET to extract data from an Access database, simple enough. However, I type in the following line:

Sql = "SELECT * FROM [IPAddressList] WHERE [StartIPNo]>=" & IP_Address And "[EndIPNo]<=" & IP_Address

It doesn’t work.

If I type in:

Sql = "SELECT * FROM [IPAddressList] WHERE [StartIPNo]>=" & IP_Address

The above sql line works.

I now realize SQL commands is very sensitive.

FIELD NAMES:

quote:
StartIP |EndIP |StartIPNo |EndIPNo |CountryCode
5.0.0.0 |5.163.66.79 |83886080 |94585423 |ZA
5.163.66.80 |5.163.66.95 |94585424 |94585439 |SE
5.163.66.96 |5.255.255.255 |94585440 |100663295 |ZA

If the user types in a value, I want it to check the values between the StartIPNo and EndIPNo and return the CountryCode.

Any help would be appreciated

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-02-15 : 11:39:31

the problem is to do with your built-up sql string...you are missing an &....it should "& And [EndIPNo]<=" & IP_Addr

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-15 : 11:44:29

U can check it urself using the following 2 ways

1. Instead of a prepared statement, sent a hard coded one
2. put a label in ASP and pass the prepared SQL in to that and c whether it is of the correct format.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-15 : 12:17:50

Your other error is you did not put a string delimiter around the values in your code when you concatenated them all together. One of the many reasons you should NOT concatentate sql strings like this. Ever.

Use parameters ... That's what they are for!

Simply put ? placeholders in your command where you want a parameter to go, and then when you declare your command object add parameters and their values to the command's parameters() collection.

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx

Also, post Access questions in the MS Access forum -- this is a SQL Server forum, which handles these things very differently.

joopdog
Starting Member

4 Posts

Posted - 2006-02-15 : 14:07:46

Thanks Murphy

Sql = "SELECT * FROM [IPAddressList] WHERE [StartIPNo]<=" & IP_Address & "And [EndIPNo]>=" & IP_Address

That corrected my problem.

SQL is very touchy.

Thanks everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources