SQL ??

I am trying to insert values into a database, from a textbox. I do not wish to use the adodc.recordset.fields command, but would rather prefer to enter it using the -Insert Values in SQL.

This works correctly, until I try insert a string containing = or > or ' or any other such character types. What is the correct syntax to insert such characters into SQL ?

thanks

This should work

Code:

---

.Execute "Insert into [Table] ([Field1], [Field2], [Field3]) VALUES(""Value1"", ""Value2"", ""Value3"")"

.Execute "Insert into [Table] ([Field1], [Field2], [Field3]) VALUES(""" & txtMyTextBox.Text & """, """ &  txtMyTextBox2.Text & """, """ & txtMyTextBox3.Text & """)"

---

Tried that, gives a Unknown Identifier

My text3.text = " This is a test, ** >><< "

see if anybody can get that to insert into the database using INSERT INTO ;)

have fun

I have pinpointed the problem....it only gives the error, when the textbox contains a ' . The apostrophe seems to cause the error!!!!

This should be quite important to all vb programmers who wish to insert large string fields into any SQL database!
Anybody know how ?

This gives an error:
'test'n'
"test"n"

This works:
'test''n'
"test""n"


U could use a replace on your statement

If you are useing ' as string delimiter:
VALUES ('" & Replace(txtMyTextBox, "'", "''") & "')

If you are useing " as string delimiter:
VALUES (""" & Replace(txtMyTextBox, """", """""") & """)


That works in vb itself to
txtMyTextBox = "Hello This is a text, "" <--- See the thingie there."

When posting data into a string field on a sql database, you should always either restrict the user from entering special characters (ie " ' etc.) or you can make them ok by doubling them up by using..

"INSERT INTO table1 (f1) VALUES ('" & replace (replace (text1.text, "'", "''"), """", """""") & "')"

The replace function is very usefull.

Hope this helps.

J.

Quote:

---

Originally posted by Ap0c
This should be quite important to all vb programmers who wish to insert large string fields into any SQL database!
Anybody know how ?

---

hi,
large strings are not a problem if U use fields of image or text type.
With that types (image, text) you have some additional possibilities in sql. I use WRITETEXT, READTEXT, UPDATETEXT and TEXTPTR and it works.

yes, but if you use image or text types you are very restricted in terms of the types of search clauses you can use and the indexes can be very bloated.
The string search functions (ie like) are also restricted if you use text or image.

yes, you're right, but are there any other types that can store more then few thousand characters?

You can either use varchar, which under SQL Server 7 has a maximum length of about 6000. You can also implement what we call 'Chunking' routines which break the text up on the client side and store it in small chunks on the database.

We have found that this does not affect performance if you get the indexes correct.

varchar can store up to 8000 chars, nvchar (using unicode) - up to 4000 chars.

Nearly got it, just a few thousand out.

I am using NVChar in the database, and I am still unable to put the ' in the table.

strSql = "INSERT INTO Outbox (oTo, oFrom, oSubject, "
strSql = strSql + "oDate, oMessage, ClientCode) "
strSql = strSql + "VALUES ('" + txtTo.value + "', '"
strSql = strSql + txtFrom.value + "', '" + txtSubject.value
strSql = strSql + "', '" + formatdatetime(Now(), vbGeneralDate) + "', '"
strSql = strSql + txtMessage.value + "', '"
strSql = strSql + Session("UName") + "');"


That is the code I am using in ASP.....does anybody know how I can insert the apostrophe's into the database. I am inserting entire email messages into the email table.

thanks

You need to replace the single '
with 2 '

Then it will work


If this message is "Hello, Don't blabla"
Make it "Hello, Don''t blabla"

I have tried two ' it doesnt work, can you change my code, and see if it works?

Can you get it do print out the sql string it is trying to execute and post it?