help!!! insert select statement
-
hello,
i am using vbscript to fetch select system parameters and storing them in variables. then while trying to insert these values:
here's the statement i am using:
SQL = "insert into demo1(servername, cpu, loadpercentage, pagespersec, phymem, virtmem) select '" & Computer & "','" & cpuid & "', " & cpuload & "," & ppsec & "," & phymm & "," & virtmm
i am getting the following error:
ADODB.Recordset: Arguments are of the wrong type, are out of acceptable range or are in conflict with one another.
the values and datatypes i have used seem to be well within range.i dont knw wat the problem is. i tried printing the sql string and it displays in the command console with the values stored in the variable.
when i tried executing the same query with those values in SQL Server 2000, it stored just fine!!!
Can u please help me out???
-
Don't know what the rest of your code looks like but here's something google threw out that may help you...
**ASCII stupid question, get a stupid ANSI !!!** -
Good link sushila - probably a fault of the other parameters on the recordset object rather than a fault of the SQL statement you have built... May I suggest (off topic) that you use stored procedures rather than building SQL queries using string concatenation? Many many reasons why it should be so - look up "sql injection" for example...
Anyhow, to further clarify things, do you have any other working insert statements in your code? Are you able to replace the SQL query with something simple like
declare @x table(a int)
insert into @x(a) values(1)
If that works and your other SQL query doesn't, then fix your SQL. If it doesn't work then your ADO recordset params are wrong...
-
this is the rest of my code:
****************************
'ADODB Connection
Dim AdCn
'SQL Query string
Dim SQL
'ADODB Recordset
Dim adrec2
Set AdCn = CreateObject("ADODB.Connection")
Set AdRec2 = CreateObject("ADODB.Recordset")
'variable for holding total physical memory
dim phymm
'variable for holding available virtual memory
dim virtmm
'variable for holding cpu load
dim cpuload
'memory\pages/second
dim ppsec
'Update COnnection string with you servername, databasename, login and password.
connstring = "Provider=SQLOLEDB;Data Source=SRIHARI\GANESH;Initial Catalog=master;user id = sa;password=sowmiya "
AdCn.Open = connstring
' This section is to fetch the computer name
Set WshNetwork = WScript.CreateObject("WScript.Network")
Computer = WshNetwork.ComputerName
Set wbemServices = GetObject("winmgmts:\\" & Computer)
'physical memory object set
Set wbemObjectSet = wbemServices.InstancesOf("Win32_LogicalMemoryConfiguration")
'cpu load object set
Set wbemcpuloadSet = wbemservices.InstancesOf ("Win32_Processor")
'pages per sec obj set
Set wbempagepsecSet = wbemservices.InstancesOf("Win32_PerfRawData_PerfOS_Memory")
'avg disk queue length
'This part fetches the physical memory details :
For Each wbemObject In wbemObjectSet
phymm = wbemObject.TotalPhysicalMemory
virtmm =WbemObject.Availablevirtualmemory
next
'This section fetches the CPU LOAD and CPU ID/NAME:
For Each wbemcpuload in wbemcpuloadSet
cpuid = wbemcpuload.deviceid
cpuload = wbemcpuload.Loadpercentage
next
'This section is used to fetch the Pages per second:
For each wbempagepsec in wbempagepsecSet
ppsec = wbempagepsec.PagesPerSec
next
adrec2.source = "demo1"
adrec2.activeconnection = Adcn
adrec2.cursortype = 2
adrec2.locktype = 3
SQL = "insert into demo1(servername, cpu, loadpercentage, pagespersec, phymem, virtmem) select '" & Computer & "','" & cpuid & "', " & cpuload & "," & ppsec & "," & phymm & "," & virtmm
adrec2.open
@ Sushila:
----------
thx a ton for ur help! i altered the recordset properties like in the eg from the link u had sent. it has removed the error,
but the parameters are not getting stored!
@ Ian:
------
I am using only one insert statement in the code. and after altering the recordset properties, the error is gone, but the values are not getting stored!
-
Why do you want to "Select statement after insert" here? directly add "values and Parameters" pass the variable names. You already got the values into variables.
---
SQL = "insert into demo1(servername, cpu, loadpercentage, pagespersec, phymem, virtmem) values( '" & Computer & "','" & cpuid & "', " & cpuload & "," & ppsec & "," & phymm & "," & virtmm &")"
-
sowmiya - it may be too late at night for me to respond coherently -
- but a couple of things in your post are confusing...1) What is the "demo1" you have in your .source ?!?! shouldn't it be your "SQL/adCmdText"..?!?!
2) Did you try a simple insert statement as Ian suggested and did it work ?!
3) I notice a few "for each" statements - but only one value seems to be stored ?!?!
4) Lastly, if you get an insert working correctly, you should (again as Ian suggests) - pass them as parameters to a stored procedure.
I'm posting one more link with some sample code that may give you some pointers in the right direction..
**ASCII stupid question, get a stupid ANSI !!!** -
Change "adrec2.open" to:
AdCn.Execute SQL, , adCmdText + adExecuteNoRecords
Since your connection string defaults to the master database, I would expect the demo1 table to be there!
You could change the connection string to:
connstring = "Provider=SQLOLEDB;Data Source=SRIHARI\GANESH;Initial Catalog=databasename;...
I really hope that is not your sa password, if it is change it ASAP.
Or you could change your query to:
SQL = "insert into databasename.username.demo1..
You cannot Open a recordset for an INSERT query.
Andy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply