"update " + Me.TB_TableName.Text + " set [Text]='" + Me.TBFlashName.Text + "' where [id]=1;"
一般的语句是这样写的啥,现在不想要" set [Text]='
Sql语句的更新语法能不能不带字段名(set [Text]=)啊,把所有的字段(不含自动编号的字段)都更新 .
不是插入语法里面的带指定字段和不带字段的两种吗,Update里面有吗?
一般的语句是这样写的啥,现在不想要" set [Text]='
Sql语句的更新语法能不能不带字段名(set [Text]=)啊,把所有的字段(不含自动编号的字段)都更新 .
不是插入语法里面的带指定字段和不带字段的两种吗,Update里面有吗?
可以先查询表,从表中得到列的名字,然后再拼Sql语句
'参数inData的个数比实际列数小“1”,因为首列为id(自动编号的)
Public Sub myUpdate(ByVal TableName As String, ByVal AccessPath As String, ByVal inData As ArrayList)
Dim MyAccess As New Class_Provider
MyAccess.DataAccess(AccessPath)
'得到表的查询
Dim myT_Query As DataTable = MyAccess.ReturnOleDataSet("select * from " + TableName).Tables(0) '得到表的列的名字() Dim SqlStr As String = "" 'Update 的“set”部分的字符串
For i As Integer = 1 To inData.Count - 1
If i = 1 Then
SqlStr = SqlStr + " [" + myT_Query.Columns(i).ColumnName + "]='" + inData.Item(i) + "'"
Else
SqlStr = SqlStr + ", [" + myT_Query.Columns(i).ColumnName + "]='" + inData.Item(i) + "'"
End If
Next
SqlStr = "update " + TableName + " set " + SqlStr + " where [id]=" + inData.Item(0) MyAccess.doSql(SqlStr)
End Sub调用:
'查询
Dim MyAccess As New Class_PublicValues
Dim MyDataBasePath As String = Server.MapPath("DataSource/LiuLi_FST.mdb") '表名
Dim ShowTableName As String = Me.TB_TableName.Text
'Id
Dim myId As String = Me.TextBox1.Text
Dim MyAccess_Query As New Class_Provider
MyAccess_Query.DataAccess(MyDataBasePath)
Dim myT As DataTable = MyAccess_Query.ReturnOleDataSet("select * from " + ShowTableName + " where [id]=" + myId).Tables(0) '得到更新数据的ArrayList
Dim Update_al As New ArrayList
Update_al.Clear()
If myT.Columns.Count = 3 Then
Update_al.Add(Me.TextBox1.Text)
Update_al.Add(Me.TextBox2.Text)
Update_al.Add(Me.TextBox3.Text)
End If
If myT.Columns.Count = 4 Then
Update_al.Add(Me.TextBox1.Text)
Update_al.Add(Me.TextBox2.Text)
Update_al.Add(Me.TextBox3.Text)
Update_al.Add(Me.TextBox4.Text)
End If
If myT.Columns.Count = 5 Then
Update_al.Add(Me.TextBox1.Text)
Update_al.Add(Me.TextBox2.Text)
Update_al.Add(Me.TextBox3.Text)
Update_al.Add(Me.TextBox4.Text)
Update_al.Add(Me.TextBox5.Text)
End If
If myT.Columns.Count = 6 Then
Update_al.Add(Me.TextBox1.Text)
Update_al.Add(Me.TextBox2.Text)
Update_al.Add(Me.TextBox3.Text)
Update_al.Add(Me.TextBox4.Text)
Update_al.Add(Me.TextBox5.Text)
Update_al.Add(Me.TextBox6.Text)
End If
If myT.Columns.Count = 7 Then
Update_al.Add(Me.TextBox1.Text)
Update_al.Add(Me.TextBox2.Text)
Update_al.Add(Me.TextBox3.Text)
Update_al.Add(Me.TextBox4.Text)
Update_al.Add(Me.TextBox5.Text)
Update_al.Add(Me.TextBox6.Text)
Update_al.Add(Me.TextBox7.Text)
End If
If myT.Columns.Count = 8 Then
Update_al.Add(Me.TextBox1.Text)
Update_al.Add(Me.TextBox2.Text)
Update_al.Add(Me.TextBox3.Text)
Update_al.Add(Me.TextBox4.Text)
Update_al.Add(Me.TextBox5.Text)
Update_al.Add(Me.TextBox6.Text)
Update_al.Add(Me.TextBox7.Text)
Update_al.Add(Me.Textbox8.Text)
End If
If myT.Columns.Count = 9 Then
Update_al.Add(Me.TextBox1.Text)
Update_al.Add(Me.TextBox2.Text)
Update_al.Add(Me.TextBox3.Text)
Update_al.Add(Me.TextBox4.Text)
Update_al.Add(Me.TextBox5.Text)
Update_al.Add(Me.TextBox6.Text)
Update_al.Add(Me.TextBox7.Text)
Update_al.Add(Me.Textbox8.Text)
Update_al.Add(Me.Textbox9.Text)
End If
If myT.Columns.Count = 10 Then
Update_al.Add(Me.TextBox1.Text)
Update_al.Add(Me.TextBox2.Text)
Update_al.Add(Me.TextBox3.Text)
Update_al.Add(Me.TextBox4.Text)
Update_al.Add(Me.TextBox5.Text)
Update_al.Add(Me.TextBox6.Text)
Update_al.Add(Me.TextBox7.Text)
Update_al.Add(Me.Textbox8.Text)
Update_al.Add(Me.Textbox9.Text)
Update_al.Add(Me.Textbox10.Text)
End If MyAccess.myUpdate(ShowTableName, MyDataBasePath, Update_al)
Page.RegisterStartupScript("pp", "<script>alert('更新成功!');</script>")
数据访问这样写,不写SQL即可实现增、删、改、查,跨Oracle/SQLServer多种数据库
http://community.csdn.net/Expert/topic/5461/5461087.xml?temp=.3022882
'更新,参数inData的首列为id(自动编号的),所以使用部分从第1列开始
' 参数inData 为要更新的列的内容的ArrayList 。是所有列的数据
Public Sub myUpdate(ByVal TableName As String, ByVal AccessPath As String, ByVal inData As ArrayList)
Dim MyAccess As New Class_Provider
MyAccess.DataAccess(AccessPath)
'得到表的查询
Dim myT_Query As DataTable = MyAccess.ReturnOleDataSet("select * from " + TableName).Tables(0) '得到表的列的名字() Dim SqlStr As String = "" 'Update 的“set”部分的字符串
For i As Integer = 1 To inData.Count - 1
If i = 1 Then
SqlStr = SqlStr + " [" + myT_Query.Columns(i).ColumnName + "]='" + inData.Item(i) + "'"
Else
SqlStr = SqlStr + ", [" + myT_Query.Columns(i).ColumnName + "]='" + inData.Item(i) + "'"
End If
Next
SqlStr = "update " + TableName + " set " + SqlStr + " where [id]=" + inData.Item(0) MyAccess.doSql(SqlStr)
End Sub '插入
Public Sub myInsert(ByVal TableName As String, ByVal AccessPath As String, ByVal inData As ArrayList)
Dim MyAccess As New Class_Provider
MyAccess.DataAccess(AccessPath)
'得到表的查询
Dim myT_Query As DataTable = MyAccess.ReturnOleDataSet("select * from " + TableName).Tables(0) Dim Sql_field_Str As String = ""
'insert 的“字段”部分的字符串
For i As Integer = 1 To inData.Count - 1
If i = 1 Then
Sql_field_Str = Sql_field_Str + myT_Query.Columns(i).ColumnName
Else
Sql_field_Str = Sql_field_Str + "," + myT_Query.Columns(i).ColumnName
End If
Next
'插入字段的值
Dim Sql_fieldValue_Str As String = ""
For i As Integer = 1 To inData.Count - 1
If i = 1 Then
Sql_fieldValue_Str = Sql_fieldValue_Str + "'" + inData.Item(i) + "'"
Else
Sql_fieldValue_Str = Sql_fieldValue_Str + ",'" + inData.Item(i) + "'"
End If
Next Dim SqlStr As String = ""
SqlStr = "insert into " + TableName + "(" + Sql_field_Str + ")values(" + Sql_fieldValue_Str + ")" MyAccess.doSql(SqlStr)
End Sub '删除
Public Sub myDel(ByVal TableName As String, ByVal AccessPath As String, ByVal myid As String)
Dim MyAccess As New Class_Provider
MyAccess.DataAccess(AccessPath) Dim SqlStr As String = "Delete from " + TableName + " Where [id]=" + myid
MyAccess.doSql(SqlStr)
End Sub