Solved

SQL array function

Posted on 2014-12-31
8
175 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
  • 4
  • 3
8 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
 

Author Comment

by:Genius123
Comment Utility
Can you do something like this?

rst.AddNew Array(*), Array(1,2,3,4)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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 76

Accepted Solution

by:
arnold earned 500 total points
Comment Utility
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
Comment Utility
Thanks for the help.  I putting down that the solution is that there is no solution to my question.
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now