Source language: Translate to:

nbpquery problem..

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

nbpquery problem..

Postby TinTin » Mon Apr 23, 2012 5:49 am

Hi all!

I'm running SQL DB and have run into a problem with DBquery..
It's MS SQL DB

I'm getting an error when searching for records containing Mr

dbpQuery "Customer" "Names" "Title = [#34]Mr[#34]"

I'm getting alertbox with:

Invalid Column Name 'Mr'.. any help would be appreciated.

Thanks
TinTin
 
Posts: 162
Joined: Sun Dec 06, 2009 4:03 am
Location: UK

Postby Gaev » Mon Apr 23, 2012 6:33 am

TinTin:

According to this web page ... http://msdn.microsoft.com/en-us/library ... 80%29.aspx ... NAMES is a keyword that could be reserved in future releases of SQL Server as new features are implemented ... perhaps "the future" is "now".

As suggested numerous times, always use "non-real english words" for Database, Table, View and Field names ... e.g CustomerNames or cTitle or ttNames etc. ... so reserved words don't come back to bite you.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby TinTin » Mon Apr 23, 2012 6:50 am

thanks for the check on this, however I have changed the ID table / names but still same result... :-(
TinTin
 
Posts: 162
Joined: Sun Dec 06, 2009 4:03 am
Location: UK

Postby Wrangler » Mon Apr 23, 2012 7:47 am

Please post the exact code you are using.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Mon Apr 23, 2012 7:48 am

TinTin:

Copy (from your pub) and Paste (here) the exact commands you are using ... both, for the dbpQuery command ... and what you use to create the Database Table ... dbpCreateTable and/or the result of dbpGetFieldDefs.

Also, does it only balk at this particular Query ? ... try doing Queries on other fields/values ... and tell us if those work.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby TinTin » Mon Apr 23, 2012 7:55 am

Table Created:


dbpCreateTable "Patient" "RoomAllocation" "PointNo Integer;Room String(20);Title String(4);[#34]First Name[#34] String(35);Surname String(35);[#34]Assigned Consultant[#34] String(25);Additional String(60);[#34]Entry Date[#34] String(10);[#34]Entry Time[#34] String(10);ENserial String(20);Country String(15)"


Query String:

dbpQuery "Patient" "RoomAllocation" "Title = [#34]Mr[#34]"


Note: Dbpfind seems to work, but I need to make changes only to a matched record! so need to use DbpQuery for this to work.

Cheers chaps.
TinTin
 
Posts: 162
Joined: Sun Dec 06, 2009 4:03 am
Location: UK

Postby Wrangler » Mon Apr 23, 2012 8:00 am

Your problem is in the createtable action. This:

[#34]First Name[#34] String(35);

should be: First_Name String(35); with no quote symbols. All quote symbols should be removed. Also note it is a good idea to use underscores for spaces in field names.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby TinTin » Mon Apr 23, 2012 8:03 am

Ah spaces!! doh thanks for the quick reply.. I'm usually quite good at using the underscores!!

I'll modify.

Thanks Wrangler top work.
TinTin
 
Posts: 162
Joined: Sun Dec 06, 2009 4:03 am
Location: UK

Postby Wrangler » Mon Apr 23, 2012 8:07 am

Also note that you will need to delete the old db before creating a new table and testing the code again. If it finds an existing copy, it will not create a new one.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby TinTin » Mon Apr 23, 2012 8:50 am

Ok...

Old table deleted, new one created..

dbpCreateTable "Patient" "RoomAllocation" "PointNo Integer;Room String(20);Title String(4);First_Name String(35);Surname String(35);Assigned_Consultant String(25);Additional String(60);Entry_Date String(10);Entry_Time String(10);ENserial String(20);Country String(15)"

Still get the same issue..

It won't allow me to search the Room field..

Really strange!
TinTin
 
Posts: 162
Joined: Sun Dec 06, 2009 4:03 am
Location: UK

Postby TinTin » Mon Apr 23, 2012 8:54 am

I have a record in the db - in Rooms field - I have Room 1

dbpQuery "Patient" "RoomAllocation" "Room = Room 1"

Same thing which was happening earlier with the

dbpQuery "Patient" "RoomAllocation" "Title = [#34]Mr[#34]"

It seems to be consistent throughout.. damn.
TinTin
 
Posts: 162
Joined: Sun Dec 06, 2009 4:03 am
Location: UK

Postby Wrangler » Mon Apr 23, 2012 8:56 am

It's not enough just to delete the table. You should delete the DB, create a new db and then a new table.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby TinTin » Mon Apr 23, 2012 8:57 am

Hold fire...

My fault... ! didn't keep grid open in background!

Doh
TinTin
 
Posts: 162
Joined: Sun Dec 06, 2009 4:03 am
Location: UK

Postby Wrangler » Mon Apr 23, 2012 8:58 am

dbpQuery "Patient" "RoomAllocation" "Room = Room 1"


If room is a string, you must include quotes:

dbpQuery "Patient" "RoomAllocation" "Room = [#34]Room 1[#34]"
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby TinTin » Mon Apr 23, 2012 12:17 pm

DBpro experts...

Sorry to disturb you all...!

My search issues have now been solved - Dbpfind seems to be working much better than the query...

However, I'm trying to just change the active record's field data from the database.

At the moment if I use

dbpStrReplace "Patient" "RoomAllocation" "Additional" "Entered" "Exited"


It replaces ALL records which match "Entered" to Exited not just the found record which I am highlighting by finding the room name.

My method is:

- Search For The 'Room Name' in the Database.
- When matched..
- Replace The 'Additional' Field Data with the new data only for the found record for the room.



I'm sure this is really easy for most people, it seemed to work when I was working with access dB but I have begun to delve into SQL for this now.

Thanks peeps.

Tin tin
TinTin
 
Posts: 162
Joined: Sun Dec 06, 2009 4:03 am
Location: UK


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests