Field cannot be a zero-length string

[cmstoner]

In my program, I have a section to insert information about a customer into an Access database. I get the error below at the .ExecuteNonQuery.

Message: System.Data.OleDb.OleDbException (0x80004005): Field 'Customer.Business_Phone' cannot be a zero-length string.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

The problem I'm having, is that the Business_Phone does have a value in it. I added the msgbox to be sure, and it displays "9876543210", just like I entered. The database field is set to not allow zero length strings, because I want this to be a required field. What I don't understand is how it's getting that @BUSPH is a zero length string, when I set it to the value of the textbox, which is the same value that I print to the msgbox so I know that there is a value there. Also, I have a check before I add the field to the insert string to make sure I am not adding null values, so in theory it shouldn't be included in the input string if it is zero length, right? My code section is below. Any ideas what could be causing this error?

Dim custstr As String = "INSERT INTO [Customer] ("
Dim valuestr As String = ") VALUES ("

If txt_company.Text.ToString <> vbNullString Then
    custstr += "Company"
    valuestr += "@CMPNY"
End If
If txt_lastname.Text.ToString <> vbNullString Then
    custstr += ", Last_Name"
    valuestr += ", @LSTNM"
End If
If txt_firstname.Text.ToString <> vbNullString Then
    custstr += ", First_Name"
    valuestr += ", @FSTNM"
End If
If txt_busphone.Text.ToString <> vbNullString Then
    custstr += ", Business_Phone"
    valuestr += ", @BUSPH"
End If
If txt_homephone.Text.ToString <> vbNullString Then
    custstr += ", Home_Phone"
    valuestr += ", @HOMPH"
End If
If txt_mobilephone.Text.ToString <> vbNullString Then
    custstr += ", Mobile_Phone"
    valuestr += ", @MBLPH"
End If
If txt_fax.Text.ToString <> vbNullString Then
    custstr += ", Fax"
    valuestr += ", @FAXNM"
End If
If txt_email.Text.ToString <> vbNullString Then
    custstr += ", Email"
    valuestr += ", @EMAIL"
End If
custstr += valuestr + ")"

Dim custcmd As New OleDbCommand(custstr, con)
custcmd.Parameters.AddWithValue("@CMPNY", txt_company.Text.ToString)
custcmd.Parameters.AddWithValue("@LSTNM", OleDbType.VarChar).Value = txt_lastname.Text.ToString
custcmd.Parameters.AddWithValue("@FSTNM", OleDbType.VarChar).Value = txt_firstname.Text.ToString
custcmd.Parameters.AddWithValue("@BUSPH", OleDbType.VarChar).Value = txt_busphone.Text.ToString
custcmd.Parameters.AddWithValue("@HOMPH", OleDbType.VarChar).Value = txt_homephone.Text.ToString
custcmd.Parameters.AddWithValue("@MBLPH", OleDbType.VarChar).Value = txt_mobilephone.Text.ToString
custcmd.Parameters.AddWithValue("@FAXNM", OleDbType.VarChar).Value = txt_fax.Text.ToString
custcmd.Parameters.AddWithValue("@EMAIL", OleDbType.VarChar).Value = txt_email.Text.ToString

MsgBox("Business phone: " & txt_busphone.Text.ToString)

custcmd.ExecuteNonQuery()

thanks!

[kRod]

Hi cmstoner , what is the access database data type of your Business Phone field from the design view in ACCESS? If this is correct could you post the entire Insert command after you have built it?

[cmstoner]

Hi,
the data type of the Business_Phone field is Text in Access, which I want since I'm going to save the number pre-formatted (i.e. "[phone]").

I'm entering in the following data:
Company = Test
Business Phone = [phone]

and getting the following insert string:
INSERT INTO [Customer] (Company, Business_Phone, Home_Phone, Mobile_Phone, Fax) VALUES (@CMPNY, @BUSPH, @HOMPH, @MBLPH, @FAXNM)

Which doesn't make sense, since it should only add the fields to the string if there is data entered. Is there something wrong with the way I'm checking if there is text entered in the textboxes?

[BitBlt]

This is kind of a shot in the dark, but I notice that the format of line 39 seems to be different from the formats of the subsequent 7 lines. Could it be that the error message is a symptom of THAT rather than a problem with the actual data values?

[TnTinMN]

You are adding parameters that may not exist in the command string.

INSERT INTO [Customer] (Company, Business_Phone, Home_Phone, Mobile_Phone, Fax) VALUES (@CMPNY, @BUSPH, @HOMPH, @MBLPH, @FAXNM)
In this case you will have parameters for Last_Name and First_Name that are empty. I believe that the parameters must be in the order requested as it doe not do a look-up of the provided parameters.

I have restructured your code and eliminated some unnecessary functional but confusing code.

   Dim custstr As String = "INSERT INTO [Customer] ("
   Dim valuestr As String = ") VALUES ("

   Dim custcmd As New OleDbCommand()
   Dim txt As String
   txt = txt_company.Text

   If Not String.IsNullOrEmpty(txt) AndAlso txt.Trim.Length <> 0 Then
       custstr &= "Company"
       valuestr &= "@CMPNY"
       custcmd.Parameters.AddWithValue("@CMPNY", txt_company.Text)
   End If

   txt = txt_lastname.Text
   If Not String.IsNullOrEmpty(txt) AndAlso txt.Trim.Length <> 0 Then
       custstr &= ", Last_Name"
       valuestr &= ", @LSTNM"
       custcmd.Parameters.AddWithValue("@LSTNM", txt_lastname.Text)
   End If

   txt = txt_firstname.Text
   If Not String.IsNullOrEmpty(txt) AndAlso txt.Trim.Length <> 0 Then
       custstr &= ", First_Name"
       valuestr &= ", @FSTNM"
       custcmd.Parameters.AddWithValue("@FSTNM", txt_firstname.Text)
   End If

   txt = txt_busphone.Text
   If Not String.IsNullOrEmpty(txt) AndAlso txt.Trim.Length <> 0 Then
       custstr &= ", Business_Phone"
       valuestr &= ", @BUSPH"
       custcmd.Parameters.AddWithValue("@BUSPH", txt_busphone.Text)
   End If

   txt = txt_homephone.Text
   If Not String.IsNullOrEmpty(txt) AndAlso txt.Trim.Length <> 0 Then
       custstr &= ", Home_Phone"
       valuestr &= ", @HOMPH"
       custcmd.Parameters.AddWithValue("@HOMPH", txt_homephone.Text)
   End If

   txt = txt_mobilephone.Text
   If Not String.IsNullOrEmpty(txt) AndAlso txt.Trim.Length <> 0 Then
       custstr &= ", Mobile_Phone"
       valuestr &= ", @MBLPH"
       custcmd.Parameters.AddWithValue("@MBLPH", txt_mobilephone.Text)
   End If

   txt = txt_fax.Text
   If Not String.IsNullOrEmpty(txt) AndAlso txt.Trim.Length <> 0 Then
       custstr &= ", Fax"
       valuestr &= ", @FAXNM"
       custcmd.Parameters.AddWithValue("@FAXNM", txt_fax.Text)
   End If

   txt = txt_email.Text
   If txt_email.Text <> vbNullString Then
       custstr &= ", Email"
       valuestr &= ", @EMAIL"
       custcmd.Parameters.AddWithValue("@EMAIL", txt_email.Text)
   End If

   custstr &= valuestr & ")"
   custcmd.CommandText = custstr
   custcmd.Connection = con

   custcmd.ExecuteNonQuery()

[cmstoner]

Moving where I added the parameters fixed the error. Thanks!