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]

Re: SQL UPDATE query help please

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.owen@sstaffs .gov.ukwrote in message
news:1164184455 .462971.10840@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]

Re: SQL UPDATE query help please

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]

Re: SQL UPDATE query help please

Thats brilliant works a treat

thanks very much.

Cheers,

Simon

[si_owen]

Re: SQL UPDATE query help please

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]

Re: SQL UPDATE query help please

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]

Re: SQL UPDATE query help please

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]

Re: SQL UPDATE query help please


"si_owen" <s.owen@sstaffs .gov.ukwrote in message
news:1164207697 .339233.33490@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]

Re: SQL UPDATE query help please

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]

Re: SQL UPDATE query help please


Robinson wrote:

"si_owen" <s.owen@sstaffs .gov.ukwrote in message
news:1164207697 .339233.33490@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

[Robinson]

Re: SQL UPDATE query help please


"si_owen" <s.owen@sstaffs .gov.ukwrote in message
news:1164211152 .154796.284820@ k70g2000cwa.goo glegroups.com.. .

>
Robinson wrote:
>

>"si_owen" <s.owen@sstaffs .gov.ukwrote in message
>news:116420769 7.339233.33490@ m7g2000cwm.goog legroups.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
>

It often helps to break it right down....... something like this (I'm not
sure what your categoryID and Username variables look like....):


Dim strSQL As String

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



becomes:

INSERT INTO [faq questions] (question, categoryID, UserName) VALUES ( 'a new
question', 8, 'a user name')

[Oenone]

Re: SQL UPDATE query help please

si_owen wrote:

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)"

There's a double-quote character missing before the single quote following
NewQuestion.

These are just simple typos now rather than programming questions, you need
to check carefully through the statements you're entering if you get
compilation errors like that.

Hope that helps,

--

(O)enone

[Rad [Visual C# MVP]]

Re: SQL UPDATE query help please

Hey Simon,

I STRONGLY recommend you use parameterised queries instead of inline
SQL. Among the benefits of that is:

1) You don't have to somersault for odd characters like '
2) You are more protected from SQL Injection attacks


On 22 Nov 2006 00:34:15 -0800, "si_owen" <s.owen@sstaffs .gov.uk>
wrote:

>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( dataQuestions.C onnectionString )
>
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

--

Bits.Bytes.

http://bytes.thinkersroom.com