SQL UPDATE query help please

[si_owen]

Hi all,

I have a SQL query that worked fine in my project until it came to
testing. I found that the NvarChar fields I have wont accept the use of
an '

My code and query is here does anyone know how to change the query to
accept an ' whilst keeping the data true.
Dim dbConn2 As SqlConnection = New
SqlConnection(d ataQuestions.Co nnectionString)

Dim NewQuestion As String = Question.Text

Dim NewAnswer As String = Answer.Text

Dim QuestionID As Integer = CInt(List1.Sele ctedValue)

Dim CategoryID As Integer = CInt(Category2. SelectedValue)

dbConn2.Open()

Dim strSQL As String = "UPDATE [faq questions] SET question
= '" & NewQuestion & "', answer = '" & NewAnswer & "', categoryID = " &
CategoryID & " where questionID = " & QuestionID

----------------------------------------------------------------------------------------------------------------------------------------------

I have also tried using brackets as below but this again causes the
program to crash...

Dim strSQL As String = "UPDATE [faq questions] SET question
= " & NewQuestion & "[, answer = " & NewAnswer & "][, categoryID = " &
CategoryID & "] where questionID = " & QuestionID
any help would be much appreceiated,

Simon

[Robinson]

Dim strSQL As String = "UPDATE [faq questions] SET question
= '" & NewQuestion & "', answer = '" & NewAnswer & "', categoryID = " &
CategoryID & " where questionID = " & QuestionID

The query is:

UPDATE [faq questions]
SET
question = NewQuestion,
answer = NewAnswer,
categoryID = CategoryID
WHERE
questionID = QuestionID

I'm assuming questionID and categoryID as integers, so:

Dim strSQL As String = "UPDATE [faq questions] SET " & _
"question=" & "'" & NewQuestion & "'
" & _
"answer=" & "'" & NewAnswer & "' " &
_
"categoryID =" & CategoryID.ToSt ring
& _
" WHERE questionID=" &
QuestionID.ToSt ring
..... I think ;)

(one of the reasons I prefer stored procedures and parameter passing!)


si_owen" <s.****@sstaffs .gov.ukwrote in message
news:11******** *************@e 3g2000cwe.googl egroups.com...

Hi all,

I have a SQL query that worked fine in my project until it came to
testing. I found that the NvarChar fields I have wont accept the use of
an '

My code and query is here does anyone know how to change the query to
accept an ' whilst keeping the data true.
Dim dbConn2 As SqlConnection = New
SqlConnection(d ataQuestions.Co nnectionString)

Dim NewQuestion As String = Question.Text

Dim NewAnswer As String = Answer.Text

Dim QuestionID As Integer = CInt(List1.Sele ctedValue)

Dim CategoryID As Integer = CInt(Category2. SelectedValue)

dbConn2.Open()

Dim strSQL As String = "UPDATE [faq questions] SET question
= '" & NewQuestion & "', answer = '" & NewAnswer & "', categoryID = " &
CategoryID & " where questionID = " & QuestionID

----------------------------------------------------------------------------------------------------------------------------------------------

I have also tried using brackets as below but this again causes the
program to crash...

Dim strSQL As String = "UPDATE [faq questions] SET question
= " & NewQuestion & "[, answer = " & NewAnswer & "][, categoryID = " &
CategoryID & "] where questionID = " & QuestionID
any help would be much appreceiated,

Simon

[Oenone]

si_owen wrote:

I have a SQL query that worked fine in my project until it came to
testing. I found that the NvarChar fields I have wont accept the use
of an '

You need to double up all apostrophe characters in your SQL. The easiest way
is to replace ' characters with '' (that's two single quotes):

\\\
Dim strSQL As String = "UPDATE [faq questions] " _
& "SET question = '" & Replace(NewQues tion, "'", "''") & "'" _
& ", answer = '" & Replace(NewAnsw er, "'", "''") & "'" _
& ", categoryID = " & CategoryID _
& " where questionID = " & QuestionID
///

That should do the job.

HTH,

--

(O)enone

[si_owen]

Thats brilliant works a treat

thanks very much.

Cheers,

Simon

[si_owen]

could uou solves this one too:

Dim strSQL As String = "INSERT INTO [faq questions]
(question, categoryID, UserName) VALUES ('" & NewQuestion & "',8")"
I have tried folowing ur method from before but its not accepting it as
a valid statement.

cheers,

Simon

[si_owen]

could uou solves this one too:

Dim strSQL As String = "INSERT INTO [faq questions]
(question, categoryID, UserName) VALUES ('" & NewQuestion & "',8")"
I have tried folowing ur method from before but its not accepting it as
a valid statement.

cheers,

Simon

[Oenone]

si_owen wrote:

could uou solves this one too:

Dim strSQL As String = "INSERT INTO [faq questions]
(question, categoryID, UserName) VALUES ('" & NewQuestion & "',8")"

I have tried folowing ur method from before but its not accepting it
as a valid statement.

It looks like you've a couple of mistakes here: some unexpected quotes after
the categoryID value of 8, and a missing value for the username. Try:

\\\
Dim strSQL As String = "INSERT INTO [faq questions]
(question, categoryID, UserName) VALUES ('" & Replace(NewQues tion, "'",
"''") & "',8, '" & Replace(Usernam e, "'", "''") &"')"
///

(sorry for the wrapping, you'll have to plug that all back together into a
single line of code).
--

(O)enone

[Robinson]

"si_owen" <s.****@sstaffs .gov.ukwrote in message
news:11******** *************@m 7g2000cwm.googl egroups.com...

could uou solves this one too:

Dim strSQL As String = "INSERT INTO [faq questions]
(question, categoryID, UserName) VALUES ('" & NewQuestion & "',8")"
I have tried folowing ur method from before but its not accepting it as
a valid statement.

cheers,

Simon

It isn't valid because it requires 3 fields, whereas your values contain
only two. You are forgetting to append the username after the categoryID
;).

[si_owen]

sorry i posted the code wrong,

it was:

Dim strSQL As String = "INSERT INTO [faq questions] (question,
categoryID) VALUES ('" & NewQuestion & "',8)"

i have tried using the one u previously posted taking out the user name
but i am getting an error saying end of statement expected, and some of
the line was commented out, what i had after removing username was:

Dim strSQL As String = "INSERT INTO [faq questions] " _
(question, categoryID) VALUES ('" & Replace(NewQues tion '", "''") &
"',8)"

sorry to keep bugging you bout these queries, but I have very little
knowledge of SQL.

Cheers

simon

[si_owen]

Robinson wrote:

"si_owen" <s.****@sstaffs .gov.ukwrote in message
news:11******** *************@m 7g2000cwm.googl egroups.com...

could uou solves this one too:

Dim strSQL As String = "INSERT INTO [faq questions]
(question, categoryID, UserName) VALUES ('" & NewQuestion & "',8")"
I have tried folowing ur method from before but its not accepting it as
a valid statement.

cheers,

Simon

It isn't valid because it requires 3 fields, whereas your values contain
only two. You are forgetting to append the username after the categoryID
;).

sorry i posted the code before i removed username,

I have reposted the code again