vb script insert problems
-
The following code, the insert statement I'm trying to execute in the while loop gives me an error saying 'PharmacyGenericDrugs' is an invalid object. I don't get it. That is the name of the table. What I'm trying to do here is load a .csv file into a table. I can't seem to get bcp to work, so this is the other way I know. The server is SQL 2005 SQLExpress so I don't have SSIS at my disposal. The code doesn't work on my SQL 2005 box either.
set oCn = CreateObject("ADODB.Connection")
cnStr = "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=memSQL05;InitialCatalog=NPNPlus;Mode=ReadWrite"
oCn.Open cnStr
cnStrTxt = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\websites;Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
set cnTxt = CreateObject("ADODB.Connection")
set rsTxt = CreateObject("ADODB.RecordSet")
sqlStr = "select * from PharmDrugs.csv"
cnTxt.Open cnStrTxt
rsTxt.Open sqlStr,cnTxt
do while not rsTxt.eof
sqlStr = "insert into PharmacyGenericDrugs(DrugName,DrugGroup) values('" + rsTxt(0) + "' ,'" & rsTxt(1) & "')"
oCn.Execute SqlStr
rsTxt.MoveNext
loop
oCn.Close
rsTxt.close
set rsTxt = nothing
set oCn = nothing
-
What schema is the table in? If it's not dbo, or the default schema of the user is not dbo, you will have to explicitly include it in your insert statement.
-
The schema is dbo. I've tried it with the server.dbo.filename and it fails that way as well. Also just dbo.filename. Same failure.
-
The only other thing I can see is that you have left out the space in Initial Catalog in the connection string.
-
Richard Fryar (3/9/2009)
The only other thing I can see is that you have left out the space in Initial Catalog in the connection string.That is exactly what the problem was. Thank you ever so much. Do we rate the answers on this post? If so you get the top rate!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply