Sending text from Visual Basic to SQL Server Express Database

Visual Basic General

mekipstrob

I am trying to send a string from a input text field in my visual basic 2005 program to a SQL Server Express database I have set up...

Heres the problem:

The program links to the database just fine, and sends the inputs from the text fields without error, BUT when I send a string more than 10 characters the program truncates the remaining characters. In my database on SQL Server, I set the field's Data Type to 'ntext'.... and I can add long strings when accessing the database and adding data through SQL Server Express Management Studio, but I want to send a string from my Visual Basic program from the input text field to the database untruncated.

Is there a way to stop this truncating because I want my program to save user comments in my database along with the data it stores already.

PLEASE HELP!

Thankyou
Top

Re: Visual Basic General Sending text from Visual Basic to SQL Server Express Database

Aw Ali

You don't need to use ntext unless you are planning to use non-english characters. Use varchar and set the size to your desired one. By default it's varchar(50) which means it can hold 50 characters. You can set varchar(MAX), if you want to set it to the muximum size.





Top

Re: Visual Basic General Sending text from Visual Basic to SQL Server Express Database

mekipstrob

PLEASE HELP!

In SQL Server Express i changed the datatype to varchar(MAX) but when I send the input from the text field from the application into the database the data still gets truncated to only ten characters

ANY OTHER SOLUTIONS

Here is the Visual Basic code i am using so far:

(connection string omitted)

Dim con As New SqlConnection(Connect)

con.Open()

Dim cmd As New SqlCommand("AddNewEmployee", con)

cmd.CommandType = CommandType.StoredProcedure

Dim param As SqlParameter

param = cmd.Parameters.AddWithValue("@ID", txtID.Text)

param = cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)

param = cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)

Dim NumAffected As Integer

NumAffected = cmd.ExecuteNonQuery()

con.Close()

And the Stored procedure in SQL Server Express is:

CREATE PROCEDURE AddNewEmployee

@ID varchar(MAX), @FirstName varchar(MAX),

@LastName varchar(MAX)

AS INSERT INTO Employees(ID, FirstName, LastName)

VALUES(@ID, @FirstName, @LastName)

PLEASE HELP!!!!!!!!!!!!!


Top

Re: Visual Basic General Sending text from Visual Basic to SQL Server Express Database

Bruno Yu - MSFT

mekipstrob,

According to your problem on string truncate when inserting to the SQL Server database, I would like to provide you the following suggestion:

1. There is no problem with the code snippet. Your Visual Basic .NET program works fine.

2. Please try to add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary.

The following code snippet shows the stored procedure on the issue:

Code Snippet

--This batch will fail with the SQL Server error message

DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @MyParameter = REPLICATE('Z', 52)

INSERT INTO @MyTable(MyValue)

VALUES (@MyParameter)

GO

--This batch will fail with a custom error message

DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @MyParameter = REPLICATE('Z', 52)

IF LEN(@MyParameter) > 10

BEGIN

RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

RETURN

END

ELSE

BEGIN

INSERT INTO @MyTable(MyValue)

VALUES (@MyParameter)

END

GO

For further information, please take a look at the following thread: "String or binary data would be truncated" and field specifications

You can also post the related question on SQL Server database in the SQL Server forums for the further support.

Hope that can help you and thanks again for your question.





Top