Genius123
asked on
SQL array function
Hello,
Within vb script, I have a function that adds a record to a SQL table and it works fine.
rst.Open "Select * FROM BW_AUFTR_KTXT", oADOConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
rst.AddNew Array("ID", "LFD_NR", "TEXT_ID", "DRUCK_KZ", "MOD", "BEZ"), Array(Item.UserProperties( "CSIOrderN o1").value , "45", "45", "X", "0", "LIFT GATE REQUEST")
rst.Update
Is it possible to select all the fields and update them without calling out the field names. For example:
rst.Open "Select * FROM BW_AUFTR_KTXT", oADOConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
rst.AddNew Array(test1,test2,test3)
rst.Update
Thanks, Joel
Within vb script, I have a function that adds a record to a SQL table and it works fine.
rst.Open "Select * FROM BW_AUFTR_KTXT", oADOConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
rst.AddNew Array("ID", "LFD_NR", "TEXT_ID", "DRUCK_KZ", "MOD", "BEZ"), Array(Item.UserProperties(
rst.Update
Is it possible to select all the fields and update them without calling out the field names. For example:
rst.Open "Select * FROM BW_AUFTR_KTXT", oADOConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
rst.AddNew Array(test1,test2,test3)
rst.Update
Thanks, Joel
ASKER
Thanks for your response. What I am trying to do is create a record with a set of known values. So lets say the table fields are as such:
ID (autonumber)
East
West
North
South
I will create a record and just assign the same values each time like:
ID (autonumber)
East = 1
West = 2
North = 3
South = 4
I know this might not make sense, but I'm just giving a simple example. The table actually has about 100 fields.
Thanks.
ID (autonumber)
East
West
North
South
I will create a record and just assign the same values each time like:
ID (autonumber)
East = 1
West = 2
North = 3
South = 4
I know this might not make sense, but I'm just giving a simple example. The table actually has about 100 fields.
Thanks.
You have to make sure the table column definitions allow nulls, you would then specify the columns to which the data you are adding will correspond the remaining cells will eighter be the default or null depending on your table definition.
Note that you should not include the Id(autonumber)
The order of the declared columns is how the values you are adding are referenced.
A table of 100 columns begs for normalization into sub tables.
Note that you should not include the Id(autonumber)
rst.AddNew Array("ID","LFD_NR", "TEXT_ID", "DRUCK_KZ", "MOD", "BEZ"), Array(Item.UserProperties("CSIOrderNo1").value, "45", "45", "X", "0", "LIFT GATE REQUEST")
rst.Update
The order of the declared columns is how the values you are adding are referenced.
A table of 100 columns begs for normalization into sub tables.
ASKER
Can you do something like this?
rst.AddNew Array(*), Array(1,2,3,4)
rst.AddNew Array(*), Array(1,2,3,4)
As an aside, instead of a SELECT * recordset is there any chance you can create an UPDATE statement, then execute that as a command? That would save you the overhead of loading the entire recordset.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help. I putting down that the solution is that there is no solution to my question.
Depending on what you trying to do, there are different more efficient ways to load data.
If the answer does not answer you might want to rephrase it to have others look at it and see whether there is a different way to achieve what you want.
If the answer does not answer you might want to rephrase it to have others look at it and see whether there is a different way to achieve what you want.
If you use a cursor to go through your response, in defining the cursor you can specify which columns are updateable.
I am unclear what it is you are doing, it seems less as an update and more as though you are overwriting the contents of response without seemingly a reason.