请问要怎么写一段SQL语法?
要新增一笔资料~但是要先查询资料后再新增~

1.我有一个表内有档案的资讯
2.另一个表要新增相关资讯

'我要先在表tblFileList内用ClassNo.Text查询SubjectNo, SubFolderNo, FileNo这三个值
Dim mySQL As String = "SELECT SubjectNo, SubFolderNo, FileNo FROM tblFileList Where FileName = '" & ClassNo.Text & "'"

'然后把上面查到的三个值塞进下面做资料新增
mySQL = "INSERT INTO tblSelectClass(UserID, ClassID, SubClassID, FileNo, StartDate, EndDate, StudyTime, StudyCount, StudyDate, Results) VALUES (" ...未完

这语法要怎么写?
能否两段合并一次处理?

MSSQL可以这样，但MYSQL就不知道
declare @UserID int,@Text varchar(100)
set @UserID = '1001'
set @Text = '要查的值'
INSERT INTO tblSelectClass(UserID, ClassID, SubClassID, FileNo)
SELECT @UserID,SubjectNo, SubFolderNo, FileNo FROM tblFileList Where FileName = @Text

收到的鲜花

• wube 于 2017-05-31 13:08 送鲜花  10朵   附言：好文章

在MSSQL中：
declare @值1 int,@值2 int,@值3 int
set @值1=1
set @值2=2
set @值3=3
insert into 表名(字段1,字段2,字段3) values(1,2,3)
等效
insert into 表名(字段1,字段2,字段3) values(@值1,@值2,@值3)
等效
insert into 表名(字段1,字段2,字段3)
select 1,2,3
等效
insert into 表名(字段1,字段2,字段3)
select @值1,@值2,@值3

等效
insert into 表名(字段1,字段2,字段3)
select @值1,@值2,值3 from 表名1 where 值3=3

刚在MSSQL管理介面上试是可以耶
只是后段资料都是空的

明明在数据库设计时声明栏位不得为NULL了
但是还是能只插入部分资料而不报错
真是奇怪了

所以还是要再接个
UPDATE tblSelectClass Set StartDate='', EndDate='', StudyTime='', StudyCount='', StudyDate='', Results=''
Where UserID='', SubjectNo='', SubFolderNo='', FileNo='';

插入这些栏位资料

所以这三个栏位资料怎么取出来?SubjectNo, SubFolderNo, FileNo

感觉好像不行~真麻烦~
只好修改数据库设计~避过这问题~

数据库查询出来的资料无法直接存到变量中?

TMD~数据库改完马上找到解法...
又要再改回来...

OK...

    Protected Sub btnQuery_Click(sender As Object, e As System.EventArgs) Handles btnQuery.Click
        Dim FileNo1 As String = "", FileNo2 As Integer = 0, FileNo3 As Integer = 0
        Dim mySQL As String = "SELECT SubjectNo, SubFolderNo, FileNo FROM tblFileList Where FileName = '" & ClassNo.Text & "'"
        Dim UserN As String = Mid(Mid(UserNo.Text, InStr(UserNo.Text, "(") + 1), 1, Len(Mid(UserNo.Text, InStr(UserNo.Text, "(") + 1)) - 1)
        Try
            Using mySqlDS = New SqlConnection(ConfigurationManager.ConnectionStrings(strCnnType & "DbBTA").ToString)
                Dim myCmd As SqlCommand
                myCmd = New SqlCommand(mySQL, mySqlDS)
                mySqlDS.Open()
                Using dbDRSQL = myCmd.ExecuteReader
                    While dbDRSQL.Read
                        FileNo1 = dbDRSQL("SubjectNo").ToString
                        FileNo2 = dbDRSQL("SubFolderNo").ToString
                        FileNo3 = dbDRSQL("FileNo").ToString
                    End While
                End Using
            End Using

            Using myCnn As New SqlConnection(ConfigurationManager.ConnectionStrings(strCnnType & "DbBTA").ToString)
                myCnn.Open()
                Dim myCmd As SqlCommand
                mySQL = "INSERT INTO tblSelectClass(UserID, ClassID, SubClassID, FileNo, StartDate, EndDate, StudyTime, StudyCount, StudyDate, Results, CreatorNo, CreateDate, ReviserNo, ReviseDate) VALUES ("
                mySQL += "'" & UserN & "', '" & FileNo1 & "', " & FileNo2 & ", " & FileNo3 & ", '" & starttime.Text & "', '" & endtime.Text & "', "
                mySQL += "0, 0,'','N','" & Session("LoginNo") & "', GetDate() ,'" & Session("LoginNo") & "', GetDate())"
                myCmd = New SqlCommand(mySQL, myCnn)
                myCmd.ExecuteNonQuery()
            End Using
        Catch ex As Exception
            Dim strErr As String = "●●● 程式错误讯息如下 ●●●" & "\n\n" & Replace(Replace(Replace(Replace(Trim(ex.ToString()), Chr(10), ""), Chr(13), ""), "\", "\\"), "'", "\'")
            ScriptManager.RegisterStartupScript(Me, Me.GetType(), "alert", "alert('" & strErr & "');", True)
        End Try
    End Sub

因为我这是公司内网网站所以不连外~就不做sql injection的防范了~

[此贴子已经被作者于2017-5-31 17:59编辑过]

我没MYSQL，我只说MSSQL的语法，MSSQL的表，有个默认值，如
create table 表1(name varchar(10),sex varchar(2) default '男')
insert into 表1(name) values('张三')
插入的数据将会是
name    sex
张三    男
insert into 表1(name,sex) values('李四','女')
插入的数据将会是
name    sex
李四    女

declare @变量1 int
select @变量1=count(字段1) from 表1
就是在查询的时候，把记录数保存到 @变量1 去