添加数据库中，内容有单引号，怎么办?(急 急 急)

[luoyiming]

我想用
insert into Tree () Values ()
的方法往数据库里加东西

但是我写的信息里面，可能内容中含有单引号,
比如我想添加一个Name字段为"我的分类1", SQL字段为 " trade='系统' "

那么写成SQL就是：
insert into Tree (Name,SQL) Values ('我的分类1','trade='系统'' )

哪个 'trade ='系统'' 连我都分不清楚了，更何况是电脑
这个问题怎么解决??

[gc801]

外面那个用双引号.
insert into Tree (Name,SQL) Values ("我的分类1","trade='系统'" )

[luoyiming]

但是VB里要用这个双引号啊，
strSQL = "insert into Tree (Name,SQL) Values ('" & Text1.Text & "' ....

[clear_zero]

方法1:tb1:表1 c1:column1 string1:string型变量
sql="insert int tb1 select c1='" & Replace(string1, "'", "''") & "'"
conn.excute sql

方法2
dim rs as adodb.recordset
set rs=new adodb.recordset
sql="select * from tb1"
rs.open sql,conn,1,3
rs.addnew
'相应字段
rs.field(n)=string1
rs.update
rs.close

[bilujun]

ADO里面不是有Command对象嘛，用参数查询

dim cmd as new Command
sql = "insert into Tree (field1,field2) Values (?,?)"
cmd.activeconnection = conn
cmd.commandtext = sql
cmd.parameters.append cmd.createparams(,adVariant,,value1)
cmd.parameters.append cmd.createparams(,adVariant,,value2)
cmd.excute

[bilujun]

当然也可以用 clear_zero(清晰) 的第二个方法

[faysky2]

那可以不用insert into语句，可以用rs.AddNew
rs.AddNew
rs(0)=...
rs(1)=...
....
rs.UpDate

[drinkcrow]

Public Function MakeStr(ByVal Str As String) As String
MakeStr = Trim(Replace(Str, "'", "''"))
End Function

[starwalker]

用两个单引号表示一个单引号

[drinkcrow]

在添加之前 用 MakeStr 过滤一下信息，把'替换成 '', 即转义一下

[lsftest]

chr$(39)='

[vbman2003]

个人认为用存储过程或参数化查询比较好，可以解决的不单单是'号，许多特殊符号都可以处理

[fishmans]

1.字符串内的单引号用两个单引号表示
2.以一个转意字符代替单引号写入，读出来时再转成单引号

[shawls]

Public Property Set ParentControl(ByVal vData As Object)
'used when assigning an Object to the property, on the left side of a Set statement.
'Syntax: Set x.ParentControl = Form1
Set mvarParentControl = vData
End Property

Public Property Get ParentControl() As Object
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.ParentControl
Set ParentControl = mvarParentControl
End Property

Public Property Let Icon(ByVal vData As ttIconType)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.Icon = 5
mvarIcon = vData
If lHwnd <> 0 And mvarTitle <> Empty And mvarIcon <> TTNoIcon Then
SendMessageAny lHwnd, TTM_SETTITLE, CLng(mvarIcon), ByVal mvarTitle
End If

End Property

Public Property Get Icon() As ttIconType
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.Icon
Icon = mvarIcon
End Property
Public Property Let ForeColor(ByVal vData As Long)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.ForeColor = 5
mvarForeColor = vData
If lHwnd <> 0 Then
SendMessageAny lHwnd, TTM_SETTIPTEXTCOLOR, mvarForeColor, 0&
End If

End Property

Public Property Get ForeColor() As Long
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.ForeColor
ForeColor = mvarForeColor
End Property

Public Property Let Title(ByVal vData As String)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.Title = 5
mvarTitle = vData
If lHwnd <> 0 And mvarTitle <> Empty And mvarIcon <> TTNoIcon Then
SendMessageAny lHwnd, TTM_SETTITLE, CLng(mvarIcon), ByVal mvarTitle
End If
End Property

Public Property Get Title() As String
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.Title
Title = Ti.lpStr
End Property

Public Property Let BackColor(ByVal vData As Long)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.BackColor = 5
mvarBackColor = vData
If lHwnd <> 0 Then
SendMessageAny lHwnd, TTM_SETTIPBKCOLOR, mvarBackColor, 0&
End If

End Property

Public Property Get BackColor() As Long
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.BackColor
BackColor = mvarBackColor
End Property

Public Property Let TipText(ByVal vData As String)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.TipText = 5
Ti.lpStr = vData
If lHwnd <> 0 Then
SendMessageAny lHwnd, TTM_UPDATETIPTEXTA, 0&, Ti
End If

End Property

Public Property Get TipText() As String
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.TipText
TipText = mvarTipText
End Property

Private Sub Class_Terminate()
If lHwnd <> 0 Then
DestroyWindow lHwnd
End If
End Sub



以上代码保存于： SourceCode Explorer(源代码数据库)
复制时间： 2005-11-07 14:15:57
软件版本： 1.0.881
软件作者： Shawls
E-Mail： [e-mail]
QQ： 9181729

[shawls]

发错地方了

对不起啊

[vbman2003]

存储过程论坛上有许多讨论，你可搜索一下。这儿就举一个ACCESS参数化查询的例子：

ACCESS参数化查询可以有二种方式：
1.直接在ACCESS的查询对象中建立参数化查询，在程序中调用
2.在代码中用“PARAMETERS”指定查询参数

下面的例子分别介绍一下：

窗体上一个CommmandBox,二个textbox，txtName可以输入任何字符，txtQty输入数量

先在ACCESS查询对象中建立一个查询tb_in，用来插入数据：
INSERT INTO tb ( iName, qty )
VALUES ([@1], [@2]);

Private Sub Command2_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim sql As String
Dim str, num
Dim StrConnect As String

StrConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & App.Path & "\test.mdb;Persist " _
& "Security Info=False;Jet OLEDB:Database Password=5966090"
Set cn = New ADODB.Connection
cn.Open StrConnect

'执行 tb_in 插入数据
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "tb_in"
cmd.CommandType = adCmdStoredProc

Set param = cmd.CreateParameter("@1", adChar, adParamInput, 20, txtName.Text)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@2", adBigInt, adParamInput, 4, txtQty.Text)
cmd.Parameters.Append param
Set rs = New ADODB.Recordset
Set rs = cmd.Execute
Set cmd = Nothing
Set rs = Nothing

'用PARAMETERS指定参数,查询上面插入的数据
str = "PARAMETERS @1 text,@2 int;"
sql = str & "SELECT * FROM tb WHERE iName=@1 And qty=@2"
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = sql
cmd.CommandType = adCmdText
Set param = cmd.CreateParameter("@1", adChar, adParamInput, 20, txtName.Text)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@2", adBigInt, adParamInput, 4, txtQty.Text)
cmd.Parameters.Append param
Set rs = New ADODB.Recordset
Set rs = cmd.Execute
Debug.Print rs!iName & " / " & rs!qty
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub

[wang_jian0_0]

vbman2003(家人)
个人认为用存储过程或参数化查询比较好，可以解决的不单单是'号，许多特殊符号都可以处理

能不能给个示例.谢谢