Insertion with single quotes problem

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it is okay. Is is a bug in sql 2005? Please help. Thanks.

blumonde

The best bet is to use parameters. Building the SQL String as you are can cause all sorts of problems.

Absent that, you need to "Escape" the apostrophe. SO, do the following...

string testme = "we don''t have anything";

Note there are 2 apostrophes in a row...

|||

douglas.reilly:

The best bet is to use parameters. Building the SQL String as you are can cause all sorts of problems.

Absent that, you need to "Escape" the apostrophe. SO, do the following...

string testme = "we don''t have anything";

Note there are 2 apostrophes in a row...

Hi Douglas,

The problem is that end-users write those statements and hit insert. And they don't type two apostrophes. I can't "escape" it. Thanks.

blumonde

|||

You then have two choices.

1.Use parameters.

2. Search the string for ' and replace it with '' (two apostrophes). Of course users are not going to use two apostrophes.

|||

douglas.reilly:

You then have two choices.

1.Use parameters.

2. Search the string for ' and replace it with '' (two apostrophes). Of course users are not going to use two apostrophes.

I will try using parameter first. Thanks.

blumonde

|||

Parameters did it for me. Thanks.

blumonde

Inserting XMl string into table.

Hi,

I want the value of a field as an XML string.

Ex: <student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>

Please let me know whether the normal insert command can be used to insert this XML string

I have done with normal insert Command like

Insert INTO listtable list_id, listDetails VALUES 1, '<student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>'

Here, the XML string is inserted into 2nd column.

Is this the way to do?

Because, when i read the string back, i am getting the characters &lt; etc. in place of "<", ">" etc.

How the XML string column is selected back to get the correct XML string without junk characters?

Please help me out.

I am not sure what goes wrong but you have not shown how you read out the data exactly.

The following is a sample that creates the table and inserts your sample data and then reads it out with a simple SELECT FROM, that works fine for me with SQL Server 2005 Management Studio Express, the angle brackets <> are certainly not escaped:

Code Snippet

CREATE TABLE #listtable (

list_id int,

listDetails xml

);

GO

INSERT INTO #listtable (list_id, listDetails)

VALUES(1, '<student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>');

SELECT list_id, listDetails FROM #listtable;

inserting the current date and time into SQL Server database

I need an SQL string that inserts the current date into a database.

So far I have tried:

SQL = "INSERT INTO X (START_DATE) VALUES('" & Date.Now &"')"

mycomm =

New SqlCommand(sql, myconn)

mycomm.ExecuteNonQuery()

However, there is a problem with the SQL command. The problem is related to the date. Is there a way of programatically inserting the current date/time into the SQL database? Language used is VB.

GetDate() is a function in SQL Server that returns the current timestamp. So you can directly use that.

SQL = "INSERT INTO X (START_DATE) VALUES( GetDate())"

inserting string with quotes

Hi Guys,
i want to insert a string for example 'abcd'edfg'gg'into a
table in sql server 2000.
but it is not working but giving the error
"not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are
not permitted."
will this require any sp_configue or db_option changes?
pls advice me.Use two single quotes instead of one like: 'abcd''edfg''gg'
Anith

Inserting SOAP formatted message data into a SQL table -- OPENXML

I have a SOAP formatted message. I am passing this message as a string from
a
C# client to a SQL Stored Procedure.
I would like to read this SOAP formatted message and store the data in the
SOAP message in appropriate columns in a SQL table. How can I achieve this?
Can OPENXML be used for this purpose?
--
ggCould you just store in a nvarchar(max) ?
William Stacey [MVP]
"gudia" <gudia@.discussions.microsoft.com> wrote in message
news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
> Is configuring IIS to be used in conjunction with SQLXML a requirement for
> the question I am asking?
> My eventual goal is to insert the data present in the SOAP message into a
> SQL table.
> Please let me know how I can achieve this goal.
> Thanks|||There are a couple of approaches. You could pass the entire SOAP XML doc to
a stored procedure and use OPENXML to shred it into tables, or you could
create an annotated XSD schema that maps the elements/attributes in your
SOAP message to the tables/column in the database and use the SQLXML Bulk
Load component.
Neither of these approaches requires a SQLXML IIS site.
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"gudia" <gudia@.discussions.microsoft.com> wrote in message
news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
Is configuring IIS to be used in conjunction with SQLXML a requirement for
the question I am asking?
My eventual goal is to insert the data present in the SOAP message into a
SQL table.
Please let me know how I can achieve this goal.
Thanks

Inserting pictures in DB - For VB programmers

Private conn As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub ConnectToDB()

Dim strData As String

'Establish the connection.
Set conn = New ADODB.Connection
Call conn.Open("driver={SQL Server};server=srv_scgb\scg_sgbd;uid=tdela;pwd=pas sword;database=Mercure_tst")

'Open the recordset
Set rs = New ADODB.Recordset

'Make sure you don't retrieve any data !!!
Call rs.Open("Select * From Images Where FileName='0'", _
conn, _
adOpenKeyset, _
adLockOptimistic)

Call SaveToDB

Call rs.Close
Call conn.Close

Set rs = Nothing
Set conn = Nothing

End Sub

Private Sub SaveToDB()

Dim bytBLOB() As Byte
Dim strImagePath As String
Dim intNum As Integer

'Save the record
strImagePath = Trim("c:\windows\bureau\MyImage.bmp")

With rs
'Open the picture file
intNum = FreeFile
Open strImagePath For Binary As #intNum
ReDim bytBLOB(FileLen(strImagePath))

'Read data and close file
Get #intNum, , bytBLOB
Close #1

Call .AddNew
.Fields("FileName") = "The image title"
Call .Fields("Picture").AppendChunk(bytBLOB)
Call .Update
End With

End SubTable name >>> Images
Column 1 >>> FileName varchar(50)
Column2 >>> Pisture image(16)