Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL array function

Posted on 2014-12-31
8
Medium Priority
?
198 Views
Last Modified: 2014-12-31
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("CSIOrderNo1").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
0
Comment
Question by:Genius123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 80

Expert Comment

by:arnold
ID: 40525948
The only time you can is when you include all columns in the correct order
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.
0
 

Author Comment

by:Genius123
ID: 40526076
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.
0
 
LVL 80

Expert Comment

by:arnold
ID: 40526097
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)
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

Open in new window


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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Genius123
ID: 40526100
Can you do something like this?

rst.AddNew Array(*), Array(1,2,3,4)
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40526131
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.
0
 
LVL 80

Accepted Solution

by:
arnold earned 2000 total points
ID: 40526132
No, there is no way to which columns the 1,2,3,4 belong.
you have to define the columns
rst.AddNew Array ("East","West","North","South"), Array(1,2,3,4)

The other way is to use the fields properies to set the column and assign the value.
0
 

Author Closing Comment

by:Genius123
ID: 40526135
Thanks for the help.  I putting down that the solution is that there is no solution to my question.
0
 
LVL 80

Expert Comment

by:arnold
ID: 40526147
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.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question