pb with the ' in sql request

[231166]

Hi,

In my application,i give the possibility to a user to type a term by the way of an inputbox.
After this, the term in inserted in a a field of a table in a database( with an sql request).

the problem is that when a term contains a '( because in french we can find words like this, example : maison de l'habitat), the programs stops bacause the ' is considered as a ' in an sql request.
Could you help me to solve this pb, because the application stops each time because of it.

Thanks a lot from you.

Regards.

Nathalie

 

[chrissie1]

we need code, but I already know the problem and it is because you aren't using parameters.

Christiaan Baes
Belgium

"My new site" - Me

 

[231166]

Hi,

Here is the code you need
strLibelle is the input box which contains the new term

strSQLInsertTerme = "INSERT INTO TERMES(Lib_TERME,EM,ID_THES) VALUES ('" & strLibelle & "','0'," & intNumThes.ToString & ")"
                    'on ouvre la connexion à la base de données
                    objConn.Open()
                    objInsertTerme = New SqlCommand(strSQLInsertTerme, objConn)
                    objInsertTerme.ExecuteNonQuery()

Thanks a lot for your help

Regards

Nathalie

 

[chrissie1]

so that would become something like this

strSQLInsertTerme = "INSERT INTO TERMES(Lib_TERME,EM,ID_THES) VALUES (@Lib_terme,@em,@id_thes)"
                    'on ouvre la connexion à la base de données
                    objConn.Open()
                    objInsertTerme = New SqlCommand(strSQLInsertTerme, objConn)
                    objinsertterme.parameters.add("@lib_terme", sqldbtype.varchar,30)
                    objinsertterme.parameters.add("@em", sqldbtype.varchar,30)
                    objinsertterme.parameters.add("@id_thes", sqldbtype.int)
                    objinsertterme.parameters(0).value = strLibelle 
                    objinsertterme.parameters(1).value = "0"
                    objinsertterme.parameters(2).value = intNumThes
                    objInsertTerme.ExecuteNonQuery()

BTW I was guessing the type of the parameters to use, but I'm sure you will know what to put there.

Christiaan Baes
Belgium

"My new site" - Me

 

[PsychoCoder]

231166,

I expand on chrissie1's code I would further modify it like:

strSQLInsertTerme = "InsertDate"
'on ouvre la connexion à la base de données
objInsertTerme = New SqlCommand(strSQLInsertTerme, objConn)
With objInsertTerme
   .CommandText = strSQLInsertTerme
   .CommandType = CommandType.StoredProcedure
   .Connection = objConn
   .Parameters.AddWithValue("@lib_terme",param_value)
   .Parameters.AddWithValue("@em", param_value)
   .Parameters.AddWithValue("@id_thes", param_value)
End With                   
Try
    objInsertTerme.ExecuteNonQuery()
Catch ex As Exception
    MsgBox(ex.message)
End Try

I would take your code and create a stored procedure and use AddWithValue for the parameters. Also, if you set the .Connection] property of the Command object you dont have to use objConn.Open.

Senior Qik III, ASP.Net, VB.Net ,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM Users WHERE clue > 0
0 Rows Returned

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~