By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

SQL UPDATE query help please

P: n/a
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.ConnectionString)

Dim NewQuestion As String = Question.Text

Dim NewAnswer As String = Answer.Text

Dim QuestionID As Integer = CInt(List1.SelectedValue)

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

Nov 22 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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.ToString
& _
" WHERE questionID=" &
QuestionID.ToString
..... I think ;)

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


si_owen" <s.****@sstaffs.gov.ukwrote in message
news:11*********************@e3g2000cwe.googlegrou ps.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(dataQuestions.ConnectionString)

Dim NewQuestion As String = Question.Text

Dim NewAnswer As String = Answer.Text

Dim QuestionID As Integer = CInt(List1.SelectedValue)

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

Nov 22 '06 #2

P: n/a
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(NewQuestion, "'", "''") & "'" _
& ", answer = '" & Replace(NewAnswer, "'", "''") & "'" _
& ", categoryID = " & CategoryID _
& " where questionID = " & QuestionID
///

That should do the job.

HTH,

--

(O)enone
Nov 22 '06 #3

P: n/a
Thats brilliant works a treat

thanks very much.

Cheers,

Simon

Nov 22 '06 #4

P: n/a
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

Nov 22 '06 #5

P: n/a
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

Nov 22 '06 #6

P: n/a
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(NewQuestion, "'",
"''") & "',8, '" & Replace(Username, "'", "''") &"')"
///

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

(O)enone
Nov 22 '06 #7

P: n/a

"si_owen" <s.****@sstaffs.gov.ukwrote in message
news:11*********************@m7g2000cwm.googlegrou ps.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
;).
Nov 22 '06 #8

P: n/a
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(NewQuestion '", "''") &
"',8)"

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

Cheers

simon

Nov 22 '06 #9

P: n/a

Robinson wrote:
"si_owen" <s.****@sstaffs.gov.ukwrote in message
news:11*********************@m7g2000cwm.googlegrou ps.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

Nov 22 '06 #10

P: n/a

"si_owen" <s.****@sstaffs.gov.ukwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
>
Robinson wrote:
>"si_owen" <s.****@sstaffs.gov.ukwrote in message
news:11*********************@m7g2000cwm.googlegro ups.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')


Nov 22 '06 #11

P: n/a
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(NewQuestion '", "''") &
"',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
Nov 22 '06 #12

P: n/a
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.****@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.ConnectionString)

Dim NewQuestion As String = Question.Text

Dim NewAnswer As String = Answer.Text

Dim QuestionID As Integer = CInt(List1.SelectedValue)

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
Nov 22 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.