textbox to ntext sqlserver column 1

[oakgrove]

I am trying to put the contents of a text box into an SQL server ntext field with an UPdate statement and parameters

<code>
...
cmdUpdate.commandtext = &quot;Update table SET comments=@comments&quot;
cmdUpdate.Parameters.Add(&quot;@comments&quot;,System.Data.SqlDbType.NText, 16).Value = txtcomments.Text
conn.Open()
cmdUpdate.ExecuteNonquery()
</code>

Nothing gets put into the field. What am I doing wrong?

TIA

Oakgrove Computer Grouper
Lansing, MI

 

[Joulius]

Add this to your code:

dim objCon as SqlConnecion = New SqlConnection("your_connection_string")
....
cmdUpdate.Connection()=objCon
....

 

[oakgrove]

Sorry should have put more code. The actual update involves several txtboxes and columns that sucessfully update. On further testing I find that what is actually going on is that the update stops at the first cr/lf.
If the text box has
&quot;I am the first line
and I are the second.&quot;
The only thing that is put in the data column is:
&quot;I am the first line&quot;

Can anybody direct me to some way to deal with this. The users will want to put in cr/lf as well as empty lines.



Oakgrove Computer Grouper
Lansing, MI

 

[Joulius]

Try adding this to all your textboxes

Server.HTMLEncode(textbox.Text.Replace("'","''"))

 

[chiph]

oakgrove -

Do NOT do as Joulius suggests -- it will leave you open to a SQL injection attack (see

http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf)

Regarding the NText column, you need to set the size in the Parameters.Add call to the size of the data you plan to insert:

cmdUpdate.Parameters.Add("@comments",System.Data.SqlDbType.NText, txtcomments.Text.Length).Value = txtcomments.Text

This is because SQL Server needs to know how much space to allocate in the database ahead of time (large blocks of data are sent to the database by the ADO layer in chunks -- the first chunk needs to have the total size in it).

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Joulius]

Sorry about that, oakgrove ... chiph is right here!
Here you go, buddy!

 

[oakgrove]

Thanks a bunch, you folks. Guess I was mixing up the issue of cr/lf with the size declaration.

Both your posts clarified the issues and fixed my problem



Oakgrove Computer Grouper
Lansing, MI