 |
Talahaski
Enchanter
Joined: 10 Oct 2000
Posts: 656
Location: USA
|
 Posted: Sun Aug 25, 2002 2:46 am
ADO
|
Hello,
Can somebody help me with a ADO script that will insert a varable list into a database.
I have a string list call skill_list
I have a database called skill_DB
skill_DB has a table called skill_table with a single field called skill.
I want to open the database and loop through skill_list inserting each entry from skill_list into skill_table.
Then close the database and free the variables.
I have the following:
#VAR Conn %comcreate( "ADODB.Connection")
#CALL @Conn.Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=skill_DB.mdb")
/* missing code to loop and insert here */
#CALL @Conn.Close
#VAR Conn ""
Talahaski
Processor PIII 1000
Memory 265MB
Video NVIDIA 64MB
Windows XP
Zmud Version 6.34
Zmapper Version 1.10 |
|
|
|
|
Kjata
GURU
Joined: 10 Oct 2000
Posts: 4379
Location: USA
|
Posted: Sun Aug 25, 2002 12:46 pm
|
You should try to learn SQL. This is one of the most basic actions you can do in SQL and you can't expect to use ADO scripting without knowing SQL. A great place to start learning is:
http://www.sqlcourse.com/
and its continuation:
http://www.sqlcourse2.com/
Anyway, here is what you should put between what you already have:
#FORALL @skill_list {#CALL @Conn.Execute(%concat("INSERT INTO skill_table (skill) VALUES ('", %i, "')"))}
Kjata |
|
|
|
|
Talahaski
Enchanter
Joined: 10 Oct 2000
Posts: 656
Location: USA
|
Posted: Sun Aug 25, 2002 8:41 pm
|
Thanks for the help, I know the basics of SQL, I was just not sure how to use a zmud variable inside the sql statement.
I've got another question though, how can I get this insert to work if @skill_list contains entries with single quotes in them.
For example I may have "Peat's Pickle Picker" as an entry.
#FORALL @skill_list {#CALL @Conn.Execute(%concat("INSERT INTO skill_table (skill) VALUES ('", %i, "')"))}
Talahaski
Processor PIII 1000
Memory 265MB
Video NVIDIA 64MB
Windows XP
Zmud Version 6.34
Zmapper Version 1.10 |
|
|
|
|
Kjata
GURU
Joined: 10 Oct 2000
Posts: 4379
Location: USA
|
Posted: Mon Aug 26, 2002 12:21 am
|
You need to replace single quotes with two single quotes:
#FORALL @skill_list {#CALL @Conn.Execute(%concat("INSERT INTO skill_table (skill) VALUES ('", %replace(%i, "'", "''"), "')"))}
Kjata |
|
|
|
|
Talahaski
Enchanter
Joined: 10 Oct 2000
Posts: 656
Location: USA
|
Posted: Mon Aug 26, 2002 12:42 am
|
ahh great!
Thanks for all the help today Kjata.
Talahaski
Processor PIII 1000
Memory 265MB
Video NVIDIA 64MB
Windows XP
Zmud Version 6.34
Zmapper Version 1.10 |
|
|
|
|
|
|
|
