March 29, 2024, 07:09:05 AM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


Updating dBase Tables

Started by grid51, February 15, 2008, 01:00:48 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

grid51

February 15, 2008, 01:00:48 PM Last Edit: February 15, 2008, 04:25:54 PM by grid51
This is my first post so please excuse if in wrong area.
How can I update dBase files as in Microsoft dBaseIV or V .dbf files?
I can access but cannot update or change data. Does EBasic only support .mdb files?
Any help appreciated.
It may be that I'm not getting the syntax right so perhaps someone could give me some more
examples other than what came with EBasic that might help. I've tried just about everything but
get errors or no errors but no change in a column after attempted update statement.
I've tried both direct dbexecSQL and prepared SQL . Can access fine but no update or changes on
data occur. Pulling what hair I have left out!
Thanks

LarryMc

Yes, EBasic will allow you to access and modify dbase files if you get the right driver connected and use the correct syntax.

Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

grid51

 Thanks Larry, that may be my problem, esp syntax but don't know where to go from here.
I could use more examples on update statement other than what came with EBasic helpfile and
samples.

REDEBOLT


http://www.connectionstrings.com/
http://www.connectionstrings.com/?carrier=dbffoxpro
http://www.w3schools.com/sql/default.asp

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=Admin;Password=;

ODBC
Standard
Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\mydbpath;

Remote drives
Driver={Microsoft dBase Driver (*.dbf)};datasource=dBase Files;
SQL syntax example: "select * from \\remotemachine\thefile.dbf"

http://www.w3schools.com/sql/sql_update.asp
Regards,
Bob

billhsln

When all else fails, get a bigger hammer.

grid51

Hey, thank you guys for all your help!  I will study all this stuff and see if it can solve my problem.
The syntax I've been using seems ok, just not working. I figure I must be doing something wrong.

Again, my thanks to you all!!!


grid51

billhsln

I may not be very experienced in EBasic, but I am VERY experienced in SQL.  Give some examples of what you are trying to do and maybe I can help.

Bill
When all else fails, get a bigger hammer.

grid51

Sorry I haven't been able to get back to this dbase problem I'm having, time is so short.
I've still not had much success on updating dbase .dbf or access .mdb files. I can access but not update. I have
played with sytax to no end and keep getting errors. Main error says to few parematers expected.
expected 2. or sometimes it's more sometimes less. I only have one example really of the update
statement used in a program and that was bill's but it's not really helping in my case. I also think it
has something to do with the tables. I can run his program and it works but I can't update his
tables outside of his program. I will try to put together some of my code and example of what I've
been trying and get back here with it as soon as I can. I haven't been working with data bases much
before now and I am somewhat confused as to what constitutes a table. Is it the data base file  or
is it in the file as what I have seen it's seems like it can be both. I'm pretty sure I'm gonna have to
do some more study on this but if anyone can offer some clarification on this it might be helpful.
I will mainly be working with existing files but will need to creat my own as well. But right now I am
just trying to get the basic SQL fuctions down, Select doesn't seem to be a problem and I haven't
even trien to insert a new row. I'm just stuck on the Update of a file to change infomation in a file.
I'll check back later with you guys.
Thanks
grid

Ionic Wind Support Team

Example update/insertions can be found in the sample programs include with Emergence.

"Addressbook.eba" is a good starting point.

Paul.
Ionic Wind Support Team

grid51

 Thanks Paul but I only see one UPDATE example .
I can insert and select but cannot update even on a .mdb database I created. Here is an example.
Maybe someone can tell me what I am doing wrong.
I left in some rem lines so you can see what I did to create and put a couple of Items in the table.
Can't get past error on Update...
OPENCONSOLE
DEF fname[26],lname[26] as ISTRING
DEF tid,id as INT
DEF nlast[26] as ISTRING
DEF pDrivers,pTables,pBindings as POINTER
'pDrivers = dbEnumDrivers()
'IF pDrivers <> NULL
'   PRINT "Available database drivers:"
'   FOR temp = EACH pDrivers as STRING
'      PRINT #temp
'   NEXT
'   ListRemoveAll(pDrivers,TRUE)
'ELSE
'   PRINT "Unable to enumerate drivers"
'ENDIF
'DO:UNTIL INKEY$ <> ""
'PRINT "Creating database atest.mdb"
'PRINT dbCreateMDB(GETSTARTPATH + "atest1.mdb")
pdb = dbConnect("Microsoft Access Driver (*.mdb)",GETSTARTPATH + "atest1.mdb","")
'pdb = dbConnectDSN("IONICWIND TEST","")
IF pdb <> NULL
   PRINT "Connection established to " + GETSTARTPATH + "atest1.mdb"
'hstmt = dbExecSQL(pdb,"CREATE TABLE  atest (id integer,lname CHAR(26),fname CHAR(26))")
'hstmt_in = dbExecSQL(pdb,"INSERT INTO atest (id, lname, fname) VALUES ( 1, 'jimmy', 'joe')")
'hstmt_in = dbExecSQL(pdb,"INSERT INTO atest (id, lname, fname) VALUES ( 2, 'sammy', 'sue')")
   pTables = dbListTables(pdb)
   PRINT "Tables:"
   IF pTables <> NULL
      FOR temp = EACH pTables as STRING
         PRINT #temp
         PRINT "Cardinality:",dbCardinality(pdb,#temp)
         PRINT "\tColumns:"
         pColumns = dbListColumns(pdb,#temp)
         IF pColumns <> NULL
            FOR temp2 = EACH pColumns as STRING
               PRINT "\t\t",#temp2
            NEXT
            ListRemoveAll(pColumns,TRUE)
         ENDIF
      NEXT
      ListRemoveAll(pTables,TRUE)
   ELSE
      PRINT "Unable to list tables"
   ENDIF
   PRINT
    nlast="Smithy "
    tid=1
   hstmt_up = dbExecSQL(pdb,"UPDATE atest SET lname=nlast WHERE id=tid") : ' This is problem
   error = dbGetErrorCode(hstmt_up)
   IF LEN(error)
      PRINT
      PRINT "Error Code: ", error
      PRINT "Error Text: ", dbGetErrorText(hstmt_up)
' This is where I get error [ Microsoft Access Driver] too few parameters. expected 2
   ENDIF
'      dbFreeSQL(hstmt)
        dbFreeSQL(hstmt_up)
'        dbFreeSQL(hstmt_in)
   dbDisconnect(pdb)
      PRINT "Anykey"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
ENDIF

LarryMc

changehstmt_up = dbExecSQL(pdb,"UPDATE atest SET lname=nlast WHERE id=tid") : ' This is problem

to hstmt_up = dbExecSQL(pdb,"UPDATE atest SET lname=nlast WHERE id="+str$(tid)) : ' This is problem


INTs have to be covered to STRINGs.

Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

grid51

Thanks Larry , but that didn't work either.  It did change the error to 1 expected instead of 2.
Puzzling???.

Ionic Wind Support Team

You're constructing a string.  The initial string you used is a constant so it doesn't know what nlast or tid is.

You have to concatenate the separate parts together,  a simple print in a console would tell you if you had it right.

hstmt_up = dbExecSQL(pdb,"UPDATE atest SET lname=nlast WHERE id=tid") : ' This is problem

Corrected:

hstmt_up = dbExecSQL(pdb,"UPDATE atest SET lname='" + nlast+ "' WHERE id=" + ltrim$(str$(tid))) :

Also

nlast="Smithy "

Shouldn't have a space in it.  A space is a valid character for a text field in the database.

nlast="Smithy"

Paul.
Ionic Wind Support Team

grid51

Wow!!! It works!,   Thanks a million Paul.
Yes, I had tried a lot of combinations on building the string but not yours. The qoutes " inside the '
did the trick I guess. Also I didn't even think about their being a space in the int string. On the
"smithy "  I originally didn't have a space there, I was just trying everything cause I didn't know
what this thing wanted. I think hopefully now this will get me started again as I was stuck but good.
Thank you again Paul, and for your quick response. Now I will go back and try this on the .dbf's

Steven
P.S. I see your a night owl too...

pistol350

Quote from: grid51 on February 29, 2008, 02:04:09 AM
...
I was just trying everything cause I didn't know what this thing wanted
...
I'll give you a tip then :D

Just be very very patient with "this thing",
be very very gentle when you talk to "this thing", don't hesitate to breath smoothly, it helps things to get better.
when/while you talk to "this thing"drink a cup of tea or two , well three if you really feel like  :D
If it's still not enough don't hesitate to say hi to the cup of coffee that is just on the upper right corner of your desk  ::)
Well well, i think that right after all that deep involvement of yours to "this thing"'s wish, "this thing" should let you know everything it needs and desires  ::)

However, if things still don't get the way you wanted,
please don't get mad at "this thing" or you may be tempted to madly push the "DEL" button of your keyboard.
Well, we are agree about it, pushing the "DEL" button is not your wish so when you reach that critical point,
better resort to come to this forum and left click "new Topic" and share your headach with us  ;D we'll be glad to ease your pain whenever it is possible :)
Well,you don't need to reach the critical point to come to the forum by the way.  :)
Regards,

Peter B.

Ionic Wind Support Team

You're welcome.

Just so you are clear the single quote is inside the double quoted fixed part of the string.  When you are setting a text field most db's require surrounding the data with single quotes to account for embedded spaces.

What you were trying to construct was a string that looked like this to the DB driver:

UPDATE atest SET lname='smithy' WHERE id=1

You had a variable for the last name and ID so breaking it down into parts you have:

"UPDATE atest SET lname=' " +
nlast +
" ' WHERE id=" +
ltrim$(str$(tid))

Not trying to confuse you, since it works now, just trying to help you construct other SQL statements in the future.

Paul.

Quote from: grid51 on February 29, 2008, 02:04:09 AM
Wow!!! It works!,   Thanks a million Paul.
Yes, I had tried a lot of combinations on building the string but not yours. The qoutes " inside the '
did the trick I guess. Also I didn't even think about their being a space in the int string. On the
"smithy "  I originally didn't have a space there, I was just trying everything cause I didn't know
what this thing wanted. I think hopefully now this will get me started again as I was stuck but good.
Thank you again Paul, and for your quick response. Now I will go back and try this on the .dbf's

Steven
P.S. I see your a night owl too...
Ionic Wind Support Team

grid51

 Thanks again Paul and to every one who tried to help me. Much appreciated.
Yes I think I get it now. I will have to get it, as some of these db's have over 140 columns with
various data types. So I will probably be back with a question or two.

Thanks again,

Steve

REDEBOLT

February 29, 2008, 03:17:21 PM #17 Last Edit: February 29, 2008, 03:20:53 PM by REDEBOLT
QuoteFinally found my avatar back to 2004 :)

What is that thing? It looks like a dead body hanging onto a spinning thing. ???
Regards,
Bob

pistol350

Quote from: REDEBOLT on February 29, 2008, 03:17:21 PM
QuoteFinally found my avatar back to 2004 :)

What is that thing? It looks like a dead body hanging onto a spinning thing. ???

Not to start an off topic talk, i created a new Topic here : http://www.ionicwind.com/forums/index.php/topic,2286.msg19924.html#msg19924
Regards,

Peter B.