Data from Powershell forms with blank fields into SQL Server database

Question

So far I have created a form in Powershell studio and from that form I want to take the user's input and place it into a SQL Server database.

For the most part this works fine, unless a textfield in the form is left blank, then an error occurs. I need to be able to insert the data even if some of the fields are not complete as people are able to go back and edit and add to that data and then update it in the database.

This is the error I get if I leave a textbox blank:

ERROR: Exception calling "Fill" with "1" argument(s): "Incorrect syntax near ','." testTabForm.psf (59): ERROR: At Line: 59 char: 3
ERROR: + $SqlAdapter.Fill($DataSet)
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : SqlException

And the code which does this aspect of it is the following:

    $server = "server"
    $database = "database"
    $A = $record.Text
    $B = $textB.Text
    $C = $textC.Text

    $insert = "INSERT INTO dbo.AMY (ColumnA, ColumnB, ColumnC) VALUES ($A,$B,$C)"

    $connectionTemplate = "Data Source=$server;Integrated Security=SSPI;Initial       Catalog=$database;"
    $connectionString = [string]::Format($connectionTemplate, $server, $database)
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString

    $command = New-Object System.Data.SqlClient.SqlCommand
    $command.CommandText = $insert
    $command.Connection = $connection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()

Any ideas on how to fix this would be appreciated.

IMHO You are missing the relevant part of the code here. My 2 Cents: You need to check for empty value before build the $insert variable. — CB.
– CB., Commented Jul 18, 2014 at 12:40
I added how I got those values, I guess I don't fully understand what I'm missing. I get the values from the user input in the form. If they do not add something to one of the textfields then I want there to just be a blank or empty value in that spot in the database. I tried to set the variables to both $null and an empty string and only changing them from that if there is actual data in the textfields but I got the same error. — user3852959
– user3852959, Commented Jul 18, 2014 at 12:50
I looked at that and have come to realize my real issue is that it will only accept numbers. I had only been testing with numbers but I need B and C to accept strings as well. In the database ColumnA is the only one that specifies int so I am not sure why it is that the other two cannot be strings. — user3852959
– user3852959, Commented Jul 18, 2014 at 13:56

Bruce · Accepted Answer · 2014-07-18 15:41:50Z

0

Test for the existance of B and C building your insert command along the way.

$insert = "INSERT INTO dbo.AMY (ColumnA, ColumnB, ColumnC) VALUES ($A, "
if ($B.Length) { $insert += "'$B', " } 
else { $insert += "NULL, " }
if ($C.Length) { $insert += "'$C')" }
else { $insert += "NULL)" }

answered Jul 18, 2014 at 15:41

Bruce

1,6231 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3852959 · Accepted Answer · 2014-07-22 18:35:46Z

0

The problem ended up being a simple syntax error, the variables needed to be in quotes for a string specifically single quotes, once I did this it worked perfectly. Thanks.

answered Jul 22, 2014 at 18:35

user3852959

92 bronze badges

Collectives™ on Stack Overflow

Data from Powershell forms with blank fields into SQL Server database

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related