Problem with Insert command statement and updating SqlServer 
Author Message
 Problem with Insert command statement and updating SqlServer

Hi,

I'm having problems saving data that has apostrophe marks
in it on a vb web application.   The variable "Private
strArticleBody as string" will be holding text that will
have " ' " in it.  Where I think I'm having problems is
in my Insert Command statement where I have to put in
apostrophes for SQL know that strArticleBody is a string
variable instead of an integer.  Here is the code that
I'm using:

InsertQuery = "INSERT INTO tblArticle
(ArticleID,ArticleHeader,ArticleBody) VALUES (" &
lngArticleID & ",'" & strArticleHeader & "','" &
strArticleBody & "')"
        InsertCommand = New SqlCommand(InsertQuery,
Connection1)
        InsertCommand.CommandType = CommandType.Text
        SqlDataAdapter1.InsertCommand = InsertCommand

        SqlDataAdapter1.Update(ds)

The Error that I get when SqlDataAdapter1.Update(ds)
executes is Err.Number = 5  Err.Description = "Line 1:
Incorect syntax near 's'"
If I enter in data without apostrophes it updates
successfully.  In SqlServer the data type
for "ArticleBody" is Text and it does accept apostrophes
if the are directly entered in SqlServer.

My question is How can I update strArticleBody to
SqlServer when it holds apostrophes?

Any response is appreciated.
Thanks,

Mark



Mon, 04 Jul 2005 01:01:10 GMT  
 Problem with Insert command statement and updating SqlServer

Quote:
>Hi,

>I'm having problems saving data that has apostrophe marks
>in it on a vb web application.   The variable "Private
>strArticleBody as string" will be holding text that will
>have " ' " in it.  Where I think I'm having problems is
>in my Insert Command statement where I have to put in
>apostrophes for SQL know that strArticleBody is a string
>variable instead of an integer.  Here is the code that
>I'm using:

>InsertQuery = "INSERT INTO tblArticle
>(ArticleID,ArticleHeader,ArticleBody) VALUES (" &
>lngArticleID & ",'" & strArticleHeader & "','" &
>strArticleBody & "')"
>        InsertCommand = New SqlCommand(InsertQuery,
>Connection1)
>        InsertCommand.CommandType = CommandType.Text
>        SqlDataAdapter1.InsertCommand = InsertCommand

>        SqlDataAdapter1.Update(ds)

>The Error that I get when SqlDataAdapter1.Update(ds)
>executes is Err.Number = 5  Err.Description = "Line 1:
>Incorect syntax near 's'"
>If I enter in data without apostrophes it updates
>successfully.  In SqlServer the data type
>for "ArticleBody" is Text and it does accept apostrophes
>if the are directly entered in SqlServer.

>My question is How can I update strArticleBody to
>SqlServer when it holds apostrophes?

>Any response is appreciated.
>Thanks,

>Mark

Just tried it, if you use "double quotes", it allows you to save
apostrophes in the field data. But that doesn't really help much
because then saving double quotes causes an error...

Hmmm...

--
J a c k F r o s t



Mon, 04 Jul 2005 01:34:04 GMT  
 Problem with Insert command statement and updating SqlServer

use '' in place of any single '

D.


Quote:
> Hi,

> I'm having problems saving data that has apostrophe marks
> in it on a vb web application.   The variable "Private
> strArticleBody as string" will be holding text that will
> have " ' " in it.  Where I think I'm having problems is
> in my Insert Command statement where I have to put in
> apostrophes for SQL know that strArticleBody is a string
> variable instead of an integer.  Here is the code that
> I'm using:

> InsertQuery = "INSERT INTO tblArticle
> (ArticleID,ArticleHeader,ArticleBody) VALUES (" &
> lngArticleID & ",'" & strArticleHeader & "','" &
> strArticleBody & "')"
>         InsertCommand = New SqlCommand(InsertQuery,
> Connection1)
>         InsertCommand.CommandType = CommandType.Text
>         SqlDataAdapter1.InsertCommand = InsertCommand

>         SqlDataAdapter1.Update(ds)

> The Error that I get when SqlDataAdapter1.Update(ds)
> executes is Err.Number = 5  Err.Description = "Line 1:
> Incorect syntax near 's'"
> If I enter in data without apostrophes it updates
> successfully.  In SqlServer the data type
> for "ArticleBody" is Text and it does accept apostrophes
> if the are directly entered in SqlServer.

> My question is How can I update strArticleBody to
> SqlServer when it holds apostrophes?

> Any response is appreciated.
> Thanks,

> Mark



Mon, 04 Jul 2005 01:23:14 GMT  
 Problem with Insert command statement and updating SqlServer
Thanks Jack Frost and dnagel for your response!

Since the user of this program is going to copy and paste
text into the text box that shows the value of the
variable strArticleBody, I don't think that I should go
through all the text and find and replace all the
apostrophes with a double quote or ' '.  

I'll have to keep on trying. Feel free to post if you
have any other ideas.
Thanks for your time!

Mark

Quote:
>-----Original Message-----
>use '' in place of any single '

>D.


in message

>> Hi,

>> I'm having problems saving data that has apostrophe
marks
>> in it on a vb web application.   The variable "Private
>> strArticleBody as string" will be holding text that
will
>> have " ' " in it.  Where I think I'm having problems is
>> in my Insert Command statement where I have to put in
>> apostrophes for SQL know that strArticleBody is a
string
>> variable instead of an integer.  Here is the code that
>> I'm using:

>> InsertQuery = "INSERT INTO tblArticle
>> (ArticleID,ArticleHeader,ArticleBody) VALUES (" &
>> lngArticleID & ",'" & strArticleHeader & "','" &
>> strArticleBody & "')"
>>         InsertCommand = New SqlCommand(InsertQuery,
>> Connection1)
>>         InsertCommand.CommandType = CommandType.Text
>>         SqlDataAdapter1.InsertCommand = InsertCommand

>>         SqlDataAdapter1.Update(ds)

>> The Error that I get when SqlDataAdapter1.Update(ds)
>> executes is Err.Number = 5  Err.Description = "Line 1:
>> Incorect syntax near 's'"
>> If I enter in data without apostrophes it updates
>> successfully.  In SqlServer the data type
>> for "ArticleBody" is Text and it does accept
apostrophes
>> if the are directly entered in SqlServer.

>> My question is How can I update strArticleBody to
>> SqlServer when it holds apostrophes?

>> Any response is appreciated.
>> Thanks,

>> Mark

>.



Mon, 04 Jul 2005 03:28:47 GMT  
 Problem with Insert command statement and updating SqlServer

<<Hey Bob, I finally got someone who wants to do things the right way...!>>

The Parameters object of the Command Object will allow you to pass anything
you want to, without modifying the data to do so...

search for
    parameterized commands (ADO)
in the ADO Help file located in the
    ProgramFiles\Common\System\ADO folder

I Pulled this from the ADO Help File to assist you...

parameterized command

A query or command that allows you to set parameter values before the
command is executed. For example, a SQL string can be parameterized by
embedding parameter markers in the SQL string (designated by the '?'
character). The application then specifies values for each parameter and
executes the command.

Step 2: Create a Command (ADO Tutorial) Sidebar
Commands can be parameterized. A parameterized command consists of command
text that can be modified with a user-specified value each time the command
is invoked.

A placeholder ('?') indicates the part of the command text that will be
modified. Each placeholder in the command text will be replaced by the value
of the corresponding Parameter object in the Parameters collection when the
command is executed.

There are two ways to create a Parameter object and append it to the Command
object Parameters collection. The first way is to create a Parameter object,
set its Name, Type, Direction, Size, and Value properties individually, then
append it to the Parameters collection. The second way is to create and set
properties of the Parameter object with the Command object CreateParameter
method, then append the newly created Parameter object, all in one
statement.
Finally, call the Command.Execute or Recordset.Open methods to substitute
the parameter for the placeholder and obtain a Recordset. Change the
Parameter object Value property to another name. Then, call the Execute
method or the Recordset.Close and Open methods again to obtain a new
Recordset for another author.

Optimize the performance of the parameterized command with the Prepared
property. Note that the Execute method cannot specify the Recordset object
CursorType or LockType properties.

Here is a brief example:

Public Sub main()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Dim strCmd As String

strCmd = "SELECT * FROM Authors WHERE au_lname = ?"
cmd.CommandText = strCmd
cmd.Parameters.Append _
    cmd.CreateParameter("last name", adVarChar, adParamInput, 40, "")

cnn.Open "dsn=Pubs;uid=sa;pwd=;"
cmd.ActiveConnection = cnn

cmd.Parameters("last name") = "Ringer"
DisplayRst cmd.Execute
cmd.Parameters("last name") = "Karsen"
DisplayRst cmd.Execute

cnn.Close
End Sub

Private Sub DisplayRst(rst As ADODB.Recordset)
If rst.EOF = True Then
    Debug.Print "No recordset returned for Name = '"; _
                    rst.ActiveCommand.Parameters(0); "'"
End If
While rst.EOF = False
    Debug.Print "Name = '"; rst!au_fname; " "; rst!au_lname; "'"
    rst.MoveNext
Wend
End Sub


Quote:
> Thanks Jack Frost and dnagel for your response!

> Since the user of this program is going to copy and paste
> text into the text box that shows the value of the
> variable strArticleBody, I don't think that I should go
> through all the text and find and replace all the
> apostrophes with a double quote or ' '.

> I'll have to keep on trying. Feel free to post if you
> have any other ideas.
> Thanks for your time!

> Mark
> >-----Original Message-----
> >use '' in place of any single '

> >D.


> in message

> >> Hi,

> >> I'm having problems saving data that has apostrophe
> marks
> >> in it on a vb web application.   The variable "Private
> >> strArticleBody as string" will be holding text that
> will
> >> have " ' " in it.  Where I think I'm having problems is
> >> in my Insert Command statement where I have to put in
> >> apostrophes for SQL know that strArticleBody is a
> string
> >> variable instead of an integer.  Here is the code that
> >> I'm using:

> >> InsertQuery = "INSERT INTO tblArticle
> >> (ArticleID,ArticleHeader,ArticleBody) VALUES (" &
> >> lngArticleID & ",'" & strArticleHeader & "','" &
> >> strArticleBody & "')"
> >>         InsertCommand = New SqlCommand(InsertQuery,
> >> Connection1)
> >>         InsertCommand.CommandType = CommandType.Text
> >>         SqlDataAdapter1.InsertCommand = InsertCommand

> >>         SqlDataAdapter1.Update(ds)

> >> The Error that I get when SqlDataAdapter1.Update(ds)
> >> executes is Err.Number = 5  Err.Description = "Line 1:
> >> Incorect syntax near 's'"
> >> If I enter in data without apostrophes it updates
> >> successfully.  In SqlServer the data type
> >> for "ArticleBody" is Text and it does accept
> apostrophes
> >> if the are directly entered in SqlServer.

> >> My question is How can I update strArticleBody to
> >> SqlServer when it holds apostrophes?

> >> Any response is appreciated.
> >> Thanks,

> >> Mark

> >.



Mon, 04 Jul 2005 04:42:49 GMT  
 Problem with Insert command statement and updating SqlServer

Quote:
> <<Hey Bob, I finally got someone who wants to do things the right
> way...!>>

LOL, one down, 4832672 to go!


Mon, 04 Jul 2005 05:34:02 GMT  
 Problem with Insert command statement and updating SqlServer


Quote:
> Thanks Jack Frost and dnagel for your response!

> Since the user of this program is going to copy and paste
> text into the text box that shows the value of the
> variable strArticleBody, I don't think that I should go
> through all the text and find and replace all the
> apostrophes with a double quote or ' '.

you got a good response about using the parameters object but you should
understand that replacing a single ' with the pair '' does not affect the
data stored in the database -- when you read it back you will get just a
single ' character.  It's just a way of getting around the problem in the
syntax for the insert
insert into mytable (myname) values ('O''Malley')
writes the value O'Malley to the myname field of the new record


Mon, 04 Jul 2005 05:36:18 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Problem Inserting batch insert statement

2. Update Database - SQL Insert Statement

3. insert and update statements and VB controls

4. update/insert statement

5. INSERT INTO and UPDATE statement

6. Problem updating record in SQLServer 2000 using VB5

7. ADO-SqlServer Joined Update problem

8. Command:Update Statement/SET

9. oledb update command not inserting recently added row...

10. Sending a Insert or Update command to SQL-Server from Excel

11. Update command not inserting correct date

12. Insert statement problem - text fields with apostrophes

 

 
Powered by phpBB® Forum Software