Inserting data into table from DTS package
-
HELP!!!
What is wrong with the following statement? I simply want to insert a row from a DTS Package into one of my tables:
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = constring
objCommand.CommandText = "INSERT INTO site.dbo.invoiceheader " & _
"(RecordLetter) " & _
"VALUES " & _
"('A')"
objCommand.CommandType = adCmdText
objCommand.Execute
Set objCommand = Nothing
The error I get is
" Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
..which I've read indicates that there is something wrong with how I've specified the command (which makes sense, because the actual SQL statement works fine when entered directly from SQL), but I can't find examples of doing this! Please help!
-
Could you be a little more specific in what you are trying to achieve.
IE- Where is the coding running from (activex?)
Why don't you do a normal insert statement?
------------------------------
The Users are always right - when I'm not wrong! -
I'm doing a REALLY basic thing. I'm running an activeX script in my DTS package that selects some data from one table and then inserts that data into another table, but I've apparently got the syntax wrong for executing an INSERT statement from within the DTS package's activeX script.
Could you give me an example of how you would write an insert statement that ran directly from an activeX script in DTS?
-
Now why would you be running an Insert statement within an ActiveXScript task in DTS?
You could try capturing the concatenated SQL string and seeing if there is a syntax error. Maybe you're missing a quote, or you have an extra quote.
--------------------
Colt 45 - the original point and click interface
-
instead of using activeXScript why don't you use "execute Sql Task". It would be a lot easier and convient. just paste your query it in..then indicate a workflow(if needed).
Glad to Help,Crischell Olegario
-
A few things to check... Does your table, [invoiceheader], really only have a single column in it and maybe an ID column? Do all of your other columns, if any have a default value defined and all additional columns set to NOT NULL? Do you have any unique indexes defined for this table?
Honestly, I am quite certain that there is a better way of handling this type of INSERT; ActiveX-Script being about the worst based on a performance perspective... But, I'm not here to pass judgement. I believe your error is caused by a table constraint.
You may want to drop your "site.dbo." prefixes in your table name. This is most likely already defined in your ActiveConnection assignment.
-Mike Gercevich
-
I agree with everybody else that an ActiveX Script task is not the best way of achieving this, but if you insist to do so... I guess u must have your reasons...
by looking at the code, it is either a SQL error or a VBScript error you are getting.
on the SQL side, the only thing I can think of is that the type of the field is not correct, or that the value you are trying to insert is incorrect for that field definition.
on the VBScript side, and based on your variable names, it seems like you are assigning the connection string to the active connection property, and that is going to throw an error like the one you are getting.
The active connection property stores the connection object for that command, and not the connection string. You will need to open a connection before executing your command.
hope this helps...
Rayfuss.-
-
Matthew:
I hope you solved the problem....if not you can try this ....
you remove this statement from your code
objCommand.CommantType=adCmdText
it ll work properly......
because this statement ll work in VB not in ActivexScripting.....VB compiler understands this(adCmdText) as a aconstant value....where as ActiveXScripting treats it as a variable of default type.if u need to use these commandtype statements u can use the values directly.......various commandtype values are
adCmdStoreProc =4
adCmdText = 1
adCmdTable = 2
adCmdFile = 256
adCmdTableDirect= 512
adCmdStoredProc= 4
or try this
objCommand.CommandType = 1
or
Dim adCmdText
adCmdText =1
objCommand.CommandType = adCmdText
ok...byeeeee
all the best...
Regards
vishnu
-
Not sure if this applies to you, but when I'm concatenating various strings into one sql string that I want to execute, explicitly casting variable with the cstr() function helps to avoid those problems.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply