SQL UPDATE query help please

  • Thread starter Thread starter si_owen
  • Start date Start date
S

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(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
 
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 said:
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 '
Click to expand...

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,
 
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
 
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 said:
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.
Click to expand...

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).
 
si_owen said:
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
Click to expand...

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 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
 
Robinson said:
si_owen said:
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
Click to expand...

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
;).
Click to expand...


sorry i posted the code before i removed username,

I have reposted the code again
 
si_owen said:
si_owen said:
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
Click to expand...

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
;).
Click to expand...


sorry i posted the code before i removed username,

I have reposted the code again
Click to expand...


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')
 
si_owen said:
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)"
Click to expand...

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,
 
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


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
Click to expand...
 
Back
Top