<\/div>\n
After we make the connection to the database we then use the following line of code to return a recordset consisting of all the data found in the Computers table:<\/p>\n
$objRecordset.Open(\"Select * From Computers\", $objConnection,$adOpenStatic,$adLockOptimistic)\n<\/pre>\nBelieve it or not, we\u2019re now ready to add a record to our database; in fact, that\u2019s what this block of code is for:<\/p>\n
$objRecordSet.AddNew()\n$objRecordSet.Fields.Item(\"ComputerName\").Value = \"atl-ws-001\"\n$objRecordSet.Fields.Item(\"SerialNumber\").Value = \"192ATG43R\"\n$objRecordSet.Update()\n<\/pre>\nWhat\u2019s going on here? Well, for starters, we use the AddNew<\/b> method to create a new, blank record. (Again, note the parentheses that follow the method name.) That brings us to this line of code:<\/p>\n$objRecordSet.Fields.Item(\"ComputerName\").Value = \"atl-ws-001\"\n<\/pre>\nWe\u2019re willing to bet, AB, that this is the line that\u2019s been causing you the most trouble. In this line of code we\u2019re assigning a value (atl-ws-001<\/i>) to the ComputerName field. This isn\u2019t a particularly troublesome line of code unless you\u2019re trying to port a VBScript script. That\u2019s because, in VBScript, this same line of code looks like this:<\/p>\nobjRecordSet(\"ComputerName\") = \"atl-ws-001\"\n<\/pre>\nIn VBScript all we have to do is specify the object reference (objRecordset) followed by a pair of parentheses containing the name of the field (ComputerName). But remember, that\u2019s in VBScript. In Windows PowerShell that simplified command won\u2019t work; if we try something similar we merely get an error message like this one:<\/p>\n
Unexpected token '(' in expression or statement.\nAt C:\\scripts\\test.ps1:11 char:15\n+ $objRecordSet(\" <<<< ComputerName\") = \"atl-ws-001\"\n<\/pre>\nTo tell you the truth, we don\u2019t know exactly why this problem occurs. We do know that, unless you specify otherwise, VBScript automatically gives you the default property for any object; most other languages (including Windows PowerShell) make you explicitly spell out that property, default property or not. We\u2019re guessing that Fields.Item.(<\/b>Field name<\/i><\/b>).Value<\/b> is the default property for our recordset; that would explain why VBScript only needs the field name while PowerShell requires the complete property name.<\/p>\n
Of course, like we said, that\u2019s just a guess, and there\u2019s a good chance we\u2019re completely wrong. But that\u2019s OK. After all, it doesn\u2019t matter why<\/i> you have to \u201cspell out\u201d the complete property name; the only thing that matters is that you follow this same approach. With that in mind, how would we assign a value to the SerialNumber field? You got it:<\/p>\n$objRecordSet.Fields.Item(\"SerialNumber\").Value = \"192ATG43R\"\n<\/pre>\nFrom there, of course, we could continue to assign properties to as many fields as there are in the Computers table. Once we\u2019re done assigning properties we then call the Update<\/b> method to actually write the new record to the database:<\/p>\n$objRecordSet.Update()\n<\/pre>\nAfter that all we have to do is close the Connection and Recordset objects, and then return to our Scripting Guys Day festivities.<\/p>\n
That should do the trick, AB. We hope you find this script useful, and we hope you enjoy what\u2019s left of the Scripting Guys Day holiday season. Speaking of which, we should also note that we aren\u2019t upset or disappointed that everyone except Joel forgot about Scripting Guys Day this year. That\u2019s no big deal, and there are no hard feelings on our end.<\/p>\n
\n<\/thead>\n\n\n| \n Note<\/b>. OK, that\u2019s not true; as it turns out, there are plenty<\/i> of hard feelings on our end. But if we told the truth about that the Scripting Editor would just edit that part out. Therefore we figured that we might as well pretend that everything\u2019s peaches and cream. Even if it\u2019s not.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n In all fairness, though, we must admit that Scripting Guys Day does<\/i> come at a bad time of year; after all, having a major holiday like Scripting Guys Day follow so closely on the heels of a major holiday like Christmas can be difficult. Then when you throw in the fact that January 11th<\/sup> is also the birthday of singer\/songwriter Mary J. Blige; one-time Major League shortstop Rey Ordonez; and<\/i> former Canadian Prime Minister Jean Chretien, well, it\u2019s no wonder people get confused. Tell you what: go ahead and send your presents in now (even though you\u2019re a little late), and we\u2019ll see what we can do about moving Scripting Guys Day to a new date for 2009.<\/p>\n","protected":false},"excerpt":{"rendered":"Hey, Scripting Guy! Awhile back you showed us how we could use Windows PowerShell to retrieve data from a Microsoft Access database. I\u2019m now trying to add data to a database using PowerShell, but I can\u2019t get the script to work. Any chance you could show me how to add a record to a database […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[19,146,54,49,3,45],"class_list":["post-63283","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-microsoft-access","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":" Hey, Scripting Guy! Awhile back you showed us how we could use Windows PowerShell to retrieve data from a Microsoft Access database. I\u2019m now trying to add data to a database using PowerShell, but I can\u2019t get the script to work. Any chance you could show me how to add a record to a database […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63283","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=63283"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63283\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=63283"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63283"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63283"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a> <\/p>\n