By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,483 Members | 1,039 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,483 IT Pros & Developers. It's quick & easy.

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

P: 2
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.

Expand|Select|Wrap|Line Numbers
  1. Imports System.Data.OleDb
  2. Imports System.IO
  3. Imports System.Data.Common
  4. Imports System.Data
  5. Imports System.Data.SqlClient
  6. Imports System.Collections.Generic
  7. Imports System.Collections
  8. Imports Microsoft.Reporting.WebForms
  9.  
  10. Partial Public Class Exam
  11.     Inherits System.Web.UI.Page
  12. ---------------
  13. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  14.         Dim StrSQL = ("SELECT CertExamID,MemberName,ExamName,ExamDate,ExamNo FROM ExamCertificate")
  15.         Dim ds = New DataSet
  16.         Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=true; Initial Catalog=ISM;Data Source=MY-ANASTASIA"
  17.         Dim connection As SqlConnection = New SqlConnection(connectionString)
  18.         connection.Open()
  19.         Dim da As New SqlDataAdapter(StrSQL, connectionString)
  20.         da.Fill(ds)
  21.  txtID.Text = ds.Tables(0).Rows(0).Item("CertExamID").ToString()
  22.         txtName.Text = ds.Tables(0).Rows(0).Item("MemberName").ToString()
  23.         txtExam.Text = ds.Tables(0).Rows(0).Item("ExamName").ToString()
  24.         txtDateExam.Text = ds.Tables(0).Rows(0).Item("ExamDate").ToString()
  25.         txtNo.Text = ds.Tables(0).Rows(0).Item("ExamNo").ToString()
  26.         'End If
  27.  
  28.         Try
  29.             Console.WriteLine(connection.State.ToString())
  30.         Finally
  31.             connection.Close()
  32.         End Try
  33.     End Sub
  34.  
  35. ----------
  36. Public Sub InsertExam()
  37.         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) & ")"
  38.         Dim ds As New DataSet
  39.         Dim intCurrentIndex As Integer = 0
  40.         Dim connectionString As String = "Data Source=MY-ANASTASIA;Initial Catalog=ISM;Integrated Security=True"
  41.         Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT CertExamID, MemberName, ExamName, ExamDate, ExamNo FROM ExamCertificate", connectionString)
  42.         Dim connection As SqlConnection = New SqlConnection(connectionString)
  43.         da.Fill(ds)
  44.         connection.Open()
  45.  
  46.         Try
  47.             Dim command As SqlCommand = _
  48.                 New SqlCommand("InsertExam", connection)
  49.             command.CommandType = CommandType.StoredProcedure
  50.  
  51.  
  52.             command.Parameters.Add("@CertExamID", SqlDbType.VarChar, 40, Me.txtID.Text)
  53.             command.Parameters.Add("@MemberName", SqlDbType.VarChar, 40, Me.txtName.Text)
  54.             command.Parameters.Add("@ExamName", SqlDbType.VarChar, 40, Me.txtExam.Text)
  55.             command.Parameters.Add("@ExamDate", SqlDbType.VarChar, 40, Me.txtDateExam.Text)
  56.             command.Parameters.Add("@ExamNo", SqlDbType.VarChar, 40, Me.txtNo.Text)
  57.  
  58.             Console.WriteLine("Rows inserted: " + _
  59.                               command.ExecuteNonQuery().ToString)
  60.  
  61.         Catch ex As Exception
  62.             Console.WriteLine(ex.Message)
  63.             Throw
  64.         Finally
  65.             connection.Close()
  66.         End Try
  67.     End Sub
  68. ---------------
  69.     Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave1.Click
  70.         Dim StrSQL = "insert into ExamCertificate(CertExamID,MemberName,ExamName,ExamDate,ExamNo) values(" & (txtID.Text.Trim) & "," & (txtName.Text.Trim) & "," & (txtExam.Text.Trim) & "," & (txtDateExam.Text) & "," & (txtNo.Text) & ")"
  71.         Dim ds As New DataSet
  72.         Dim intCurrentIndex As Integer = 0
  73.         Dim connectionString As String = "Data Source=MY-ANASTASIA;Initial Catalog=ISM;Integrated Security=True"
  74.         Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT CertExamID, MemberName, ExamName, ExamDate, ExamNo FROM ExamCertificate", connectionString)
  75.         Dim connection As SqlConnection = New SqlConnection(connectionString)
  76.         da.Fill(ds)
  77.         connection.Open()
  78.         da.SelectCommand.Connection = connection
  79.  
  80.         Try
  81.             Dim command As SqlCommand = New SqlCommand("InsertExam", connection)
  82.             command.CommandType = CommandType.StoredProcedure
  83.  
  84.             command.Parameters.Add("@CertExamID", SqlDbType.VarChar, 40, Me.txtID.Text)
  85.             command.Parameters.Add("@MemberName", SqlDbType.VarChar, 40, Me.txtName.Text)
  86.             command.Parameters.Add("@ExamName", SqlDbType.VarChar, 40, Me.txtExam.Text)
  87.             command.Parameters.Add("@ExamDate", SqlDbType.VarChar, 40, Me.txtDateExam.Text)
  88.             command.Parameters.Add("@ExamNo", SqlDbType.VarChar, 40, Me.txtNo.Text)
  89.  
  90.             Console.WriteLine("Rows inserted: " + _
  91.                               command.ExecuteNonQuery().ToString)
  92.         Catch ex As Exception
  93.             Console.WriteLine(ex.Message)
  94.             Throw
  95.         Finally
  96.             connection.Close()
  97.         End Try
  98.     End Sub
  99. -----------
  100.  Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnNext1.Click
  101.         Dim ds = New DataSet
  102.         Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=true; Initial Catalog=ISM;Data Source=MY-ANASTASIA"
  103.         Dim intCurrentIndex As Integer = 0
  104.         Dim connection As SqlConnection = New SqlConnection(connectionString)
  105.         Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT CertExamID, MemberName, ExamName, ExamDate, ExamNo FROM ExamCertificate", connectionString)
  106.  
  107.         connection.Open()
  108.  
  109.         If intCurrentIndex > ds.Tables(0).Rows.Count - 1 Then
  110.  
  111.             'Add one to the current index.
  112.             intCurrentIndex = intCurrentIndex + 1
  113.  
  114.             txtID.Text = ds.Tables(0).Rows(intCurrentIndex).Item("CertExamID").ToString()
  115.             txtName.ValidationGroup = ds.Tables(0).Rows(intCurrentIndex).Item("MemberName").ToString()
  116.             txtExam.Text = ds.Tables(0).Rows(intCurrentIndex).Item("ExamName").ToString()
  117.             txtDateExam.ValidationGroup = ds.Tables(0).Rows(intCurrentIndex).Item("ExamDate").ToString()
  118.             txtNo.Text = ds.Tables(0).Rows(intCurrentIndex).Item("ExamNo").ToString()
  119.         End If
  120.  
  121.         Try
  122.             Console.WriteLine(connection.State.ToString())
  123.         Finally
  124.             connection.Close()
  125.         End Try
  126.     End Sub
  127. -------------
  128.  Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
  129.         Dim ds = New DataSet
  130.         Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=true; Initial Catalog=ISM;Data Source=MY-ANASTASIA"
  131.         Dim intCurrentIndex As Integer = 0
  132.         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) & ")"
  133.         Dim connection As SqlConnection = New SqlConnection(connectionString)
  134.  
  135.         connection.Open()
  136.  
  137.         txtID.Text = ""
  138.         txtName.Text = ""
  139.         txtExam.Text = ""
  140.         txtDateExam.Text = ""
  141.         txtNo.Text = ""
  142.  
  143.         Try
  144.  
  145.  
  146.             Dim command As SqlCommand = _
  147.                     New SqlCommand("InsertExam", connection)
  148.             command.CommandType = CommandType.StoredProcedure
  149.  
  150.             command.Parameters.AddWithValue("@CertExamID", txtID.Text)
  151.             command.Parameters.AddWithValue("@MemberName", txtName.Text)
  152.             command.Parameters.AddWithValue("@ExamName", txtExam.Text)
  153.             command.Parameters.AddWithValue("@ExamDate", txtDateExam.Text)
  154.             command.Parameters.AddWithValue("@ExamNo", txtNo.Text)
  155.  
  156.         Catch ex As Exception
  157.  
  158.         End Try
  159.         Try
  160.             Console.WriteLine(connection.State.ToString())
  161.         Finally
  162.             connection.Close()
  163.         End Try
  164.     End Sub
  165. End Class
  166. -------
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
May 10 '10 #1
Share this Question
Share on Google+
3 Replies


ThatThatGuy
Expert 100+
P: 449
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
May 10 '10 #2

P: 2
Tq for ur advice thatGuy. when i click new data and save i'll get that error. Hope u can help me..
Expand|Select|Wrap|Line Numbers
  1.  Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave1.Click
  2.         Dim StrSQL = "insert into ExamCertificate(CertExamID,MemberName,ExamName,ExamDate,ExamNo) values(" & (txtID.Text.Trim) & "," & (txtName.Text.Trim) & "," & (txtExam.Text.Trim) & "," & (txtDateExam.Text) & "," & (txtNo.Text) & ")"
  3.         Dim ds As New DataSet
  4.         Dim intCurrentIndex As Integer = 0
  5.         Dim connectionString As String = "Data Source=MY-ANASTASIA;Initial Catalog=ISM;Integrated Security=True"
  6.         Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT CertExamID, MemberName, ExamName, ExamDate, ExamNo FROM ExamCertificate", connectionString)
  7.         Dim connection As SqlConnection = New SqlConnection(connectionString)
  8.         da.Fill(ds)
  9.         connection.Open()
  10.         da.SelectCommand.Connection = connection
  11.  
  12.         Try
  13.             Dim command As SqlCommand = New SqlCommand("InsertExam", connection)
  14.             command.CommandType = CommandType.StoredProcedure
  15.             command.Parameters.Add("@CertExamID", SqlDbType.VarChar, 40, Me.txtID.Text)
  16.             command.Parameters.Add("@MemberName", SqlDbType.VarChar, 40, Me.txtName.Text)
  17.             command.Parameters.Add("@ExamName", SqlDbType.VarChar, 40, Me.txtExam.Text)
  18.             command.Parameters.Add("@ExamDate", SqlDbType.VarChar, 40, Me.txtDateExam.Text)
  19.             command.Parameters.Add("@ExamNo", SqlDbType.VarChar, 40, Me.txtNo.Text)
  20.             Console.WriteLine("Rows inserted: " + _
  21.                               command.ExecuteNonQuery().ToString)
  22.         Catch ex As Exception
  23.             Console.WriteLine(ex.Message)
  24.             Throw
  25.         Finally – the problem shows here
  26.             connection.Close()
  27.         End Try
  28.     End Sub
-----

My stored proc
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE [dbo].[InsertExam]
  2.  
  3.          @CertExamID int,
  4.          @MemberName nvarchar(50),
  5.          @ExamName nvarchar(50),
  6.          @ExamDate datetime,
  7.          @ExamNo int
  8. AS
  9. BEGIN
  10.     SET NOCOUNT ON;
  11.     Select 
  12.         CertExamID,
  13.         MemberName,
  14.         ExamName,
  15.         ExamDate,
  16.         ExamNo 
  17.     from dbo.ExamCertificate
  18.  
  19.     INSERT INTO ExamCertificate
  20.     (    CertExamID,
  21.     MemberName,
  22.     ExamName,
  23.     ExamDate,
  24.     ExamNo     )
  25.     VALUES
  26.  
  27.     (    @CertExamID,
  28.     @MemberName,
  29.     @ExamName,
  30.     @ExamDate,
  31.     @ExamNo     )
  32. End
TQ
May 10 '10 #3

Frinavale
Expert Mod 5K+
P: 9,731
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
May 12 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.