Hi Guys
Im a newbie in vb.net and trying to insert data into students table and some related details to scores table.
Point is, a student registered in a class(level) must offer subjects (courses) for that class.

I insert a student in students table (see Cmd below). This is easy.
THEN I must insert his studentId and SubjectIds for his Class in scores table (see Cmd1). some error LIKE SUBQUERY NOT PERMITTED IN SQLSTRING)
How do I get around this

 Sub SaveNewStudent()
        Conn.ConnectionString = "Server=gbhs;database=aboutinsert;Trusted_Connection=True;"
        Dim strSQL As String = "INSERT INTO Students (StudentNames,gender,classid,dob,rollno) VALUES (@studentnames, @gender,@classid ,@dob,@rollno) SET  @Identity = SCOPE_IDENTITY()"

        Dim cmd As New SqlCommand(strSQL, Conn)

        With cmd

            .Parameters.Add(New SqlParameter("@studentnames", SqlDbType.VarChar, 50)).Value = txtStudentNames.Text
            .Parameters.Add(New SqlParameter("@gender", SqlDbType.Char, 1)).Value = txtGender.Text
            .Parameters.Add(New SqlParameter("@classid", SqlDbType.Int)).Value = txtClassID.Text
            .Parameters.Add(New SqlParameter("@dob", SqlDbType.Date)).Value = txtDoB.Text
            .Parameters.Add(New SqlParameter("@rollno", SqlDbType.Int)).Value = txtRollNo.Text

        End With
        Dim parameter As SqlParameter = cmd.Parameters.Add("@Identity", SqlDbType.Int, 0, "StudentID")
        parameter.Direction = ParameterDirection.Output

        Dim RetValue As Integer
        MsgBox("New Student Saved successfully")
        cmd.CommandText = "SELECT MAX(StudentID) FROM students"
        RetValue = cmd.ExecuteScalar()

        Dim strSQL1 As String = "INSERT INTO scores (StudentID,subjectid,classid) VALUES (@studentID,(SELECT DISTINCT SubjectID FROM Scores WHERE classid=" & Me.txtClassID.Text & " ),@classid )"
        Dim cmd1 As New SqlCommand(strSQL1, Conn)
        With cmd1
            .Parameters.Add(New SqlParameter("@studentID", SqlDbType.Int)).Value = RetValue
            .Parameters.Add(New SqlParameter("@classid", SqlDbType.Int)).Value = txtClassID.Text

        End With
        cmd1.ExecuteNonQuery() **'ERROR IS HERE ABOUT SUBQUERY IN strsql1**
        MsgBox("Scores Table Updated successfully")
    End Sub

Try using the insert like this:

INSERT INTO scores (StudentID,subjectid,classid) VALUES 
SELECT DISTINCT @studentID, SubjectID, @classid FROM Scores WHERE classid= @classid 
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.