Procedure or function 'InsertExam' expects parameter '@CertExamID..

[sumandak]

I want to insert data from User Input to DB (MySQL 2005) using stored proc and a report to show the data which has one parameter at a time to view information.

I've tried for almost 2 weeks on this by refer to other sites but still i got errors. These are my codes.

Imports System.Data.OleDb
Imports System.IO
Imports System.Data.Common
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Collections
Imports Microsoft.Reporting.WebForms
 
Partial Public Class Exam
    Inherits System.Web.UI.Page
---------------
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim StrSQL = ("SELECT CertExamID,MemberName,ExamName,ExamDate,ExamNo FROM ExamCertificate")
        Dim ds = New DataSet
        Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=true; Initial Catalog=ISM;Data Source=MY-ANASTASIA"
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        connection.Open()
        Dim da As New SqlDataAdapter(StrSQL, connectionString)
        da.Fill(ds)
 txtID.Text = ds.Tables(0).Rows(0).Item("CertExamID").ToString()
        txtName.Text = ds.Tables(0).Rows(0).Item("MemberName").ToString()
        txtExam.Text = ds.Tables(0).Rows(0).Item("ExamName").ToString()
        txtDateExam.Text = ds.Tables(0).Rows(0).Item("ExamDate").ToString()
        txtNo.Text = ds.Tables(0).Rows(0).Item("ExamNo").ToString()
        'End If
 
        Try
            Console.WriteLine(connection.State.ToString())
        Finally
            connection.Close()
        End Try
    End Sub
 
----------
Public Sub InsertExam()
        Dim StrSQL = "insert into ExamCertificate(CertExamID,MemberName,ExamName,ExamDate,ExamNo) values(" & (txtID.Text.Trim) & "," & (txtName.Text.Trim) & "," & (txtExam.Text.Trim) & "," & (txtDateExam.Text.Trim) & "," & (txtNo.Text.Trim) & ")"
        Dim ds As New DataSet
        Dim intCurrentIndex As Integer = 0
        Dim connectionString As String = "Data Source=MY-ANASTASIA;Initial Catalog=ISM;Integrated Security=True"
        Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT CertExamID, MemberName, ExamName, ExamDate, ExamNo FROM ExamCertificate", connectionString)
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        da.Fill(ds)
        connection.Open()
 
        Try
            Dim command As SqlCommand = _
                New SqlCommand("InsertExam", connection)
            command.CommandType = CommandType.StoredProcedure
 
 
            command.Parameters.Add("@CertExamID", SqlDbType.VarChar, 40, Me.txtID.Text)
            command.Parameters.Add("@MemberName", SqlDbType.VarChar, 40, Me.txtName.Text)
            command.Parameters.Add("@ExamName", SqlDbType.VarChar, 40, Me.txtExam.Text)
            command.Parameters.Add("@ExamDate", SqlDbType.VarChar, 40, Me.txtDateExam.Text)
            command.Parameters.Add("@ExamNo", SqlDbType.VarChar, 40, Me.txtNo.Text)
 
            Console.WriteLine("Rows inserted: " + _
                              command.ExecuteNonQuery().ToString)
 
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Throw
        Finally
            connection.Close()
        End Try
    End Sub
---------------
    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave1.Click
        Dim StrSQL = "insert into ExamCertificate(CertExamID,MemberName,ExamName,ExamDate,ExamNo) values(" & (txtID.Text.Trim) & "," & (txtName.Text.Trim) & "," & (txtExam.Text.Trim) & "," & (txtDateExam.Text) & "," & (txtNo.Text) & ")"
        Dim ds As New DataSet
        Dim intCurrentIndex As Integer = 0
        Dim connectionString As String = "Data Source=MY-ANASTASIA;Initial Catalog=ISM;Integrated Security=True"
        Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT CertExamID, MemberName, ExamName, ExamDate, ExamNo FROM ExamCertificate", connectionString)
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        da.Fill(ds)
        connection.Open()
        da.SelectCommand.Connection = connection
 
        Try
            Dim command As SqlCommand = New SqlCommand("InsertExam", connection)
            command.CommandType = CommandType.StoredProcedure
 
            command.Parameters.Add("@CertExamID", SqlDbType.VarChar, 40, Me.txtID.Text)
            command.Parameters.Add("@MemberName", SqlDbType.VarChar, 40, Me.txtName.Text)
            command.Parameters.Add("@ExamName", SqlDbType.VarChar, 40, Me.txtExam.Text)
            command.Parameters.Add("@ExamDate", SqlDbType.VarChar, 40, Me.txtDateExam.Text)
            command.Parameters.Add("@ExamNo", SqlDbType.VarChar, 40, Me.txtNo.Text)
 
            Console.WriteLine("Rows inserted: " + _
                              command.ExecuteNonQuery().ToString)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Throw
        Finally
            connection.Close()
        End Try
    End Sub
-----------
 Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnNext1.Click
        Dim ds = New DataSet
        Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=true; Initial Catalog=ISM;Data Source=MY-ANASTASIA"
        Dim intCurrentIndex As Integer = 0
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT CertExamID, MemberName, ExamName, ExamDate, ExamNo FROM ExamCertificate", connectionString)
 
        connection.Open()
 
        If intCurrentIndex > ds.Tables(0).Rows.Count - 1 Then
 
            'Add one to the current index.
            intCurrentIndex = intCurrentIndex + 1
 
            txtID.Text = ds.Tables(0).Rows(intCurrentIndex).Item("CertExamID").ToString()
            txtName.ValidationGroup = ds.Tables(0).Rows(intCurrentIndex).Item("MemberName").ToString()
            txtExam.Text = ds.Tables(0).Rows(intCurrentIndex).Item("ExamName").ToString()
            txtDateExam.ValidationGroup = ds.Tables(0).Rows(intCurrentIndex).Item("ExamDate").ToString()
            txtNo.Text = ds.Tables(0).Rows(intCurrentIndex).Item("ExamNo").ToString()
        End If
 
        Try
            Console.WriteLine(connection.State.ToString())
        Finally
            connection.Close()
        End Try
    End Sub
-------------
 Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
        Dim ds = New DataSet
        Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=true; Initial Catalog=ISM;Data Source=MY-ANASTASIA"
        Dim intCurrentIndex As Integer = 0
        Dim StrSQL = "insert into ExamCertificate(CertExamID,MemberName,ExamName,ExamDate,ExamNo) values(" & (txtID.Text.Trim) & "," & (txtName.Text.Trim) & "," & (txtExam.Text.Trim) & "," & (txtDateExam.Text.Trim) & "," & (txtNo.Text.Trim) & ")"
        Dim connection As SqlConnection = New SqlConnection(connectionString)
 
        connection.Open()
 
        txtID.Text = ""
        txtName.Text = ""
        txtExam.Text = ""
        txtDateExam.Text = ""
        txtNo.Text = ""
 
        Try
 
 
            Dim command As SqlCommand = _
                    New SqlCommand("InsertExam", connection)
            command.CommandType = CommandType.StoredProcedure
 
            command.Parameters.AddWithValue("@CertExamID", txtID.Text)
            command.Parameters.AddWithValue("@MemberName", txtName.Text)
            command.Parameters.AddWithValue("@ExamName", txtExam.Text)
            command.Parameters.AddWithValue("@ExamDate", txtDateExam.Text)
            command.Parameters.AddWithValue("@ExamNo", txtNo.Text)
 
        Catch ex As Exception
 
        End Try
        Try
            Console.WriteLine(connection.State.ToString())
        Finally
            connection.Close()
        End Try
    End Sub
End Class
-------

Please give advice where it goes wrong because it keep saying parameter is not supplied and if i put parameter into my sp it wilI say i did not put any parameter. I also need an image steps to develop this web app.

TQ in advance

[ThatThatGuy]

Posting such huge chunk of code won't help.... post the part of the code that's problematic

Check if you're passing the first parameter of the stored procedure youre passing is not null or blank

[sumandak]

Tq for ur advice thatGuy. when i click new data and save i'll get that error. Hope u can help me..

 Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave1.Click
        Dim StrSQL = "insert into ExamCertificate(CertExamID,MemberName,ExamName,ExamDate,ExamNo) values(" & (txtID.Text.Trim) & "," & (txtName.Text.Trim) & "," & (txtExam.Text.Trim) & "," & (txtDateExam.Text) & "," & (txtNo.Text) & ")"
        Dim ds As New DataSet
        Dim intCurrentIndex As Integer = 0
        Dim connectionString As String = "Data Source=MY-ANASTASIA;Initial Catalog=ISM;Integrated Security=True"
        Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT CertExamID, MemberName, ExamName, ExamDate, ExamNo FROM ExamCertificate", connectionString)
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        da.Fill(ds)
        connection.Open()
        da.SelectCommand.Connection = connection
 
        Try
            Dim command As SqlCommand = New SqlCommand("InsertExam", connection)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.Add("@CertExamID", SqlDbType.VarChar, 40, Me.txtID.Text)
            command.Parameters.Add("@MemberName", SqlDbType.VarChar, 40, Me.txtName.Text)
            command.Parameters.Add("@ExamName", SqlDbType.VarChar, 40, Me.txtExam.Text)
            command.Parameters.Add("@ExamDate", SqlDbType.VarChar, 40, Me.txtDateExam.Text)
            command.Parameters.Add("@ExamNo", SqlDbType.VarChar, 40, Me.txtNo.Text)
            Console.WriteLine("Rows inserted: " + _
                              command.ExecuteNonQuery().ToString)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Throw
        Finally – the problem shows here
            connection.Close()
        End Try
    End Sub

-----

My stored proc

CREATE PROCEDURE [dbo].[InsertExam]
 
         @CertExamID int,
         @MemberName nvarchar(50),
         @ExamName nvarchar(50),
         @ExamDate datetime,
         @ExamNo int
AS
BEGIN
    SET NOCOUNT ON;
    Select 
        CertExamID,
        MemberName,
        ExamName,
        ExamDate,
        ExamNo 
    from dbo.ExamCertificate
 
    INSERT INTO ExamCertificate
    (    CertExamID,
    MemberName,
    ExamName,
    ExamDate,
    ExamNo     )
    VALUES
 
    (    @CertExamID,
    @MemberName,
    @ExamName,
    @ExamDate,
    @ExamNo     )
End

TQ

[Frinavale]

Line 23 in your code you have: Console.WriteLine(ex.Message)
Why do you have Console.WriteLine in an ASP.NET application???

Anyways, you aren't supplying values you to your parameters properly.
Please review the following article that outline how to use a database:
Database tutorial Part 1
Database tutorial Part 2[/quote]

In Part 1 there are examples on how to use parameters properly.

-Frinny