Writing to SQL Server in VB - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Writing to SQL Server in VB

Author

Topic

DOlivastro
Starting Member

41 Posts

Posted - 2004-06-03 : 17:06:36

I have to write a program (in VB) that will take records from an Access table, make some alterations, and then add them to an SQL Database.

I am using ADO to connect to both Access and SQL Server.

Is this correct:

dim a as adodb.connection ' connection to access
dim s as adobd.connection ' connection to SQL Server
dim ra as adodb.recordset
dim rs as adodb.recordset

set a = new adodb.connection
set s = new adodb.connection

call a.open (...)
call s.open (...)

set ra = a.execute ("SELECT * FROM t_Data")
set rs = s.execute ("SELECT * FROM t_Output")

do while (not ra.eof)
call rs.addnew
[...]
call rs.update
call ra.movenext
loop

What bothers me is the line "rs = s.execute ("SELECT * FROM t_Output")
Do I really need to open a recordset, if I only use it for output? Isn't that unnecessary overhead?

Thanks,
Dom

stephe40
Posting Yak Master

218 Posts

Posted - 2004-06-03 : 17:27:05

Well.. since your using the addnew and update methods of the "rs" recordset, yes you need to create it.

If you dont use the methods, a more efficient way would be to just send the insert via the connection's execute method.


do while (not ra.eof)
  s.execute("insert into t_Output fields(...) values (...)", ,adExecuteNoRecords)
  ra.movenext
loop

- Eric

DOlivastro
Starting Member

41 Posts

Posted - 2004-06-03 : 17:42:24

I know what your saying. But do I need to select the entire table? Can I get by just selecting nothing, with a "WHERE 1=2" clause? It seems I still have the rs object at that point.

stephe40
Posting Yak Master

218 Posts

Posted - 2004-06-04 : 15:55:27

I would assume it would work, but I dont know for sure. I have NEVER used the addnew and update methods of a recordset. I have always formed my SQL manually and sent it through the connection's execute method.

Check out this book for more information: http://www.amazon.com/exec/obidos/tg/detail/-/189311516X/qid=1086378778/sr=8-3/ref=sr_8_xs_ap_i3_xgl14/104-0732976-1402300?v=glance&s=books&n=507846

- Eric

DOlivastro
Starting Member

41 Posts

Posted - 2004-06-07 : 10:45:50

I always try to use the execute command ("INSERT INTO ...") instead of addnew. But how do you do that if one of the values is a string that contains a quote? I always get an error message because of this, which doesn't happen with addnew

princeofegypt
Starting Member

2 Posts

Posted - 2004-06-10 : 18:18:07

always replace a quote with double quotes using

newstring = Replace ( oldstring , "'" ,"''")

Prince Of Egypt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources