Home All Groups Group Topic Archive Search About

SQL UPDATE query help please

microsoft.public.dotnet.languages.vb
Author
22 Nov 2006 8:34 AM
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

Author
22 Nov 2006 10:37 AM
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.ToString
& _
                                        " WHERE questionID=" &
QuestionID.ToString


..... I think ;)

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




si_owen" <s.o***@sstaffs.gov.uk> wrote in message
Show quoteHide quote
news:1164184455.462971.10840@e3g2000cwe.googlegroups.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
>
Author
22 Nov 2006 12:10 PM
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(NewQuestion, "'", "''") & "'" _
        & ", answer = '" & Replace(NewAnswer, "'", "''") & "'" _
        & ", categoryID = " & CategoryID _
        & " where questionID = " & QuestionID
///

That should do the job.

HTH,

--

(O)enone
Author
22 Nov 2006 1:18 PM
si_owen
Thats brilliant works a treat

thanks very much.

Cheers,

Simon
Author
22 Nov 2006 2:37 PM
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
Author
22 Nov 2006 3:41 PM
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(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
Author
22 Nov 2006 3:56 PM
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(NewQuestion '",  "''") &
"',8)"

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

Cheers

simon
Author
22 Nov 2006 6:36 PM
Oenone
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
Author
22 Nov 2006 3:01 PM
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
Author
22 Nov 2006 3:43 PM
Robinson
Show quote Hide quote
"si_owen" <s.o***@sstaffs.gov.uk> wrote in message
news:1164207697.339233.33490@m7g2000cwm.googlegroups.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
;).
Author
22 Nov 2006 3:59 PM
si_owen
Robinson wrote:

Show quoteHide quote
> "si_owen" <s.o***@sstaffs.gov.uk> wrote in message
> news:1164207697.339233.33490@m7g2000cwm.googlegroups.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
Author
22 Nov 2006 4:44 PM
Robinson
Show quote Hide quote
"si_owen" <s.o***@sstaffs.gov.uk> wrote in message
news:1164211152.154796.284820@k70g2000cwa.googlegroups.com...
>
> Robinson wrote:
>
>> "si_owen" <s.o***@sstaffs.gov.uk> wrote in message
>> news:1164207697.339233.33490@m7g2000cwm.googlegroups.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')
Author
22 Nov 2006 7:39 PM
Rad [Visual C# MVP]
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


Show quoteHide quote
On 22 Nov 2006 00:34:15 -0800, "si_owen" <s.o***@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



Post Other interesting topics
How to get a form's property value from a class?
Russian text output
How to identiy numerics in a string?
another vb .net xml question
Help needed in using FSO's, TextStreams, etc. --- Code Review and Advice requested
PDF Creation components
"Four" to 4
How to deploye SQL 2005 Express with my application
adding listbox selected items
Debugging a COM Interop Class Library