Winform pads SQL Server table with white space

[jonnod123]

Hi all,

I have a c# winform with a SQL Server 2005 backend. When I update my database from textboxes in the form, in one particular table all the nvarchar entries are padded with white space. This doesn't happen for other tables.

All the fields that are being padded are nvarchar (not nchar), and are padded to their maximum length. If I run the query manually and enter values myself without using the form, then everything works fine

Any ideas what's going on???

Thanks
Jon

[kvprajapati]

>All the fields that are being padded are nvarchar (not nchar), and are padded to their maximum length.

Verify (code) the parameter type/size.

[jonnod123]

Do you mean in the VS application or in SQL Server itself? I've attached a screen shot of the table design from SQl Server.

Thanks

[kvprajapati]

>Do you mean in the VS application or in SQL Server itself?

No. Please show us your code.

[jonnod123]

The database is updated as follows:

int quantity_int = Convert.ToInt32(quantity_txtbx.Text);
                int latestID_int = Convert.ToInt32(this.hardware_Asset_tblTableAdapter.SelectLatestHardwareIDQuery()) + 1;
                string username = fetchAlias();

                for (int i = 0; i < quantity_int; i++)
                {
                    try
                    {
                        this.hardware_Asset_tblTableAdapter.Insert(PO_combobx.Text, "", hostType_combobx.Text, manufacturer_combobx.Text, modelTextBox.Text, "", false, false, DateTime.Today.AddYears(Convert.ToInt32(replacementYears)).AddMonths(Convert.ToInt32(replacementMonths)).Date);
                        this.asset_History_tblTableAdapter.Insert(latestID_int, "", "", "", "", "", DateTime.Today, username, "");
                        latestID_int++;
                    }

                    catch (System.Data.OleDb.OleDbException)
                    {
                        MessageBox.Show("Please ensure that the PO Number matches an existing PO on the system, or leave it blank.");
                        break;
                    }
                    catch (Exception exc)
                    {
                        MessageBox.Show("Error inserting record.  Please give your system administrator the following error code: " + exc.ToString());
                        break;
                    }
                }

It hardware_Asset_tblTableAdapter.Insert() that's causing the problem (the insert method works without fine). I have several other update methods in the application which all work ok too, just not this particular one.

I can give you the sql query too if needed, but I don't think that's the problem. i.e. I can run it manually and it updates without any problems (syntax or otherwise).

Thanks for your help,
Jon

[jonnod123]

Apologies - Please disregard my previous post. I posted the wrong code snippet :$ (It's a massive application).

The problematic code is as follows...

private void update_btn_Click(object sender, EventArgs e)
        {
            try
            {
                bool disposed = false;
                bool replaced = false;

                if (disposed_ofCheckBox.Checked)
                    disposed = true;
                if (to_Be_ReplacedCheckBox.Checked)
                    replaced = true;

                string username = this.operators_tblTableAdapter.getAlias_query(Environment.UserName);

                if (username == null)
                    username = "Unknown user";

                this.hardware_Asset_tblTableAdapter.UpdateQuery(pO_NumberTextBox.Text, asset_NumberTextBox.Text, host_TypeTextBox.Text, manufacturerTextBox.Text, modelTextBox.Text, serial_NumberTextBox.Text, disposed, Convert.ToDateTime(replacement_DateDateTimePicker.Text), replaced, hardware_ID_int);
                this.asset_History_tblTableAdapter.Insert(hardware_ID_int, userTextBox.Text, roomTextBox.Text, departmentTextBox.Text, operating_SystemTextBox.Text, host_NameTextBox.Text, DateTime.Now, username, notesTextBox.Text);

                MessageBox.Show("Successfully updated.");
                this.Close();

            }
            catch (FormatException)
            { }
            catch (OverflowException)
            { }
            catch (Exception exc)
            {
                MessageBox.Show("The Purchase Order Number you have chosen does not exist in the database.  If you do not know the Purchase Order number for this assets, please enter 'Unknown' in the PO Number field.","Error updating database", MessageBoxButtons.OK, MessageBoxIcon.Warning );
            }
        }

And it's hardware_Asset_tblTableAdapter.UpdateQuery() that's causing the problems.

Thanks again.

[jonnod123]

Any ideas?

[JuhaW]

What is the datatype? Try to use varchar instead of char type in database.

[jonnod123]

Hi,

I found the solution in the end. I was using NVarChar in the database, not NChar. However, for some reason the data types that had been carried across to the VS winform for this table were Wchar. I changed these to VarWChar and all works well now.

I'm not sure why this happened for this one table. The database has about 15 table in all, but this was the seemed to inherit the wChar datatype instead of VarWChar.

Thanks to everyone who helped.
J