Just started studying, this one small operation how also not TT
Condition is: Student table AND Score list of students, Student id manual input, is this a DELETE FROM Student, Score the WHERE Student. StudentID=Score. The StudentID AND Student. The StudentID="+ Text1. Text
Adodc1 RecordSourse is Student table, DataGrid1 DataSourse is adodc1
Adodc2 RecordSourse is Score table, DataGrid2 DataSourse is adodc2
The code is as follows:
Private Sub Command1_Click ()
The Set stu=New ADODB. Connection
Stu. The ConnectionString="Provider=SQLOLEDB. 1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=student performance management; Data Source=MIKI \ SQLEXPRESS "
Stu. Open
Dim strSQL As String
StrSQL="DELETE FROM Student, Score the WHERE Student. StudentID=Score. The StudentID AND Student. The StudentID=" + Text1. Text
Stu. Execute strSQL//show here are wrong! Yellow!!!!!
MsgBox "fire!"
Adodc1. Refresh
DataGrid1. Refresh
Adodc2. Refresh
DataGrid2. Refresh
End Sub
CodePudding user response:
A DELETE statement to DELETE a table, only two tables to DELETE to are divided into two statements,CodePudding user response:
Transact - SQL reference
The DELETE
To delete rows from the table,
Syntax
The DELETE
[FROM]
{table_name WITH (& lt; Table_hint_limited & gt; N [...])
| view_name
| rowset_function_limited
}
[the FROM {& lt; table_source & gt;} n], [...]
[WHERE
{& lt; Search_condition & gt;
| {[CURRENT OF
{{[GLOBAL] cursor_name}
| cursor_variable_name
}
]}
}
]
[OPTION (& lt; query_hint & gt;,... [n])]Table_name [[AS] table_alias] [WITH (& lt; table_hint & gt;,... [n])]
| view_name [[AS] table_alias]
| rowset_function [[AS] table_alias]
| derived_table table_alias [AS] [(column_alias,... [n])]
| & lt; Joined_table & gt;| & lt; Table_source & gt; CROSS JOIN & lt; Table_source & gt;
| & lt; Joined_table & gt;[INNER | {{LEFT | RIGHT | FULL}} [OUTER]]
[& lt; join_hint & gt;]
The JOIN{FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}{INDEX (index_val [,... n])
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}{{HASH | ORDER} GROUP
UNION | {CONCAT | HASH | MERGE}
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}
Parameter
The FROM
Keyword is optional, can be used in the DELETE keyword with the target table_name, view_name or between rowset_function_limited,
Table_name
Is to delete rows from the name,
Within the scope of the table variable, or the OPENDATASOURCE function as the server name is made up of four parts of the name of the table (or view name) can also be used as a source of table in the DELETE statement,
WITH (& lt; Table_hint_limited & gt; N [...])
Specify the target table is allowed to one or more table tips, need to have the WITH keyword and opening parenthesis, there is no READPAST, NOLOCK and READUNCOMMITTED, more information about the table suggests, please see the FROM,
View_name
Is the name of the VIEW that, in VIEW of the FROM clause view_name referenced VIEW must be renewable and correct a base table, about more information, to update the VIEW see the CREATE VIEW,
If exists in another database table or view or have a different from the current user's owner, use the format for server_name. Database. [owner] object_name consists of four parts of legal name, for more information, please refer to Transact - SQL syntax rules,
Rowset_function_limited
OPENQUERY or OPENROWSET function, depending on the provider functions, the relevant provider necessary function for more information, please see the OLE DB provider, UPDATE and DELETE statements requirements, for more information on line set function, please see the OPENQUERY and OPENROWSET,
The FROM & lt; Table_source & gt;
Specify additional FROM clause, this Transact - SQL to DELETE extension allows you to FROM & lt; Table_sources & gt; The specified data, and FROM the first in the FROM clause to delete the corresponding line of the table,
This extension to connect, can replace the subquery in the WHERE clause to identify to delete rows,
Table_name [[AS] table_alias]
Is to provide standard for deleting the table name,
View_name [[AS] table_alias]
Is to provide standard values for the delete operation view name, bring home the UPDATE trigger view cannot is the goal OF containing the FROM clause OF the UPDATE,
WITH (& lt; Table_hint & gt;
Specify one or more tables, the table tips for more information, see the FROM,
Rowset_function [[AS] table_alias]
Line is set function name and the optional alias, for more information on line set function list, please see the line set function,
Derived_table [AS] table_alias
Retrieved from the database row subquery, derived_table used as outer query input,
Column_alias
Replace the result set column names within optional alias, insert each column in the select list an alias, and the whole column alias list enclosed in parentheses,
Consisting of two or more tables product result set, for example:
SELECT *
The FROM tab1 LEFT OUTER JOIN tab2 ON tab1. C3=tab2. C3
The RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4,
ON tab3, c1=tab4, c1
ON tab2. C3=tab4,. C3
For multiple CROSS connection, please use parentheses to change the connection of the natural order,
Specify the type of the join operation,
INNER
Specify the returns of each pair of matching lines, abandoned two does not match the row in the table, if you do not specify a connection type, then this is the default setting,
LEFT (OUTER)
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull