i want same task from vb.net

[muhammadanzar]

HI,

I am new in store procedure, i want to insert in many to many relation ship Table. I have Three table. given in image

when I Insert Unique value it work good. but when insert duplicate it create following error.



code of sql server store procedure is.

Create procedure spInsertIntoStudentCourses
@StudentName nvarchar(50),
@CourseName nvarchar(50)
as
Begin

Declare @StudentId int
Declare @CourseId int

Select @StudentId = Id from Students where StudentName = @StudentName
Select @CourseId = Id from Courses where CourseName = @CourseName

If (@StudentId is null)
Begin
Insert into Students values(@StudentName)
Select @StudentId = SCOPE_IDENTITY()
End

If (@CourseId is null)
Begin
Insert into Courses values(@CourseName)
Select @CourseId = SCOPE_IDENTITY()
End

Insert into StudentCourses values(@StudentId, @CourseId)

End

my vb code

Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Students;Integrated Security=True")
Using command As New SqlCommand("spInsertIntoStudentCourses", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@StudentName", TextBox2.Text)
command.Parameters.AddWithValue("@CourseName", TextBox5.Text)
Try
connection.Open()
If command.ExecuteNonQuery() = 1 Then
MessageBox.Show("Data Save in Database", "Read Message Carefully", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2)
End If
connection.Close()
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End Using
End Using


Now My Requirement is.

how can i by pass store procedure, i want same task from vb.net. what is the code now?

2. what is the value of @courseid =scope_identity? how can i see in vb.net

thanks





MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

Continue reading...