473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL UPDATE query help please

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
12 1594
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
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
Thats brilliant works a treat

thanks very much.

Cheers,

Simon

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

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

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

"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bob Kaku | last post by:
I'm trying to retrieve a text value from a MySQL database, put it into an updateable form, allow edits, and send back the edited text back into the MySQL database. I've been able to successfully...
4
by: Karaoke Prince | last post by:
Hi There, I have an update statement to update a field of a table (~15,000,000 records). It took me around 3 hours to finish 2 weeks ago. After that no one touched the server and no...
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
3
by: Ken Bush | last post by:
How can I write an update query that removes part of a field? Like if I have a field with values such as 8/3/68 (a birthday obviously) and I need to put values in a new column but I need...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
3
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all...
5
by: colleen1980 | last post by:
Hi: In my table there is a field of type checkbox. I create a button on my form and wants to deselect all the checkboxes in that field (PrintQueue). Table: Research_New PrintQueue Format Yes/No...
5
by: keeps21 | last post by:
A little problem I've run into is the following. I have a script that allows a user to edit a story. I have an HTML form for title and main_text which gets it's values by pulling the selected...
1
by: giovannino | last post by:
Dear all, I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !). 1) Given that I'm not a programmer I can't understand why...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.