Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Data type mismatch need to convert string to a number

 
k Ratliff
Greenhorn
Posts: 6
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, I want to apologize in advance if I am posting in the wrong area or if this topic has been address. This is my first time posting to a forum and I did search the forum. That said, here is my coding dilemma.

The field (custID) I am working with in the access database is a number. In my code, I retrieve the value from the custID field and then convert it to a String so I can assign the value to a textField. My problem is that I am trying to use the same custID value to retrieve values from a different table. I have attempted to get the vale from the textField and convert it back to a number so I can query the second table. I have been working on this code for three days now and I can't figure out why I continue to get the error data mismatch in criteria expression.

Here is my code I am working with. I am very new to Java. Any suggestions will be welcomed.



Thanks.
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

use parameters (binds) in your query:


setInt( index, value ) sets the designated parameter to the given vallue. Index is a number of ? parameter in the query.

Use parametrized queries whenever possible, that usually give you better performance (but not always - there are seldom exception to this "rule of thumb").
For the database the query is like source code, the database must first parse it (compile the source code of the query and find the best execution plan).
In most modern databases parametrized query is parsed only once, then stored in the memory and reused on consecutive calls to PrepareStatement without parsing
(only with binding parameters to placeholders).
Hardcoded queries like "SELECT ... WHERE custId = 1", "SELECT ... WHERE custId = 2" etc. are completely different queries for the database,
and the database must parse each of them wasting cpu time and memory.
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
and also - don't sue "SELECT *", name your fields explicitly in the select statement "SELECT field1, field2 ... FROM ...".
SELECT * tells to the database "give me the WHOLE row" (all fields). The database don't know that you just want only field2 and field4,
you told to it "select *", so it retrieves and sends you THE WHOLE ROW (even if the table has 3000 fields) from the disk and sends you over the wire.
There are also some optimizations that database can perform when you explicitly show fields you want to get,
for example if you execute query: SELECCT field1, field3 FROM... and there is a composite index on field1 + field3,
then database can retrieve these values directly from this index, not touching the table at all,
but if you say to it "SELECT *" - no way, it must read the table.
 
k Ratliff
Greenhorn
Posts: 6
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ireneusz Kordal wrote:Hello,

use parameters (binds) in your query:


setInt( index, value ) sets the designated parameter to the given vallue. Index is a number of ? parameter in the query.


Ok, so am I literally supposed to put a ? in my query or is it supposed to be replaced with a specific parameter. Because I changed my query to exactly what you typed and added the setInt method, but I got the following error: Syntax error in string query expression 'CustID = Pa_RaM000'. I guess I am not understanding what you mean by use a parameter in your query. Does it have something to do with the fact that I am using MS Access? I did change my queries to select the specific fields. Thank you for the suggestion.
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
(sigh...) Yes, you're supposed to do what Ireneusz suggested. Only it would have helped if he hadn't made a small mistake and left an extra quote at the end of the SQL statement. Can I leave you to look for that by yourself?
 
k Ratliff
Greenhorn
Posts: 6
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you. It worked like a charm. I should have caught that single quote.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic